Week 11 Excel Assignment - CVP Use the original data, along with the changes in option 1 for questions de 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. d.) Calculate the break even point in units after applying Option 1: Option 1: What is the new fixed cost in total? Purchase machinery to automate their operations. This machinery costs $450,000 (an increase in fixed cost), but What is the contribution margin per unit? will decrease variable costs by 15% (i.e. from 55% to 40% of sales.) What is the break even point in units? Option 2: Outsource the production of one of their main components that requires a substantial amount of machinery and e.) Calculate the operating leverage after applying Option 1: skilled labor. This will reduce fixed costs by $375,000, but increases variable costs from their current 55% of sales to 65% of sales. What is the new contribution margin in total? Use the original data, with no data from option 1 and 2 applied for questions a-c What is the new operating income in total? a.) Determine the break even point in units (before any changes) What is the new operating leverage factor? \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \end{tabular} What is the fixed cost in total? What is the contribution margin per unit? What is the break even point in units? Use the original data, along with the changes in option 2 for 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? b.) Ignoring tax, what dollar value of sales is required to earn a profit of $650,000. What is the break even point in units? What is the contribution margin ratio? What dollar amount of sales would be required to earn the after tax profit described above? g.) Calculate the operating leverage after applying Option 2: c.) Calculate the operating leverage before applying any of the options: What is the new contribution margin in total? What is the contribution margin in total? What is the new operating income in total? What is the operating income in total? What is the operating leverage factor? What is the new operating leverage factor? \begin{tabular}{|l|} \hline \\ \hline \end{tabular} 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? Week 11 Excel Assignment - CVP Use the original data, along with the changes in option 1 for questions de 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. d.) Calculate the break even point in units after applying Option 1: Option 1: What is the new fixed cost in total? Purchase machinery to automate their operations. This machinery costs $450,000 (an increase in fixed cost), but What is the contribution margin per unit? will decrease variable costs by 15% (i.e. from 55% to 40% of sales.) What is the break even point in units? Option 2: Outsource the production of one of their main components that requires a substantial amount of machinery and e.) Calculate the operating leverage after applying Option 1: skilled labor. This will reduce fixed costs by $375,000, but increases variable costs from their current 55% of sales to 65% of sales. What is the new contribution margin in total? Use the original data, with no data from option 1 and 2 applied for questions a-c What is the new operating income in total? a.) Determine the break even point in units (before any changes) What is the new operating leverage factor? \begin{tabular}{|l|} \hline \\ \hline \\ \hline \\ \hline \end{tabular} What is the fixed cost in total? What is the contribution margin per unit? What is the break even point in units? Use the original data, along with the changes in option 2 for 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? b.) Ignoring tax, what dollar value of sales is required to earn a profit of $650,000. What is the break even point in units? What is the contribution margin ratio? What dollar amount of sales would be required to earn the after tax profit described above? g.) Calculate the operating leverage after applying Option 2: c.) Calculate the operating leverage before applying any of the options: What is the new contribution margin in total? What is the contribution margin in total? What is the new operating income in total? What is the operating income in total? What is the operating leverage factor? What is the new operating leverage factor? \begin{tabular}{|l|} \hline \\ \hline \end{tabular} 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