Boost Profits With Excel

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

Related Articles

Check Also
Back to top button
Stay up to date
Don't miss out on the latest industry news and articles
Stay up to date
Don't miss out on the latest industry news and articles
You are Subscribed!
Your subscriptions means a lot to us.
Don't miss out on the latest Industry news
You are Subscribed!