This tutorial will show how to create a Monthly Budget and Forecast in Microsoft Excel.
It will first cover estimating revenues. Then, the tutorial will move on to cost calculations, including direct expenses such as marketing or operating expenditures such as salaries, office, etc.
Finally, it will conclude by estimating the amount of cash you will need to have to fund the plan and a way to test the model for any unexpected events.
Download my Monthly Budget and Forecast Template to follow the examples used.
You can also watch the video version of the tutorial at the end of this post.
Table of Contents
Step 1. List Monthly Operating Expenses
Start your budget by listing the actual costs. Those can be expenses from a previous month or an average taken over the last few months.
Analysing 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.
My Monthly Budget and Forecast Template lists historical expenses from the last three months preceding the start of the budget year. Then, it extrapolates the cost to the further months. It also uses an inflation index to account for future increases.
Step 1. An example of a monthly operating costs budget. Notice that the budget first groups cost by category so you can see the expenses related to a business area, such as office, maintenance, licences, and other additional costs. It also adds a contingency in case of unplanned costs. You can download the entire Excel template here.
If you still need to do it, itemise your expenses on a separate spreadsheet and use one of the columns to categorise them into broader categories.
Learn More: Visit my tutorial on How to Build a Cash Flow Forecast in Microsoft Excel for more examples of general cost projections.
Step 2. Budget Monthly Headcount and Staff Cost
It is also good to separate and budget expenses related to salaries and staff costs. Add flexibility to your spreadsheet so it’s easy to increase or decrease the number of employees throughout the year.
For example, my Monthly Budget and Forecast Template lists employees by their function. Then, it links the headcount numbers to the monthly salary assumption to calculate the costs. It also uses a salary cost index to adjust the forecast for potential increases.
Step 2. An example of a monthly headcount and staff costs budget. Notice that the calculation starts with inputs related to the headcount numbers for each function. It then links the numbers to the average monthly salary assumption. You can download the entire spreadsheet here.
Remember to calculate salaries and other business overheads such as taxes, pensions and bonuses to budget the fully loaded staff cost.
Learn More: Visit my tutorial on How to Create a Merger and Acquisition Model to discover more ways of projecting employees’ numbers and costs.
Step 3. Adding Revenue Forecast to a Monthly Budget
The fixed and salaries expenses are the starting point of our budget. Its other side will be revenue projections. List the main monthly drivers (e.g., sales or customer number forecast) and link them to revenue estimates.
Clearly showing the link between sales and revenues should help you to identify any risks and opportunities for future growth.
For example, my Monthly Budget and Forecast Template takes the historical sales average as a starting point to forecast future sales numbers. Also, it includes average revenue per sale assumptions spread across the year. Multiplying the two numbers allows you to budget the income.
Step 3. An example of adding revenue forecast to a monthly budget. Notice that the template allows you to set the average revenue monthly so you can adjust it up or down monthly. You can download the entire Excel file here.
Finally, remember to highlight the input cells to easily adjust sales estimates, allowing you to run different growth scenarios.
Learn More: Visit my tutorial on How to Create a Subscription Model with Churn Calculation to discover more ways of forecasting revenues.
Step 4. Forecast Cost of Sales and Other Direct Expenses
Use sales and the revenue forecast to budget the costs of sales and direct expenses. Generally, they are variable costs linked directly to the business performance.
Splitting the cost will help you to analyse the items impacting your gross margin and their overall impact on the cash flow.
For example, my Monthly Budget and Forecast Template uses three types of direct expenses/costs of sales:
- Fixed Cost per Sale – an expense related to producing or servicing the unit or customer calculated per sale, e.g. shipping fee,
- % Share of Revenue – cost based on the amount of income, e.g. payment handle fee.
Step 4. An example of direct expenses/cost of sales in a monthly budget. Notice that the cost of sales links directly to the number of units sold, and it’s a fixed amount per sale. The processing fee/revenue share, on the other hand, is calculated as a percentage of the received income. You can download the full spreadsheet here.
Finally, try adjusting the sales number to see its impact on the direct costs in your budget.
Learn More: Visit my tutorial on How to Create a Telecom Financial Model for more examples of linking a cost forecast to sales.
Step 5. Include Accruals, Deferrals and Other Budget Adjustments
Remember to add other costs, such as working capital or one-off expenses. Also, include taxes and additional fees such as interest or bank fees.
For example, my Monthly Budget and Forecast Template includes the accrual of taxes and bonus payments, assuming their outflow occurs at the end of the year.
Step 5. An example of a monthly cash movement with a calculation of receivables and payables, such as tax and bonus accruals. You can download the Excel template here.
Keeping track of a balance sheet of liabilities helps you avoid surprise payments. You can read more about accruals here [external link].
Learn More: Visit my tutorial on How to Create a Capital Investment Plan for more examples of how to forecast movement in payables and receivables.
Step 6. Show Monthly Cash Balance and Movement
With a monthly budget including expenses and income projections, you can complete the full monthly cash flow forecast. Include the starting cash balance and analyse the cash flow to understand any deficit or cash surplus.
For example, my Monthly Budget and Forecast Template lists the metrics, such as average monthly cash burn, to see how quickly you lose or gain money. Then, it calculates the monthly cash balance to see if you need additional money injections.
Step 6. A graph showing monthly budget and cash movement. Notice that the template links the projected revenues and costs to calculate the cash balance and highlight any cash shortages. You can download the Excel file here.
Having a complete cash flow view in your budget helps you plan to raise funds in the future or adjust your expenses to avoid a cash deficit.
Learn More: Visit my tutorial on How to Create a Financial Model with Debt Financing for another example of estimating cash movement and its impact on a funding requirement.
Step 7. Converting a Monthly Budget into a Long-Term Forecast
Summarise your monthly budget into an annual view and extrapolate the output into the future to create a long-term plan.
For example, my Monthly Budget and Forecast Template adds long-term growth assumptions to make simple growth predictions for revenue growth. Also, it allows you to increase costs by adding inflation and expenses.
Step 7. An example of converting a monthly budget into a long-term forecast. Notice that the spreadsheet takes the budget output and increases the future sales and pricing in line with the assumptions. You can add staff and other expenses to adjust the operating cost forecast to any anticipated expansion. You can download the entire spreadsheet here.
Creating a long-term forecast helps you see the business trajectory and set expectations for the future.
Learn More: Visit my tutorial on How to Create a Real Estate Investment Model in Excel for more examples of creating a long-term forecast.
Step 8. List the Main Assumptions and Test the Budget
List the main assumptions and changes to make your spreadsheet more functional. You can use them to stress-test the model and find any weaknesses in your budget.
For example, my Monthly Budget and Forecast Template lets you easily change sales forecast assumptions and check their effect on the overall cash requirement. Consequently, it makes it easier to plan for unexpected events and test scenarios under which your business may need more money.
Step 8. An example of a budget and financial forecast assumptions table. Try changing the inputs to see their impact on revenue, costs and overall cash flow. You can download the Excel file here.
Finally, list the assumptions that break the model and think of ways to mitigate the negative outcome.
Learn More: Visit my tutorial on How to Create a Project Finance Model to discover more ways of using assumptions tables to stress-test a financial forecast.
Summary: How to Create a Monthly Budget and Forecast
This tutorial has shown how to create a Monthly Budget and Long-Term Forecast in Microsoft Excel.
It started with calculating general and staff expenses. Then, it showed how to forecast sales and revenues. Finally, it concluded with budget adjustments, movements in cash and a long-term forecast.
The tutorial focused on the following steps:
- List operating expenses. Show the recurring costs associated with running the business.
- Budget staff costs. Link the headcount projections to the overall employees’ costs.
- Add revenue forecast to your budget. Show how a change in monthly sales impacts the overall cash flow.
- Forecast cost of sales and other direct costs. Those would be the costs directly related to generating business revenues.
- Add other adjustments costs such as taxes, bonus accruals, receivables, etc. This will allow you to budget for any unexpected one-off payments.
- Show cash movements and balance. Look out for any cash deficit causing an additional funding requirement.
- Convert a monthly budget into a long-term forecast. See how the current business trajectory affects future results.
- Add an assumptions table and test the budget. Vary the inputs to test their impact on the costs and revenues.
Download Monthly Budget and Forecast Template
Click the button below to download my Monthly Budget and Forecast Template.
The Excel spreadsheet calculates monthly operating expenses, sales and revenues. It also includes adjustments for accruals such as taxes or bonuses. Finally, it converts the monthly budget into a long-term forecast.
The template consists of the following spreadsheets and sections:
- Monthly Budget: a detailed forecast of sales, revenue and costs, including calculation of cash balance, tax and bonus accruals.
- Long-Term Forecast: a five-year projection based on the monthly budget and a table of assumptions.
- Graphs: an example of showing the monthly revenues and cost visually.
The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].
*Order risk-free with a 30-day Money Back Guarantee
*Order risk-free with a 30-day Money Back Guarantee
Video: How to Use the Monthly Budget and Forecast Template
For more details and a step-by-step explanation of using and creating the Monthly Budget and Forecast Template in Microsoft Excel, watch my video tutorial below:
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. If you have any questions about creating a Monthly Budget in particular or financial analysis in general, don’t hesitate to get in touch.
Explore my other tutorials to learn more about financial modelling or data analysis. If you need further support, find out about my One-to-One Training and Financial Modelling Services.
Please note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.
Learn More
Cash Flow Forecast in Excel – a tutorial on creating an extended long-term forecast.
Subscription Model with Churn Calculation – monthly projections of customer revenues, costs and lifetime value.
Telecom Financial Model – an infrastructure forecast with different sources and streams of revenues and costs.
Capital Investment Plan – an example of how to model an initial equity injection and its impact on return on investment.
Merger and Acquisition – a tutorial focusing on combining two companies and its impact on the overall cash flows.
Real Estate Investment Model – an example of revenue and cost projections from sales and rental of properties.
Financial Model with Debt Financing – this post focuses on showing how to introduce leverage into cash flow projections.
Marketing Investment Plan – an example of analysis of performance data and converting the results into a long-term cash flow.
Project Finance Model – learn how to use a mix of equity and debt to finance and consolidate multiple projects.
Your First Steps in Excel – Beginner’s Crash Tutorial – the essential tips and formulas on using spreadsheets.
