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 Mas-

ter 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

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 Pro-

duction Budget, the Direct Materials Budget, the Sched-

ule 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 ManufacturingOverhead BudgetAs in the previous budgets we’ve discussed, all the infor-

mation for the Manufacturing Overhead Budget is drawn

34 S T R AT E G IC F I N A N C E I A p r i l 2 0 1 0

BUDGETING

Excel-Based Budgeting for Indirect Costs

Creating Linked Budgets for Overhead and S&AExpenses

By Jason Porter and Teresa Stephenson, CMA

Part 3 of 6

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 per-

centage (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 addi-

tional 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 compa-

ny’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 cre-

ating 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 num-

bers 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 estimat-

ed cost drivers (direct labor hours in this case) by the

variable overhead rate gives you the total variable over-

A p r i l 2 0 1 0 I S T R AT E G IC F I N A N C E 35

Figure 1: Manufacturing Overhead Budget and Schedule of Cash Payments for Overhead

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 calcula-

tions, you can easily calculate the predetermined over-

head 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 cal-

culate the total unit cost when you create the Ending Fin-

ished Goods Inventory Budget. If your company uses

departmental overhead rates, we suggest a separate Over-

head 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 esti-

mates 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 Over-

head, simply take the total manufacturing overhead

reported at the top of the worksheet and deduct the non-

cash 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

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 los-

ing 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 round-

ing effects everyone is familiar with (troubles balancing,

high estimates, low estimates, etc.). But using an Excel-

based 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 GoodsInventory BudgetAlthough you can easily put the various budgets together

on the same Excel worksheet, we feel that placing each of

36 S T R AT E G IC F I N A N C E I A p r i l 2 0 1 0

BUDGETING

Figure 2: Formulas for the Manufacturing Overhead 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. Re-

member, in a budget we use standard amounts instead of

actual, even when estimating ending inventory. For exam-

ple, the number of units in ending finished goods inven-

tory 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 previ-

ously, 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 auto-

matically. For example, right now a basic bike costs $116

to produce. Suppose that the sales department decided

A p r i l 2 0 1 0 I S T R AT E G IC F I N A N C E 37

Figure 3: Ending Finished Goods Inventory 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

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 Manufac-

turing 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 end-

ing 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 end-

ing 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 andAdministrative BudgetThe final budget we’ll discuss in this article is the Selling

and Administrative Budget, which is much less complicat-

ed than the production budgets. One important thing to

remember, though, is that where variable costs in produc-

tion are based on how many units are produced, variable

38 S T R AT E G IC F I N A N C E I A p r i l 2 0 1 0

BUDGETING

Figure 4: Selling and Administrative Budget

A p r i l 2 0 1 0 I S T R AT E G IC F I N A N C E 39

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 varia-

tions 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 per-

centage is in its own box and can be used in a formula.

The Selling and Administrative Budget for the first quar-

ter 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 perma-

nent reference (using F4), you can copy the equa-

tion 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 OnThe 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 arti-

cle showed how to create the Production, Direct Materi-

als, 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 Washing-

ton Tri-Cities Chapter. You can reach him at (208) 885-

7153 or jporter@uidaho.edu.

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 teresas@uwyo.edu.

Note: A copy of the example spreadsheet, including all the

formulas, is available from either author.

Figure 5: Example of a Possible Refinement