Boost Profits With Excel

August 1, 2004

0 2 minutes read

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.