Using Actual Financials

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, Starter Model, and all Standard Model variants (Ecommerce, SaaS, etc.)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 the Actuals section on the Hooks sheet to match the format for your Forecast. You can update these on a monthly basis with new historical data, and the model will automatically create budget variances and a rolling forecast on the Forecast sheet.

You can use the model with or without using historical financials, it's purely optional. Initial setup to use actual results from your accounting software typically takes one hour. Once the model is setup to use actual results, it should take 15 minutes to roll over the forecast each month with new financials.

Here's the process I use to pull historical financials into the Standard and Starter Models, and the sheets I use to do it.

Pulling in historical financials #

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

1. For an initial import of historical financials, 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 the sheets from the reports into the model. These sheets become 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 Hooks sheet to be presented in a format that fits the model structure.
4. Link the appropriate lines from the exported income statement and balance sheet into the matching lines in the Actuals section on Hooks. More on how to best link the lines in the Setting up Categories section below.

How the Actuals works #

The Actuals section on Hooks is created as a store of data for actual operating metrics, revenues, expenses, and some balance sheet and cash flow items to use in the Forecast sheet, so that you can then compare Forecasts with Actuals through budget variances and do rolling forecasts. The model essentially stores the raw forecasted revenues and expenses on Forecast, pulls in any imported historical financials through the inputs on Hooks, and consolidates the forecasted and actuals to create rolling forecasts for the financial statements and all financial and management analyses in the model.

The start date of the model is set on the Get Started sheet, and if you are using historical data, you will often want to set the start date to the first date of your fiscal year (for most users, Jan 1 of the current year), and then input actual data for the months until the first forecast month. If it is late in the year, I often recommend skipping that, setting the start date for Jan 1 of next year, and then manually updating the Summary sheet to add a column for the current year, to provide some context for the forecast.

You can use the Actuals section to link in revenue and expense lines from your imported Income Statement and the set of balance sheet items presented in the Actuals section to your imported balance sheet. You'll notice not every line on the financial statements are linked in; that is a conscious decision to use the model to recompute the income statement and balance sheet from the linked financials, so that the model is internally consistent all line items should sum up to the same net income, cash, as your imported statements. If the Statements does not equal your imported statements, then that means the accounts are not linked in correctly or that there are elements not captured in the changes each month, and that should be edited.

Setting up Categories #

In almost all companies I have worked with over the years, the way entrepreneurs and CEOs think about creating a forecast is a bit different than the way that the expenses and revenues are tracked in their accounting software. The chart of accounts in one's accounting sofware rarely maps exactly to the lines used to forecast expenses, and it traditionally creates some confusion and overhead to analyze the expenses and compare one's forecast to the actuals.

In the Standard Model (v4.6 and above), Starter Model (v3 and above), and all Standard Model variants (v4.6 and above), I use categories to create a common way to group revenues and expenses together so that one's forecast and actual results can be mapped to the same set of categories. These revenue and expense categories are set on the Forecast sheet, and are used on Breakdown, Summary, and Budget sheets to provide insights into the business.

When setting up the categories on Forecast, usually I'll create a set of categories that allow me to draw together some insights about the business. One way of laying out the categories aligns closely with a standard chart of accounts, with expenses grouped together according to the type of expenses:

• Salary and Benefits
• Professional Services
• Rent and Utilities
• Marketing and Advertising
• Travel
• Insurance
• etc.

Another method for assigning the categories groups the categories by functional area within the company:

• Executive and Admin
• Sales
• Product
• Marketing
• Customer Service
• G&A

Both methods can work; note that in the second method, the payroll expenses are generally split between the functional areas, and most chart of accounts are not setup to split out payroll, payroll taxes and benefits that way, so it may take more internal work or manual adjustment to use that method.

One note, the categories are aligned to Cost of Sales (COGS) and Selling, General, and Administrative (SG&A). By default, there are three COGS categories and eight SG&A categories. The number of categories can easily be adjusted simply by inserting or deleting rows in between the first and last COGS or SG&A category, respectively, copying the formulas, and changing the labels in column B. Just be sure that your edits to insert or delete rows in the category setup on the Forecast sheet are replicated on Forecast in the actuals + forecast section, Hooks in the Actuals section, on the Budget sheet, and on the Breakdown sheet.

Once the categories are setup, then when creating the forecasted expenses, simply use the dropdown in column D to assign each Payroll and Expense line to one of the categories, and that categorizes the forecasted expenses. Then when you link in Actuals, link in the expenses from your imported income statement by summing them to the relevant categories, and that matches up the forecasted budget with the actual results for consistent comparison and analysis.

Once you link in historical data into a column in Actuals on Hooks, the model automatically recognizes that actuals are included and will pull the actuals data into the Actuals + Forecast section on Forecast, and update the Statements and all analyses automatically. It will also update the Budget sheet to show the budgeted forecast compared to the actual results on an overall year-to-date basis and by quarter.

Updating the Actuals at end of month #

Once Actuals are setup, at the end of every month you can then update the model to use your actual results from Quickbooks or Xero. Simply:

1. Export the income statement and balance sheet for your current year using the same report you created for initial setup
2. Copy and paste the numbers for the latest month into the sheets you are using in the model to store your income statement and balance sheet
3. Copy the formulas in Hooks in the Actuals section from the previous month to the current month
4. Review the computed statements in the model to your imported actual statements for accuracy. Usually I check revenues, net income, and cash (balance sheet) to make sure they are the same, and if not, look at the components to figure out what changes in my actual results are not reflected in the model.

When you initially link in your imported financials, you can also carry over the formulas to future months to eliminate needing to do step 3 above each month, just note that it can be a bit more brittle when dealing with any changes in your chart of accounts lines.

As you update the model each month, watch out for shifting row references on the financial statements as you update each month, you may need to link in new lines from the chart of accounts. It should take 15 minutes to roll over the forecast each month with new financials.

I offer a one hour per month service to update the model for a new month's actuals and review the results, questions, contact me.