Get this free workbook, then enter budget forecast amounts and actual amounts. View or print the report sheets, where formulas show the year to date totals, and calculate the variance between the forecast and actuals.
This example uses a simple budget -- enter forecast amounts for each month, then fill in the actual amounts at month end. There are no macros in the sample file -- the navigation buttons use hyperlinks.
In this template, there is a menu sheet, with navigation buttons and cells to enter the location name and the fiscal year start date. The buttons have hyperlinks that go to a specific sheet, and cell A1 on that sheet.
The green cells are named --
Note: The Location is not used in any of the formulas -- it is for information only.
On a separate worksheet, named Forecast, you'll enter the budget forecast amounts, for each month and each budget category. You can change the category headings in column A, and insert or delete the rows in the Overhead section.
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.
On another worksheet, named Actual, you'll enter the actual amounts, for each month and each budget category.
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.
The only difference from the Forecast sheet is in cell A4. On this 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.
Here is the formula used in cell A4, to calculate the current month:
On another worksheet, the Year to Date (YTD) results are calculated. 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:
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.
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 and actual amounts, if the actual amounts have been entered.
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 variance:
The buttons on the Menu sheet, and the budget sheets use hyperlinks. Follow the instructions below, to create hyperlinks for the buttons in the workbook.
To learn more about hyperlinks in Excel, go to the Hyperlinks and HYPERLINK function page.
Last updated: February 16, 2018 10:51 AM