Page 1 of 1
F. Scenario Question worksheet. (24 pts) 1. Name the following cells. Cells Suggested Cells Suggested Cells Names Names
Posted: Wed May 11, 2022 5:21 pm
by answerhappygod
- F Scenario Question Worksheet 24 Pts 1 Name The Following Cells Cells Suggested Cells Suggested Cells Names Names 1 (150.61 KiB) Viewed 23 times
F. Scenario Question worksheet. (24 pts) 1. Name the following cells. Cells Suggested Cells Suggested Cells Names Names B7 ATargetUnitSold C7 APrice F11 B8 BTargetUnitSold C8 BPrice F12 CTargetUnitSold C9 CPrice F13 Suggested Names TRevenue TCOGS TGrossProfit B9 2. Create scenarios using the data and formulas given in the gray area. Use the scenario manager to create the scenarios and the required scenario summary (in a separate worksheet). You will create 3 different scenarios (with unique and meaningful scenario names) by changing the product pricing mix in order to determine their impacts to Total Gross Profit. The First Scenario is to raise the price of Product B by $6.00. However, this would cause sales of Product B to fall by 600 units and sales of Product C to increase by 500 units The Second Scenario is to raise the price of Product C by $4.00. However, this would cause sales of Product C to fall by 350 units and sales of Product B to increase by 200 units The Third Scenario is to raise the price of both Product B and Product C by $3.00. This would cause sales for Products B and C to both decrease by 150 each Your goal is to create a Scenario summary report as a separate worksheet to compare the three scenarios. Make sure your summary show Revenue, COGS and GrossProfit for the comparison 3. Rename the new sheet as "G. Scenario Report". Drag and place this worksheet after F. Scenario Question. 4. Question: Which scenario will you take for 2020? Elaborate your product pricing mix of the selected scenario, and why do you choose this scenario?
Perform the scenario analysis based on the data in the table below (The target unit sold here might be different than what you had in B. Summary, which is alright.) Product A Product B Product C Target Unit Sold Unit Price 3673 $55.00 5130 $45.00 2622 $33.00 Unit Cost $43.00 $30.00 $25.00 Revenue Cost of Goods Sold $202,015.00 $157,939.00 $230,850.00 $153,900.00 $86,526.00 $65,550.00 Total Revenue Total COGS Total Gross Profit $519,391.00 $377,389.00 $142.002.00 Questions which scenario will you choose for 2020? And why? Answer: