Latest

How to Conduct a Plan Vs Actual Analysis With Spreadsheets

September 23, 2021
By James Gussie

This article discusses how to conduct a plan vs actual analysis with spreadsheets. It also provides some examples of how this process can be used in various scenarios.

The planned vs actual excel template is a spreadsheet that will help you conduct a plan vs actual analysis. It includes the steps to take to make sure you are doing your research properly and not missing anything.

In spreadsheets, I’ve spent decades dealing with plan vs. reality. Since I began Palo Alto Software in the 1980s, I’ve utilized plan vs. real analysis once a month to compare predictions and budgets to actual outcomes. That was the only way to accomplish it without spending a fortune on corporate budgeting software.

This technique, for the record, still works. And these days, you can acquire Microsoft Excel for roughly the same price as a monthly lunch, and Google Sheets – a capable alternative — for free. There are many additional spreadsheets available.

In this post, I’ll teach you how to conduct a plan vs. reality analysis using your accounting and spreadsheet, step by step. This is a classic issue as I write this, in the year 2021. This may be done in a more efficient manner (LivePlan does it automatically). However, it continues to function, and people continue to benefit from it.

Table of Contents

Begin by looking at your spreadsheet.

Consider budgets and projections in a horizontal format, with categories in the leftmost column and months spreading to the right, one month per column, as shown below (only three months are shown here due to space constraints… Click here to see a larger version of the image):

On this cut-off worksheet, if you scroll to the hypothetical right, you’ll see 12 months and then a year-end summary column.

Spreadsheets are not just an application, but also a programming language. As a result, you have an endless variety of options. Click here for a popular variation to the aforementioned layout.

The arithmetic involved in forecasting and budgeting is generally straightforward.

Don’t be turned off by a spreadsheet’s appearance. It does not require a great deal of knowledge. The spreadsheet in the example above shows how to compute numbers using formulae in a straightforward manner. Rows and columns identify cells, and computations are usually straightforward. For example, in the picture above, the math for new bicycles is units times price. As shown in the following diagram, sales are in cell D19, and the formula is D20 (units) times D21 (price).

It’s important to categorize things.

Make sure your sales estimate is organized in rows, items, or groups in the same manner your accounting (or bookkeeping) system does. It’s much simpler to create a budget based on accounting categories. Rather of needing to create a budget with several categories, convert and synchronize your budget to compare it to real outcomes.

Match your chart of accounts, which is the term used by accountants to describe the list of things that appear in your financial statements.

If sales are divided into meals, drinks, and other in accounting, sales should be divided into meals, drinks, and other in the business plan. Keep your product or service groups intact in your sales forecast if your chart of accounts splits sales by product or service groups. Don’t anticipate your sales by channel if your accounting monitors sales by product.

If you’re starting a company, make sure your accounting and forecasting categories are in sync.

Take a look at your most recent Income Statement (also known as Profit & Loss) and keep it in mind while you work on your future forecasts.

Make the rows in the projected statement match the rows in the accounting if you don’t have more than 20 or so rows of sales, costs, and expenditures.
Consider utilizing the summary categories in your company strategy if your accounting system summaries categories for you (most systems do). Accounting necessitates precision, while planning necessitates brevity.

You won’t be able to monitor plan vs. reality effectively if your forecast categories don’t match the accounting output. Retyping and recalculating will be required. You’ll also lose the most important commercial advantage of business planning: management and direction.

Determine the appropriate scope.

These samples just show the forecasted sales budget. For all of your company financials, including sales, cost of sales, expenditures, profit and loss, balance sheet, and cash flow, we suggest monitoring and managing plan vs. reality. The same approach is used.

There are certain things that are more significant than others. You do all you can to improve management. Sales, costs, and expenditures are the areas where plan vs. reality analysis is most likely to aid management choices.

Using a spreadsheet to record your real outcomes

This is why you should match your categories to your accounting outputs in the following step. The majority of accounting and bookkeeping software allows you to export real results to a spreadsheet. From there, you may copy and paste real data from accounting reports into spreadsheets that are built up to match your spreadsheet’s precise layout. Consider the following scenario:

It’s important to keep in mind that using the spreadsheet approach, putting real data into the correct spreadsheet structure — one that matches the plan or budget — may be difficult. It’s the most probable place to make a mistake. Double-check your inputs and settings throughout the procedure. There are more methods to examine spreadsheets for errors that aren’t included in this article.

Calculating the difference between the plan and the real (Variance)

One spreadsheet contains the budget or plan, while another has the actual outcomes. These are usually distinct sheets or tabs inside a single spreadsheet or workbook in most spreadsheets.

The plan vs. actual outcomes are computed on the third spreadsheet, or sheet of a workbook, in this example.

Variance that is positive vs. variance that is negative

Positive variance is represented by positive numbers in black, while negative variance is represented by negative values in red.

Take, for example, bicycle sales in March, the first month listed. They sold 31 instead of 36, resulting in a red figure, -5, indicating a negative variance. However, they sold them for a higher average price than expected, $615 instead of $500, resulting in a $115 positive variation in the average price. That example is used to demonstrate the management consequences of a plan vs. real analysis. Even while the negative variation in unit sales is undesirable in and of itself, when coupled with the positive fluctuation in average price, the sales for that month may be excellent.

Variance is context-dependent.

The actual variance computation is determined by the following factors:

More is better when it comes to sales, whether it’s in terms of units, price, or overall sales. Subtract the intended quantity (36 units in the case above) from the reality, and you’ll get the variation (31 units). In this case, less than expected is a negative variance (31-36 = -5).
Less is more when it comes to expenditures and expenses. Subtract the actual amount from the anticipated amount to get the variation. So, if your monthly budget for an expenditure was $3,600 and you only spent $3,100, you had a $500 positive variation. If your budget was $3,000 and you spent $3,500, your plan to actual variance is $3,000 – $3,500 = -$500, which is a negative variance.

Effective management, not simply accounting, is your objective.

Good management necessitates the use of variance analysis. Budgets must be tracked and followed up on, mostly via variance analysis, or they will be worthless.

Although variance analysis may be complicated, common sense is the best guidance. Going under budget is generally a good variance, whereas going over budget is a negative variation. However, the true measure of management should be whether the outcome was beneficial to the company.

I purposefully selected those figures in the instances below to demonstrate the distinction between basic accounting calculations (known as variance) and the management implications of evaluating plan or budget outcomes, comparing them to actual results, and examining the difference. The proprietor of the bicycle shop and his management team examine the statistics before considering the implications for the company.

Perhaps they want to change the marketing message in this instance to promote higher-priced goods, or perhaps they want to change the marketing messaging to attract more individuals who desire the lower-priced item. Not the statistics, but the human choices. The solution isn’t found just in the statistics. It’s in the management that things become interesting.

The How to calculate plan vs actual percentage is a question that can be answered with a spreadsheet. Reference: how to calculate plan vs actual percentage.

Frequently Asked Questions

How do I make an actual or plan in Excel?

To make a plan in Excel, you can use the Insert tab of the ribbon to insert a table. Then, you can just type your data into the cells and it will automatically calculate for you.

How do you calculate planned vs actual?

Planned is the number of hours a project will take to complete. Actual is the number of hours that have been spent on a project.

What is the difference between plan and actual?

The difference between plan and actual is that plan is a prediction of what will happen in the future based on past experiences, while actual is the present moment.