Excel Budget Template Forecast vs Actual Totals Variance

Contextures

Excel Budget Template Forecast vs Actual Variance

Get this Microsoft Excel budget template workbook, then enter annually budget forecast amounts and actual amounts. view or print the budget composition sheets. Formulas show class to date totals, and calculate discrepancy between bode and actual budget amounts .

Introduction

This Excel template model uses a dim-witted 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 case, this summary composition shows the total internet income amounts – on the worksheet, and in a line chart .
budget variance menu

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 introduction 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 .
budget variance menu

2) Prepare Budget Forecast

After you fill in the green cells on the Menu plane, click the Forecast clitoris, to go to the sheet named Forecast .
On the Forecast sail, 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 calculate ,

  • 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

budget variance menu

Customize Budget Categories

following, check the number of budget categories, and revise them, to match your budget categories
For example, change the tax income categories :

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

future, 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 tax income categories, or viewgraph 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 early 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

future, to insert the newly 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

budget variance menu

Ungroup Sheets

then, before entering the raw 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 class names in column A, for the new rows

Copy Link Formulas on Other Sheets

following, go to the actual plane :

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

future, 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.

last, 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 bode 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.

budget variance forecast
In cellular telephone C9, there is a yoke to the beginning 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 Forecast Totals

There are entire formulas in column O and row 25, using the SUM affair. For exemplar, the rule in cellular telephone C25 is :

  • =SUM(C14:C23)

If you add or erase rows in the Overhead section, make certain that all the rows are included in the SUM recipe in the total Overhead course .
In row 27, the Net monthly Income is calculated by subtracting the sum command processing overhead time from the entire tax income .

  • =SUM(C14:C23)

Enter Actual Amounts

After the budget forecasts are entered, your work is done for a while !
After the conclusion of the first base fiscal month, you ‘ll enter the actual amounts for that calendar month ‘s gross 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 precisely the same as the Forecast sheet, with formulas to calculate the august full for each category, and for each month .
budget variance actual

Actual Sheet Formula

The only remainder from the Forecast sail is in cell A4 .
On the Actual plane, a recipe checks row 11, to see how many months have tax income entries. That consider is used to calculate the stream calendar month .
In this screen shot above, there are two entries in row 11, so the stream month is calculated as February 2013 — the second calendar 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 recipe uses the date from cell C9 alternatively. go to top

Check the Variance

In the budget template, there are 3 sheets that help you check the variation between the budget forecast and the actual amounts each calendar month, and for the annual totals .
The details for these 3 division 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 class to Date ( YTD ) results.

The structure is the like as the Actual and Forecast sheets, with budget categories and calendar month headings .
budget variance year to date
A formula compares the date head to the stream date in cell A4. here is the convention 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 postpone with the same categories and date headings .
In this table, a recipe calculates the discrepancy between the bode budget volt actual amounts, if the actual amounts have been entered .
In some cells there might be positive variance, and other cells could have negative division, or zero variability .
In the total column, the overall discrepancy is calculated .
here is the formula in cell C11, to check for an actual come, and calculate the actual variances :
=IF(Actual!C11=””,0,Actual!C11/Forecast!C11-1)
budget variance formula

Summary Line Chart

On the Summary sheet, there is a minor 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 humble table, there is a line chart based on the FC amounts and the YTD amounts .
In the screen shoot 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.

budget variance formula

Colour Code the Columns

To make it obvious which columns have actual data, and which have forecast data, conditional format is used to colour code the column .
first, all the cells in the postpone are coloured green. then conditional format is applied to the cells .
The conditional format formula is exchangeable to the worksheet rule in cell C11. It compares the date head 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 format formula, to colour the Actual amounts, based on the dates :
=C$9<=$A$4
budget variance conditional formatting
For column with a date head that is after the current date, the cells remain green .

Add Navigation Hyperlinks

There are no macro in this budget spreadsheet template. rather, hyperlinks are used for navigation between unlike 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
— vitamin c ) Change Hyperlink for Worksheet Button

A) Add Worksheet Button

To add a worksheet release, 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

budget variance conditional formatting

B) Add Hyperlink to Worksheet button

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

  • Right-click the button, and click Insert Hyperlink

budget variance navigation code

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

budget variance navigation code

Add Screen Tip to Hyperlink (Optional)

You can add a custom-made screen tiptoe feature to a worksheet button, and this step is optional .
When you point to a hyperlinked cellular telephone or form, a pop-up book gratuity 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

hyperlink with no screen tip

  • 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

hyperlink with custom screen tip
Follow these steps, if you ‘d like to add a custom Screen gratuity : .

  • 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

budget variance navigation code

  • 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

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

reference : https://www.peterswar.net
Category : Finance

Related Posts

How to Calculate Credit Card Interest Rates

interest rates are one of the ways to work out how much it will cost you to use your credit card, along with other charges and fees….

What debt collectors can & cannot do

If you are dealing with a debt collector, you have protections under the law. A debt collector must not mislead, harass, coerce or act unconscionably towards you….

Can You Afford a New Home? How to Determine Your Homebuying Budget

Can You Afford a New Home? How to Determine Your Homebuying Budget As with any major purchase, determining what you can afford before you look for a…

Why Did My Credit Score Drop?

Why Did My Credit Score Go Down When Nothing Changed? sometimes your mark does change based on factors outside of your control, but most times your behavior…

Why Do I Owe Taxes To The IRS & How To Avoid Them

Are you wondering why you owe indeed much in taxes this year ? Want to make certain you never owe a big tax bill – or any…

The 5 reasons why your credit score might suddenly drop

Select ’ s editorial team works independently to review fiscal products and write articles we think our readers will find useful. We earn a perpetration from affiliate…