Question

Chapter 5: Applying Excel The Chapter 5 Form worksheet is to be used to create your own worksheet version of the Review Pr example in the text. 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: А B C 1 Chapter 5: Applying Excel 2 نما Data 4 5 Unit sales Selling price per unit Variable expenses per unit Fixed expenses 40,000 units $ 70 per unit $ 35 per unit $ 700,000 6 7 If your formulas are correct, you should get the correct answers to the following questions, Wa What ie the branleven in dollar enlee?

Answer 1

Requirement 2 (a):

CM per Unit = Sale price - Variable cost

= $70 - $35

= $35

CM Ratio = CM margin / Sale Price

= $35 / $70

= 50%

Break even sales ($) = Fixed Cost / CM Ratio

= $700,000 / 50%

= $1,400,000

Requirement 2 (b):

Break even sales in Units = Break even sales ($) / Selling price per u nit

= $1,400,000 / $70

= 20,000 units

Margin of Safety = (Actual Sales - Break even Sales) / Actual Sales

= (40,000 units - 20,000 units) / 40,000 units

= 20,000 units / 40,000 units

= 50%

Requirement 2 (c):

Degree of Operating Leverage = Contribution Margin / Operating Income

= (40,000 units * $35) / {(40,000 units * $35) - $700,000}

= $1,400,000 / $700,000

= 2

Requirement 3:

Operating Leverage = % change in Operating Income / % Change in Sales

% Change in Operating Income = Operating Leverage * % Change in Sales

= 2 * 20%

= 40%

Requirement 4 (a):

Revised Operating Income = (units sales * CM per unit) - Fixed Cost

= (48,000 units * $35) - $700,000

= $980,000

Requirement 4 (b):

Revised Operating Income = $980,000

Initial Operating Income = $700,000

Percentage Increase in Net Operating Income = (Revised OI - Initial OI) / Initial OI

= ($980,000 - $700,000) / $700,000

= $280,000 / $700,000

= 40%

Requirement 5 (a):

CM per unit = Selling price - Variable cost

= $18,000 - $12,600

= $5,400

Break even (units) = Fixed Cost / CM per unit

= $1,728,000 / $5,400

= 320 units

Requirement 5 (b):

Margin of Safety ($) = (Actual Sales - Break even sales) * Selling price per unit

= (400 units - 320 units) * $18,000

= $1,440,000

Requirement 5 (c):

Degree of Operating Leverage = CM / Operating Income

= (400 units * $5,400) / {(400 units * $5,400) - $1,728,000}

= $2,160,000 / $432,000

= 5

Requirement 5 (d):

CM per unit = Sellinf price - Variable Cost

= $15,500 - $12,600

= $2,900

Operating Income = (Unit sales * CM per unit) - Fixed Cost

= (400 units * $2,900) - $1,435,000

= $1,160,000 - $1,435,000

= - $275,000 i.e Net Operating Loss