All new models. Save $25 with code STARTNOW

Actuals and Rolling Forecasts

How the Standard Model uses historical financials and creates rolling forecasts.

One question entrepreneurs used to ask me was “how do I input my past financials?” The answer was typically to create a new sheet, paste in an export from Quickbooks, Xero, or any other accounting program, and then manually create custom views of the business combining the actuals and the forecasts to do rolling forecasts (blending together actuals with forecasts and rolling over the model every month to show a continually updated forecast for the year) and actual v. budget analysis (comparing what was forecasted with what actually happened). The models worked, but it was not a solution that was obvious or easy to use.

In the new models I’ve worked to make this process much easier. In the [Standard Model](/standard-financial-model, the model allows you to import an export of your income statement and balance sheet from an accounting program - like Quickbooks, Xero or others - and link these actual financial results into an Actuals sheet that matches the format for your Forecast. You can update these on a monthly basis with new historical data, and the model allows you to easily create a rolling forecast on the Actuals + Forecast sheet.

Pulling in historical financials

Here’s the process I use to pull historical financials into the Standard Model, and the sheets I use to do it.

External Data

This sheet is a blank sheet to be used to paste in your actual financial and operational data if you want to use historical financials in your model, either for reporting (to show historical financials and forecasted financials), comparison (to compare actuals to the forecasted for the same time period) or forecasting (to use historical financials and ratios as a base for future forecasts).

My process in using this feature is to:

  1. Run reports for the Income Statement and Balance Sheet in Quickbooks, Xero, or other accounting software, as a report that shows each on a monthly basis looking back 6 or 12 months, depending on your situation,
  2. Export those reports to Excel (here’s how in Quickbooks and Xero)
  3. Copy and paste the data from there into this sheet. This sheet becomes a data storage sheet for the raw chart of accounts data - or any other operational data you want to use in the model - that then gets used by the Actuals sheet to be presented in a format that fits the model structure.

Actuals

This sheet is created to match the structure of the Forecast sheet, to provide the same view of a very similar set of financial data, so that you can then compare Forecasts with Actuals and do some interesting rolling forecasts.

Often the chart of accounts of the export will have more data than I need or want to use in reporting or forecasting, so I then use a “Actuals” sheet to streamline the data I pull into the model. You can create this sheet to match the operating costs you want to project in your Forecast, or you can simply create the same lines of expenses from your Actuals in your Forecast (or use the prebuilt one in the model). Sometimes we don’t want to forecast the same chart of accounts exported from our accounting software, so this is how we re-map the lines we want to forecast with the data we already have.

With that Actuals Sheet, you then have a set of detailed income statements that are the same in structure - the revenue, COGS and operating expenses from the Forecast sheet and the actual data in the Actuals Sheet. You can use this to create a new Income Statement that shows actuals + forecast together, or do any analysis comparing the past to the future. You can also to budget variance, keeping the forecast sheet “set” and unchanged, changing the Actuals and Actuals sheet, and then compare the two over the same time periods.

You can use the model with or without using historical financials. If you are not using historical financials, you can safely delete this sheet.

Using historical financials can require some manual fine-tuning; if you have any questions, contact me.

Actuals + Forecast

This sheet creates a rolling forecast, combining the Actuals with the Forecast.

The model will automatically select Actuals or Forecast based on the initial start dates of the model you select, but once you are using the model and begin inputting data for Actuals and start having months in Actuals that overlap with months in your Forecast (i.e. once you really start using it, and comparing your Actuals to your Budget), then you’ll want to override the automatic selection and begin selecting the data you want to pull into the Actuals + Forecast sheet. Simply select “Actuals” or “Forecast” from the dropdown in each appropriate month in the top input row (row 2), and the model will pull the correct data from the correct month from Actuals or Forecast. The future months will pull from Forecasts, and all the statements will adjust based on the new data from the Actuals. For example, you could start the actuals for Jan 2017, start the Forecast for April 2017, and then this sheet will reflect the past + the future in one cohesive view.

You can set the start date for the actuals and the start date for the forecast separately (on Get Started - Financial Model) and model will aggregate it all automatically. Now you can roll over your forecast for each month simply by importing a new month’s data from your accounting program and changing one cell, changing that month from “Forecast” to “Actual”, and everything updates automatically.

While budget variances are not created by default, it’s easy to do any actual v. budget variance you want, with all the data in the same format, it’s easy to compare any parts of your financials.

Was this helpful? Yes / No

Thanks, I appreciate the feedback.