BUDGETING Part 3 of 6
Excel-Based Budgeting for Indirect Costs Creating Linked Budgets for Overhead and S&A Expenses By Jason Porter and Teresa Stephenson, CMA
Budgeting. Sometimes the more effort we put into it, the less people appreciate our efforts! But there’s a way to make a budget that can be used and appreciated. This is the third in a series of six articles describing how to use Excel to create such a budget. Although it will take a bit of effort to put together your first Excel-based Master Budget, making this type of budget will provide you with a powerful tool that you can quickly and easily update in future years. Not only that, but we plan to show you how to use this spreadsheet tool to see how small changes in your business will make big changes to your bottom line, something all the managers and supervisors in your company should appreciate. First, though, we need to continue creating the budget. In Part 1, we started our Excel-based Master Budget with the Data Input Sheet, the foundation of the rest of 34
S T R AT E G I C F I N A N C E
the budget. We also discussed the creation of the Sales Budget and the Cash Collections Schedule. Part 2 took us through the first part of the production process: the Production Budget, the Direct Materials Budget, the Schedule of Cash Payments, and the Direct Labor Budget. Part 3 will take us through the rest of the production process by creating a Manufacturing Overhead Budget and an Ending Finished Goods Inventory Budget. We will then wrap up the foundation by creating the Selling and Administrative Budget. Is your computer ready? Here we go.
Creating the Manufacturing Overhead Budget As in the previous budgets we’ve discussed, all the information for the Manufacturing Overhead Budget is drawn
Manufacturing Overhead Budget and Schedule of Cash Payments for Overhead
from the Data Input Sheet or previous budgets. This process is the true magic of the spreadsheet-based budget process. Gathering all of your estimates and assumptions in one place lets you quickly and easily update and change your budget with just a few keystrokes. If you decide to switch to a Just-in-Time (JIT) inventory system, a quick adjustment to your desired ending inventory percentage (say from 10% to 2%) will automatically change your Production Budget, Direct Materials Budget, Direct Labor Budget, and so on, all the way through your Pro Forma Financial Statements, without requiring any additional work on your part. It’s awesome! Take a look at the Manufacturing Overhead Budget shown in Figure 1. The first row of numbers contains the direct labor hours for each quarter, which are the company’s chosen overhead cost drivers. Since the company has decided to use total direct labor hours as its cost driver, the numbers are carried forward from the Direct Labor Budget (Figure 9 in Part 2 of this series, March 2010), where they were calculated using assumptions from the Data Input Sheet (Figure 1 in Part 1 of this series, Febru-
ary 2010). Of course, different companies will want to use different cost drivers based on their own analyses. The Manufacturing Overhead Budget can easily be adjusted to suit the needs of your company. The only rule is to make sure the information for the driver you choose is on the Data Input Sheet. For example, if you were to use machine hours, you would need a place to put budgeted machine hours on the Data Input Sheet. Then, when creating your Manufacturing Overhead Budget, you would pull machine hours forward as the first line instead of labor hours. The rest of the Overhead Budget would then follow the format shown in Figure 1. The variable overhead rate and fixed overhead amounts also are found on the Data Input Sheet. Again, we reiterate the importance of pulling them from that sheet and not typing them in. Once you start typing numbers into the budgets instead of using formulas that pull information from the Data Input Sheet, you lose all the power this budget provides. Multiplying the total estimated cost drivers (direct labor hours in this case) by the variable overhead rate gives you the total variable overApril 2010
S T R AT E G I C F I N A N C E
BUDGETING Figure 2:
Formulas for the Manufacturing Overhead Budget
head for each quarter. Add to that the quarterly fixed overhead estimate, and you get total overhead cost for each quarter. When you’ve finished these basic calculations, you can easily calculate the predetermined overhead rate by dividing the final total overhead estimate for the year by the total estimated cost drivers for the period. This predetermined overhead rate, the standard direct material costs, and the direct labor costs are used to calculate the total unit cost when you create the Ending Finished Goods Inventory Budget. If your company uses departmental overhead rates, we suggest a separate Overhead Budget for each department. Likewise, if you use activity-based costing (ABC), a new budget for each activity or group of related activities would make keeping track of the process much easier. The final step when estimating overhead is creating the Schedule of Cash Payments for Overhead. Since one of the primary goals of a Master Budget is to provide estimates of cash needs, you have to separate depreciation and other noncash overhead from the total. This process is much easier to do when estimating overhead than it is in the middle of the Cash Budget (as we’ll discuss in Part 4). To create the Schedule of Cash Payments for Overhead, simply take the total manufacturing overhead reported at the top of the worksheet and deduct the noncash items. We chose to use a percentage estimate of 25% of the total in our example, as reported in the Data Input Sheet. In your company it might be best to use a set amount, a separate schedule of noncash items, values from your depreciation schedule, or a percentage of some 36
S T R AT E G I C F I N A N C E
other value. Regardless of which method you choose, be sure to set up the formula using numbers from the Data Input Sheet. Figure 2 shows the formulas for both the Overhead Budget and the Schedule of Cash Payments for Overhead. The columns for Q2 and Q4 are hidden so that you can see the formulas better, but they would be similar to the formulas shown for Q1 and Q3. By using cell references instead of typing numbers into Excel, you can make budgets look attractive without losing any information. For example, notice that in Figure 1 the predetermined overhead rate is displayed as $5.03, a nice, neat number. In reality, the rate is a little more than $5.02612, not a fun number to use when doing manual calculations. Of course, the alternative leads to the rounding effects everyone is familiar with (troubles balancing, high estimates, low estimates, etc.). But using an Excelbased budget gives you the best of both worlds. You can easily set your formatting option to round your overhead rate to two or three decimal places without changing the true value. As long as you reference the cell that contains the initial equation in future budgets, such as the Ending Finished Goods Inventory Budget, Excel will use the full, unrounded number, and your calculations won’t have rounding errors. This way you get both the accuracy and the clarity that often compete in manual budgets.
Creating the Ending Finished Goods Inventory Budget Although you can easily put the various budgets together on the same Excel worksheet, we feel that placing each of
Ending Finished Goods Inventory Budget
the subsidiary budgets on a different worksheet (or tab) makes the budget easier to read and follow, especially for the nonaccounting managers and supervisors who we hope will use the information. In this case, we’ve started a new tab called the “Ending Inv Budget.” Many of the numbers in our Ending Finished Goods Inventory Budget (Figure 3) will come from the Data Input Sheet, but some of the information will also need to be gathered from the production cost budgets. Remember, in a budget we use standard amounts instead of actual, even when estimating ending inventory. For example, the number of units in ending finished goods inventory comes from the Production Budget (Figure 2 in Part 2), the cost per direct labor hour comes from the Direct Labor Budget (Figure 9, Part 2), and the predetermined
overhead rate comes from the Manufacturing Overhead Budget (Figure 1 here). In reality, this particular budget isn’t strictly necessary. We could easily incorporate all the calculations in other budgets. But as we mentioned previously, the overall goal of our Master Budget is ease of use and understanding. For that reason, we prefer to have our calculations separated into worksheets that are easy to read and understand. Back to Figure 3. First we created columns that label our data clearly. For example, Column E describes the units associated with each number. As always, the terms and quantities have been carried forward from the Data Input Sheet so that any changes will flow through automatically. For example, right now a basic bike costs $116 to produce. Suppose that the sales department decided April 2010
S T R AT E G I C F I N A N C E
BUDGETING Figure 4:
Selling and Administrative Budget
that each basic bike should include a spare tire. Go back to the Data Input Sheet and enter 3 for the standard quantity of tires under the basic bike. The change “flows” forward. The first place the change occurs is in the Direct Materials Budget (see Part 2 for examples of this budget). Originally, the company was supposed to purchase 51,139 tires for the year. Now that each basic bike needs three tires, that has updated to 68,336. The cost of a basic bike has increased to $126, which makes sense because a single tire costs $10. The value of the total ending inventory has increased as well. All of those changes happened as soon as we typed in the number and hit Enter. Isn’t that cool? Okay, go back and change the tire value back to 2; we’ll wait here for you. Once you’ve gathered the per-unit direct materials costs, you can get the total direct labor hours per bike and calculate applied overhead per bike. In our example, this information is found on the Data Input Sheet. But depending on the calculations required and the driver chosen, you might have to get the data from one of the production budgets. Just remember that those budgets 38
S T R AT E G I C F I N A N C E
gathered the data from the Data Input Sheet. The cost per direct labor hour comes from the Direct Labor Budget, and the variable overhead rate comes from the Manufacturing Overhead Budget. After gathering the cost per unit of materials and labor and the cost driver for each unit, you can calculate the total standard cost per bicycle. The final step for each product is to get the total units in ending inventory from the Production Budget and multiply the ending units by the cost per unit to get an estimated ending inventory value. For basic bikes, we show an ending inventory value of $100,495. The final result of the Ending Finished Goods Inventory Budget is the sum of all ending inventory values for all products.
Creating a Selling and Administrative Budget The final budget we’ll discuss in this article is the Selling and Administrative Budget, which is much less complicated than the production budgets. One important thing to remember, though, is that where variable costs in production are based on how many units are produced, variable
Example of a Possible Refinement Figure 5:
costs in selling and administration are usually based on how many units are sold. The logic of that takes only a minute to understand. When you produce goods, your variable costs tend to be materials and labor—things that increase as you make more and decrease as you make less. For example, one variable cost for the bike manufacturer could be tires. More bikes made means more tires used. Yet regardless of how many bikes Bob’s makes, commissions— the quintessential variable selling expense—are paid on bikes sold. If Bob’s made a million bikes but didn’t sell any, the selling commission would still be zero! Figure 4 shows the Selling and Administrative Budget for Bob’s Bicycles. Our first step was to bring forward the estimated sales of each type of bike. Then we brought forward the variable selling cost rates. It’s a simple matter to multiply those quantities to show the total estimated variable costs. Next, we brought forward the fixed costs, dividing them by four to show the quarterly amounts instead of the annual amounts reported in the Data Input Sheet. Our example budget is fairly simple, but endless variations are possible in practice. For instance, suppose you anticipated that your office rent would increase 10% each quarter. Your Data Input Sheet would then show a base amount and the estimated amount of the increase. Take a look at Figure 5, where we’ve shown how to do this, but remember this isn’t part of our example bike company budget. Once the information is in the Data Input Sheet, using that data in the actual budget tab is fairly straightforward. Notice that we used three cells for the statement: “with a 10% increase each qtr.” The words to the left and the right of the 10% were isolated so that the increase percentage is in its own box and can be used in a formula. The Selling and Administrative Budget for the first quarter would use the $3,000 from the cost cell. The second
quarter would pick up the quantity on the Selling and Administrative Budget for the first quarter and multiply it by one plus the percentage from the Data Input Sheet. By making that a permanent reference (using F4), you can copy the equation to the third and fourth quarters. When this is done correctly, the amounts for Q1 through Q4, respectively, will be $3,000, $3,300, $3,630, and $3,993. You can also estimate specific dollar amounts per quarter or specific dollar increases per quarter, whatever best fits your company’s business model.
Moving On The first article in this series discussed the importance of budgets, the usefulness of an Excel-based budget, the Data Input Sheet, and the Sales Budget. The second article showed how to create the Production, Direct Materials, and Direct Labor budgets. This article continued the series by discussing the creation of the Manufacturing Overhead Budget, the Ending Finished Goods Inventory Budget, and the Selling and Administrative Budget. This completes all of the foundation budgets involved in the Master Budget. We encourage you to take these basic tools and apply them to your business. A good way to start is to recreate our example step by step, check your flow through, then save your work as a basic template. You can then use a copy of your template as the starting point to create a Master Budget for your company, one budget at a time. Now that we’ve finished the basic budgets, we can move on to discuss the Cash Budget and Pro Forma Financial Statements. These final segments of the Master Budget gather the information from the basic budgets to provide decision-making estimates. And that’s the process we’ll start in Part 4. Until then, 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) 8857153 or [email protected]
Teresa Stephenson, CMA, Ph.D., is assistant 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. April 2010
S T R AT E G I C F I N A N C E