Module 4b Excel Assignment - CVP
Shelly's Boutiques and Crafts had revenue of $6,300,000 this year on sales of 475,000 units. Variable costs were 55% of sales and fixed costs totaled $2,250,000. Although the first five years were relatively profitable, increases in competition have led to a negative trend in profitability that has led them to the point where they have to make some changes to stay afloat. The company is evaluating two options to stay afloat.
Option 1:
Purchase machinery to automate their operations. This machinery costs $450,000 (an increase in fixed cost), but will decrease variable costs down from the original 55% to 40% of sales.
Option 2:
Outsource the production of one of their main components that requires a substantial amount of machinery and skilled labor. This will reduce fixed costs by $375,000, but increases variable costs from their current 55% of sales to 65% of sales.
Use the original data, BEFORE considering data from option 1 and 2 for questions a-c
a.) Determine the break even point in units (before any changes)
	What is the fixed cost in total?
	What is the contribution margin per unit?
	What is the break even point in units?
b.) Ignoring tax, what dollar value of sales is required to earn a profit of $650,000.
	What is the contribution margin ratio?
	What dollar amount of sales would be required to earn the after tax profit described above?
c.) Calculate the operating leverage before applying any of the options:
	What is the contribution margin in total?
	What is the operating income in total?
	What is the operating leverage factor?
Use the original data, along with the changes in option 1 for questions d-e
d.) Calculate the break even point in units after applying Option 1:
	What is the new fixed cost in total?
	What is the new contribution margin per unit?
	What is the new break even point in units?
e.) Calculate the operating leverage after applying Option 1:
	What is the new contribution margin in total?
	What is the new operating income in total?
	What is the new operating leverage factor?
Use the original data, along with the changes in option 2 for questions f-g. The option 1 information does not have any impact at all for these remaining questions.
f.) Calculate the break even point after applying Option 2:
	What is the new fixed cost in total?
	What is the contribution margin per unit?
	What is the break even point in units?
g.) Calculate the operating leverage after applying Option 2:
	What is the new contribution margin in total?
	What is the new operating income in total?
	What is the new operating leverage factor?
This is a comparison of the operating leverage for all three scenarios (initial, option 1, and option 2.)
h.) If sales increase by 25%, which option would be impacted most positively?