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

The rest of this article is only available to active members of Questia

Already a member? Log in now.

Notes for this article

Add a new note
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
Cite this article

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.

Note: primary sources have slightly different requirements for citation. Please see these guidelines for more information.

Cited article

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

Settings

Typeface
Text size Smaller Larger Reset View mode
Search within

Search within this article

Look up

Look up a word

  • Dictionary
  • Thesaurus
Please submit a word or phrase above.
Print this page

Print this page

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

Help
Full screen
Items saved from this article
  • Highlights & Notes
  • Citations
Some of your highlights are legacy items.

Highlights saved before July 30, 2012 will not be displayed on their respective source pages.

You can easily re-create the highlights by opening the book page or article, selecting the text, and clicking “Highlight.”

matching results for page

    Questia reader help

    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.

    OK, got it!

    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.

    Cited passage

    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.

    Buy instant access to save your work.

    Already a member? Log in now.

    Search by... Author
    Show... All Results Primary Sources Peer-reviewed

    Oops!

    An unknown error has occurred. Please click the button below to reload the page. If the problem persists, please try again in a little while.