10. Scenario Analysis: a Worst / Base / Best Switch

The future is unknown, so a financial model is run in several scenarios. A handy technique is a scenario table plus a switch cell: the table holds three values for each assumption, and cell G1 holds the number of the active scenario (1, 2 or 3). The whole model reads its assumptions through the switch.

• =CHOOSE($G$1,B2,C2,D2) returns B2 for 1, C2 for 2, D2 for 3. Simple and clear when there are few options;
• =INDEX(B2:D2,$G$1) does the same by position in the range. Convenient when there are many scenarios;
• =INDEX(B2:D2,MATCH("Base case",B1:D1,0)) picks the scenario by name instead of by number.

Switch G1 from 2 to 1 and the whole model instantly shows the worst case. No copies of the sheet like "Model (worst case)".

Desktop Excel has two more built-in tools (on the Data › What-If Analysis menu):

• Scenario Manager saves sets of values for the changing cells and builds a summary report across scenarios;
• Data Table recalculates a formula for a whole series of values of one or two inputs (for example, profit at 150…350 cups a day).

This trainer does not have those two tools, so here we build the switch with formulas. That approach works in every version of Excel and in Google Sheets, and it is easier to audit. The 20% tax rate is a practice number.