1 Chapter 5: Applying Excel Name: G#: 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 green cells marked with a 7 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 safely 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 Reg 1 Ch5 Req 2 Ch5 Roq 3 Ch5 Rog 4 Ch5 1 RU 5 CT6 Roq 5b Ch5 2 TO + Enter 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. What is the margin of safety percentage? Did it change? Why or why not? What is the margin of safety percentage? What is the degree of operating leverage? B D E F G H - J 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? - J L D E F G 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? By what percentage did it increase? D E F G - J L 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. What would the unit sales be to break even? What is the margin of safety in dollars? What is the degree of operating leverage? D E F G H - J L 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. Do you think this is a good plan? Why? Explain the answer showing in the report for degree of operating leverage