Question

Chapter 5: Applying Excel Data Unit sales 20,000 units $60 Selling price per unit Variable expenses per unit Fixed expenses per unit $45 per unit $240,000 Enter a formula into each of the cells marked with a ? below Review Problem: CVP Relations hips Compute the CM ratio and variable expense ratio Selling price per unit Variable expenses per unit Contribution margin per unit ? per unit ? per unit ? per unit CM ratio Variable expense ratio ? Compute the break-even Break-even in unit sales ?units Break-even in dollar sales ? Compute the margin of safety Margin of safety in dollars Margin of safety percentage Compute the degree of operating leverage Sales ? Variable expenses Contribution margin Fixed expenses Net operating in come ? ? Degree of operating leverage

Answer 1

2a. BEP SOllar Sales = $2250000

2b. MOS Percentage = 25.00%

2C. Degree of Operating leverage = 400%

C A B 1 Chapter 5: Applying Excel 2 3 Data 4 Unit Sales 50,000.00 Units $ $ 5 SP 60.00 Per Unit 6 Variable Expenses Per Unit 7 Fixed Expenses 30.00 Per Unit 1,125,000.00 8 9 Enter Formula 10 Review Problem 11 12 CM Ratio and Variable Expense Ratio 13 Selling Price 14 Variable Expenses Per Unit 15 CM Per Unit $ $ 60.00 Per Unit 30.00 Per Unit 30.00 Per Unit 16 17 CM Ratio 18 Variable Expenses ratio 50.00% 50.00% 19 20 Compute the Break Even 21 Break Even in Unit Sales 37,500.00 Units 22 Break Even in Dollar Sales 2,250,000.00 23 24 Compute Margin of Safety 25 Margin of Safety in Dollars 26 Margin of Safety in Percentage 750,000.00 25.00% 27 28 Compute degree of Operating leverage 29 Sales 3,000,000.00 30 variable Expenses 31 Contribution Margin 32 Fixed Expenses 33 Net Operating Income $1,500,000.00 1,500,000.00 $1,125,000.00 375,000.00 $ 34 35 Degree of Operating Leverage 400.00% 36.

