Boost Profits with Excel: Solver Calculates the Most Cost-Effective and Profitable Product Mix
James A. Weisel · Journal of accountancy online/Journal of accountancy · 2003
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. …