Project Finance & Financial Modelling · Financial modelling structure and best practice · lesson 10 of 20 · 13 min

Scenarios, sensitivities and downside cases

Why test the model?

A base case is a single view of the future. Lenders, sponsors and boards need to know how robust the project is: what has to go wrong for debt to be at risk or for equity returns to disappear?

Sensitivities vs scenarios

Sensitivity: change one input at a time (e.g., opex +10%) to see its effect.
Scenario: change several related inputs together to represent a coherent story (e.g., "high inflation": opex up, tariff indexation partly offsets, interest rates higher).
Breakeven analysis: find the value of an input at which a threshold is hit (e.g., the energy yield at which minimum DSCR falls to 1.0x).

Standard lender sensitivities (illustrative list)

| Sensitivity | Typical test | |---|---| | Construction delay | 6 months late | | Capex overrun | +10% | | Opex | +10% | | Revenue / volume | P90 yield, low traffic case | | Availability | −2 percentage points | | Inflation | ±2% per year | | Interest rate | +2% on unhedged portion | | Exchange rate | Local currency depreciation | | Combined downside | Several of the above together |

The exact sensitivities are agreed with lenders and their advisers.

Outputs to track

For every case, show: minimum and average DSCR, LLCR, equity IRR, project IRR, NPV, peak funding, and whether debt is fully repaid by maturity. A sensitivity summary table is standard:

Case                    Min DSCR  Avg DSCR  LLCR   Equity IRR  Debt repaid?
Base                    1.30x     1.38x     1.41x  12.8%       Yes
Opex +10%               1.24x     1.31x     1.34x  11.1%       Yes
P90 yield               1.18x     1.24x     1.27x  9.4%        Yes
6-month delay           1.30x     1.37x     1.39x  10.2%       Yes
Combined downside       1.03x     1.10x     1.12x  4.6%        Yes

(Numbers are illustrative.) Lenders will focus on the combined downside: debt service is still covered, but equity returns are thin. Sponsors may decide the risk is acceptable or restructure.

Building scenario functionality

Create a scenario selector input (1 = base, 2 = downside, etc.).
Store each scenario's input set in columns; the live input picks the selected column.
Use data tables or macros to run all cases and paste results into the summary automatically.
Keep the base case stable and clearly labelled; never overwrite base inputs to run a sensitivity.

Tornado charts

Rank sensitivities by their effect on a key output (e.g., equity IRR). The result looks like a tornado: the biggest drivers at the top. This focuses due diligence and negotiation on the inputs that matter most.

Worked example: breakeven

Illustrative. For a fictional toll road in Punjab, the team asks: how far can traffic fall below the base case before minimum DSCR hits 1.0x? The model shows traffic can fall 22% before debt service is not covered (illustrative). The traffic adviser's low case is 15% below base. The margin of safety between 15% and 22% gives lenders some comfort, but they request a debt service reserve account and a cash sweep if traffic underperforms.

Monte Carlo in financial models

Some teams apply probabilistic simulation to key inputs (yield, opex, delay) to produce distributions of DSCR and IRR. This adds insight when inputs are genuinely uncertain and data supports the ranges, but it must be explained clearly and never replace the agreed lender cases.

Common mistakes

Running sensitivities by overwriting base inputs.
Testing only favourable cases.
Scenarios whose inputs are inconsistent (e.g., high inflation with low interest rates without explanation).
Presenting dozens of sensitivities without highlighting what matters.
Forgetting to check whether the model's checks still pass in each case.

Presenting results

Decision-makers do not need every sensitivity. Present the base case, the lenders' key downside cases, the combined downside and one or two breakevens, with a sentence each explaining what they mean. For example: "Energy yield would have to fall 18% below P50 before minimum DSCR reaches 1.0x; the P99 case is 11% below P50." This turns a table of numbers into a clear statement of resilience.

Quick self-check

After running each case, confirm that the checks sheet still shows no errors, that debt is fully repaid by maturity, and that no reserve account goes negative. A sensitivity that breaks the model's mechanics is not a result; it is a bug to fix before anyone relies on the numbers.

Hands-on: scenario selector and data table in Excel

