Home > Templates > Templates > Budget

Excel Budget Template Forecast vs Actual Variance

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.

Introduction

This Excel template example uses a simple budget setup:

• At the start of the fiscal year, enter forecast amounts for each month
• At the end of each month, fill in the actual amounts for income and expenses.
• As the year goes by, see the variance between your budget forecast and the actual amounts

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.

1) Get Started with Budget Template

To get started in the Excel budget template, go to the sheet named Menu.

The menu sheet has data entry cells, and navigation buttons:

• There are two green cells where you will enter details about your budget (see the steps below).
• The five blue buttons have hyperlinks that take you to a specific sheet, and to cell A1 on that sheet.

Fill the Green Cells

To get started in the budget template, follow these steps, to fill in the green cells:

• Location: Enter a location name or description in cell I3.
  • This information is not used in any of the workbook calculations. The location name is shown on the worksheets, as heading information only
  • Note: cell I3 is named Location
• Start Date: In cell I7, enter the date when your fiscal year starts.
  • This date is used to create the month headings on the template worksheets.
  • Note: cell I7 is named StartDate

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.

• In column A, there are Revenue categories, and Overhead categories.
• In column C to column N, there are green cells, with forecast amounts for each category, over the 12 months of the fiscal year

To start entering your own budget forecast,

• Clear out the sample data in the green cells on the Forecast sheet
• DO not clear out the cells in the Total rows or the Total columns
  • Those cells contain formulas

Customize Budget Categories

Next, check the list of budget categories, and revise them, to match your budget categories

For example, change the Revenue categories:

• In cells A12:A14, delete the product sales categories
• In those 3 cells, type the names of your revenue categories

Next, change the Overhead categories

• In cells A19:A28, delete the items in the overhead category cells
• In those cells, type the names of your overhead (expense) categories

Add or Remove Categories

If needed, you can add more Revenue categories, or Overhead categories.

• Warning: Be sure to follow the steps below, to update the Forecast, Actual, YearToDate and Variance sheets -- all at the same time.

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.

• First, select all 4 data sheets -- Forecast, Actual, YearToDate and Variance sheets
• After the 4 sheets are selected, click on the Forecast sheet, so it is the active sheet
• All 4 sheets should still be selected (grouped)
• To add more categories, select one or more rows in the Revenue or Overhead section -- select as many rows as you want to add new rows

In the screen shot below,

• 4 sheets are selected (grouped)
• 3 rows are selected in the Overhead section
• 3 new rows will be inserted above those selected rows

Insert New Rows

Next, to insert the new rows:

• Right-click the row button, at the left of the worksheet, for any one of the selected rows
• In the right-click menu, click Insert

Ungroup Sheets

Then, before entering the new categories, ungroup the sheets:

• Right-click any one of the sheet tabs for the grouped sheets
• Click the Ungroup Sheets command

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:

• Fill down the formula in column A, to show the categories in the new rows
• In column O, fill down the Total formula for the new rows.

Next, go to the YearToDate sheet:

• Fill down the formula in column A, to show the categories in the new rows
• In columns D:O, fill down the formula for the new rows.

Finally, go to the Variance sheet:

• Fill down the formula in column A, to show the categories in the new rows
• In columns D:O, fill down the formula for the new rows.

Enter Forecast Amounts

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 the Revenue section, enter your monthly forecast income
• In the Overhead section, enter your forecast amounts for monthly fees and other transactions.

In cell C9, there is a link to the start date on the Menu sheet.

1. In cell D9, the following formula calculates the date for the next month:

=DATE(YEAR(C9),MONTH(C9)+1,1)

2. That formula is copied across to cell N9, to create a list of 12 months.

Check Forecast Totals

There are total formulas in column O and row 25, using the SUM function. For example, the formula in cell C25 is:

• =SUM(C14:C23)

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.

• =SUM(C14:C23)

Enter Actual Amounts

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.

• Go to the worksheet named Actual
• In the orange cells, enter the actual values, for the month that just ended, for 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.

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))

