| Chapter 5: Applying Excel | ||||||
| Data | ||||||
| Unit sales | 20,000 | units | ||||
| Selling price per unit | $60 | per unit | ||||
| Variable expenses per unit | $45 | per unit | ||||
| Fixed expenses | $240,000 | |||||
| Enter a formula into each of the cells marked with a ? below | ||||||
| Review Problem: CVP Relationships | ||||||
| Compute the CM ratio and variable expense ratio | ||||||
| Selling price per unit | ? | per unit | ||||
| Variable expenses per unit | ? | per unit | ||||
| Contribution margin 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 income | ? | |||||
| Degree of operating leverage | ? | |||||
| Enter formulas in all cells that contain question marks. | ||||||
| For example, in cell B13 enter the formula “= B5”. | ||||||
| After entering formulas in all of the cells that contained question marks, verify that the dollar amounts match the example in the text. | ||||||
| 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. | ||||||
| Requirement 2: | ||||||
| Change all of the numbers in the data area of the below worksheet so that it looks like this: | ||||||
|
|
||||||
| What is the break even in dollar sales? | ||||||
| Break even in dollar sales | ? | |||||
| What is the margin of safety percentage? | ||||||
| Margin of safety percentage | ? | |||||
| What is the degree of operating leverage? | ||||||
| Degree of operating leverage | ? | |||||
| Requirement 3: | ||||||
| Using the degree of operating leverage and without changing anything in your worksheet, calculate by what percentage the net operating income should increase if unit sales increase by 20%. | ||||||
| Percentage increase in net operating income | ? | % | ||||
| Requirement 4: | ||||||
| Confirm your calculations in Requirement 3 above by increasing the unit sales in your worksheet by 20% so that the Data area looks like this: | ||||||
|
|
||||||
| What is net operating income? | ||||||
| Net operating income (loss) | ? | |||||
| By what percentage did the net operating income increase? | ||||||
| Percentage increase in net operating income | ? | % | ||||
| Requirement 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 $13,000 and at that price, Thad estimates 800 units would be sold each year. The variable cost to produce and sell the cycles would be $9,100 per unit. | ||||||
| The annual fixed cost would be $1,560,000. | ||||||
| What is the break even in unit sales? | ||||||
| Break even in unit sales | ? | |||||
| What is the margin of safety in dollars | ||||||
| Margin of safety in dollars | ? | |||||
| What is the degree of operating leverage? | ||||||
| Degree of operating leverage | ? | |||||
| 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 $10,100 to compete effectively. In that event, Thad would also reduce fixed expenses to $1,186,000 by reducing advertising expenses, | ||||||
| but he still hopes to sell 800 units per year. | ||||||
| What would the net operating income be in this situation? (Negative amount should be indicated by a minus sign. ) | ||||||
| Net operating income (loss) | ? | |||||
Attachments:
Templates5 7A….xls