Switch to the All Types worksheet. Use the Scenario Manager to create a Scenario Summary report that summarizes the effect of the Status Quo, Third Party, and Raise Rates scenarios. Use the range B17:D17 as the result cells.
Switch back to the All Types worksheet. Use the Scenario Manager as follows to compare the profit per hour in each scenario:
a. Create a Scenario PivotTable report for result cells B17:D17.
b. Remove the Filter field from the PivotTable.
c. Change the Number format of the Profit_Per_Hour_Contracted_Mana, Profit Per_Hour Contracted Chan, and Profit Per_Hour_Contracted Proc fields (located in the Values box of the PivotTable Field List) to Currency with 2 Decimal places and as the Symbol.
d. Use Management as the row label value in cell B3, Change as the value in cell , and Process as the value in cell D3.
The Correct Answer and Explanation is:
✅ Part 1: Create a Scenario Summary Report
- Go to the “All Types” worksheet.
- Click the Data tab, then choose What-If Analysis > Scenario Manager.
- You should already have the scenarios: Status Quo, Third Party, and Raise Rates. If not, create them.
- In the Scenario Manager, click Summary.
- In the Scenario Summary dialog:
- Select Scenario Summary.
- For the result cells, enter the range
B17:D17.
- Click OK. A new worksheet named Scenario Summary is created.
✅ Part 2: Create a Scenario PivotTable Report
- Go back to the All Types worksheet.
- Open the Scenario Manager again: Data > What-If Analysis > Scenario Manager.
- Click Summary, then choose Scenario PivotTable Report.
- Ensure Result cells is still
B17:D17, then click OK.
✅ Part 3: Customize the PivotTable
a. Remove Filter Field
- Click anywhere in the PivotTable.
- If a Filter field is present (usually at the top-left), drag it out of the PivotTable Field List to remove it.
b. Change Number Format
- In the Values area of the PivotTable Field List, right-click each of the following fields:
- Profit_Per_Hour_Contracted_Mana
- Profit_Per_Hour_Contracted_Chan
- Profit_Per_Hour_Contracted_Proc
- Select Number Format.
- Choose Currency, set Decimal places to 2, and use $ as the symbol.
- Click OK for each.
c. Update Row Label Values
- In the PivotTable:
- Change cell B3 to “Management”.
- Change cell C3 to “Change”.
- Change cell D3 to “Process”.
✅ Explanation
Scenario analysis in Excel is a powerful tool for comparing multiple hypothetical business cases. In this task, we are analyzing three different strategies – Status Quo, Third Party, and Raise Rates – and their impact on profit per hour across three business segments.
First, the Scenario Summary Report provides a static table comparing the output values (B17:D17) for each scenario. These output cells likely contain formulas computing profit per hour for Management, Change, and Process categories. This report helps visually compare outcomes and identify which scenario is most beneficial.
Next, the Scenario PivotTable Report allows dynamic exploration. Unlike the static summary, the PivotTable can be rearranged to show key patterns, such as grouping by business unit or comparing value changes across scenarios. Removing the Filter field improves clarity by displaying all scenarios without user interaction.
The formatting step is essential for interpretation: converting profit values to Currency with 2 decimal places and dollar signs enhances readability and professional presentation. Adjusting row labels to Management, Change, and Process ensures the report aligns with business terminology.
Altogether, this scenario analysis provides management with the quantitative insight needed to decide whether to maintain current operations, outsource, or raise rates. Excel’s tools streamline this process, making financial comparisons accurate, flexible, and actionable.
