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. Diamond, Inc. Financial Projections - 2021 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 12 7% -14% 2% 13% - 11% 3% 5% 139 0% 196 2% -2% Scenario 1 Scenario #2 2020 Actual 95.000 75 16 17 4 ? 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 ? 7 ? ? 7 ? ? ? 2 ? ? 6 1.200.000 900.000 Step 12 Income Projections: 2020 Actual 2021 Projections Scenario 1 7 2021 Projections Scenario 2 2 Sales revenue Less variable expenses Direct labor Direct materials Variable overhead Variable selling & admin Total variable expenses Contribution margin Less fixed mig overhead Lessfied selling & admin Operating income ? 2 7 2 ? 2 7 ? 2 ? ? 7 2 ? ? 7 7 ? ? ? 7 ? ? ? 7 ? ? Step 3 CVP Analysis: Contribution margin perunt Contribution margin ratio 2 2 ? 7 ? 7 Break-even sales in units Break-even sales in 55 ? ? 2 ? 7 ? Step 14 Step TR ? 2 Margin of safety in units Margin of safety in sales 5 ? 2 NIA 7 7 #Units to achieve target profit of 11 mm 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. Diamond, Inc. Financial Projections - 2021 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 12 7% -14% 2% 13% - 11% 3% 5% 139 0% 196 2% -2% Scenario 1 Scenario #2 2020 Actual 95.000 75 16 17 4 ? 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 ? 7 ? ? 7 ? ? ? 2 ? ? 6 1.200.000 900.000 Step 12 Income Projections: 2020 Actual 2021 Projections Scenario 1 7 2021 Projections Scenario 2 2 Sales revenue Less variable expenses Direct labor Direct materials Variable overhead Variable selling & admin Total variable expenses Contribution margin Less fixed mig overhead Lessfied selling & admin Operating income ? 2 7 2 ? 2 7 ? 2 ? ? 7 2 ? ? 7 7 ? ? ? 7 ? ? ? 7 ? ? Step 3 CVP Analysis: Contribution margin perunt Contribution margin ratio 2 2 ? 7 ? 7 Break-even sales in units Break-even sales in 55 ? ? 2 ? 7 ? Step 14 Step TR ? 2 Margin of safety in units Margin of safety in sales 5 ? 2 NIA 7 7 #Units to achieve target profit of 11 mm
