Question

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 loc BC 1 Chapter 5: Applying Excel 3 4 Data Unit sales Selling price per unit Variable expenses per unit Fixed expenses 60,000 units $ 30 per unit $ 21 per unit $ 486,000 7

Answer 1

Requirement 2 Data Unit Sales Selling Price per unit Variable expenses per unit Fixed Expenses $ $ $ 60000 units 30 per unit 21 per unit 4,86,000 30 Selling Price per unit Variable expenses per unit Contribution per unit -21 Break-even point (in unit sales) (Fixed Cost/Contribution per unit) Break-even point (in dollars) (Break-even point in units * Selling Price per unit) 54000 units (486000/9) 16,20,000 (54000*30) $ The break-even point is $ 1,620,000. (b) Contribution per unit Unit Sales Total Contribution (60000*9) (-) Fixed expenses Net Operating Income $ $ $ 60000 5,40,000 -4,86,000 54,000 Margin of Safety (in unit sales) (Net Operating Income/Contribution per unit) Margin of Safety (in dollars) (Margin of Safety in units * Selling Price per unit) 6000 units (486000/9) 1,80,000 (6000*30) $ The Margin of Safety is $ 180,000. 10.00 times (c) Degree of Operating Leverage (Total Contribution/Net Operating Income) (540000/54000) The degree of Operating Leverage is 10 times.

Requirement 3 If unit sales increases by 20%, The Net Operating income shall increase by 200% (20%*10 times) The formula for increase in Net Operating income shall be (Increase in unit sales * Degree of Operating Leverage). Requirement 4 Confirmation of above statement Data Unit Sales Selling Price per unit Variable expenses per unit Fixed Expenses 72000 units 30 per unit 21 per unit 4,86,000 $ 30 -21 Selling Price per unit Variable expenses per unit Contribution per unit Unit Sales Total Contribution (72000*9) (-) Fixed expenses Net Operating Income $ $ $ 72000 6,48,000 4,86,000 1,62,000 The Net Operating Income shall be $ 162,000. 108000 (b) Change in Net Operating Income (162000-54000) % Change (108000/54000) 200% Hence, Percentage change in Net Operating Income is 200% which confirms to the % calculated in Requirement 3.

Requirement 5 S 14,000 Selling Price per unit Variable expenses per unit Contribution per unit $ $ 14,000 10,500 3,500 Break-even point in unit sales) (Fixed Cost/Contribution per unit) 225 units (787500/3500) The break-even point is 225 units. (b) Contribution per unit Unit Sales Total Contribution (300*3500) (-) Fixed expenses Net Operating Income $ 3,500 300 $ 10,50,000 $ -7,87,500 $ 2,62,500 Margin of Safety (in unit sales) (Net Operating Income/Contribution per unit) Margin of Safety (in dollars) (Margin of Safety in units * Selling Price per unit) The Margin of Safety is $ 1,050,000. 75 units (262500/3500) $ 10,50,000 (75*14000) 4.00 times (c) Degree of Operating Leverage (Total Contribution/Net Operating Income) (1050000/262500) The degree of Operating Leverage is 4 times. $ (d) Contribution per unit (11700-10500) Unit Sales Total Contribution (300*3500) (-) Fixed expenses Net Operating Income/(Loss) $ $ $ 1,200 300 3,60,000 -7,17,500 -3,57,500 The Net Operating loss is $ 357,500.