Excel Question

Start by downloading the starting file for Part 3 of the exam from myCourses. In cell G6, write a formula to calculate the total Sales. This is calculated by taking the sum of the individual categories of widgets' sales, which is calculated by multiplying the units sold by the price per unit. In cell G7, write a formula to calculate the total COGS. This is calculated by taking the sum of the individual categories of widgets' COGS, which is calculated by multiplying the units sold by the COGS. In cell G10, write a formula to calculate the total Distribution Expenses. This is calculated by taking the sum of the individual categories of widgets' Distribution Expenses, which is calculated by multiplying the units sold by the Distribution Costs. (Note: 2 points bonus if you use the proper function for questions 2, 3 & 4. You must use the function for all three questions to get receive the bonus points.) Before starting the Scenario Manager in the next step, name the following cells with appropriate names: Changing Cells Name of Cells SmallUnitPrice C8 MediumUnitPrice D8 LargeUnitPrice B15 Small Marketing C15 Medium Marketing D15 Large Marketing B8 Result Cells G8 G11 G16 G18 Name of Cells GrossProfit Contribution Margin FixedExpenses Marginallncome Using the Scenario Manager, create three scenarios using the data in the table below: Small Medium Large $119 $9,500 $127 $18,500 $133 $19,250 Current Plan: Price Per Unit Marketing Expense Targeted Plan: Price Per Unit Marketing Expense Optimal Plan: Price Per Unit Marketing Expense $129 $8,250 $136 $13,400 $152 $17,100 $179 $8,750 $195 $10,750 $212 $11,450 Create a Scenario Summary by using the Gross Profit, Contribution Margin, Total Fixed Expenses and the Marginal Income before Taxes as your result cells. Save your file and name it "Your Name - Part 3B". Save your file and submit it to Exam 2/Part 3B dropbox in myCourses. A B D E F G . 1 Rochester's Widget Sales Forecast (2021) Projections: Large 1,700 133.00 Sales COGS Gross Profit $ - 2 3 Assumptions: 4 Widget Categories 5 Small Medium 6 Revenue 7 Units Sold 1,750 1,800 8 Price Per Unit $ 119.00 S 127.00 $ 9 10 Variable Expenses (per unit) 11 COGS $ 60.00 $ 75.00 $ 12 Distribution Costs $ 4.50 $ 5.50 $ 13 14 Fixed Expenses 15 Marketing Campaign $ 9,500.00 16 Payroll (not grouped by category) 17 18 Distribution Expenses Contribution Margin $ 85.00 6.50 Fixed Expenses Marketing Campaign Payroll Total Fixed Expenses $ 47,250.00 $ 55,000.00 $ 102,250.00 Marginal Income Before Taxes $(102,250.00) 19 20 21 22 23 24 25 26 27 2021 Sales Forcast