CHAPTER 6 Using Excel to Make Decisions at Current Designs Topic(s): Cost-Volume-Profit Analysis for Multiple Products; Operating Leverage Excel Functions and Tools: PRODUCT function; ROUND function Current Designs manufactures two different types of kayaks, rotomolded kayaks and composite kayaks. The following information is available for each product line. Sales price/unit Variable costs/unit Rotomolded $950 $570 Composite $2,000 $1,340 The company's fixed costs are $820,000. An analysis of the sales mix identifies that rotomolded kayaks make up 80% of the total units sold. Use sheet tab Part 1(a-b) 1. (a) Determine the weighted average unit contribution margin for Current Designs. (b) Determine the break-even point in units for Current Designs and identify how many units of each type of kayak will be sold at the break-even point. (Round to the nearest whole number.) Use sheet tab Part 1(c) (c) Assume that the sales mix changes, and rotomolded kayaks now make up 70% of total units sold. Calculate the total number of units that would need to be sold to earn a net income of $2,000,000 and identify how many units of each type of kayak will be sold at this level of income. (Round to the nearest whole number.) Use sheet tab Part 1(d-e) (d) Assume that Current Designs will have sales of $3,000,000 with two-thirds of the sales dollars in rotomolded kayaks and one-third of the sales dollars in composite kayaks. Assuming $660,000 of fixed costs are allocated to the rotomolded kayaks and $160,000 to the composite kayaks, prepare a CVP income statement for each product line. (e) Using the information in part (d), calculate the degree of operating leverage for each product line and interpret your findings. (Round to two decimal places.) WHAT-IF? Use sheet tab Part 2. 2. Suppose Current Designs' sales mix percentages shift and now the company is selling four rotomolded kayaks to each composite kayak sold. Assume the total sales revenue remains at $3,000,000 and the fixed costs are allocated in the same manner as part 1 (d). Calculate the new degrees of operating leverage for both products. Did Current Designs become more or less risky? Explain. Chapter 6: Using Excel to Make Decisions at Current Designs Part 1 (d, e) Data Total $3,000,000 Sales revenue Variable costs/unit Allocated fixed costs Sales mix: Portion of sales dollars Rotomolded Composite $950 $2,000 $570 $1,340 $660,000 $160,000 $820,000 66.67% 33.33% Required Follow the step-by-step directions to perform cost-volume-profit analysis for multiple products. (d) Rotomolded Composite Sales Variable costs Contribution margin Fixed costs Income (e) Degree of operating leverage Interpretation: Chapter 6: Using Excel to Make Decisions at Current Designs Part 2 Data Total $3,000,000 $950 Sales revenue Variable costs/unit Allocated fixed costs Sales mix: Portion of sales dollars Rotomolded Composite $2,000 $570 $1,340 $660,000 $160,000 $820,000 Required Follow the step-by-step directions to perform cost-volume-profit analysis for multiple products Rotomolded Composite Sales Variable costs Contribution margin Fixed costs Net income 660,000 160,000 $ (660,000) $ (160,000) Degree of operating leverage CHAPTER 6 Using Excel to Make Decisions at Current Designs Topic(s): Cost-Volume-Profit Analysis for Multiple Products; Operating Leverage Excel Functions and Tools: PRODUCT function; ROUND function Current Designs manufactures two different types of kayaks, rotomolded kayaks and composite kayaks. The following information is available for each product line. Sales price/unit Variable costs/unit Rotomolded $950 $570 Composite $2,000 $1,340 The company's fixed costs are $820,000. An analysis of the sales mix identifies that rotomolded kayaks make up 80% of the total units sold. Use sheet tab Part 1(a-b) 1. (a) Determine the weighted average unit contribution margin for Current Designs. (b) Determine the break-even point in units for Current Designs and identify how many units of each type of kayak will be sold at the break-even point. (Round to the nearest whole number.) Use sheet tab Part 1(c) (c) Assume that the sales mix changes, and rotomolded kayaks now make up 70% of total units sold. Calculate the total number of units that would need to be sold to earn a net income of $2,000,000 and identify how many units of each type of kayak will be sold at this level of income. (Round to the nearest whole number.) Use sheet tab Part 1(d-e) (d) Assume that Current Designs will have sales of $3,000,000 with two-thirds of the sales dollars in rotomolded kayaks and one-third of the sales dollars in composite kayaks. Assuming $660,000 of fixed costs are allocated to the rotomolded kayaks and $160,000 to the composite kayaks, prepare a CVP income statement for each product line. (e) Using the information in part (d), calculate the degree of operating leverage for each product line and interpret your findings. (Round to two decimal places.) WHAT-IF? Use sheet tab Part 2. 2. Suppose Current Designs' sales mix percentages shift and now the company is selling four rotomolded kayaks to each composite kayak sold. Assume the total sales revenue remains at $3,000,000 and the fixed costs are allocated in the same manner as part 1 (d). Calculate the new degrees of operating leverage for both products. Did Current Designs become more or less risky? Explain. Chapter 6: Using Excel to Make Decisions at Current Designs Part 1 (d, e) Data Total $3,000,000 Sales revenue Variable costs/unit Allocated fixed costs Sales mix: Portion of sales dollars Rotomolded Composite $950 $2,000 $570 $1,340 $660,000 $160,000 $820,000 66.67% 33.33% Required Follow the step-by-step directions to perform cost-volume-profit analysis for multiple products. (d) Rotomolded Composite Sales Variable costs Contribution margin Fixed costs Income (e) Degree of operating leverage Interpretation: Chapter 6: Using Excel to Make Decisions at Current Designs Part 2 Data Total $3,000,000 $950 Sales revenue Variable costs/unit Allocated fixed costs Sales mix: Portion of sales dollars Rotomolded Composite $2,000 $570 $1,340 $660,000 $160,000 $820,000 Required Follow the step-by-step directions to perform cost-volume-profit analysis for multiple products Rotomolded Composite Sales Variable costs Contribution margin Fixed costs Net income 660,000 160,000 $ (660,000) $ (160,000) Degree of operating leverage