2 Change all of the numbers in the data area of your workshe

2. Change all of the numbers in the data area of your worksheet so that it looks like this: 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 20,000 units $30 per unit $15 per unit $240,000 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 (b) What is the margin of safety percentage? Margin of safety percentage

Solution

(2)(a)     Break Even (in $) = Fixed cost/contribution margin

     Contribution margin = (SP – VC)/SP

             = (30 – 15)/30 = 50%

   = 240000/50% = $480000

(b)   Margin of Safety % :-

        = (Actual units sold – Break Even units)/Actual units sold

      Breakeven units = 480000/30 = 16000 units

      = (20000 – 16000)/20000 = 20%

(c)    Degree of Operating Leverage:-

     DOL = Contribution/Operating Income

     Contribution = (30 – 15) * 20000 units = $300000

   Operating Income = 300000 – 240000 = $60000

DOL = 300000/60000 = 5

(3) Units sale increase = 20%

      %age increase in operatin income = 20% * DOL

                      = 20% * 5 = 100%

(4) (a)   Net Operating Income (Loss) :-

       (SP – VC) * Units sold – FC = Operating Income

(30 – 15) * 24000 – 240000 = $120000

(b) %age increase in Net operating Income =

[(Revised Operating Income – Existing operating Income)/Existing Operating Income]*100

= (120000 – 60000)/60000]*100 = 100%

(5) (a) Break Even Units = Fixed cost/Contribution pu

             = [1353750/(19000 – 14250)] = 285 units

(b)   Margin of Safety Dollars =(Actual units sold – Break Even units) * SP

       = (300 – 285) * 19000 = $285000

(c) Degree of Operating Leverage =

     DOL = Contribution/Operating Income

       Contribution = (19000 – 14250) * 300 = 1425000

    Operating Income = 1425000 – 1353750 = 71250

     = 1425000/71250 = 20

(d) Net Operating Income(Loss) :-

    (SP – VC) * Units sold – FC = Operating Income

(15400 – 14250) * 300 – 1042750 = (697750)