Scenario Manager and Toggle Systems

The scenario toggle has three components. The Scenario Table stores assumptions for each scenario in a matrix format: rows are assumption names (rent growth, vacancy, expense growth, exit cap, etc.) and columns are scenarios (Conservative, Base, Optimistic). The Toggle Cell is a single dropdown cell (using Data Validation) where the user selects the active scenario. The Lookup Formulas use INDEX/MATCH or XLOOKUP to pull the active scenario's assumptions from the table into the model's assumption cells. When the toggle changes, every linked assumption updates instantly, and the entire model recalculates. This approach ensures consistency—you cannot accidentally mix Conservative vacancy with Optimistic rent growth—and enables rapid side-by-side comparisons.

The Summary Dashboard should display key metrics for all scenarios simultaneously, not just the active one. Create a summary table that runs each scenario through the model (using formulas that reference each column of the scenario table) and displays IRR, equity multiple, Year 1 CoC, average DSCR, and maximum cash deficit for Conservative, Base, and Optimistic side by side. This requires either: (a) building three parallel calculation columns (one per scenario) in the model, or (b) using a macro that toggles through each scenario, records the outputs, and populates the summary table. The parallel column approach is more transparent and auditable; the macro approach is simpler but harder to audit.

Sophisticated models add features beyond the basic toggle. Probability weights allow calculation of a probability-weighted expected return across scenarios. A "Custom" scenario option lets the user override specific assumptions while keeping others at the base case. Dynamic charts that update when the toggle changes provide visual scenario comparison. Conditional formatting on the dashboard turns metric cells red when they fall below minimum thresholds in any scenario. Finally, a scenario log records the date, author, and rationale for each scenario's assumptions, creating an audit trail that documents the analytical thinking behind the numbers.