a. Complete the worksheet by replacing every? with a formula. Every cell marked with a "?" must be replaced with formulas only; no hard-keyed numbers are allowed in these cells! Make Excel do the calculations for you. If formulas are entered correctly, the calculations will update immediately if/when any changes are made to the scenarios above. 2. Step #1 Inputs: For each scenario, calculate the new sales and cost amounts based on the 2020 actual inputs and changes to those amounts based on the applicable scenario above. 3. Step #2 Income Projections: Complete the Contribution Margin Income Statement for 2020 and each scenario. 4. Step #3 CVP Analysis: Complete the CVP calculations for 2020 and each scenario. 5. Step #4 Recommendation: Using proper grammar and complete sentences, indicate which scenario you would recommend for 2021 with a detailed description as to why you prefer that scenario. A minimum of 4-6 sentences are required for this analysis. 6. Step #5 "Extra Credit: Students who complete this successfully will receive a maximum of 6 extra points on the project. Sales in units Sales price per unit Direct labor cost per unit Direct materials cost per unit Variable overhead cost per unit Variable selling & admin per unit Fixed manufacturing costs Fixed selling & admin costs % Change from 2020 Actual Scenario #1 Scenario #2 7% -14% -2% 13% -11% 3% 5% 13% 0% 1% 2% -3% 8% 1% 1% -2% 2020 Actual Step #1 Inputs: Sales in units Sales price per unit Direct labor cost per unit Direct materials cost per unit Variable overhead cost per unit Variable selling & admin per unit Fixed manufacturing costs Fixed selling & admin costs 95.000 75 16 17 4 6 1,200,000 900,000 Scenario #1 ? ? ? ? ? ? 7 ? Scenario #2 ? ? ? ? ? ? ? ? Step #2 Income Projections: 2020 Actual 2021 Projections Scenario 1 ? 2021 Projections Scenario 2 ? ? Sales revenue Less variable expenses: Direct labor Direct materials Variable overhead Variable selling & admin Total variable expenses Contribution margin Less fixed mfg overhead Less fixed selling & admin Operating income ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Step #3 CVP Analysis: Contribution margin per unit Contribution margin ratio ? ? ? ? ? ? ? ? ? ? ? ? Break-even sales in units Break-even sales in $$ Step #4: Step #5: Margin of safety in units Margin of safety in sales $S # Units to achieve target profit of $1,800,000 ? ? 2 ? ? ? NA