How to Conduct a Plan Vs Actual Analysis With Spreadsheets

I’ve spent decades working with plan v. actual in spreadsheets. I used plan vs. actual analysis once a month, comparing forecasts and budgets to actual results since I started Palo Alto Software back in the 1980s. Short of some extremely expensive budgeting software for corporations, that was the only way to do it.

For the record, this method still works. And nowadays you can get Microsoft Excel for about what a lunch costs per month, and Google Sheets — a competent alternative — for free. And there are other spreadsheets as well.

In this article, I’ll show you how to do your plan vs. actual analysis, step by step, with your accounting and your spreadsheet.

Start with your spreadsheet

Try to think of budgets and forecasts in a horizontal layout showing categories in the leftmost column and months spreading to the right, one month per column, like this (showing only 3 months here because of space limitations)

If you scroll to the imaginary right on this cut-off spreadsheet, you scroll through 12 months and then a column summarizing for the whole year.

Spreadsheets are a programming language, not just an application. So you have the potential of infinite varieties.

Forecasting and budget math is usually simple

Don’t be put off by the look of a spreadsheet. It doesn’t take that much expertise. In the illustration above, the spreadsheet provides simple ways to calculate numbers using formulas. Cells are identified by rows and columns and calculations are normally fairly simple. For example, the math for new bicycles in the illustration above is units times price. As the next illustration shows, sales are located in cell D19 and the formula multiplies D20 (units) times D21 (price).

Categories matter

Make sure the way you organize the sales forecast in rows or items or groups matches the way your accounting (or bookkeeping) tracks them. It’s way easier to build a budget based on the categories in accounting. Rather than having to build a budget with different categories, and then convert and synchronize to compare your budget with actual results.

Match your chart of accounts, which is what accountants call your list of items that show up in your financial statements.

If the accounting divides sales into meals, drinks, and other, then the business plan should divide sales into meals, drinks, and other. So if your chart of accounts divides sales by product or service groups, keep those groups intact in your sales forecast. If bookkeeping tracks sales by product, don’t forecast your sales by channel instead.

If you’re planning for a startup business, coordinate the bookkeeping categories with the forecasting categories.

Get your last Income Statement (also called Profit & Loss) and keep it in view while you develop your future projections.

• If you don’t have more than 20 or so each rows of sales, costs, and expenses, then make the rows in the projected statement match the rows in the accounting.
• If your accounting summarizes categories for you – most systems do – consider using the summary categories in your business plan. Accounting needs detail, while planning needs a summary.

If your categories in the projections don’t match the accounting output, you’re not going to be able to track plan vs. actual well. It will take retyping and recalculating. And you’ll lose the most valuable business benefit of business planning: management, steering your company.

Set the right scope

Are examples here show just the sales budget of the forecast. We recommend tracking and managing plan vs. actual for all of your business financials, including not just sales but the cost of sales, expenses, profit and loss, balance sheet, and cash flow. The same methodology applies.

Some items are more important than others. You do what you can to optimize management. Generally, where plan vs. actual analysis is most likely to help with management decisions are on sales, costs, and expenses.

Putting your actual results into a spreadsheet

This next step is why you set your categories to match your accounting outputs. Most accounting or bookkeeping software can export actual results into spreadsheet format. From there, you can copy and paste actual data, from accounting reports, into spreadsheets set to match the exact structure of your spreadsheet. For example:

Be aware that with the spreadsheet method, getting actual results into the right spreadsheet structure — matching the plan or budget — can be tricky. It’s the most likely spot for errors. Throughout the process, double-check your inputs and your settings. There are ways to error-check spreadsheets that are beyond the scope of this article.

Calculating the plan vs. actual (Variance)

The budget or plan is one spreadsheet and the actual results another. In most spreadsheets these might be separate sheets or tabs inside a single spreadsheet or workbook.

This next illustration shows the third spreadsheet, or sheet in a workbook, with the plan vs. actual results calculated.

Positive vs negative variance

In the illustration above you can see positive variance as positive numbers in black; and negative variance as negative numbers, in red.

Take the sales of bicycles for March, the first month shown. They sold fewer than planned, 31 instead of 36; so that’s a red number, -5, a negative variance. But they sold them for a higher average price than planned, $615 instead of $500, so the average price is a positive variance, $115. I use that example to point out the management implications of plan vs. actual analysis. The negative variance on unit sales is bad by itself, but combined with the positive variance on average price, the sales for that month might be good news.

Variance is context sensitive

The actual variance calculation depends on the context:

• For sales, whether units, price, or total sales, more is better. Calculate the variance by subtracting the planned amount (36 units, in the example above) from the actual, (31 units). That way, less than planned calculates to a negative variance (31-36 = -5).
• For costs and expenses, less is better. Calculate the variance by subtracting the actual amount from the planned amount. So if the budget was $3,600 for an expense in a given month, and you spent only $3,100, then that’s a positive variance of $500. If the budget was $3,000, and you spent $3,500, then plan – actual is $3,000 – $3,500 = -$500, a negative variance.

Your goal is effective management, not just accounting

Variance analysis is vital to good management. You have to track and follow up on budgets, mainly through variance analysis, or the budgets are useless.

Although variance analysis can be very complex, the main guide is common sense. In general, going under budget is a positive variance, and over budget is negative variance. But the real test of management should be whether or not the result was good for business.

In the examples here, I chose those numbers on purpose, to show the difference between simple accounting calculations (called variance) and the management implications of reviewing plan or budget results, comparing them to actual results, and looking at the difference. The bicycle store owner and the management team look at the numbers and then consider the impact on the business.

In the case shown here, maybe they want to adjust marketing messaging to encourage higher-priced items; or maybe they want to adjust the marketing messaging to bring in more people who want the lower-priced item.

That’s in the human decisions, not the numbers. The answer here is not in the numbers alone. It’s in the management that follows.