This is for requirement 2

A B C D E F G H - J L M N Name: G#: 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 $60 per unit $45 per unit $240,000 ? 9 Enter a formula into each of the green cells marked with a 2 below. Once complete use the "Copy Formula" button. 10 Review Problem: CVP Relationships 11 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit ? per unit 14 Variable expenses per unit ? per unit Copy Formulas 15 Contribution margin per unit ? per unit 16 17 CM ratio 18 Variable expense ratio ? 19 20 Compute the break-even 21 Break-even in unit sales ? units 22 Break-even in dollar sales ? 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage ? 27 28 Compute the degree of operating leverage 29 Sales ? 30 Variable expenses ? 31 Contribution margin 7 32 Fixed expenses ? 33 Net operating income ? 34 35 Degree of operating leverage ? 36 Formulas A Req 1 Ch5 A Reg 2 Ch5 * Req3 Ch5 Req 4 Ch. Req 5a Ch5 Req 5b Ch ? TI + A B - M L D E G H K Req. 1: Check your formulas by entering $270,000 for fixed expenses. If they are correct, the degree of operating leverage should be 10. If you did not get this answer, correct your formulas on the previous page and press the copy formulas button again. 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships 20.000 units $60 per unit $45 per unit ? What is the margin of safety percentage? Did it change? Why or why not? ? per unit ? per unit ? per unit ? ? ? units ? 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 A Formulas Req 1 Ch5 A Reg 2 Chs ? ? ? ? ? ? ? ? A Red 3 Ch5 Req 4 Ch. Req 5a Ch5 A Req Sb Ch5 + B - L M D E F H K Req. 2: Enter the requirement 2 data from page 225 of the textbook in the green cells to the left. Junits 7 ? ? What is the margin of safety percentage? per unit per unit 7 ? What is the degree of operating leverage? ? per unit ? per unit ? per unit ? ? A 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships 11 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 A Formulas A Req 1 Ch5 Reg 2 Ch5 ? units ? ? ? ? ? ? ? ? ? A Req 3 Ch5 Req 4 Ch. Req 5a Ch5 A Req Sb Ch5 + New sheet Paste B I DJ Insert Delete Format Ideas EEE EI E Merge & Center Conditional Format Formatting as Table Cell Styles Sort & XV Filter Find & Select C7 A B D J L M N 1 Chapter 5: Applying Excel 2 3 Data No Data for Reg. 3 5 C E F G H Req. 3: Using the degree of operating leverage from req. 2, calculate the percentage change in net operating income if sales increase by 15%. (Hint: see the highlighted formula in the textbook at the bottom os page 216) 6 What is the percentage change in net operating income? 7 8 9 10 No report/calculations for Req. 3 11 12 12 13 15 14 14 15 15 16 16 17 18 19 20 21 22 23 24 25 26 27 28 A Formulas A Req 1 Ch5 * REC12 Reg 3 Ch5 A Req 4 Ch5 Reqba Ch5 A Req 56 Ch5 + + 150% A B - L M D E F G H K Req. 4: Confirm the calculation you made in req. 3 by increasing the req. 2 sales in units by 15%. What is the new net operating income? 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships 7 ? $120 $72 $420,000 Junits per unit per unit 11 By what percentage did it increase? ? per unit ? per unit ? per unit ? ? ? units ? 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 Formulas A Reg 1 Ch5 Req 2 Ch5 ? ? ? ? ? ? ? ? Req 3 Ch5 Req 4 Chs A Reg 5a Ch5 A Req Sb Ch5 + A B L M D E F G H J K Req. 5a: Thad Mogan, a motorcycle enthusiast, has been thinking of re-launching the Western Hombre brand of vintage motorcycles. The motorcycle would be sold for $10,000 and 600 units could be sold for that price. The variable costs would be $7,500 per unit and the annual fixed cost would be $1,200,000. Enter this data in the green cells to the left and answer the questions below. 7 Junits 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships ? per unit per unit 7 ? What would the unit sales be to break even? 11 ? per unit ? per unit ? per unit What is the margin of safety in dollars? ? ? ? units ? What is the degree of operating leverage? 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 A Formulas A Req 1 Ch5 Req 2 Ch5 ? ? ? ? ? ? ? ? Reg 3 Ch5 Reg 4 Ch5 Req 5a Ch5 A Req 5b Ch5 + A B C L M - D E G H J K Req. 5a: Thad is worried the price of the motorcycle might be too high and the price should be reduced to $9,000. The fixed expenses would also be reduced to $900,000 by reducing advertising costs. The unit sales and variable expenses would not change. Enter the new data in the green cells to the left. 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships 7 ? S7.500 ? 600 units Iper unit per unit Do you think this is a good plan? Why? 11 ? per unit ? per unit ? per unit Explain the answer showing in the report for degree of operating leverage. ? ? 7 units ? 12 Compute the CM ratlo and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 A Formulas A Reg 1 Ch. Req 2 Ch5 ? ? ? ? ? ? ? ? Req3 Ch5 Req 4 Ch. Req 5a Ch5 Req 5b Ch5 + D D A B 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 20.000 units 5 Selling price per unit $60 per unit 6 Variable expenses per unit $45 per unit 7 Fixed expenses $240,000 8 9 Enter a formula into each of the cells marked with a ? below 10 Review Problem: CVP Relationships 11 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit ? per unit 14 Variable expenses per unit ? per unit 15 Contribution margin per unit ? per unit 16 17 CM ratio ? 18 Variable expense ratio ? 19 20 Compute the break-even 21 Break-even in unit sales ? units 22 Break-even in dollar sales ? 23 24 Compute the margin of safety 25 Margin of safety in dollars 2 26 Margin of safety percentage ? 27 28 Compute the degree of operating leverage 29 Sales ? 30 Variable expenses ? 31 Contribution margin ? 32 Fixed expenses ? 33 Net operating income 34 35 Degree of operating leverage 36 HM Chapter 5 Form Filed in Chapter 5 Form 2 You should proceed to the requirements below only after completing your worksheet. 0 Required: 1. Check your worksheet by changing the fixed expenses to $270,000. If your worksheet is operating properly, the degree of operating leverage should be 10. If you do not get this answer, find the errors in your worksheet and correct them. How much is the margin of safety percentage? Did it change? Why or why not? 2. Enter the following data from a different company into your worksheet: Unit sales Selling price per unit Variable expenses per unit Fixed expenses 10,000 $120 $72 $420,000 What is the margin of safety percentage? What is the degree of operating leverage? Page 226 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 15%. 4. Confirm the calculations you made in part (3) above by increasing the unit sales in your worksheet by 15%. What is the new net operating income and by what percentage did it increase? 5. Thad Morgan, a motorcycle enthusiast, has been exploring the possibility of relaunching the Western Hombre brand of cycle that was popular in the 1930s. The retro-look cycle would be sold for $10,000 and at that price, Thad estimates that he could sell 600 units each year. The variable cost to produce and sell the cycles would be $7,500 per unit. The annual fixed cost would be $1.200.000 a. Using your worksheet, what would be the unit sales to break even the margin of safety in dollars, and the degree of operating leverage? b. Thad is worried about the selling price, Rumors are circulating that other retro brands of cycles may be revived. If so, the selling price for the Western Hombre would have to be reduced to $9,000 to compete effectively. In that event, Thad also would reduce fixed expenses by S300,000 by reducing advertising expenses, but he still hopes to sell 600 units per year. Do you think this is a good plan? Explain. Also, explain the degree of operating leverage that appears on your worksheet. A B C D E F G H - J L M N Name: G#: 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 $60 per unit $45 per unit $240,000 ? 9 Enter a formula into each of the green cells marked with a 2 below. Once complete use the "Copy Formula" button. 10 Review Problem: CVP Relationships 11 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit ? per unit 14 Variable expenses per unit ? per unit Copy Formulas 15 Contribution margin per unit ? per unit 16 17 CM ratio 18 Variable expense ratio ? 19 20 Compute the break-even 21 Break-even in unit sales ? units 22 Break-even in dollar sales ? 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage ? 27 28 Compute the degree of operating leverage 29 Sales ? 30 Variable expenses ? 31 Contribution margin 7 32 Fixed expenses ? 33 Net operating income ? 34 35 Degree of operating leverage ? 36 Formulas A Req 1 Ch5 A Reg 2 Ch5 * Req3 Ch5 Req 4 Ch. Req 5a Ch5 Req 5b Ch ? TI + A B - M L D E G H K Req. 1: Check your formulas by entering $270,000 for fixed expenses. If they are correct, the degree of operating leverage should be 10. If you did not get this answer, correct your formulas on the previous page and press the copy formulas button again. 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships 20.000 units $60 per unit $45 per unit ? What is the margin of safety percentage? Did it change? Why or why not? ? per unit ? per unit ? per unit ? ? ? units ? 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 A Formulas Req 1 Ch5 A Reg 2 Chs ? ? ? ? ? ? ? ? A Red 3 Ch5 Req 4 Ch. Req 5a Ch5 A Req Sb Ch5 + B - L M D E F H K Req. 2: Enter the requirement 2 data from page 225 of the textbook in the green cells to the left. Junits 7 ? ? What is the margin of safety percentage? per unit per unit 7 ? What is the degree of operating leverage? ? per unit ? per unit ? per unit ? ? A 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships 11 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 A Formulas A Req 1 Ch5 Reg 2 Ch5 ? units ? ? ? ? ? ? ? ? ? A Req 3 Ch5 Req 4 Ch. Req 5a Ch5 A Req Sb Ch5 + New sheet Paste B I DJ Insert Delete Format Ideas EEE EI E Merge & Center Conditional Format Formatting as Table Cell Styles Sort & XV Filter Find & Select C7 A B D J L M N 1 Chapter 5: Applying Excel 2 3 Data No Data for Reg. 3 5 C E F G H Req. 3: Using the degree of operating leverage from req. 2, calculate the percentage change in net operating income if sales increase by 15%. (Hint: see the highlighted formula in the textbook at the bottom os page 216) 6 What is the percentage change in net operating income? 7 8 9 10 No report/calculations for Req. 3 11 12 12 13 15 14 14 15 15 16 16 17 18 19 20 21 22 23 24 25 26 27 28 A Formulas A Req 1 Ch5 * REC12 Reg 3 Ch5 A Req 4 Ch5 Reqba Ch5 A Req 56 Ch5 + + 150% A B - L M D E F G H K Req. 4: Confirm the calculation you made in req. 3 by increasing the req. 2 sales in units by 15%. What is the new net operating income? 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships 7 ? $120 $72 $420,000 Junits per unit per unit 11 By what percentage did it increase? ? per unit ? per unit ? per unit ? ? ? units ? 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 Formulas A Reg 1 Ch5 Req 2 Ch5 ? ? ? ? ? ? ? ? Req 3 Ch5 Req 4 Chs A Reg 5a Ch5 A Req Sb Ch5 + A B L M D E F G H J K Req. 5a: Thad Mogan, a motorcycle enthusiast, has been thinking of re-launching the Western Hombre brand of vintage motorcycles. The motorcycle would be sold for $10,000 and 600 units could be sold for that price. The variable costs would be $7,500 per unit and the annual fixed cost would be $1,200,000. Enter this data in the green cells to the left and answer the questions below. 7 Junits 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships ? per unit per unit 7 ? What would the unit sales be to break even? 11 ? per unit ? per unit ? per unit What is the margin of safety in dollars? ? ? ? units ? What is the degree of operating leverage? 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 A Formulas A Req 1 Ch5 Req 2 Ch5 ? ? ? ? ? ? ? ? Reg 3 Ch5 Reg 4 Ch5 Req 5a Ch5 A Req 5b Ch5 + A B C L M - D E G H J K Req. 5a: Thad is worried the price of the motorcycle might be too high and the price should be reduced to $9,000. The fixed expenses would also be reduced to $900,000 by reducing advertising costs. The unit sales and variable expenses would not change. Enter the new data in the green cells to the left. 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 5 Selling price per unit 6 Variable expenses per unit 7 Fixed expenses 8 9 10 Review Problem: CVP Relationships 7 ? S7.500 ? 600 units Iper unit per unit Do you think this is a good plan? Why? 11 ? per unit ? per unit ? per unit Explain the answer showing in the report for degree of operating leverage. ? ? 7 units ? 12 Compute the CM ratlo and variable expense ratio 13 Selling price per unit 14 Variable expenses per unit 15 Contribution margin per unit 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dollar sales 23 24 Compute the margin of safety 25 Margin of safety in dollars 26 Margin of safety percentage 27 28 Compute the degree of operating leverage 29 Sales 30 Variable expenses 31 Contribution margin 32 Fixed expenses 33 Net operating income 34 35 Degree of operating leverage 36 A Formulas A Reg 1 Ch. Req 2 Ch5 ? ? ? ? ? ? ? ? Req3 Ch5 Req 4 Ch. Req 5a Ch5 Req 5b Ch5 + D D A B 1 Chapter 5: Applying Excel 2 3 Data 4 Unit sales 20.000 units 5 Selling price per unit $60 per unit 6 Variable expenses per unit $45 per unit 7 Fixed expenses $240,000 8 9 Enter a formula into each of the cells marked with a ? below 10 Review Problem: CVP Relationships 11 12 Compute the CM ratio and variable expense ratio 13 Selling price per unit ? per unit 14 Variable expenses per unit ? per unit 15 Contribution margin per unit ? per unit 16 17 CM ratio ? 18 Variable expense ratio ? 19 20 Compute the break-even 21 Break-even in unit sales ? units 22 Break-even in dollar sales ? 23 24 Compute the margin of safety 25 Margin of safety in dollars 2 26 Margin of safety percentage ? 27 28 Compute the degree of operating leverage 29 Sales ? 30 Variable expenses ? 31 Contribution margin ? 32 Fixed expenses ? 33 Net operating income 34 35 Degree of operating leverage 36 HM Chapter 5 Form Filed in Chapter 5 Form 2 You should proceed to the requirements below only after completing your worksheet. 0 Required: 1. Check your worksheet by changing the fixed expenses to $270,000. If your worksheet is operating properly, the degree of operating leverage should be 10. If you do not get this answer, find the errors in your worksheet and correct them. How much is the margin of safety percentage? Did it change? Why or why not? 2. Enter the following data from a different company into your worksheet: Unit sales Selling price per unit Variable expenses per unit Fixed expenses 10,000 $120 $72 $420,000 What is the margin of safety percentage? What is the degree of operating leverage? Page 226 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 15%. 4. Confirm the calculations you made in part (3) above by increasing the unit sales in your worksheet by 15%. What is the new net operating income and by what percentage did it increase? 5. Thad Morgan, a motorcycle enthusiast, has been exploring the possibility of relaunching the Western Hombre brand of cycle that was popular in the 1930s. The retro-look cycle would be sold for $10,000 and at that price, Thad estimates that he could sell 600 units each year. The variable cost to produce and sell the cycles would be $7,500 per unit. The annual fixed cost would be $1.200.000 a. Using your worksheet, what would be the unit sales to break even the margin of safety in dollars, and the degree of operating leverage? b. Thad is worried about the selling price, Rumors are circulating that other retro brands of cycles may be revived. If so, the selling price for the Western Hombre would have to be reduced to $9,000 to compete effectively. In that event, Thad also would reduce fixed expenses by S300,000 by reducing advertising expenses, but he still hopes to sell 600 units per year. Do you think this is a good plan? Explain. Also, explain the degree of operating leverage that appears on your worksheet