Last Updated on May 11, 2025
This tutorial will show how to create a Monthly Budget and Forecast in Microsoft Excel. It will first cover estimating revenues. Then, it will move on to cost calculations, including direct expenses such as marketing or operating expenditures such as salaries or office expenses.
Finally, I will discuss estimating the amount of cash you will need to fund the plan and a way to test the model for any unexpected events.
To follow along, you can download my Monthly Budget and Forecast Template. For a full walkthrough, watch the video tutorial at the end of this post.
Table of Contents
Step 1. List and Categorize Monthly Operating Expenses
Start your budget by listing the actual costs. These can be expenses from a previous month or an average taken over the last few months.
Analyzing your costs first allows you to understand the level of your outgoings and how much income you need to bring in to cover those expenses and break even.
For example, list historical expenses from the last three months preceding the beginning of the budget year. Then, extrapolate these costs using an inflation index to account for future increases.
If you haven’t already done so, itemize your expenses in a separate spreadsheet and use a column to categorize them into broader groups such as office, maintenance, licenses, and other operational areas. In addition, include a contingency line to cover unexpected costs.
Step 2. Forecast Monthly Headcount and Staff Costs
Create a separate calculation for expenses related to salaries and staff costs. Add flexibility to your spreadsheet to easily adjust the number of employees throughout the year as your business needs change.
For example, list employees by their function, then link headcount numbers to monthly salary assumptions to calculate staffing costs. Also, you can use a salary cost index to adjust the budget for potential increases over time.
Remember to calculate fully loaded staff costs, including salaries plus business overheads like taxes, pensions, and bonuses.
Need help modeling headcount and salaries? Download my budget template, which includes a built-in staff cost calculator with monthly forecasting.
Step 3. Add a Revenue Forecast to Your Budget Spreadsheet
After listing your fixed and staffing expenses, the next step is to build out your revenue projections. List the main monthly drivers, such as expected sales numbers or customer growth, and link them to revenue estimates.
Clearly showing the relationship between sales and revenues will help you identify risks and opportunities for future growth.
For example, you can use the historical sales average as a starting point to forecast future sales numbers. Then, apply average revenue per sale assumptions and spread them across the year. Multiplying the two numbers allows you to budget the projected income.
Highlight the input cells to adjust sales estimates easily. This flexibility lets you quickly run different growth scenarios and see their impact on your budget.
📈 Take your forecasts further with my Marketing Investment Plan tutorial showing how to link customer growth to long-term revenue streams.
Step 4. Estimate Cost of Sales and Direct Expenses
Use your sales and revenue forecasts to estimate the cost of sales and other direct expenses. Generally, these are variable costs linked directly to business performance.
Splitting costs into different types will help you analyze gross margin drivers better and understand their impact on cash flow.
Here are two examples of direct cost types:
- Fixed Cost per Sale: a cost related to producing or servicing the unit or customer, calculated per sale (e.g., shipping fees),
- % Share of Revenue: a cost based on the amount of income (e.g., payment processing fees).
Adjust the sales number in your spreadsheet and observe how it affects your direct costs. This flexibility is crucial for stress testing different sales scenarios.
Step 5. Include Accruals, Deferrals, and Adjustments
Add other essential budget items such as working capital needs, taxes, one-off expenses, interest payments, and bank fees.
For example, include the accrual of taxes and bonus payments, assuming these outflows occur at the end of the year.
Keeping track of liabilities on your balance sheet — such as tax payables and bonuses — helps you avoid surprise payments and maintain a healthy cash flow.
Step 6. Calculate Monthly Cash Balance and Cash Flow
Once you have projected monthly expenses and revenues, you can complete the full cash flow forecast. Start by including the starting cash balance and then analyze the cash flow to understand any potential deficit or surplus.
For example, track metrics such as the average monthly cash burn, which shows how quickly you lose or gain cash. Calculate the monthly cash balance to highlight any additional funding needs.
A complete view of the monthly cash position helps you plan when to raise funds or adjust expenses in advance to avoid cash shortages.
🔄 Strengthen your monthly projections with my Cash Flow Forecasting tutorial showing how to calculate inflows, outflows, and net cash balance using a fully linked Excel model.
Step 7. List Key Assumptions and Stress Test Your Forecast
Once your monthly budget is complete, summarize it into an annual view and extrapolate the results to build a long-term financial forecast.
For example, my Monthly Budget and Forecast Template applies long-term growth assumptions to project revenue increases over time. It also allows you to apply inflation adjustments to future costs and expenses.
The spreadsheet takes the monthly budget output and increases future sales and pricing based on customizable assumptions. You can also add new staff and operating expenses to adjust the projections for any planned business expansion.
Creating a long-term forecast helps you visualize your business trajectory and set clear financial expectations for future periods.
Step 8. List the Main Assumptions and Stress Test the Budget
List the main assumptions to make your spreadsheet more functional. Having the inputs as a separate table allows you to test the model and identify potential weaknesses early.
Stress testing the model makes it much easier to plan for unexpected events and quickly model different scenarios. For example, change sales forecast assumptions to immediately see the impact on the cash flow and funding requirements.
Finally, list the assumptions that could break the model and think about strategies to mitigate adverse outcomes before they arise.
📌 Recap: 8 Steps to Build a Monthly Budget and Forecast
Here’s a quick recap of the steps we covered to build a complete Monthly Budget and Forecast:
- List Operating Expenses. Review historical costs and categorize them into key business areas to establish a monthly expense baseline.
- Budget Headcount and Staff Costs. Link employee headcount to salary assumptions and calculate fully loaded labor costs, including benefits, taxes, and bonuses.
- Add Revenue Forecasts to the Budget. Estimate monthly sales volumes and average revenue per unit to project total income and identify growth opportunities.
- Forecast Cost of Sales and Direct Expenses. Model variable costs based on unit sales and revenue share assumptions to reflect performance-linked expenses.
- Include Accruals, Deferrals, and Adjustments. Add items like taxes, one-time expenses, and bonus accruals to account for non-operating and future obligations.
- Show Monthly Cash Movements and Balances. Track cash inflows and outflows each month to monitor the business’s cash position and identify potential shortfalls.
- Convert Monthly Budget into a Long-Term Forecast. Apply revenue growth and inflation assumptions to build a multi-year forecast aligned with your business plan.
- List Assumptions and Stress Test the Budget. Keep key inputs in a centralized table and use scenario analysis to evaluate the model’s flexibility and risk exposure.
By following these steps, you’ll build a reliable Monthly Budget and Forecast that supports cash flow management, strategic planning, and informed decision-making.
📥 Download My Monthly Budget and Forecast Template
Use this Excel template to build a monthly cash flow forecast and long-term financial plan. It’s designed to help you track short-term performance while projecting five-year outcomes using customizable assumptions.
The spreadsheet file includes:
- Monthly Budget — detailed projections of sales, revenues, costs, cash movements, and accruals like taxes and bonuses.
- Long-Term Forecast — five-year projection using customizable growth and inflation assumptions.
- Graphs — visual summaries of monthly revenues, costs, and cash balance.
✔️30-day Money Back Guarantee included
Video Tutorial on Building a Monthly Budget and Forecast
You can follow along step-by-step with my complete video tutorial, where I explain exactly how to create and customize a Monthly Budget and Forecast using Microsoft Excel.
In this video, I cover:
- How to list and group operating expenses
- How to forecast headcount, staffing costs, and revenues
- How to model cash movements and project funding needs
- How to build a long-term financial forecast from a monthly budget
By the end of the tutorial, you’ll know exactly how to use my Monthly Budget and Forecast Template to build dynamic financial models and analyze cash flow scenarios.
Get in Touch
Hi, my name is Jacek, and I love spreadsheets! I hope you’ve enjoyed reading this tutorial as much as I did writing it. Feel free to reach out if you have any questions about creating a Monthly Budget and Forecast or financial analysis in general.
You can also explore my other tutorials to build your skills in financial modelling and data analysis. If you’d like personalized help, check out my One-to-One Training and Financial Modelling Services.
Please note: This tutorial is for general informational and educational purposes only and is not a substitute for professional advice.
Explore More Financial Modeling Tutorials
Want to dive deeper into financial modelling? Here are more tutorials you might find helpful:
- How to Build a Cash Flow Forecast in Microsoft Excel — Learn how to create a full customer-driven forecast, including discounted cash flow (DCF) valuation.
- Financial Model with Debt Financing — Explore how to add debt financing to a project and calculate the impact on funding and returns.
- Subscription Model with Churn Calculation — Estimate customer growth, churn, lifetime value, and revenue forecasts for subscription businesses.
- How to Analyse Data in Microsoft Excel with Power Query and Pivot Tables — Automate historical data imports and prepare tables for budget variance analysis.
- Your First Steps in Microsoft Excel – Beginner’s Crash Tutorial — Refresh essential spreadsheet formulas and functionalities to build stronger financial models.
