Calculating Operating Variances: Completing a Benchmarking Analysis with Your Excel-Based Master Budget
Porter, Jason, Stephenson, Teresa, Strategic Finance
Budgeting. Once the year is over, company leaders often think that the budget no longer serves a purpose. The accountants and management team typically spend a great deal of time and energy creating the budget, but then the year winds to a close, and the budget is pushed to one side or thrown into the recycling bin. A new budget is being created, new data is being gathered, and new decisions are being made. What help could the old budget be now? But throwing away a good budget at the end of the year is like closing the book on a good mystery just before the final chapter. Using your budget to perform solid variance analyses allows you to finish the story: to see how the company performed, when it deviated from the plan, and why those deviations occurred. It also provides you with the tools to create a more convincing story--a more accurate budget--next year.
This is the third and final article in our series describing how you can use an Excel-based Master Budget for making managerial decisions. In the first article, we added a Contribution Margin Income Statement to our Master Budget and calculated breakeven and margin of safety for Bob's Bicycles. In the second article, we created a Flexible Budget and started analyzing the company's sales and contribution margin variances. In this article, we examine Bob's actual results and use them to calculate the company' operating variances. In doing so, we hope to provide enough details and discussion so you can use these tools to analyze any type of business. Unfortunately, we won't be able to look at every possible type of operating variance, but we'll look at some of the most important examples and discuss their implications.
So fire up your spreadsheet, warm up your calculator, stretch your fingers, and let's go!
Creating the Actual Contribution Margin Income Statement
In the first two articles of this series, we created two of the three "budgets" needed to analyze last year's results. We developed the Static Budget first (Strategic Finance, July 2011) using the information from Bob's Master Bud-get (originally developed in Strategic Finance, February-July 2010). Next came the Flexible Budget (August 2011) using the budgeted production information but actual sales quantities. This month we add the last "budget," which isn't really a budget at all, even if it does get lumped in with the budgets. Instead, this final statement reports actual results in the Contribution Margin Income Statement format. Putting the "budgets" together allows managers to easily compare actual results side by side with the original budget and the variable budget, and they can investigate the differences, or variances, from their original Master Budget.
[FIGURE 1 OMITTED]
Unlike the Static and Flexible Budget columns, we use the actual results from operations when creating the Actual Results column. Let's take a look at our example company, Bob's Bicycles. If you don't have a copy of the Master Budget, including the Static and Flexible Budgets that we created for this current series, you can get one by e-mailing either author. Open your spreadsheet to the CM IS tab; that's where we put the three versions of the Contribution Margin Income Statement that we'll use to calculate Bob's cost variances. The first column (as you can see in Figure 1) shows the Static Budget, which consists of numbers pulled directly from Bob's Master Budget. The second column is Bob's Flexible Budget, which we created last month. The final column, which you can easily insert into your budget, uses all the same categories, but this time it shows the actual results from Bob's 2010 operations. As we've done in the past, we highlighted the data that we typed directly into our spreadsheet.
You probably noticed that we typed in only the summary information for each type of cost: total direct materials, total direct labor, total manufacturing overhead, etc. We like the clean look this simplified format provides because it's easy to differentiate the actual results from the budgets so we can focus on a summary analysis first. …