Inputs sheet: columns E Base | F Downside | G High inflation | H Combined ; D = Live
Scenario (named cell)  1..4
D5 (live)              =INDEX(E5:H5, Scenario)          fill down every scenario-driven input
Results block (outputs sheet)
   Min_DSCR  =MIN(DSCR_row)    Avg_DSCR =AVERAGEIF(Repay_flag_row,1,DSCR_row)
   LLCR_at_COD, Equity_IRR, Debt_repaid (=Final_balance<0.001), Master_check
Data table: list 1..4 down a column, results across the top row, Data > What-If Analysis > Data Table,
   column input cell = Scenario
Breakeven: Data > What-If Analysis > Goal Seek: set Min_DSCR to 1.0 by changing a yield or traffic haircut input

Data tables recalculate with every change and can slow large models; set calculation to "Automatic except data tables" and recalculate deliberately (F9) before reading results.

Hands-on: breakeven arithmetic in Python

target_dscr = 1.30
cfads = 10.0                              # USD M, illustrative
debt_service = cfads / target_dscr
print(f"CFADS headroom to 1.0x: {1 - debt_service / cfads:.1%}")      # = 1 - 1/1.30 ≈ 23.1%

# with fixed opex, revenue must fall by less than the CFADS headroom
revenue, opex = 14.0, 4.0                 # CFADS = revenue - opex
rev_breakeven = debt_service + opex
print(f"Revenue can fall {1 - rev_breakeven / revenue:.1%} before DSCR = 1.0x")

The second result (about 16.5%) shows why yield or traffic breakevens are tighter than the headline CFADS headroom when costs are fixed.

How to measure success

Every case in the summary shows a zero master check and debt repaid by maturity.
Resilience stated in one or two sentences with breakevens against adviser downside cases.
Base inputs never overwritten; scenario inputs documented with sources.

Video lecture: Scenarios, sensitivities and downside cases

Lecture coming soon · 9 chapters · about 8 minutes. Read the full transcript below.

What has to go wrong?
Why it matters
The concept: three kinds of test
Standard lender cases and outputs
Worked example one: a quick breakeven
Worked example two: a toll road in Punjab
Watch me do it: a scenario selector and a data table
Tornado charts and Monte Carlo
Common mistakes, recap and try this now

Lecture transcript

What has to go wrong?

Every base case is wrong. Not because modellers are careless, but because it's a single view of an uncertain future. So the question lenders, sponsors and boards really care about isn't 'what does the base case say?' It's 'what has to go wrong for debt to be at risk, or for equity returns to disappear?' In this lecture you'll learn the difference between sensitivities, scenarios and breakeven analysis, the standard cases lenders run, the outputs to track for every case, how to build scenario functionality without breaking your model, how to read a tornado chart, and when Monte Carlo simulation adds insight. By the end, you'll be able to present a project's resilience in a few clear sentences instead of a wall of numbers.

Why it matters

Why does this matter? Lenders size and price debt on downside cases, not on the sponsor's optimism. Sponsors need to know how thin their margin is before they commit equity. And decision-makers need resilience expressed in plain words they can act on. A table of forty sensitivities doesn't help a board. One sentence does: 'energy yield would have to fall eighteen per cent below P50 before minimum DSCR reaches one times; the P99 case is eleven per cent below P50.' That sentence tells everyone how much headroom there is, and where it comes from.

The concept: three kinds of test

There are three kinds of test. A sensitivity changes one input at a time, such as opex plus ten per cent, to see its effect. A scenario changes several related inputs together to tell a coherent story, such as high inflation: opex up, tariff indexation partly offsetting it, and interest rates higher. And a breakeven finds the value of an input at which a threshold is hit, for example the energy yield at which minimum DSCR falls to one times. Think of it like testing a bridge. A sensitivity adds weight in one place. A scenario simulates a storm: wind, rain and traffic together. And a breakeven finds exactly how much load the bridge can take before it fails. You need all three.

Standard lender cases and outputs

