our company CEO or client asks you to figure out
a way to maximize profits. Specifically, he wants
to know the most profitable product mix, whether
the company has the capacity to meet demand with
that mix and the value of adding capacity.Using paper and a pencil, you can
calculate the answers in a few hours. Or, if you
use Excel’s Solver, you can
produce not only one analysis but several with
multiple options—in just a few minutes.
To find out how Solver
can perform a wide variety of tasks, follow along
as we explore a practical business question about
calculating the best product mix for a fictitious
company, Southern Frozen Foods, which produces
three frozen-food product lines: sauces, soups
and casseroles.
| The company makes sauces and
soups in five-pound boil-in-bag packages
and casseroles in four-pound aluminum
pans. The product lines vary
significantly in their consumption of
machine and labor time. I’ve
prepared a basic spreadsheet (exhibit
1) to
illustrate how to determine the most
profitable product mix. To download it,
go to http://www.aicpa.org. The spreadsheet includes
monthly budgeted sales volume, revenues,
variable cost and contribution margin by
product line. The totals are found in
cells C5 to J9. Volume,
price per case, variable cost per case
and total fixed costs are specified as
numerical values. All other elements of
the income statement contain the
appropriate cell references and formulas.
Fixed costs consist of
machine-time-related costs ($7,000),
labor-time-related costs ($10,000) and
general & administrative (G&A)
expenses ($3,000). Machine-time- and
labor-time-related costs are allocated
based on product-line standard operating
data and resource utilization. For
example, sauces consume 12 minutes of
machine time per case and budgeted sales
volume is 200 cases, thus total
machine-time utilization is D20=D5*D16. Machine-time-related fixed
costs are specified as D10=D20/J24*J10. Create similar formulas for
the remaining product lines as well as
for labor-time-related fixed product
costs.
|
More on the
Theory of Constraints
The theory of
constraints (TOC), developed by
Eliyahu M. Goldratt and Jeff Cox,
states that every for-profit
organization is faced with
factors—limits on production
rates, raw materials, labor cost
and availability, for
example—that restrict its
ability to earn maximum profits.
The TOC identifies such limits as
constraints. Thus the manager who
wants to maximize profits must
find ways to manage these
constraints to produce, say, the
most cost-effective mix.
The TOC is
sufficiently flexible to be used
in a wide variety of businesses
including manufacturing,
service-based retail and
not-for-profit.
Suggested reading
on the topic:
 The
