C D E F . L N 0 P R S T Name: G#: 1 Chapter 5: Applying Excel 2 DATA 4 Unit vales S Selling price per un 6 Variable expenses perunt 7 Fbed expenses 20,000 units Sto per un $45 perunt $240,000 7 9 Enter a formula into each of the green cells marked with a 7 below. Once complete use the "Copy Formula" button. 10 Review Problem: CVP Relationships 11 12 Compute the CM Pato and variable experise ratio 13 Saling price per unit ? per unit 14 Variable expenses perunt 7 perunt Copy Formulas 15 Contribution irurgin perunt ? per unit 16 17 CM ratia ? 18 Variable expense rallo 19 20 Compute the break-even 21 Break-even in unil sales ? units 22 Break even in dollar sales ? 23 24 Compute the margin of safety 25 Margin of safety in dollars ? 2 25 Margin of safety percentago 7 27 28 Compute the degree of operating leverage 29 Sales ? 30 Variable expenses ? 31 Contribution margin ? 32 Foed expenses 33 Net operating income 34 35 Degree of operating loverage 36 37 38 39 40 41 42 43 ? 7 ? Formulas A Reg 1 Ch5 Reg 2 Ch5 Req 3 Ch Req 4 Ch5 Req Sa Ch5 Req 5b Ch5 + - B C L M N 0 F D E F G H 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. 20.000 units S60 per unit $45 per unit ? What is the margin of safety percentage? Did it change? Why or why not? 7 per unit 7 per unit 7 per unit ? 7 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 37 38 7 units ? ? ? 7 ? ? ? ? ? Formulas A Req 1 Ch5 A Req 2 Chs Req3 Ch Reg 4 Ch5 Rec ba Ch5 Reg 5b Ch5 + B C L M N P Q R S D H Req. 2: Enter the requirement 2 data from pre 225 of the textbook in the green cells to the lett. 1 Chapter 5: Applying Excel 2 3 Data 4 Unil sales S Selling price per un 6 Variable expenses perunt 7 Fbed expenses What is the margin of safety percentage? ? 2 ? ? lunils perunt perunt What is the degree of operating leverage? ? perunt ? per un ? per unit 7 ? ? units 2 7 ? 9 10 Review Problem: CVP Relationships 11 12 Compute the CM ratio and variable expense ratio 13 Saling price par un 14 Variable expenses perunt 15 Contribution margin per unit 16 17 CM ratia 18 Variable expense ralio 19 20 Compute tre break-even 21 Break-even in unit wales 22 Brak-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 opereling leverage 29 Sales 30 Variable expenses 31 Contribution imagin 32 Fbed expenses 33 Net operating income 34 35 Degree of operating leverage 36 37 38 39 40 41 42 43 A Formulas Reg 1 Ch ? 7 ? 2 7 ? Reg 2 Ch5 A Reg 3 Ch5 Req 4 Ch Reqsa Ch5 Req 5b Ch5 + D A B C 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 ? 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 Chapter 5 Form Filled in Chapter 5 Form 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 $300,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. C7 fx K - L M N 0 P Q R S . 1 Chapter 5: Applying Excel 2 3 Data 4 No Data for Reg. 3 5 B D 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) What is the percentage change in net operating income? 7 8 9 10 No report/calculations for Req. 3 11 12 13 14 15 16 17 18 19 20 21 22 23 24 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 Formulas Req 1 Ch5 Req 2 Ch5 Reg 3 Ch5 Reg 4 Ch5 Req 5a Ch. Reg 5b Ch5 + PHE BI B4 fix ? A B C - L N M 0 P Q D H J 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? ? $120 $72 $420,000 units per unit per unit By what percentage did it increase? 7 per unit ? per unit ? per unit ? ? ? units ? 1 Chapter 5: Applying Excel 2 3 Data 4 Unit Beles 5 Selling price per unit 6 Variable expenses per unit 7 Fored 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 perunt 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dolar sales 23 24 Compute the margin of safety 25 Margin of safety in dolars 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 37 38 39 40 41 42 43 AA Formulas Reg 1 Ch5 7 2 ? ? ? ? ? ? Reg 2 Chs Req 3 Ch5 Red 4 Ch5 Req 5a Ch5 Reg 5b Ch5 + HE B4 fix ? A B C L M N 0 P Q D E G H K Req. 5a: Thad Mogan, a motorcycle enthusiast, has been thinking afre-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 2 2 lunits per unit per unit What would the unit sales be to break even? 7 per unit ? per unit ? per unit What is the margin of safety in dollars? ? ? ? units ? What is the degree of operating leverage? 1 Chapter 5: Applying Excel 2 3 Data 4 Unit Beles 5 Selling price per unit 6 Variable expenses per unit 7 Fored 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 perunt 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dolar sales 23 24 Compute the margin of safety 25 Margin of safety in dolars 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 37 38 39 40 41 42 43 AA Formulas Reg 1 Ch5 7 2 ? ? ? ? ? ? Reg 2 Chs Req 3 Ch5 Req 4 Ch5 Req 5a Ch5 Reg 5b Ch5 + HE B5 fox ? A B C L M N 0 P Q D G H 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. 500 units ? Iper unit $7,500 per unit 2 Do you think this is a good plan? Why? 7 per unit ? per unit ? per unit Explain the answer showing in the report for degree of operating leverage ? ? ? units ? 1 Chapter 5: Applying Excel 2 3 Data 4 Unit Bales 5 Selling price per unit 6 Variable expenses per unit 7 Fored 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 perunt 16 17 CM ratio 18 Variable expense ratio 19 20 Compute the break-even 21 Break-even in unit sales 22 Break-even in dolar sales 23 24 Compute the margin of safety 25 Margin of safety in dolars 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 37 38 39 40 41 42 43 AA Formulas Reg 1 Ch5 7 2 ? ? ? ? ? ? Reg 2 Chs Req 3 Ch5 Req 4 Ch5 Req 5a Ch5 Reg 5b Chs + HE