A scenario table keeps three budgets side by side, lean, base and stretch, and one dropdown cell decides which is live. A column of INDEX and MATCH formulas pulls the chosen budget, and every calculation downstream reads that live column, so changing the dropdown changes the whole sheet at once. The same steps work in Excel and Google Sheets.
How do I lay out the table?
Row 1 holds the headings. Say this is your table, in made-up rupees:
| Item | Lean | Base | Stretch |
|---|---|---|---|
| Income | 80,000 | 80,000 | 80,000 |
| Rent | 20,000 | 20,000 | 20,000 |
| Groceries | 8,000 | 10,000 | 12,000 |
| Everything else | 22,000 | 26,000 | 30,000 |
| Savings (income minus the three spending lines) | 30,000 | 24,000 | 18,000 |
In cells B6, C6 and D6 the savings come from =B2-SUM(B3:B5), and so on across. Check the first: 80,000 minus 20,000 minus 8,000 minus 22,000 is 30,000. For base, 80,000 minus 56,000 is 24,000; for stretch, 80,000 minus 62,000 is 18,000.
How do I add the dropdown?
- Click G1 and type a label in F1, such as Scenario.
- In Excel choose Data, Data Validation, Allow: List. In Google Sheets choose Data, Data validation, then Dropdown.
- Type the source as Lean,Base,Stretch, or point it at the headings in B1:D1.
- Choose Base for now. The validation stops typing mistakes such as "base case" that would break the lookup.
How does the live column work?
Add a heading Live in E1 and copy this formula down from E2 to E6:
- =INDEX(B2:D2,MATCH($G$1,$B$1:$D$1,0))
- Alternative with CHOOSE: =CHOOSE(MATCH($G$1,$B$1:$D$1,0),B2,C2,D2)
- In Excel 365 and 2021 only: =XLOOKUP($G$1,$B$1:$D$1,B2:D2)
MATCH finds which of the three headings equals your choice, returning 1, 2 or 3. INDEX then takes that position from the row. The final 0 asks for an exact match, and leaving it out is the classic cause of wrong answers.
How do I make everything else follow?
Point every other formula at column E, never at B, C or D. A savings rate cell can be =E6/E2: with the lean case selected it shows 30,000 / 80,000, which is 37.5 percent; base shows 30 percent; stretch shows 22.5 percent. A chart of the budget lines, a yearly total such as =E6*12, a runway calculation: all of them need only E. Selecting Stretch changes them in one click, and the yearly savings drop from ₹3,60,000 to ₹2,16,000.
What is the difference between lean, base and stretch?
- Lean: the version you could hold for a few tight months. Cuts that are real but temporary.
- Base: what you expect on a normal month. Use your recent averages.
- Stretch: a looser budget with festival months, travel or a higher grocery bill.
The labels are yours; what matters is that each is a plan you could genuinely follow. If the lean case needs a cut you have never managed, it is a wish, not a scenario.
What could go wrong?
- The lookup returns #N/A: the dropdown cell is empty or has text that does not match a heading exactly.
- Numbers do not change when you switch: a formula somewhere still points at column B or C.
- Spaces: "Base " with a trailing space fails to match. The dropdown avoids this.
A short check
- Switch through all three options and look at the savings row each time.
- Compare the live savings with a hand calculation for one scenario.
- Keep a note of which assumptions changed, so the table does not turn into mystery numbers by next year.
You can also add a row for a goal that matters, such as a monthly top-up to an emergency fund, and see which scenario still pays for it. If the stretch case leaves ₹18,000 and the goal needs ₹20,000, you know before the month starts.
The base column works best when it comes from real spending. SpendixAI can import statements, group spending by category and show you averages to start from.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

