# Boost Profits with Excel: Solver Calculates the Most Cost-Effective and Profitable Product Mix

By Weisel, James A. | Journal of Accountancy, December 2003 | Go to article overview

# Boost Profits with Excel: Solver Calculates the Most Cost-Effective and Profitable Product Mix

Weisel, James A., Journal of Accountancy

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

If you are trying to select text to create highlights or citations, remember that you must now click or tap on the first word, and then click or tap on the last word.
One moment ...
Default project is now your active project.
Project items
Notes

#### Cited article

Style
Citations are available only to our active members.
Buy instant access to cite pages or passages in MLA 8, MLA 7, APA and Chicago citation styles.

(Einhorn, 1992, p. 25)

(Einhorn 25)

(Einhorn 25)

1. Lois J. Einhorn, Abraham Lincoln, the Orator: Penetrating the Lincoln Legend (Westport, CT: Greenwood Press, 1992), 25, http://www.questia.com/read/27419298.

#### Cited article

Boost Profits with Excel: Solver Calculates the Most Cost-Effective and Profitable Product Mix
Settings

#### Settings

Typeface
Text size Reset View mode
Search within

Look up

#### Look up a word

• Dictionary
• Thesaurus
Please submit a word or phrase above.

Why can't I print more than one page at a time?

Help
Full screen
• Highlights & Notes
• Citations
Some of your highlights are legacy items.

### How to highlight and cite specific passages

1. Click or tap the first word you want to select.
2. Click or tap the last word you want to select, and you’ll see everything in between get selected.
3. You’ll then get a menu of options like creating a highlight or a citation from that passage of text.

## Cited passage

Style
Citations are available only to our active members.
Buy instant access to cite pages or passages in MLA 8, MLA 7, APA and Chicago citation styles.

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences." (Einhorn, 1992, p. 25).

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences." (Einhorn 25)

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences." (Einhorn 25)

"Portraying himself as an honest, ordinary person helped Lincoln identify with his audiences."1

1. Lois J. Einhorn, Abraham Lincoln, the Orator: Penetrating the Lincoln Legend (Westport, CT: Greenwood Press, 1992), 25, http://www.questia.com/read/27419298.

## Thanks for trying Questia!

Please continue trying out our research tools, but please note, full functionality is available only to our active members.

Your work will be lost once you leave this Web page.