Selection of most profitable product. Body-Builders, Inc., produces two basic types of weightlifting equipment, Model 9 and Model 14. Pertinent data are as follows:

Per Unit Model 9 Model 14 Sales price $110.00 $77.00 Costs:

Direct materials $ 30.80 $14.30 Direct manufacturing labour 16.50 27.50 Variable manufacturing overhead* 27.50 13.75 Fixed manufacturing overhead* 11.00 5.50 Marketing costs (all variable) 15.40 11.00 Total costs $101.20 $72.05 Operating income $ 8.80 $ 4.95

‘Allocated on the basis ofmachine-hours.

The weightlifting craze is such that enough of either Model 9 or Model 14 can be sold to keep the plant operating at full capacity. The two products are processed through the same production departments.

Required Which product should be produced? If both should be produced, indicate the proportions of each. Briefly explain your answer.

Excel Application For students who wish to practise their spreadsheet skills, the following is a step-by-step approach to creating an Excel spreadsheet to work this problem.

Step-by-Step 1. Open a new spreadsheet. At top, create an “Original Data” section for the data provided by Body-Builders, Inc. Create rows for the per unit data on selling price, costs, and operating income.

(Program your spreadsheet to perform all necessary calculations. Do not “hard-code” any amounts, such as contribution margin per unit, requiring addition, subtraction, multiplication, or division operations.)

2. Skip two rows and create a “Product Mix Analysis” section. Create columns for Model 9 and Model 14 and rows for “Selling Price per Unit, Variable Cost per Unit, Contribution Margin per Unit, Relative Use of Machine-Hours per Unit of Product,” and

“Contribution Margin per Unit of Machine Time.” Use the data from your Original Data section to calculate selling price per unit, variable cost per unit, and contribution margin per unit.

3. In the “Relative output (units of product) per machine-hour” row, enter the relative output of units of product per machine-hour for Models 9 and 14, respectively. (Hint: Variable and fixed manufacturing overhead are allocated on the basis of machine-hours, and the variable and fixed manufacturing overhead costs per unit for Model 9 are twice as high as for Model 14.)

4. Enter calculations for contribution margin per unit of machine time for Models 9 and 14 by multiplying contribution margin per unit by the relative use ofmachine-hours per unit of product.

5. Check the accuracy ofyour spreadsheet. Go to your Original Data section and change the selling price of Model 14 from $77 to $75.90 per unit. If you programmed your spread¬

sheet correctly, contribution margin per unit of machine time for Model 14 should change to $18.70.