How to Conduct Plan Vs Actual Analysis With Spreadsheets
I have decades of experience working with plan vs. actual in spreadsheets. I have been using plan vs. actual analysis once a month, comparing forecasts and budgets to actual results, since I started Palo Alto Software in the 1980s. Short of expensive budgeting software for corporations, this was the only way to do it.
For the record, this method still works. Nowadays, you can get Microsoft Excel for a low monthly cost, equivalent to a lunch, and Google Sheets, a competent alternative, for free. There are also other spreadsheets available.
In this article, I will show you how to conduct your plan vs. actual analysis step by step, using your accounting and spreadsheet. As I write this in 2021, this topic is a classic. There may be better ways to do this (LivePlan does it automatically), but this method still works effectively and is widely used.
Contents
Start with your spreadsheet
Think of budgets and forecasts in a horizontal layout, with categories in the leftmost column and months spreading to the right, one month per column. Here is an example (showing only 3 months due to space limitations).
If you scroll to the right on this spreadsheet, you’ll see 12 months and a column summarizing the whole year.
Spreadsheets are a programming language, not just an application, with infinite possibilities.
Forecasting and budget math in spreadsheets is usually simple.
Don’t be intimidated by the appearance of a spreadsheet. It doesn’t require much expertise. In the example above, the spreadsheet uses formulas to calculate numbers. Cells in the spreadsheet are identified by rows and columns, and the calculations are usually straightforward. For instance, the calculation for new bicycles is units multiplied by price. Sales are located in cell D19, and the formula multiplies D20 (units) by D21 (price).
Categories matter
Ensure that your sales forecast is organized in the same categories as your accounting system. This makes it easier to build a budget and compare it with actual results. Keep your chart of accounts aligned with your financial statements.
If your accounting system categorizes sales into meals, drinks, and other, your business plan should do the same. Maintain the product or service groups in your sales forecast that align with your chart of accounts. Avoid forecasting sales by channel if your bookkeeping tracks them by product.
For startup businesses, coordinate the bookkeeping and forecasting categories.
Refer to your last Income Statement (Profit & Loss) while developing your future projections.
- If you have fewer than 20 rows for sales, costs, and expenses, match the rows in your projected statement with those in your accounting.
- If your accounting system provides summary categories, consider using them in your business plan. Accounting requires detail, while planning needs a summary.
If your projection categories do not match the accounting output, it will be difficult to track plan vs. actual. This will require retyping and recalculating, and you’ll lose the most valuable benefit of business planning: effective management.
Set the right scope
These examples focus on the sales budget, but we recommend tracking and managing plan vs. actual for all your financials, including sales, costs, expenses, profit and loss, balance sheet, and cash flow.
Some items are more important than others, so prioritize accordingly. The areas where plan vs. actual analysis is most beneficial for management decisions are sales, costs, and expenses.
Putting your actual results into a spreadsheet
Align your categories with your accounting outputs so that you can easily export actual results into a spreadsheet. Most accounting software allows for exporting data in spreadsheet format. Simply copy and paste the actual data from accounting reports into spreadsheets that match the structure of your own spreadsheet.
Be aware that using the spreadsheet method to match the plan or budget can be tricky and prone to errors. Remember to double-check your inputs and settings throughout the process. There are ways to error-check spreadsheets that are not covered in this article.
Calculating the plan vs. actual (Variance)
The budget or plan is one spreadsheet and the actual results are another. They can be separate sheets or tabs within a single workbook.
The following illustration shows the third spreadsheet, or sheet in a workbook, with the calculated plan vs. actual results.
Positive vs Negative Variance
In the illustration above, positive variance is represented by black numbers, while negative variance is represented by red numbers.
Let’s take the sales of bicycles for March as an example. Instead of selling the planned 36 units, they sold 31 units, resulting in a negative variance of -5. However, they were able to sell the units at an average price of $615, higher than the planned $500. This positive variance of $115 on the average price should be considered along with the negative variance on unit sales. It indicates that the sales for that month might be good news.
Variance is Context Sensitive
The calculation of variance depends on the context:
- For sales, whether it’s units, price, or total sales, more is better. Variance is calculated by subtracting the planned amount (e.g. 36 units) from the actual amount (e.g. 31 units). A negative variance is obtained when the actual amount is less than the planned (31-36 = -5).
- For costs and expenses, less is better. Variance is calculated by subtracting the actual amount from the planned amount. For example, if the budget for an expense in a given month was $3,600, and you spent only $3,100, then you have a positive variance of $500. Conversely, if the budget was $3,000 and you spent $3,500, then the variance would be -$500, indicating a negative variance.
Your Goal is Effective Management, not Just Accounting
Variance analysis plays a crucial role in effective management. Tracking and following up on budgets through variance analysis is essential; otherwise, budgets become useless.
While variance analysis can be complex, common sense should be the guiding principle. In general, being under budget is considered a positive variance, while being over budget is a negative variance. However, the true measure of good management lies in whether the results were beneficial for the business.
In the examples provided, the highlighted numbers demonstrate the distinction between simple accounting calculations, known as variance, and the managerial implications of reviewing plan or budget results, comparing them to actual results, and assessing the impact on the business. The numbers alone do not provide the answer; it is the subsequent management actions and decisions that determine the outcome.
In this particular case, the bicycle store owner and the management team would consider adjusting marketing messaging either to promote higher-priced items or to attract more customers interested in the lower-priced item. This decision is not solely based on the numbers, but on human judgment and managerial expertise. The answer lies in the actions taken by the management team following the analysis.
Hello!
I’m Andrew Brooks, a seasoned finance consultant from the USA and the mind behind phonenumber247.com.
My career is built on a foundation of helping individuals and businesses thrive financially in an ever-changing economic landscape. At phonenumber247.com, my aim is to demystify the complex world of finance, providing clear, actionable advice that can help you navigate your financial journey with confidence. Whether it’s personal finance management, investment strategies, or understanding the nuances of market dynamics, I’m here to share insights and tools that can propel you towards your financial goals.
Welcome to my digital space, where every piece of advice is a step closer to financial clarity and success!