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 $$270,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 $$60$ per unit

Variable expenses per unit $$45$ per unit

Contribution margin per unit $$15$ per unit

CM ratio $25\%$

Variable expense ratio $75\%$

Compute the break$-$even point

Break$-$even in unit sales $18,000$ units

Break$-$even in dollar sales $$1,080,000$

Compute the margin of safety

Margin of safety in dollars $$120,000$

Margin of safety percentage $10\%$

Compute the degree of operating leverage

Sales $$1,200,000$

Variable expenses $900,000$

Contribution margin $$300,000$

Fixed expenses $$270,000$

Net operating income $$30,000$

Degree of operating leverage $10\mathrm{.}00$

$2.$ Change all of the numbers in the data area of your worksheet so that it looks like this:

Chapter $5$: Applying Excel

Data

Unit sales $60,000$ units

Selling price per unit $$50$ per unit

Variable expenses per unit $$25$ per unit

Fixed expenses $$1,350,000$

f your formulas are correct, you should get the correct answers to the following questions.

$($a$)$ What is the break$-$even in dollar sales?

$($b$)$ What is the margin of safety percentage?

$($c$)$ What is the degree of operating leverage? $($Note: Round your answer to $2$ decimal places.$)$

$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 $20\%.$

$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:

Data

Unit sales $72,000$ units

Selling price per unit $$50$ per unit

Variable expenses per unit $$25$ per unit

Fixed expenses $$1,350,000$

$($a$)$ What is net operating income?

Note: Negative amount should be indicated by a minus sign.

$($b$)$ By what percentage did the net operating income 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 $1930$s$.$ The retro$-$look cycle would be sold for $$16,000,$ and at that price, Thad estimates he could sell $200$ units each year. The variable cost to produce and sell the cycles would be $$12,000$ per unit. The annual fixed cost would be $$760,000.$

a$.$ What is the break$-$even in unit sales?

b$.$ What is the margin of safety in dollars?

c$.$ What is the degree of operating leverage?

Note: Round your answer to $2$ decimal places.

d$.$ 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 $$13,200$ to compete effectively. In that event, Thad also would reduce fixed expenses to $$586,000$ by reducing advertising expenses, but he still hopes to sell $200$ units per year.

What would the net operating income be in this situation?

Note: Negative amount should be indicated by a minus sign.