Question

17. Chapter 5: Applying Excel: Exercise (Part 2 of 2) 2. Change all of the numbers in the data area of your worksheet so that it looks like this: Part 2 of 2 1 Chapter 5: Applying Excel pints eBook 3 4 5 6 | 7 Data Unit sales Selling price per unit Variable expenses per unit Fixed expenses 70,000 units $ 30 per unit $ 18 per unit $ 756,000 Print References If your formulas are correct, you should get the correct answers to the following questions. (a) What is the break-even in dollar sales? Break-even in dollar sales

Answer 1

(a) What is the break-even in dollar sales? Break-Even Sales (dollars) = (Fixed Expenses / Contribution Margin Ratio) Fixed Expenses = $756,000 Contribution margin ratio = (Selling price per unit - Variable cost per unit) / Selling price per unit = ($30-$18)/ $18 Break-Even Sales (dollars) = $756,000/(($30 - $18)/$18) = $1,134,000. (b) What is the margin of safety percentage? Margin of safety percentage = Margin of safety dollars / Actual sales Margin of safety dollars = (70,000 units x $30) - $1,134,000 Margin of safety dollars = $2,100,000-$1,134,000 Margin of safety dollars = $966,000 Margin of safety percentage = $966,000/$2,100,000 = 46% (c) What is the degree of operating leverage? Degree of Operating leverage = Contribution margin / Operating Income Contribution margin = Sales x Contribution margin ratio = $2,100,000 x (($30 - $18)/ $18) = $1,400,000 Operating income = Contribution margin - Fixed expenses = $1,400,000 - $756,000 = $644,000 Degree of Operating leverage = $1,400,000 / 644,000 = 2.17 times.

3. Using the degree of operating leverage and without changing anything in your worksheet, calculate the percentage change in net operating income if unit sales increase by 20%. Using Degree of Leverage to calculate the percentage change in net operating income for a 20% increase in sales. Degree of operating leverage = (% Change in Operating Income) /% Change in Sales) => DOLX (% Change in Sales) = % Change in Operating Income = 2.17 times x 20% = 43.4% 4. Confirm your calculations in Requirement 4 above by increasing the unit sales in your worksheet by 20% so that the Data area looks like this: Contribution margin = Sales x Contribution margin ratio Sales = 84,000 units x $30 = $2,520,000 Contribution margin = = $2,520,000 x (($30-$18)/$18) = $1,680,000 a) Operating income = Contribution margin - Fixed expenses = $1,680,000 - $756,000 = $924,000 b) % increase in Net Operating Income = ($924,000 - $644,000)/$644,000 = 43.49 This confirms the answer calculated in Question 3 above.

a) What is the break-even in unit sales? Breakeven point(BEP) = Fixed cost (F)/contribution per unit(C) F= $1,140,000 C=$19,000 - $15,200 =$3,800 Therefore BEP = $1,140,000/ $3,800 =300 units. b. What is the margin of safety in dollars? = (Total units to be sold - BEP) x sale price = (400-300)x $19,000 = $1,900,000. c. What is the degree of operating leverage? = Contribution / Net Income Contribution = 400 x $3,800 = $1,520,000 Net Income = Contribution - Fixed cost =$1,520,000-$1,140,000 = $380,000 Degree of operating leverage = $1,520,000/$380,000 = 4.00 times. d. What would the net operating income be in this situation? Fixed Costs = $912,000 Contribution per unit = $17,000 - $15,200 = $1,800 Contribution = 400 x $1,800 = $720,000 Netoperating Income (loss) = Contribution - Fixed cost = $720,000 - $912,000 = ($192,000)