This formula uses the INDEX function and the COUNT function to find the current date.

If the INDEX function returns an error, the formula uses the date from cell C9 instead.

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:

• Year To Date
• Variance
• Summary

Show Year to Date Results

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:

=IF(C$9>$A$4,Forecast!C11,Actual!C11)

• If the column heading date in C9 is after the Current date in A4, the Forecast amount is shown.
• If the column heading date in C9 is on or before the Current date in A4, the Actual amount is shown.

Calculate the Variance

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:

=IF(Actual!C11="",0,Actual!C11/Forecast!C11-1)

Summary Line Chart

On the Summary sheet, there is a small table with data linked from other sheets.

• Heading row in the table show the month dates
• First data row (FC) is linked to the Net Income row on the Forecast sheet
• Second data row (YTD) is linked to the Net Income row on the YearToDate sheet
• Bottom row (Var) is a formula that subtracts forecast amount from YTD amount. Here is the formula in cell C6: =C5-C4

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.

• There are variances in the amounts for the first 5 months, which you can see in row 6 (Var), and in the line chart
• Forecast amounts are used in the YTD row, where actual amounts have not been entered yet.
  • As a result, there is no variance for the months from June to December.

Colour Code the Columns

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:

=C$9<=$A$4

For columns with a date heading that is after the current date, the cells remain green.

Add Navigation Hyperlinks

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:

• On the Excel Ribbon, go to the Insert tab
• In the Illustrations group, click on Shapes
• In the Rectangles group, click on the Rectangle with Rounded Corners
• Click on the worksheet, to add the button shape
• Drag the shape handles, to change the size of the button, if needed
• With the shape still selected, type the text that you want on the button, e.g. Forecast
• On the Shape Format tab, use the Shape Styles options, to change the shape's appearance
• On the Ribbon's Home tab, use the Font and Format commands to change the text appearance

B) Add Hyperlink to Worksheet button

After you add a button, you can assign a Hyperlink to that button, so it works for navigation.

• Right-click the button, and click Insert Hyperlink

• Click 'Place in This Document', then click the Sheet name that you want to link to.
• You can leave the cell reference as A1, or type a different cell reference.

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:

• If you DO NOT add a custom screen tip, the hyperlink destination appears in the pop-up tip.
  • The destination could be a long file path, and the file name, plus sheet name and cell address
  • That can look confusing and people might not want to click the hyperlink

• If you DO add a custom screen tip, your custom text appears in the pop-up
  • By creating a short, informative custom screen tip, it's easier to see where the link goes

Follow these steps, if you'd like to add a custom Screen tip:.

• Right-click the button where you've added a hyperlink
• In the right-click menu, click Edit Link
• In the Edit Hyperlink dialog box, click the Screen Tip button, at the top right corner
• In the Set Hyperlink ScreenTip dialog box, click in the ScreenTip text box
• Enter a short description for the hyperlink, such as 'Go to Forecast Sheet'
• Click OK, to complete the change, and to close the dialog box

• Click OK to close the Edit Hyperlink window.

Then, you can test the button and its hyperlink

• Click the button to activate its hyperlink
• Check that the hyperlink goes to the correct sheet and cell
• If it doesn't edit the button hyperlink, to fix the problem

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:

• Enter all the amounts in a table, and a pivot table shows the summary.
• Click a report type in the Slicer, and see those values in the pivot table.
• The Report column in the data entry table calculates which value to show, and macros refresh the pivot table.

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.

Download the Sample File

1. To download the Budget Varience workbook for this tutorial, click here: Excel Budget YTD. The zipped file is in xlsx format, and DOES NOT have macros.
2. To see a different type of Budget Variance workbook, download the Budget Report Selector -- Enter Forecast and Actual data for a budget, see the summary in a pivot table. Click the Slicer to change values in the Pivot Table report - choose the forecast, actual, YTD, Variance or Variance Percent. This version DOES have macros.

More Tutorials

Cost Calculator, Annual

Limit Budget Entry Amounts

INDEX / MATCH

SUM / SUMIF

VLOOKUP