Comparing budgets to performance: how to calculate sales and contribution margin variances with an Excel-based budget.
This is the second of three articles describing how to actually use an Excel-based Master Budget for making managerial decisions rather than just keeping it on display. In the first article (July 2011), we created a Pro Forma Contribution Margin Income Statement and then used it to calculate the breakeven point and margin of safety for our example business, Bob's Bicycles. In this article, we take that basic analysis to the next step: bench-marking. We'll start by adding a Flexible Budget to our spreadsheet; then we'll use those numbers to calculate a set of variances that will let us dig into both the positive and negative surprises in sales.
Let's get started.
Creating a Flexible Budget
Three "budgets" need to be created in order to perform a good analysis. The first, a "Static Budget," is typically presented as a Pro Forma Contribution Margin Income Statement, and we created one of those last month using the information from the Master Budget we developed in Strategic Finance last year (February-July 2010). The second, a "Flexible Budget," updates the Static Budget using actual sales in units. When creating this second budget, we still use our budgeted prices and our expected inputs for production. The goal with a Flexible Budget is simply to show what we expect our contribution margin and profit to be at that level of sales--not to show actual income. We'll get to that later. The final "budget" isn't really a budget at all, although it's typically created at the same time as these other budgets. It's the actual Contribution Margin Income Statement, using not only our actual units sold but our actual sales prices, costs, inputs, etc. (We'll create that statement next month in the final article in this series.)
When we create the Static and Flexible budgets, we use "expected inputs." An expected input is how many units typically are needed for production. For example, if we plan to make 20 gadgets and each gadget requires three widgets of raw materials, then our expected widget input would be 60.
Let's take a look at the numbers for Bob's Bicycles. (If you don't have a copy of the Master Budget, or if you need an updated one that contains these analyses, you can get one from either author.) The Static Budget is in the tab we set up last month called CM IS (Figure 1, July 2011). Since we created that Contribution Margin Income Statement based on our original Master Budget, essentially it's a Static Budget. As we described before, all of the numbers we used are based on the planned sales for the year, with planned sales prices, planned inputs, and planned costs used to complete the rest of the Income Statement. This Static Budget provides our best guess at Bob's Contribution Margin Net Income for the period, as well as the estimated fixed costs and the estimated contribution margin. Last month we were able to calculate Bob's breakeven point and margin of safety using these numbers.
Our next step is to add a Flexible Budget to the CM IS tab. This budget uses the actual sales levels at the end of the period. That's all that should change--just the sales levels. Why? Because comparing only the change from budgeted units sold to actual units sold (without changing estimated inputs) allows managers to investigate how much of the change in profits is strictly because of the differences between projected and actual sales. And that's the first step in performing a good variance analysis.
The first step in creating a Flexible Budget, then, is to set up columns F and G to look just like the Static Budget. You can copy and paste, or you can insert new columns and recreate it manually. Just be careful that the formulas work properly. The per-unit numbers (in column C of the Static Budget and column F of the Flexible Budget) should be the same. In fact, you'll probably want to link the per-unit numbers in the Flexible Budget columns to the numbers in the Static Budget. That way they'll update automatically each year as you update your Master Budget. The equations in the Overall column should be the product of the per-unit number to the left and the total at the top of the column.
If you copied your Static Budget into the new columns, be careful to update the equations. When we originally set up the Static Budget, we used absolute cell references. For example, in Cell D11 we have =C11*$D$6 so that we can easily copy our formulas through the rest of the Static Budget and still have each cell refer to the estimated units sold. If you copied these columns into the new Flexible Budget, that permanent reference will still link back to cell D6. For the Flexible Budget, though, the equation needs to refer to cell G6. One easy way to fix these references is to use the "Find and Replace" function in Excel. To do that quickly, highlight the cells from G6 to G45, then type Ctrl+H to open Excel's "Find and Replace" function. Put D in the "Find what" box and G in the "Replace with" box. By choosing "Replace All," all of the changes are made in less than a second. You can see the result in Figure 1. You'll notice that, without any other changes, the two budgets give us the same Net Income.
The final step is to replace budgeted units sold with actual units sold. You'll notice that we've highlighted the units sold cells in our Flexible Budget in green. Throughout our budget, we've been careful to highlight only those cells that need to be updated manually. All of the other cells are equations or links to other data input cells. Most of the manual inputs are on the Basic Information tab to make the process of updating the spreadsheet simple, but occasionally it's easier or more logical to input the information on our actual budget tabs. That's what we've done here to emphasize the differences between the Static Budget and the Flexible Budget (typing in the information here allows us to instantly see the differences between what was planned and what actually happened).
Our investigation showed that Bob's actually sold 17,074 basic and 8,356 deluxe bikes over the course of the year. So let's input those into the Flexible Budget in Cells G7 and G8. The rest of the information flows through, instantly updating the total sales revenues, variable costs, and contribution margin. It's a beautiful thing to see a budget working automatically! Of course, the fixed expenses are the same on both the Static and the Flexible Budgets since the only difference is in the units sold.
Using the Flexible Budget and Static Budget
Once you have these two budgets done, you can start your variance analysis. We aren't quite ready to do a full variance analysis; we can't do that until we finish creating the actual Contribution Margin Income Statement. But we can, and should, start our analysis now. Trying to compare our original Static Budget directly to the actual results will often confuse the issue: Are the variances caused by inaccurate sales estimates or problems with performance? We've got to figure out the inaccuracies in our sales estimates first; then we can move on to evaluating performance.
In Figure 1, you can see that Bob's income is $587,646 in the Flexible Budget and $603,748 in the Static Budget. This difference, about $16,102, is called the Contribution Margin Volume Variance, and for Bob's it's the opposite of what we hope to see. We prefer to see the Flexible Budget income end up higher than the Static Budget income since that means that we did better than expected. But you typically won't see a discussion of the differences between Net Incomes in these two budgets. Since the fixed expenses haven't changed at all between the two budgets (by definition), any differences are in the contribution margin (hence the name of the variance). The equation is generally shown as the difference between the Static and the Flexible Budget contributions to margin, but the difference between net incomes is the same as the difference in the contribution margin, so you can calculate it in whichever way makes the most sense to you and your management team.
[FIGURE 1 OMITTED]
In order to get the most information out of a Contribution Margin Volume Variance, we need to break it down. The first cause of any differences is probably the sales mix. Standard costing and cost-volume-profit analysis assume that sales mix is constant. This assumption allows us to use a weighted average contribution margin to analyze the effect of various situations on our profits. But it's very unlikely that the sales mix will actually stay constant. To see the effects of this assumption, start by calculating the weighted average contribution margin by dividing the total budgeted contribution margin by estimated total units sold. For our example, we performed this calculation on a new Sales Variances tab, shown in Figure 2.
[FIGURE 2 OMITTED]
Using the weighted average contribution margin lets us get a feel for what caused the Contribution Margin Volume Variance because breaking the total variance into two smaller variances gives us a better picture of what happened during the period. The first of these variances is the Sales Volume Variance, shown in Figure 3, which allows us to see how overall sales contributed to a drop in Bob's budgeted net income. We had originally expected Bob's to sell 16,486 basic bikes, but the company really sold 17,074. They sold almost 500 more basic bikes than expected, and that's good. If we multiply the difference between anticipated and actual sales units by the weighted average contribution margin, we get the dollar amount of this change. For basic bikes, when we subtract 16,486 from 17,074, we get a favorable unit variance of 588 units. To get the dollar value, we multiply the 588 units by the weighted average contribution margin of $55.64 for a total of $32,694. But the deluxe bikes didn't fare so well: 8,356 actually sold, so when we subtract the 8,620 expected sales, this yields an unfavorable unit variance of 264 bikes, and when we multiply that by the weighted average contribution margin, we get an unfavorable variance of $14,704 ($14,688 if you round to the nearest penny). The sum of these two variances is a positive $17,990, a favorable variance. Essentially, this is telling us that since Bob's sold 323 more bikes than planned, if the sales mix had stayed constant, Bob's would have made $17,990 more than planned. But that isn't what happened.
[FIGURE 3 OMITTED]
To figure out why net income is lower than planned, we need to calculate the other part of the Contribution Margin Volume Variance, the Sales Mix Variance. This variance allows us to see how the change in sales mix affected Bob's budgeted net income. Figure 4 shows the calculations. First, calculate Bob's expected sales mix by taking the budgeted unit sales for each model and dividing by the total number of units Bob's planned on selling. The sales mix shows that Bob's has an expected sales mix of about 66% basic bikes (16,486/25,107) and 34% deluxe bikes (8,620/25,107). Next, multiply those percentages by Bob's actual total sales of 25,430 units. This shows the number of each model that Bob's would have sold if the sales mix had stayed constant. This is the "Flexible Budget Volume," or the number of each model Bob's would have sold if the budgeted sales mix had actually occurred. (Remember: On the Flexible Budget, everything remains at standard except volume.) Next, take the actual number of units that Bob's sold for each model and subtract the Flexible Budget Volume (for example, 17,074 - 16,699 for basic bikes), and that yields the Sales Mix Variance in units. In this case, Bob's sold 375 more basic bikes than its Flexible Budget predicted at this volume level. You'll always have at least one favorable variance and at least one unfavorable variance when investigating sales mix, and the sum of these unit variances will always equal zero. If we had three or more products, the equations would be more complicated, but the results would still follow these rules.
[FIGURE 4 OMITTED]
This is a long process, but we're almost finished. As we did before, we want to turn the unit variances into dollar amounts. When we calculated the Sales Volume Variance, we multiplied both unit variances by the weighted average contribution margin. This time, however, we multiplied each unit variance by the budgeted contribution margin of that unit. You can see this calculation in the bottom right corner of Figure 4. Now we start to get a feel for why Bob's total Contribution Margin Volume Variance was negative. While we show a $9,175 increase because of the extra number of basic bikes that Bob's sold, the company lost out on more than $43,000 by selling fewer deluxe bikes. In other words, the company's sales mix shifted from the high-margin units to the low-margin units, dropping its overall contribution margin. Our analysis, then, demonstrates that Bob's net income will fall below the Static Budget income by more than $30,000 because its sales mix was off!
As depicted in Figure 5, the sum of the Sales Volume Variance and the Sales Mix Variance is the Contribution Margin Volume Variance. But that isn't just a number. We understand where it comes from and where Bob's management can focus its attention in order to improve performance in the future. Bob's had a big drop in income because its sales mix shifted to its less profitable units, and even though some of that loss was offset by higher-than-expected total sales, Bob's management needs to try to shift the sales mix back toward Bob's high-end units if they want to improve Bob's bottom line.
[FIGURE 5 OMITTED]
The Effects of Market Variances
For many managers, the next step would be to get angry at the sales department for not pushing the more profitable product or at the budget team for setting unrealistic expectations (or perhaps some combination of the two). But the next step should actually be to dig deeper into the causes of these variances. Too often managers and other business leaders do a cursory or high-level variance analysis and then start trying to fix the problem. But we don't yet know enough about Bob's situation for the managers to start fixing anything. A true variance analysis will start with the big picture and then try to dig a little deeper or a little wider to figure out what caused the change in sales mix and what allowed the company to improve the total number of units sold. These questions aren't answered easily, but we can look at two additional pieces of information to glean some preliminary answers.
The first piece of information we need for this analysis actually comes from outside our business. We need to figure out both the budgeted number of units sold and the actual number of units sold in our market. This information typically can be found in industry publications and other public sources, but it will depend on the product you're selling and your industry. For the sake of our example, let's suppose that we found estimates by industry experts that 2,500,000 bikes would be sold in 2010 in Bob's market. Since Bob's had planned to sell a total of only 25,107 bikes, the sum of both models in Bob's Static Budget, the company had anticipated an approximate market share of 1% (25,107/2,500,000). Let's assume that, at the end of the period, 2,675,000 were actually sold in the market during 2010, so Bob's actual market share was just under 1%. We've added this calculation to the other variances on our Sales Variances tab in Excel (see Figure 6).
[FIGURE 6 OMITTED]
By subtracting the estimated sales from the actual sales, we see that the overall bicycle market in Bob's area was better than anticipated. Overall, 175,000 additional bicycles were sold during the period. Yet if we subtract Bob's expected market share from Bob's actual market share, we see that Bob's actually lost ground to its competitors. It will take some dedicated market research to figure out why the market went up while Bob's share went down, but for now we can use these figures to find out how each of these differences affected Bob's anticipated profits. More specifically, we can calculate two additional variances that will help us better understand what caused Bob's Sales Volume Variance.
The first of these variances is the Market Size Variance. To find this variance, multiply the 175,000 additional units sold in the market by Bob's expected market share. This gives you the total number of additional units Bob's would have sold if it had maintained its budgeted market share. Based on this calculation, Bob's sales should have been 1,700 units higher than it actually was. If we multiply that number by the weighted average contribution margin, we get Bob's Market Size Variance, our estimate of how much the change in the overall market size should have changed Bob's net income. As you can see in Figure 6, the larger market size should have increased Bob's profits by nearly $98,000! What went wrong?
To answer that question, we move on to another variance, the Market Share Variance. Our earlier calculation showed that Bob's market share dropped from 1% to 0.95% during the period. To estimate the net income effect of that drop, we multiply the total actual sales in the market (2,675,000) by the drop in market share (-0.054%). This gives us the number of sales that Bob's missed. That result, 1,434 units, multiplied by Bob's weighted average contribution margin provides the Market Share Variance--and that's where we find Bob's problem. Because Bob's lost market share, it lost out on nearly $75,000 of that $98,000 it could have made. As a final note, the sum of these two market variances is $17,990, which is Bob's Sale Volume Variance.
Our analysis so far has shown that Bob's has two large unfavorable variances that reduce its net income: the $34,000 that Bob's lost because of a shift in sales mix toward its lower-end products and the $75,000 Bob's lost because it dropped market share. These two results give the company something concrete it can analyze and investigate. What kind of advertising does the company need to do to gain back its market share? How can Bob's provide incentives to sell more high-end bikes? What focus should the company have moving forward? How should its sales mix change based on demand and its need to regain market share? Should it consider price changes to make the deluxe bike more competitive or to make the basic bike more profitable? What changes did the company make in its marketing, or what changes did its competitors make in their marketing? Did the quality of Bob's deluxe bikes go down? Did the competitors' quality go up? These questions will provide Bob's management team with a starting place for gathering the information it needs to regain market share and improve its cash flows. And it all comes from the budgeting process. Isn't that cool?
Continuing the Variance Analysis
By adding the Sales Variances tab, calculating the Contribution Margin Volume Variance, and then breaking it down into its component parts, we've been able to analyze and pinpoint some of the most important challenges Bob's Bicycles faced. But we've only just scratched the surface of what a good benchmarking or variance analysis can really do. With each step, we added more to what our budgets do for us, more to what they provide to our companies and managers. The Master Budget that we so carefully crafted last year is a powerful tool for planning the future. Now we're adding the tools that will allow it to also be a powerful tool for examining the past and improving the future. Next month we'll be back to build a Contribution Margin Income Statement that reflects actual results and to discuss how to use that final piece of the budget to dig into operating costs, finding out what led to the increases and decreases in operating performance. Until then, Happy Budgeting!
Note: A copy of the example spreadsheet, including all the formulas, is available from either author. IMA members can access all previous articles in the first series via the IMA website at www.imanet.org after logging in.
By Teresa Stephenson, CMA, and Jason Porter
Teresa Stephenson, CMA, Ph.D., is associate professor of accounting at the University of Wyoming and is a member of IMA's Denver-Centennial Chapter. You can reach her at (307) 766-3836 or email@example.com.
Jason Porter, Ph.D., is assistant professor of accounting at the University of Idaho and is a member of IMA's Washington Tri-Cities Chapter. You can reach him at (208) 885-7153 or firstname.lastname@example.org.