Scenario analysis in financial modeling

Forecast assumptions here are ratio-driven. Every driver is a ratio you just computed from history, projected forward with a story attached, the habit your forecasting work drilled into you. They're also scenario-structured from birth. Each driver gets three typed candidate rows (Base / Bull / Bear) plus one ACTIVE row that INDEX-selects whichever the switch names. Formulas on M only ever read ACTIVE rows, so they neither know nor care which scenario is live. That one design decision is what lets the whole model flip cases from one cell.

How does the INDEX scenario switch work?

The full A-tab map follows. Type the three candidate rows of each block in blue. The ACTIVE row carries the historical ratio formulas you just computed in C:E (green) and the INDEX selector in F:J (black). You write the INDEX pattern once and fill it right. For revenue growth, in F15, =INDEX(F12:F14,$C$6) reads "from the three cells stacked above me, take the one whose position equals the switch." CHOOSE does the same job as =CHOOSE($C$6,F12,F13,F14). INDEX wins because it takes a range, so inserting a fourth scenario row extends it naturally, while CHOOSE's argument list has to be hand-edited. OFFSET also works, but it's volatile. It recalculates on every sheet change and drags large models down, so it's best avoided on principle.

Which assumptions stay the same in every scenario?

Then the structural inputs, one row each, with the same value under every scenario. That's a modeling choice you can revisit. They're kept outside the blocks so the sheet is clear about what varies:

Notice the share count isn't one of these assumptions. It's a historical fact, typed on M in the equity schedule. Even flat assumptions get a whole row of cells. Say you want the tax rate to step one day. You retype cells and the formulas stay as they are. The step might come from India's new-regime ~25.17% for most large corporates versus concessional rates, or a US model stepping state taxes. Kaveri's 25% is a deliberate simplification of India's ~25.17% (22% + surcharge + cess) new-regime rate. Note it in the README. For a real company you'd anchor on its actual effective-rate history and the reconciliation in its tax note.

What story sits behind the base, bull and bear cases?

Each number needs a story behind it. An assumption without a story is a guess wearing a suit. The Base case extends history with a polite fade. Growth decays from 10% to 7%, because no business grows at a constant rate forever, and you've already met the fade. Margins stay flat, working-capital days are pinned at FY25 levels, and capex holds at its historical ~5.5% intensity. Bull is the story of distribution wins and premiumization. Growth runs 12% fading to 9%. Gross margin gains +100bp and opex leverage adds 50bp. Working capital gets slightly tighter, and capex rises to 6% to fund it. Because cash piles up, Bull also buys back ₹75 cr/yr from FY28.

The Bear case is a two-year demand recession with an input-cost spike. Revenue drops 2% twice and then crawls back. Gross margin is crushed to 36% before a partial recovery, and opex deleverages to 27% because costs are sticky when sales fall. Then comes the classic downturn working-capital trap. Receivables stretch to 55 days as customers pay slower, inventory bloats to 75 days because sales fell faster than purchasing, and suppliers tighten terms to 40 days. Every days assumption moves the way recessions really move them. Capex is slashed to 4%, the one lever management controls quickly.

Notice what you can't see yet. Does bear-Kaveri run out of cash? The model answers that once you flip the switch, and the answer is the whole reason revolvers get modeled.