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 frozenfood product lines: sauces, soups and casseroles.
The company makes sauces and soups in fivepound boilinbag packages and casseroles in fourpound 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 machinetimerelated costs ($7,000), labortimerelated costs ($10,000) and general & administrative (G&A) expenses ($3,000). Machinetime and labortimerelated costs are allocated based on productline 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 machinetime utilization is D20=D5*D16. Machinetimerelated fixed costs are specified as D10=D20/J24*J10. Create similar formulas for the remaining product lines as well as for labortimerelated fixed product costs. 

Tagged with: Education 