Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Week 11 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%
Week 11 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 by 15% (i.e. from 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, with no data from option 1 and 2 applied 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 contribution margin per unit? | ||||||||||
What is the 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? | ||||||||||
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started