Lenders typically run a standard set, agreed with them and their advisers. Illustrative examples: a six-month construction delay, a ten per cent capex overrun, opex up ten per cent, P90 yield or a low traffic case, availability down two percentage points, inflation up or down two per cent a year, interest rates up on any unhedged portion, local currency depreciation, and a combined downside with several at once. For every case, track the same outputs: minimum and average DSCR, LLCR, equity IRR, project IRR, NPV, peak funding and whether debt is fully repaid by maturity. In the lesson's illustrative table, the combined downside shows minimum DSCR at one point nought three and equity IRR at four point six per cent. Debt is still covered, but equity returns are thin. Sponsors might accept that, or restructure.

Worked example one: a quick breakeven

Let's do a simple breakeven. Suppose a project's CFADS is ten million a year, and the debt was sized at a DSCR of one point three, so annual debt service is about seven point six nine million. At what CFADS does DSCR hit exactly one times? When CFADS equals debt service: seven point six nine million. So CFADS can fall by about twenty-three per cent before debt service isn't covered. That's the mathematical link between the sizing DSCR and your headroom: one minus one over one point three. Now translate it. If CFADS is driven mostly by energy yield, and opex is fixed, yield has to fall by somewhat less than twenty-three per cent to cause that CFADS fall, because fixed costs don't shrink with output. That's why you run the breakeven in the model, not on the back of an envelope.

Worked example two: a toll road in Punjab

Now the realistic example from the lesson. For a fictional toll road in Punjab, the team asks: how far can traffic fall below the base case before minimum DSCR hits one times? The model says twenty-two per cent, which is illustrative. The traffic adviser's low case is fifteen per cent below base. So the margin of safety sits between fifteen and twenty-two per cent. That gives lenders some comfort, but not a lot, because traffic forecasts on new roads can be wrong by more than that. So they ask for two protections: a debt service reserve account, to cover temporary shortfalls, and a cash sweep if traffic underperforms, to pay debt down faster. Notice how the breakeven turned into structure. That's the real purpose of sensitivity analysis: not just to report risk, but to design against it.

Watch me do it: a scenario selector and a data table

Let me show you how I build scenario functionality without breaking the model. On the inputs sheet, each scenario gets its own column: base, downside, high inflation and so on. A live column picks the selected scenario using CHOOSE or INDEX with a scenario number. The rest of the model only ever reads the live column. So I never overwrite base inputs, and the base case stays stable and clearly labelled. Then, to run all cases at once, I use a one-variable data table with the scenario number as the input, returning minimum DSCR, average DSCR, LLCR, equity IRR, whether debt is repaid, and, crucially, the master check. If any case returns a non-zero master check, that result isn't a result. It's a bug to fix before anyone relies on the numbers. Macros can do the same for heavier models.

Tornado charts and Monte Carlo

A tornado chart ranks sensitivities by their effect on a key output, such as equity IRR or minimum DSCR. The biggest drivers sit at the top, so it looks like a tornado. It's the best way to focus due diligence and negotiation on the inputs that matter. Monte Carlo simulation goes further, applying probability distributions to key inputs like yield, opex and delay to produce distributions of DSCR and IRR. That adds real insight when inputs are genuinely uncertain and data supports the ranges. But it must be explained clearly, and it never replaces the agreed lender cases, which remain the basis for sizing and covenants. We'll build a simple Monte Carlo on CFADS in the Python lesson later in this module.

Common mistakes, recap and try this now

The common mistakes. Running sensitivities by overwriting base inputs. Testing only favourable cases. Scenarios with inconsistent inputs, such as high inflation with low interest rates and no explanation. Presenting dozens of sensitivities without highlighting what matters. And forgetting to check whether the model's checks still pass in each case. So, to recap. Use sensitivities, scenarios and breakevens together. Run the agreed lender cases, track the same outputs for every case, and present the base case, the key downsides, the combined downside and one or two breakevens, each with a sentence explaining what it means. Your try-this-now: in any model you have, add a scenario selector with a base and a downside case, and produce a small results table with at least three outputs and the master check.

Key takeaways

Sensitivities change one input; scenarios change related inputs coherently; breakevens find thresholds.
Report min/avg DSCR, LLCR, IRRs and debt repayment for every case.
Build a scenario selector and automated results table; never overwrite the base case.
Use tornado charts and breakeven analysis to focus on what matters.

Try it

In any model you have, add a scenario selector with base and downside cases, and produce a small results table with at least three outputs.