C A B 1 Chapter 5: Applying Excel Data 50000 Unit Sales Units A 5 SP 6 Variable Expenses Per Unit 7 Fixed Expenses 60 Per Unit 30 Per Unit 1125000 8 9 Enter Formula 10 Review Problem 11 12 CM Ratio and Variable Expense Ratio 13 Selling Price 14 Variable Expenses Per Unit 15 CM Per Unit -B5 Per Unit B6 Per Unit B13-B14 Per Unit 16 17 CM Ratio 18 Variable Expenses ratio -B15/B13 =1- B17 19 20 Compute the Break Even 21 Break Even in Unit Sales 22 Break Even in Dollar Sales Units B22/B5 B7/B17 23 24 Compute Margin of Safety 25 Margin of Safety in Dollars 26 Margin of Safety in Percentage =B4 B5-B22 B25/(B4"B5) 27 28 Compute degree of Operating leverage 29 Sales =B4"B5 30 variable Expenses 31 Contribution Margin 32 Fixed Expenses 33 Net Operating Income B4 B6 -B29- B30 [=B7 |=B31-B32 34 35 Degree of Operating Leverage B31/B33 36

3. Percentage Increase in Net Operating income = 400% * 20% = 80%

4. a. Net Operating income = $675000

b. Percentage Increase in Net Operating income = 80%

A 1 Chapter 5: Applying Excel 2 3 Data 4 Unit Sales 5 SP 60,000.00 Units $ $ 60.00 Per Unit 30.00 Per Unit 6 Variable Expenses Per Unit 7 Fixed Expenses 1,125,000.00 8 9 Enter Formula 10 Review Problem 11 12 CM Ratio and Variable Expense Ratio 13 Selling Price 14 Variable Expenses Per Unit 15 CM Per Unit $ $ $ 60.00 Per Unit 30.00 Per Unit 30.00 Per Unit 16 17 CM Ratio 50.00% 18 Variable Expenses ratio 50.00% 19 20 Compute the Break Even 37,500.00 Units 2,250,000.00 21 Break Even in Unit Sales 22 Break Even in Dollar Sales 23 24 Compute Margin of Safety 25 Margin of Safety in Dollars 26 Margin of Safety in Percentage 1,350,000.00 37.50% 27 28 Compute degree of Operating leverage 29 Sales 3,600,000.00 30 variable Expenses 31 Contribution Margin 32 Fixed Expenses 33 Net Operating Income $1,800,000.00 $1,800,000.00 1,125,000.00 $ 675,000.00 34 35 Degree of Operating Leverage 266.67% 20 A

A 1 Chapter 5: Applying Excel 2 Data 3 Units Per Unit 4 Unit Sales 5 SP 60000 60 Per Unit 6 Variable Expenses Per Unit 7 Fixed Expenses 30 1125000 8 Enter Formula 10 Review Problem 11 12 CM Ratio and Variable Expense Ratio 13 Selling Price 14 Variable Expenses Per Unit 15 CM Per Unit =B5 Per Unit = B6 Per Unit -B13- B14 Per Unit 16 17 CM Ratio 18 Variable Expenses ratio B15/B13 = 1 - B17 19 20 Compute the Break Even - B22/B5 21 Break Even in Unit Sales Units 22 Break Even in Dollar Sales =B7/B17 23 24 Compute Margin of Safety 25 Margin of Safety in Dollars 26 Margin of Safety in Percentage B4 B5-B22 -B25/ (B4"B5) 27 28 Compute degree of Operating leverage 29 Sales -B4"B5 30 variable Expenses 31 Contribution Margin 32 Fixed Expenses 33 Net Operating Income =B4"B6 B29-B30 B7 -B31-B32 34 35 Degree of Operating Leverage =B31/B33 36

5a. BEP in Units = 570

5b. MOS in dollars = 510000

5c. 2000.00%

A B 1 Chapter 5: Applying Excel 2 3 Data 4 Unit Sales 600.00 Units $ $ 5 SP 17,000.00 Per Unit 6 Variable Expenses Per Unit 7 Fixed Expenses 12,750.00 Per Unit 2,422,500.00 9Enter Formula 10 Review Problem 11 12 CM Ratio and Variable Expense Ratio 13 Selling Price 14 Variable Expenses Per Unit 17,000.00 Per Unit $ $ 12,750.00 Per Unit 15 CM Per Unit 4,250.00 Per Unit 16 17 CM Ratio 25.00% 18 Variable Expenses ratio 75.00% 19 20 Compute the Break Even 21 Break Even in Unit Sales 570.00 Units 9,690,000.00 22 Break Even in Dollar Sales 23 24 Compute Margin of Safety 25 Margin of Safety in Dollars 26 Margin of Safety in Percentage 510,000.00 5.00% 27 28 Compute degree of Operating leverage $10,200,000.00 29 Sales 30 variable Expenses 31 Contribution Margin 32 Fixed Expenses 33 Net Operating Income $ 7,650,000.00 2,550,000.00 2,422,500.00 $ 127,500.00 34 35 Degree of Operating Leverage 2000.00% A A A

5d. Net Operating Income = -$1235500

A B C 1 Chapter 5: Applying Excel 2 3 Data 4 Unit Sales 600.00 Units $ $ 5 SP 13,800.00 Per Unit 6 Variable Expenses Per Unit 7 Fixed Expenses 12,750.00 Per Unit 1,865,500.00 8 9 Enter Formula 10 Review Problem 11 12 CM Ratio and Variable Expense Ratio $ $ $ 13 Selling Price 14 Variable Expenses Per Unit 13,800.00 Per Unit 12,750.00 Per Unit 15 CM Per Unit 1,050.00 Per Unit 16 17 CM Ratio 7.61% 92.39% 18 Variable Expenses ratio 19 20 Compute the Break Even 1,776.67 Units 21 Break Even in Unit Sales $24,518,000.00 22 Break Even in Dollar Sales 23 24 Compute Margin of Safety 25 Margin of Safety in Dollars 26 Margin of Safety in Percentage (16,238,000.00) -196.11% 27 28 Compute degree of Operating leverage 8,280,000.00 $ 7,650,000.00 $ $1,865,500.00 (1,235,500.00) 29 Sales 30 variable Expenses 31 Contribution Margin 32 Fixed Expenses 33 Net Operating Income 630,000.00 34 35 Degree of Operating Leverage -50.99% 36