Get this Microsoft Excel budget template workbook, then enter yearly budget forecast amounts and actual amounts. View or print the budget report sheets. Formulas show year to date totals, and calculate variance between forecast and actual budget amounts.
This Excel template example uses a simple budget setup:
For example, this summary report shows the total net income amounts - on the worksheet, and in a line chart.
Note: There is also a Pivot Table Budget Reporter example, at the bottom of the page, which uses a different approach for data entry and reporting.
To get started in the Excel budget template, go to the sheet named Menu.
The menu sheet has data entry cells, and navigation buttons:
Fill the Green Cells
To get started in the budget template, follow these steps, to fill in the green cells:
Tip: For instructions on naming cells, go to the Excel Named Ranges page.
2) Prepare Budget Forecast
After you fill in the green cells on the Menu sheet, click the Forecast button, to go to the sheet named Forecast.
On the Forecast sheet, there is sample data entered, to show how the template works.
To start entering your own budget forecast,
Customize Budget Categories
Next, check the list of budget categories, and revise them, to match your budget categories
For example, change the Revenue categories:
Next, change the Overhead categories
Add or Remove Categories
If needed, you can add more Revenue categories, or Overhead categories.
The categories are entered on the Forecast sheet only -- the other 3 sheets are linked to the cells on the Forecast sheet.
Group Data Entry Sheets
Follow these steps, if you want to add or remove categories from the budget template sheets.
In the screen shot below,
Insert New Rows
Next, to insert the new rows:
Then, before entering the new categories, ungroup the sheets:
Add New Categories
Next, on the Forecast sheet, enter the new category names in column A, for the new rows
Copy Link Formulas on Other Sheets
Next, go to the Actual sheet:
Next, go to the YearToDate sheet:
Finally, go to the Variance sheet:
On a separate worksheet, named Forecast, you'll enter the budget forecast amounts, for each month and each budget category.
In cell C9, there is a link to the start date on the Menu sheet.
There are total formulas in column O and row 25, using the SUM function. For example, the formula in cell C25 is:
If you add or delete rows in the Overhead section, make sure that all the rows are included in the SUM formulas in the Total Overhead row.
In row 27, the Net monthly Income is calculated by subtracting the total overhead from the total revenue.
After the budget forecasts are entered, your work is done for a while!
After the end of the first fiscal month, you'll enter the actual amounts for that month's revenue and overhead.
This data entry section of the sheet is set up exactly the same as the Forecast sheet, with formulas to calculate the grand total for each category, and for each month.
Actual Sheet Formula
The only difference from the Forecast sheet is in cell A4.
On the Actual sheet, a formula checks row 11, to see how many months have revenue entries. That count is used to calculate the current month.
In this screen shot above, there are two entries in row 11, so the current month is calculated as February 2013 -- the second month in the fiscal year.
The below formula is used in cell A4, to calculate the current month:
=IFERROR(INDEX($C$9:$N$9, COUNT($C$11:$N$11)), DATE(YEAR(C9),MONTH(C9),0))
Check the Variance
In the budget template, there are 3 sheets that help you check the variance between the budget forecast and the actual amounts each month, and for the annual totals.
The details for these 3 variance analysis sheets are in the sections below:
On the YearToDate sheet, there are formulas that calculate the Year to Date (YTD) results.
The structure is the same as the Actual and Forecast sheets, with budget categories and month headings.
A formula compares the date heading to the current date in cell A4. Here is the formula from cell C11:
On another sheet, named Variance, there is table with the same categories and date headings.
In this table, a formula calculates the variance between the forecast budget vs actual amounts, if the actual amounts have been entered.
In some cells there might be positive variance, and other cells could have negative variance, or zero variance.
In the Total column, the overall variance is calculated.
Here is the formula in cell C11, to check for an Actual amount, and calculate the actual variances:
Summary Line Chart
On the Summary sheet, there is a small table with data linked from other sheets.
Below that small table, there is a line chart based on the FC amounts and the YTD amounts.
In the screen shot below, actual amounts have been entered up to May.
To make it obvious which columns have actual data, and which have forecast data, conditional formatting is used to colour code the columns.
First, all the cells in the table are coloured green. Then conditional formatting is applied to the cells.
The conditional formatting formula is similar to the worksheet formula in cell C11. It compares the date heading to the current date in cell A4, and colours the cells orange for dates that are on or before the current date.
Here is the conditional formatting formula, to colour the Actual amounts, based on the dates:
For columns with a date heading that is after the current date, the cells remain green.
There are no macros in this budget spreadsheet template. Instead, hyperlinks are used for navigation between different worksheets in the budget template.
The same way that you can add hyperlinks in a worksheer's cells, you can add hyperlinks to worksheet shapes, like the buttons on the Menu sheet, and the budget sheets.
To learn more about hyperlinks in Excel, go to the Hyperlinks and HYPERLINK function page.
In the sections below, see how to:
-- a) Add Worksheet Button
-- b) Add Hyperlink to Worksheet Button
-- c) Change Hyperlink for Worksheet Button
A) Add Worksheet Button
To add a worksheet button, follow these steps:
B) Add Hyperlink to Worksheet button
After you add a button, you can assign a Hyperlink to that button, so it works for navigation.
Add Screen Tip to Hyperlink (Optional)
You can add a custom screen tip feature to a worksheet button, and this step is optional.
When you point to a hyperlinked cell or shape, a pop-up tip appears:
Follow these steps, if you'd like to add a custom Screen tip:.
Then, you can test the button and its hyperlink
Pivot Table Budget Reporter
In the Download section below, there is a sample file (#2) that shows a different approach for entering and reporting the budget amounts, compared to the method explained above.
Instead of having one entry line per budget item, with amounts entered in monthly columns, this approach has a separate line for each budget item per month.
Here's an overview of the steps:
There's another Slicer too -- use it to show or hide the zeros on the pivot table sheet.
This animated screen shot shows what happens when you click a report type button on the pivot table slicer.
Last updated: March 27, 2023 3:41 PM