Spreadsheets

Scenario Manager in Excel: save and compare budget cases

Use Excel's Scenario Manager to save named budget cases with rent, income and EMI as changing cells, then build a Scenario Summary report to compare them.

Editorial illustration for Scenario Manager in Excel: save and compare budget cases

Scenario Manager lets you store several sets of values for the same input cells, name each set, and switch between them or print them side by side in a Scenario Summary report. You find it under Data, What-If Analysis, Scenario Manager in desktop Excel. It suits a one-off comparison such as staying put against moving, more than a budget you open every month.

How do I prepare the sheet?

Scenario Manager changes input cells and reads result cells, so you need both. Say this is your sheet, with invented figures:

CellMeaningCurrent value (₹)
B2Monthly income (changing cell)85,000
B3Rent (changing cell)20,000
B4EMI (changing cell)12,000
B5Other spending30,000
B6Monthly savings, =B2-B3-B4-B5 (result cell)23,000

The inputs must be typed values, not formulas, and the result must be a formula that uses them.

How do I save the first scenario?

  1. Choose Data, What-If Analysis, Scenario Manager, then Add.
  2. Name it "Current". For Changing cells, select B2:B4, holding Ctrl to pick separate cells if needed.
  3. Click OK and confirm the values, which should be the ones already in the cells. Click OK again.
  4. Add a second scenario, "Income dip", and change B2 to 68,000 in the values box. Rent and EMI stay as they are.
  5. Add a third, "Smaller flat", with B3 at 16,000 and income back at 85,000.

Save "Current" first. Showing any other scenario overwrites the live input cells, and "Current" is your way back.

What does the Scenario Summary report show?

In Scenario Manager click Summary, choose Scenario summary, and set the result cell to B6. Excel creates a new sheet with the current values and each scenario side by side:

CaseIncome (₹)Rent (₹)EMI (₹)Savings (₹)
Current85,00020,00012,00023,000
Income dip68,00020,00012,0006,000
Smaller flat85,00016,00012,00027,000

Check the arithmetic against B6. With the dip, 68,000 minus 20,000 minus 12,000 minus 30,000 leaves 6,000, and with the cheaper flat 85,000 minus 16,000 minus 12,000 minus 30,000 leaves 27,000. The report needs the numbers to speak for themselves, so keep the scenario names short and clear.

What are the limits of Scenario Manager?

  • Excel allows up to 32 changing cells per scenario.
  • The scenarios are hidden in a dialog box, so another person opening the file will not see them unless you point them to the menu.
  • Showing a scenario overwrites your inputs. It is easy to leave the sheet in the wrong case.
  • It is a desktop Excel feature. Check whether your version, an online version or a phone app offers it before you rely on it.
  • It has no Google Sheets equivalent.

When is a dropdown table better?

If you will switch between the same few cases all year, a table of columns with a dropdown, as in a lean, base and stretch layout, keeps everything visible on the sheet and updates charts instantly. Scenario Manager is better when you want a single printed comparison from one set of inputs, or when you want to keep a stack of what-ifs without cluttering the sheet.

Before you share the file

  • Show "Current" and save the file in that state.
  • Delete scenarios you no longer need, using Delete in the dialog.
  • Add a note beside the inputs saying a scenarios list exists.

Write down what each scenario assumes in a cell beside the inputs, for example that Income dip means a second income stops for three months. Six months later the scenario names alone will not remind you why a number was chosen, and a note costs ten seconds.

If you find yourself re-entering income and spending figures each time, SpendixAI keeps transactions and category totals in one workspace, which gives you the real numbers to put into scenarios like these.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports