Scenario Manager in Excel: Compare Best, Likely and Worst Case
Shahrukh is planning next month for a quick-commerce dark store in Nagpur. Orders, average order value and cost share could each go up or down. His manager wants three versions: Best, Likely and Worst. Rather than three copies of the sheet, he saves three scenarios on one model.
Scenario Manager (Data > What-If Analysis > Scenario Manager) saves named sets of values for up to 32 input cells. Show puts a scenario's values into the cells, and Summary builds a report that compares the result cells for every scenario side by side.
Download the practice file (scenario-manager.xlsx)
Excel file, 6 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.
Quick answer
- Build the model: orders in B2, average order value in B3, cost share in B4, profit in B7.
- Click Data > What-If Analysis > Scenario Manager > Add.
- Name it
Likely, Changing cellsB2:B4, click OK, type the values, click OK. - Add
BestandWorstthe same way. - Click Summary, choose Scenario summary, Result cells
B7, and click OK.
The model
| Cell | Item | Formula or value |
|---|---|---|
| B2 | Orders | input |
| B3 | Average order value (₹) | input |
| B4 | Cost share (product + delivery) | input |
| B5 | Revenue | =B2*B3 |
| B6 | Fixed cost | ₹4,50,000 |
| B7 | Profit | =B5*(1-B4)-B6 |
Three scenarios
| Scenario | Orders | AOV | Cost share | Profit |
|---|---|---|---|---|
| Likely | 5,200 | ₹420 | 32% | ₹10,35,120 |
| Best | 6,400 | ₹450 | 30% | ₹15,66,000 |
| Worst | 4,100 | ₹390 | 35% | ₹5,89,350 |
These are fictional planning numbers. The Worst case shows a loss, which is exactly what the manager wants to see before signing the rent agreement.
Show and Summary
- In Scenario Manager, select Best and click Show. B2:B4 change and B7 recalculates.
- Click Summary, keep Scenario summary, type
B7in Result cells (you can add B5 too), click OK. - Excel adds a Scenario Summary sheet with columns Current Values, Likely, Best and Worst.
- Give the input cells names (Orders, AOV, CostShare) before you make the summary; the report then shows names instead of $B$2.
No Scenario Manager? Use a switch
The practice file also has a formula version: a drop-down in E2 picks the scenario and =INDEX(H3:J3,MATCH($E$2,$H$2:$J$2,0)) pulls its values. It works in every Excel, including the web.
Ravindra Bagale's Tip
Name the input cells first (Formulas > Define Name). A summary that says Orders, AOV and CostShare can go straight to your manager; one that says $B$2, $B$3, $B$4 needs an explanation.
Ravindra Bagale's Tip – मराठी
आधी input cells ला नावं द्या (Formulas > Define Name). Orders, AOV, CostShare असं लिहिलेली summary थेट manager ला पाठवता येते; $B$2, $B$3, $B$4 वाली summary समजावून सांगावी लागते.
Ravindra Bagale's Tip – हिंदी
पहले input cells को नाम दीजिए (Formulas > Define Name)। Orders, AOV, CostShare वाली summary सीधे manager को भेजी जा सकती है; $B$2, $B$3, $B$4 वाली summary समझानी पड़ती है।
Practice task
- Open the Model sheet. Add the Likely, Best and Worst scenarios with changing cells B2:B4.
- Show each one and note the profit in B7.
- Create a Scenario summary with result cell B7.
- Check the numbers against the formula switch: change E2 to Best, Likely and Worst and read E7.
- Add a fourth scenario called Festive with 7,000 orders.
Learn it properly
Course lessons:
Related guides: Goal Seek in Excel: Work Backwards to the Number You Need · Excel Data Tables: See Many What-If Answers at Once · Named Ranges in Excel: Name a Cell or List and Use It in Formulas · Dependent Drop-Down List in Excel (City, then Area)
Frequently asked questions
How many changing cells can a scenario have?
Up to 32.
Where are scenarios stored?
On the sheet where you created them. Use Merge in Scenario Manager to copy scenarios from another sheet.
Does the summary update when I change a scenario?
No. The summary is a static report. Create it again after changes.
Is Scenario Manager in Excel for the web?
No. Use the formula switch method shown in the practice file.