Boost profits with Excel: Solver calculates the most cost-effective and profitable product mix.

Your 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, page 63) to illustrate how to determine the most profitable product mix. To download it, go to http://wvw.alcpa.org/down load/pubs/Jofa/2003_are_welsel.xls. 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.

[ILLUSTRATION OMITTED]

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.

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=J24-J20. The cost of unused capacity in the income statement is computed as I10=J10-SUM(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," page 62.

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, page 63). Note: If Solver is not in the menu, see "Are You Missing the Solver Tool?" below.

[ILLUSTRATION OMITTED]

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).

[ILLUSTRATION OMITTED]

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, at right).

[ILLUSTRATION OMITTED]

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 (at right).

[ILLUSTRATION OMITTED]

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.

[ILLUSTRATION OMITTED]

The income statement has now been altered, as illustrated in exhibit 7, above.

[ILLUSTRATION OMITTED]

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.

[ILLUSTRATION OMITTED]

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, page 66.

[ILLUSTRATION OMITTED]

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 (12 minutes/20 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, page 66.

[ILLUSTRATION OMITTED]

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.

Key to Instructions

To help readers follow the instructions in this article, we use two different typefaces.

Boldface type is used to identify the names of icons, agendas and URLs.

Sans serif type indicates commands and instructions that users should type into the computer and the names of files.

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.

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 them, 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.

AICPA 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.