Build a best, base, and worst case scenario model in Excel
Turns your assumptions into a simple Excel scenario model with an inputs tab, a scenario switch, and side-by-side results.
The prompt
Help me build a scenario model in Excel from my assumptions.
What the model needs to answer: [e.g. "next year's profit", "whether the new hire pays for itself", "cash at the end of Q4"]
Key assumptions and my base values: [LIST - e.g. "price $120", "volume 4,000 units/month", "churn 3%", "start date March"]
How the assumptions combine: [describe the logic or PASTE existing formulas]
Time horizon and granularity: [e.g. "12 months, monthly"]
What I already know about best and worst cases: [ranges, past highs and lows - or "suggest them"]
Build:
1. Inputs tab: every assumption in one place, one row each, with Base, Best, and Worst columns, units, and a source or rationale column. No numbers typed into formulas elsewhere.
2. Scenario switch: one dropdown cell (Data Validation list: Base, Best, Worst) and a "live" column using CHOOSE, INDEX/MATCH, or XLOOKUP to pull the selected values.
3. Calculation tab: the model logic referencing only the live column.
4. Results summary: key outputs for all three scenarios side by side, plus the difference from base. Keep it live with a one-variable Data Table (What-If Analysis) whose input cell is the scenario switch, so all three columns recalculate when an assumption changes; no macros or pasted values.
5. Consistency check: make sure each scenario is internally believable (e.g. high volume usually comes with higher costs, not just more revenue).
6. What would have to be true for the worst case to happen, and early warning signs to watch.
Return the tab layouts, the formulas for the switch and key outputs, the scenario values table with your reasoning for any you suggested, and the checks. Mark suggested ranges as [CONFIRM] - I own the final assumptions.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- describe the logic or PASTE existing formulas
- e.g. "12 months, monthly"
- ranges, past highs and lows - or "suggest them"
- CONFIRM
How this prompt is built
This prompt follows the Few-Shot structure (Few-shot prompting). A few-shot prompt includes one or more worked examples of the input and the output you want, so the model can copy the pattern instead of guessing it.
Related prompts
- Audit and retire unused or duplicate dashboardsA plan to inventory BI content, find what's unused or redundant, and retire it safely without breaking anyone's workflow.
- Explain, debug, or speed up a SQL, Python, or DAX snippetExplains what an existing query or script does, fixes errors, and optimizes it.
- Explain a dataset or report in plain business languageExplains what a dataset or report contains, what each column or metric means, and what it shows, in words anyone can follow.
- Find what my dashboard is not telling meReviews a dashboard for blind spots, missing comparisons, vanity metrics, and misleading views, and suggests what to add.