Search Contextures Sites

 

Contextures
Excel news
by email

 

 

 

 

Learn how to create Excel dashboards.

 

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

Learn how to create Excel dashboards.

 

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

Time-saving
Pivot Table add-in

 

 

Forecast vs Actual - Variance

In this Excel template, you can enter budget forecast amounts and actual amounts. Then, view or print the report sheets, where formulas show the year to date totals, and calculate the variance between the forecast and actuals.

The sample workbook uses macros for navigating between the worksheets. If you'd rather not use macros, you can insert hyperlinks instead.

budget variance menu

Workbook Menu Sheet

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 run macros that select a specific sheet, and cell A1 on that sheet.

The green cells are named --

    • I3: Location
    • I7: StartDate

budget variance menu

Enter the 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.

budget variance forecast

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:
  2. =DATE(YEAR(C9),MONTH(C9)+1,1)

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

Check the 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 Income is calculated by subtracting the total overhead from the total revenue.

=SUM(C14:C23)

Enter the Actual Amounts

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.

budget variance actual

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:

=IFERROR(INDEX($C$9:$N$9,COUNT($C$11:$N$11)),DATE(YEAR(C9),MONTH(C9),0))

It 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.

Show Year to Date Results

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.

budget variance year to date

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.

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

budget variance conditional formatting

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

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

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

budget variance formula

Use Navigation Macros

The only macros in this workbook are used to navigate between the worksheets.

  • The buttons on the menu sheet take you to one of the data entry or report sheets.
  • The menu button on each of the data entry and report sheets takes you back to the menu sheet.

The code is very simple -- it selects a sheet, and selects cell A1 on that sheet.

The code uses the Code name for each sheet, instead of the name that shows on the worksheet's tab. For example, the code name for the Forecast sheet is wsFC.

budget variance navigation code

Use Navigation Hyperlinks

If you don't want to use macros in your workbook, you can use hyperlinks instead. Follow the instructions below, to create hyperlinks for the buttons in the workbook.

Note: For a fancier system of hyperlinks, see Zoran Stanojevic's instructions for Dynamic Hyperlinks Using Formulas, on the My Online Training Hub website, where you can download his sample workbook..

To remove the macro from a button:

  1. Right-click the button, and click Assign Macro
  2. Clear the macro name from the Assign Macro window, and click OK

To add a hyperlink to a button:

  1. Right-click the button, and click Insert Hyperlink

    budget variance navigation code

  2. Click 'Place in This Document', then click the Sheet name that you want to link to.
  3. You can leave the cell reference as A1, or type a different cell reference.
  4. budget variance navigation code

  5. (optional) To add a Screen Tip that will appear when someone points to the button, click the Screen Tip button, and enter a short name for the link, such as 'Forecast Sheet', then click OK
  6. budget variance navigation code

  7. Click OK to close the Insert Hyperlink window.
  8. Click the button to go to the linked sheet.

Download the Sample File

To download the zipped sample file, in Excel 2007/2010 format, click here: Excel Budget YTD

There are navigation macros in the file. You could create a copy of the workbook without the navigation buttons and macros.

Learn how to create Excel dashboards.

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.