Budget

How to use the prebuilt Budget sheet to do variance comparisons between the forecast and actual results.

The Budget sheet is an easy way to do analyses of your forecast compared to your actual results. Prebuilt into the Starter, Standard, and all Standard model variants, the Budget sheet was introduced to prebuild an analysis that had always been a custom addition; if you are using the model to include your historical financials, then the budget variances will automatically appear as you start to link in your actual results into the Hooks sheet.

More about how to pull in your actual financials from Quickbooks, Xero, or any accounting software and create rolling forecasts at Actual Financials →

How to use

The key in using this sheet is mapping the revenues and expenses in the forecast section to a set of categories that are mapped to a similar set of revenues and expenses from the chart of accounts from your export of accounting financials. Rarely do the lines used to create a forecast match the lines used to track the actual results, so mapping them to similar categories is important for making the analysis useful.

How it works

The sheet uses the actuals and the forecast directly from the categories on Forecast. If you input actual financial results by linking into the Actuals section on Hooks, the model will automatically update the financials used in Statements, Key Reports, Summary, Breakdown, and all reports, but since the model will still keep the forecast in the forecasted revenues and expenses on Forecast, it can then pull in the forecast to compare to the actuals.

The sheet will display the budget (or forecast), actuals, variance, and % variance in their own columns. The analyses are done in five groups, covering year-to-date and Q1, Q2, Q3, Q4. For each, the model will only draw in the forecast that covers the same period represented in the actuals; meaning, if you’ve pulled in Jan and Feb of 2020, the model will display the forecast for Jan-Feb 2020. This is done so the comparisons are on the same base.

Inputs

In C2, there is a dropdown that allows you to select the fiscal year you want to show in the sheet. Select the year you want to show, and the model will automatically update all calculations and date ranges.

Common Modifications

The sheet is not commonly edited unless the revenue and expense categories used on Forecast were expanded or contracted, in which case editing this sheet is required for it to display the correct information.

  • The one common modification is to edit the revenue and expense categories displayed on the sheet to match any changes in the number of categories made on the Forecast sheet. Changing the category names will not matter, they willupdate automatically, but if you create new categories or delete categories and insert or delete rows, you will have to edit this sheet to match. Simply insert rows in the category section to match the relative category rows on Forecast, select the row above or below the empty rows, and copy the formula into the blank rows, and all formulas will adjust automatically. Check the categories to confirm that they match the categories and the order used on the Forecast sheet.
  • If you want to use this for multiple years of data, simply use the dropdown to select the year to change the year displayed dynamically, or duplicate the sheet and use the dropdowns to select the appropriate year for each sheet.

This sheet can be hidden or deleted with no impact on the rest of the model, as no results here are used elsewhere in the model.

Questions, contact me.

Was this helpful? Yes / No

Thanks, I appreciate the feedback.