Question

2 Chapter 5: Applying Excel. cxercise (rart 2 OF 2) (Ago) 2. Change all of the numbers in the data area of your worksheet so that it looks like this: art 2 of 2 А B с 1 Chapter 5: Applying Excel nts vaWN Data Unit sales Selling price per unit Variable expenses per unit Fixed expenses eBook 50,000 units $ 30 per unit $ 12 per unit $ 450,000 Print References If f your formulas are correct, you should get the correct answers to the following questions (a) What is the break-even in dollar sales? Break-even in dollar sales

Answer 1

answer 2)

a) break even in dollar sales = fixed costs/proft volume ratio

break even in dollar sales = $450000/60% = $750000

profit volume ratio = contribution per unit x 100/selling price per unit

profit volume ratio = $18 x 100/$30 = 60%

contribution = selling price per unit - variable expense per unit = $30 - $12 = $18

b) margin of safety % = (current sales - break even sales) x 100/current slaes

margin of safety % = ($1500000 - $750000) x 100/$1500000 = 50%

current sales = units sold x selling price per unit = 50000 x $30 = $1500000

break even sales = $750000

c) degree of operating leverage = (sales - variable costs)/(sales - variable costs - fixed costs)

degree of operating leverage = ($1500000 - $600000)/($1500000 - $600000 - $4500000) = $900000/$450000 = 200%

answer 3) if units sale is increased by 20% than new units sold :

	50000 units	50000 + 20% = 60000 units
sales	50000 x $30 = $1500000	60000 x $30 = $1800000
less : variable costs	50000 x $12 = -$600000	60000 x $12 = -$720000
contribution	$900000	$1080000
less fixed costs	-$450000	-$450000
operating income	$450000	$630000

degree of operating leverage = change in operating income x 100/change in sales

degree of operating leverage = ($630000 - $450000) x 100/($1800000 - 1500000) = $180000 x 100/$300000 = 60%

answer 4)

a) net operating income :

for 60000 units

particular	amounts
sales	60000 x $30 = $1800000
less : variable costs	60000 x $12 = -$720000
contribution	$1080000
less fixed costs	-$450000
operating income	$630000

b) net operatin income increased in % as under :

if units sale is increased by 20% than new units sold :

	50000 units	50000 + 20% = 60000 units
sales	50000 x $30 = $1500000	60000 x $30 = $1800000
less : variable costs	50000 x $12 = -$600000	60000 x $12 = -$720000
contribution	$900000	$1080000
less fixed costs	-$450000	-$450000
operating income	$450000	$630000

% increase in net operating income = ($630000 - $450000) x 100/$450000 = 40%

answer 5)

a) break even in units = fixed costs/contribution per unit

break even point = $1650000/$2750 = 600 units

fixed costs = $1650000

contribution per unit = selling price per unit - variable expense per unit = $11000 - $8250 = $2750

b) margin of safety in dollar = (current sales - break even sales) = $8800000 - $6600000 = $2200000

current sales = units sold x selling price per unit = 800 x $11000 = $8800000

break even sales = break even sales in units x selling price per = 600 x $11000 = $660000

c) degree of operating leverage = (sales - variable costs)/(sales - variable costs - fixed costs)

degree of operating leverage = ($8800000 - $6600000)/($8800000 - $6600000 - $1650000) = $2200000/$550000 = 400%

d) net operating income :

for 800 units, selling price $9000 and fixed cost reduced to $1287000

particular	amount
sales	800 x $9000 = $7200000
less : variable costs	800 x $8250 = -$6600000
contribution	$600000
less fixed costs	-$1287000
operating income( negative in operating loss)	-$687000

net operating loss = $687000

thank you