Download Analyzing Variances: Direct Labor & Materials for Bob's Bicycles and more Schemes and Mind Maps Finance in PDF only on Docsity!
This is the third and final article in our series describ- ing 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 operat- ing variance, but we’ll look at some of the most impor- tant 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 S e p t e m b e r 2 0 1 1 (^) I S T R AT E G I C F I N A N C E 4 5
BUDGETING
Calculating
Operating Variances
P a r t 3 o f 3
Completing a Benchmarking Analysis with Your Excel-based Master Budget
By Jason Porter and Teresa Stephenson, CMA
B
udgeting. Once the year is over, company leaders often think that the budget no longer serves a pur-
pose. 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.
their original Master Budget. 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 con- sists of numbers pulled directly from Bob’s Master Bud- get. 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 cate-
4 6 S T R AT E G I C F I N A N C E (^) I S e p t e m b e r 2 0 1 1
BUDGETING
Figure 1: CM Income Statements
actually sold (found in the Actual Results column on the CM IS tab). Row 6 of Figure 3 calculates the products that form the basis of the variance calculation. First, we multiply AP times AQ, then AQ times SP, and finally SP times EQ. Then we calculate the Direct Labor Price and Quantity Variances by subtracting the products as shown in row 8 of Figure 3. The final number, in cell R11, is Bob’s total direct labor variance. This final value can be calculated two ways. You can either subtract the actual cost (AP 5 AQ) from the standard cost (SP 5 EQ), or you can add up the price and quantity vari- ances. All of these equations are shown in formula view in Figure 4. A more detailed explanation of the math underly- ing the variance calculations is available from either author. In our example spreadsheet, we set up the equations for all of the different inputs so that they are automatical- ly calculated each year when we input Bob’s budgeted and actual cost, price, and unit values. As part of setting up these equations, we added a simple if/then statement in Excel to automatically report if each variance is favorable or unfavorable. The statement we used looks like this: =IF(Q8<0,“unfavorable”,“favorable”), which means if the variance is negative, show the word “unfavorable”; if the variance is zero or positive, show “favorable.” You can see
examples of this equation in Figure 4. Bob’s Labor Rate (or price) Variance is $40,420 unfa- vorable (Figure 3). This makes sense because Bob’s actual hourly wage was $0.52 higher on average than expected. When the management team talked to the production manager, they found out that there was a great deal of turnover, so rising starting wages were probably the cause of the higher average wage rate. But another explanation could be that the inexperienced workers needed to put in overtime in order to complete orders on time. Rising wages would need to be reflected in next year’s budget, but overtime that was the result of lack of experience would need to be monitored and eliminated as soon as possible. Bob’s Labor Efficiency (or quantity) Variance is also unfavorable, but it’s only $22,834 (Figure 3). This vari- ance was caused by the more than 1,600 extra hours used to produce Bob’s bikes during 2010. Again, this variance probably occurred because of extra hours caused by high turnover either in the form of overtime to complete the bikes or learning-curve hours. Either way, it’s a number worth watching in the coming year. This examination of causes is the true benefit of vari- ance analysis! It allows you to figure out things that are
BUDGETING
Figure 4: Direct Labor Variances in Formula View
4 8 S T R AT E G I C F I N A N C E (^) I S e p t e m b e r 2 0 1 1
Figure 3: Direct Labor Variances
going on in the company that otherwise may never cross your desk. This gives you the opportunity to follow through on operating “inefficiencies,” to ensure that the production manager is given the support needed to train the new employees well enough to shorten the learning curve, and to update the budget so next year’s predictions are more accurate. Over time, as the line workers’ effi- ciency increases, Bob’s will be able to reduce its budgeted number of hours needed to assemble a bike.
Calculating and Investigating Direct
Materials Variances
Direct materials variances are calculated in much the same way as direct labor variances. When creating a Direct Materials Budget as part of the Master Budget (March 2010), most companies base their estimates on the specific units needed for each item they produce. In practice, however, companies typically don’t track their direct materials inventory for each production model. The information should be part of a company’s cost of goods sold (COGS) calculations, so with a little digging you can find what you need. Luckily, you don’t really need to start with a lot of detailed information in a typi- cal variance analysis. You can start with the overall cost of each part and how many parts were used in production compared to how many were budgeted to be used. Only if this general analysis isn’t sufficient to improve your pro- duction process would you need to dig into separate vari- ances for each item produced. To begin, then, you just need to know the total amount of materials actually used during the year, which you can easily calculate by adding beginning direct materials inventory and net purchases and subtracting ending direct materials inventory. The result for Bob’s Bicycles can be seen in the Total Direct Materials line of our Actual Results column on the CM IS tab ($2,591,693). If we subtract that number from the Flexible Budget amount, we see that Bob’s used
$144,395 more in raw materials than it should have need- ed to produce 17,074 basic bicycles and 8,356 deluxe bicycles. Figure 5 shows this calculation on the Cost Vari- ances tab of our Excel spreadsheet. Bob’s direct materials variance is unfavorable because the company spent more buying raw material than planned. Be careful, however, not to interpret favorable variances as “good” and unfavorable variances as “bad.” An unfavorable variance may be caused by a variety of rea- sons, including an out-of-date budget, inexperienced labor, rising input prices, or even a sudden increase in demand leading to overtime. A favorable variance could be caused by dropping prices, a change in demand, or a failure to perform maintenance, which might lower variable manu- facturing overhead in the short run but lead to huge costs in future years. Similarly, bookkeeping errors can cause variances. The goal is to find the differences between actual and budgeted spending, break the differences into smaller pieces, investigate them, and find the causes. Not until you get to the actual causes can you be sure if a variance is “good” and should be incorporated into your business model or “bad” and should be eliminated. Since Bob’s uses 10 different materials to produce the two types of bicycles it carries, we really need to break its materials variance into at least 10 individual sets of calcu- lations (more if the company wanted to split it out by model). This may seem like a lot of information, but it will provide the detail that management needs to figure out why Bob’s spent $144,395 more than it had planned. Using an Excel-based budget, we can easily automate this process. We have the standard number of parts per bicy- cle and cost per part in the Master Budget. The only numbers we need from Bob’s actual records are how many units of each part the company used in production and the actual costs of those parts. Because we need addi- tional information, we added the actual results to the Cost Variances tab (shown in Figure 2). S e p t e m b e r 2 0 1 1 (^) I S T R AT E G I C F I N A N C E 4 9
Figure 5: Direct Material Overall Variance
In calculating the remaining direct materials variances, we used the same format. For some items that are used in only one model of bike, however, such as rubber handles and regular seats, we had to be sure that the expected quantity (EQ) included only the model that used the material. As an example, let’s take a quick look at the direct materials variance for Bob’s expanded gear shift. This gear shift is used only in the production of deluxe bikes. If you look at the calculations in Figure 6, you can see that it, too, had an unfavorable quantity variance, but it had a favorable price variance. Bob’s management team discovered that the production manager changed vendors because she found a start-up company willing to give Bob’s a price break in exchange for a long-term contract. Again, Bob’s will need to use the lower cost of this new item in its next budget. Bob’s ended up with unfavorable quantity variances for all raw materials primarily because of its new workforce. Upper management should monitor the situation careful- ly to make sure that this waste disappears as the training period ends. If it doesn’t, the company will need to con- sider other explanations—shrinkage, for example. It isn’t comfortable to suspect that one of your employees is tak- ing home raw materials for personal use, but persistent unfavorable quantity variances can uncover just such a problem. Alternatively, perhaps the “recipe” for raw mate- rial usage is wrong and needs to be updated. In Bob’s case, though, it’s hard to see a bike taking, for example, more than one seat!
Other Variances
Probably the best part about working with cost variances is their similarity. Although the titles and interpretations change, the equations stay the same. Because they are so similar, we aren’t going to specifically calculate Bob’s overhead variances. Instead, we’ll just mention three important facts to keep in mind. First, the Variable Over- head Quantity Variance measures the efficiency of the allocation base or cost driver, not how well variable costs were used. Second, because there isn’t an explicit “recipe” of overhead inputs to outputs, determining the cause of overhead variances typically takes some work, so it’s best to focus on larger variances. Third, while you can occa- sionally fire a salaried employee or find a cheaper office space to rent, most Fixed Overhead Variances require a change in future budgets.
Keep Your Old Budget
Even as you start preparing a new budget for a new year,
your old budget doesn’t have to stop being useful. Once the year is over, you can use your old budget to examine how your business lived up to the plan that you and your colleagues so carefully crafted before the year began. This series of articles demonstrated some of the analyses that you can do with an Excel-based Master Budget after the year ends. We started by creating a Contribution Margin Income Statement and calculating the breakeven point and margin of safety for our exam- ple company, Bob’s Bicycles. By adding this tab to your Master Budget, you can have those useful pieces of information at your fingertips from day one of the plan- ning process. In the second article, we added a Flexible Budget to the spreadsheet and used it to investigate how the contribu- tion margin was affected by changes in unit sales and to calculate the contribution margin variance, sales volume, and sales mix variances for Bob’s Bicycles. These slices of information can point a company in the right direction to find out why the business didn’t meet its budgeted expectations or how to keep doing something that worked better than expected. In this article we added actual results to the spread- sheet and walked through some of Bob’s operating vari- ances. We then talked about the interpretation of those variances and how to use them to improve next year’s budget and operating results. We hope that adding these new tabs and calculations to your Master Budget will provide you with a working Excel-based budget for your company that will not only allow you to quickly and easi- ly update your projections from year to year but will also help you plan for the future, investigate the past, and track your successes. If you have questions, please let us know, but until we hear from you, Happy Budgeting! SF
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 [email protected].
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 protected].
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. S e p t e m b e r 2 0 1 1 (^) I S T R AT E G I C F I N A N C E 5 1