Goal: A Process of Ongoing
Improvement, 2nd ed., by
Eliyahu M. Goldratt and Jeff Cox,
North River Press, Great
Barrington, Massachusetts, 1992.
“Theory of Constraints (TOC)
Management System
Fundamentals,” Statement on
Management Accounting 4HH,
Institute of Management
Accountants, Montvale, New
Jersey, 1999.
|
|
Since these
formulas link the operating data to the
resource-utilization information and income
statement, any changes in sales volumes will be
appropriately reflected in the income statement
and resource utilization figures.
| We also can measure the unused
capacity, both in terms of minutes and
costs. Given Southern’s budgeted
level of activity, it has 16,600 minutes
of unused machine-time activity. The
formula for actual time used is J20=SUM(D20:H20) and for unused machine time it
is I20=J242J20. The cost of unused capacity in
the income statement is computed as I10=J102SUM(D10:H10). DETERMINE
CONSTRAINTS
Solver
applies the theory of constraints
(TOC)—a management tool that
analyzes the bottom-line impact of
production and marketing limitations.
Such limitations are called constraints.
For further information on the TOC, see
“More on the Theory of
Constraints.”
|
|
In this exercise I will
identify the constraints within which Southern
Frozen Foods must operate. In this case it has
40,000 minutes of machine time and 50,000 minutes
of labor time available each period.
Additionally, the company serves a limited
market: As a result, it cannot expect to sell
more than 500 cases of sauces, 400 cases of soup
and 700 cases of casseroles.
| The budgeted income statement
shows Southern generates a monthly loss
of $800, with a product mix of 200 cases
of sauces, 300 soups and 500 casseroles.
Standard contribution margin analysis
suggests that casseroles, at $22
contribution margin per case, are the
most profitable product. Sauces generate
$14 per case and soups $18. However, since each product
consumes different manufacturing time, we
have to assess how efficiently each
product generates income—and
that’s where Solver
shows its muscle by relating operating
data and constraints to the income
statement and then calculating the
company’s most profitable product
mix, whether it has the capacity to meet
demand and the value of adding capacity.
To load Excel’s Solver,
go to Tools and click on
Solver to bring up the Solver
Parameters dialog box (see exhibit
2). Note: If
Solver is not in the
menu, see “Are You Missing the Solver Tool?” at right.
|
Are You Missing
the Solver Tool?
Depending on how
you or your technology staff
installed Excel on your PC, Solver
may or may not appear in your
drop-down Tools
menu. That’s because,
although the necessary files are
there, they haven’t been
activated.
To launch them go
to Tools and Add-ins
and then scroll down the Add-ins
list and check Solver
Add-in and then click on
OK.
If Solver
is missing from the Add-ins
list, then you have a little more
work to do. Close Excel and
insert your Microsoft Office
CD-ROM. Once the CD is running,
select Add or Remove
Features and click on
the plus sign (+) next to Microsoft
Excel for Windows. Then
click on + next to Add-ins
and on Solver
and select Run from My
Computer. Finally click
on the Update Now
button.
|
|
| |
|
| To establish our desire to
maximize income, we first have to set our
target cell by clicking in the Set
Target Cell box and typing J13. Be sure to select the Max
button in the Equal To
box. Since income is determined by sales
volume, click in the By Changing
Cells box and type D5, insert a comma, F5, insert a comma and H5—so it looks like this: D5, F5, H5. Now
click on Options and
check both Assume Linear Model and
Assume Non-Negative (see
exhibit 3, at right).
Click on OK
to return to the Solver
Parameters dialog box. Since we
have identified our desire to maximize
income by allowing the sales volume to
change, we must now specify the
constraints—which limit the
company’s ability to sell products.
Southern has two capacity constraints:
machine time used cannot exceed 40,000
minutes and labor time cannot exceed
50,000 minutes. To add them to the Solver
formula, click on Add
next to Subject to the
Constraints box; that opens the Add
Constraint dialog box (see exhibit
4, below).
|
|
|
| |
|
|
Click in
the Cell Reference box
and type J20.
Then click in the Constraint
box and enter J24.
Be sure the constraint is <= and click on Add.
|
|
|
| |
|
|
| Repeat
this process for the labor-time
constraint—only in this case select J21 and J25.
Click on OK to return
again to the Solver Parameters
dialog box. Your dialog box should now
look like exhibit 5 (above). You’re now ready to run the
Solver tool. Click on
the Solve button and
then click on OK in the
resulting Solver Results
dialog box (see exhibit
6, at right) to
Keep Solver Solution.
|
|
|
The income statement has
now been altered, as illustrated in exhibit 7, below.
READING THE RESULTS
As you can see,
the capacity constraints limit monthly income to
$15,000, achieved by selling 2,500 cases of
sauces and none of the remaining product lines.
As a practical matter, it’s unreasonable to
conclude that sales of sauces would jump to 2,500
cases from 200 while the remaining product lines
remain flat. But the analysis is still useful
because it illustrates that with the current
capacity constraints no other product mix will
yield as much income. We also can see that labor
time is fully used and 10,000 minutes of machine
time remains unused—telling us that labor
time is a bottleneck in the production process.
| To seek
more practical answers, we’ll add
constraints. Again launch Solver
so we can add market-share limitations.
Open the Solver Parameters dialog
box (Tools, Solver) and
click on Add. Click in
the Cell Reference box
and type D5 and
then in the Constraint
box and select D26.
Be sure the constraint is <= and click on Add. Repeat the process for the
remaining product lines: Click in the Cell
Reference box and type F5 and click in the Constraint
box and add F26.
Again, be sure the constraint is <= and click on Add.
Click in the Cell Reference
box and type H5 and
in the Constraint box
and select H26,
again being sure the constraint is <= and click on OK.
Your Solver Parameters Dialog
box should now have five constraints, as
shown in exhibit 8, at right.
|
|
|
The previous parameters
remain as specified earlier. Now click on Solve
and then on OK to Keep
Solver Solution. The resulting income
statement is illustrated in exhibit 9.
Notice that
Southern’s income is now maximized at
$9,160, with a product mix of 500, 400 and 680
cases for each of the three products. We know
from the previous analysis that sauces are the
most profitable products when considering
contribution margin and time utilization; hence
Southern meets the market-share limitation for
that product. The next most profitable products
are soups. Once again, Southern runs up against
the market-share limitation.
The analysis shows that
casseroles are the least profitable products and
that the company produces them until they meet
market-share limitations or the company runs out
of capacity. In this case Southern runs out of
labor time before reaching its market-share
limitation, and there is sufficient labor time to
produce 680 cases of casseroles.
The Solver
algorithm gives the highest priority to products
based on their contribution margin per minute of
resource consumed. While sauces’
contribution margin is 64% of casseroles’
contribution margin ($14/$22), sauces use only
40% of the machine time used by casseroles (12
minutes/30 minutes) and 50% of the labor time (20
minutes/40 minutes). Similarly, sauces’
contribution margin is 78% of soups’
contribution margin ($14/$18), but use only 60%
of the machine time (12 minutes/20 minutes) and
63% of the labor time (20 minutes/32 minutes). In
other words, sauces generate more contribution
margin per minute than either sauces or
casseroles.
ADDITIONAL
ANALYSIS
We can use the
spreadsheet to analyze other conditions as well.
For example, assume Southern can “hire”
2,000 additional minutes of labor for $500. Is
that worthwhile from an income standpoint? To
find out, enter 52000 in J25
and 10500 in J11 to reflect the changes in
labor-time capacity and total labor cost. Launch Solver
again. Since we’ve specified all of the Solver
parameters, simply click on Solve
and then on OK in the resulting Solver
Results dialog box to Keep the
Solver Solution. The resulting income
statement is illustrated in exhibit 10.
| |
|
| Note
that the additional labor time is only
partially used by increasing production
of casseroles to 700 from 680 cases. The
resulting income is $9,100, $60 less than
the $9,160 achieved in the previous
solution. Thus, while the additional
labor time allows the frozen foods
company to expand production, much of the
labor time is underutilized because of
market-share limitations. Solver, in
combination with the theory of
constraints, has grown in popularity in
the business world because, as you can
see, it’s so easy to analyze various
scenarios. Use it and you’ll find it
equally effective for your business. 
|
Resource
CPE
Lean Management:
Optimizing Capacity Management.
For more on this course, go to www.cpa2biz.com.
|
|
JAMES A. WEISEL, DBA, CPA, CMA,
is an associate professor at the Stetson School
of Business & Economics, Mercer University,
Atlanta. His e-mail address is weisel_ja@mercer.edu.
|
| HOME | ARCHIVE | CONTACT | ADVERTISE | SUBSCRIBE | AICPA 
