Sensitivity & Scenario Modeling

Build a sensitivity table in Excel (.xlsx) for our business model.

BASE CASE ASSUMPTIONS:
- Monthly revenue growth: ${growthRate}%
- Monthly churn: ${churnRate}%
- Starting MRR: $${mrr}
- Gross margin: ${grossMargin}%
- Monthly opex: $${opex}

OUTPUT METRIC: ${outputMetric}

SENSITIVITY AXES:
- Rows: Revenue growth rate from ${growthLow}% to ${growthHigh}% in ${growthStep}% increments
- Columns: Churn rate from ${churnLow}% to ${churnHigh}% in ${churnStep}% increments

REQUIREMENTS:
- Base case highlighted (bold + blue background)
- Conditional formatting: green for values above base case, red for below
- All cells should contain formulas that reference the model — not hardcoded values
- Include a separate "Assumptions" area where I can change inputs and the entire table recalculates
- Show the output metric at both 12-month and 24-month horizons (two tables)

Below the table, add a row showing: "If growth drops to X% with churn at Y%, we hit breakeven in Z months" — calculate the breakeven point for each cell.

Build a three-scenario financial model for the next ${years} years:

**BULL CASE** (things go right)
${bullAssumptions}

**BASE CASE** (plan)
${baseAssumptions}

**BEAR CASE** (things go wrong)
${bearAssumptions}

For each scenario, project monthly and show:
- Revenue
- Gross Profit
- EBITDA
- Cash Balance
- Runway (months)
- Headcount

OUTPUT FORMAT:
1. **Side-by-side annual summary table**
| Metric | Bull Y1 | Base Y1 | Bear Y1 | Bull Y2 | Base Y2 | Bear Y2 |

2. **Scenario comparison charts** (describe for chart creation)
- Revenue trajectory: 3 lines on one chart
- Cash balance trajectory: 3 lines, with a red "zero line" so I can see when cash runs out in the bear case

3. **Key decision points**
"In the bear case, we run out of cash in Month X. To extend runway to Month Y, we would need to cut $Z in monthly opex by Month W."
"In the bull case, we become cash-flow positive in Month X — ${months} months earlier than base."

4. **Probability weighting**
Expected value = (25% × Bull) + (50% × Base) + (25% × Bear) for each annual metric.

Make every assumption a named cell so I can adjust scenarios without hunting through formulas.

Build a Monte Carlo simulation in Python that models our revenue over ${months} months.

INPUT ASSUMPTIONS (with distributions):
- Monthly growth rate: Normal distribution, mean=${meanGrowth}%, std=${stdGrowth}%
- Monthly churn rate: Normal distribution, mean=${meanChurn}%, std=${stdChurn}%, min=0 (can't have negative churn)
- Starting MRR: $${mrr} (fixed)
- New deal probability per month: ${dealProb}%, deal size if won: Uniform $${dealMin}-$${dealMax}

SIMULATION:
- Run ${simulations} simulations
- Each simulation: for each month, sample growth rate, churn rate, and deal probability independently
- Calculate ending MRR for each month in each simulation

OUTPUT:
1. **Distribution chart** of MRR at Month ${months}
 - Histogram with 50 bins
 - Mark the 10th percentile, median, and 90th percentile

2. **Fan chart** showing the spread of outcomes over time
 - 10th-90th percentile band (light shading)
 - 25th-75th percentile band (medium shading)
 - Median line (dark)

3. **Probability table**
 - P(MRR > $X at Month ${months}) for X = ${targets}

4. **Cash runway analysis**
 - Given monthly opex of $${opex}, what's the probability we run out of cash before Month ${months}?
 - Show the distribution of "months until cash-flow positive"

5. **Key insights**
 - "There's a ${pct}% chance MRR exceeds $X by Month ${months}"
 - "The median outcome is $Y, but there's a 10% chance we're below $Z"
 - "Cash runway risk: ${riskPct}% probability of running out of cash"

Save all charts as PNGs and generate an HTML report with the charts embedded.

I have an existing financial model (uploading the file). Add guard clauses to prevent common formula errors:

1. **DIVISION BY ZERO**
Wrap all division formulas in =IFERROR(formula, "N/A") or =IF(denominator=0, 0, formula) — whichever is more appropriate for the context.

2. **NEGATIVE VALUES WHERE IMPOSSIBLE**
- Cash balance: =MAX(0, cash_formula) — or flag it red if cash goes negative (this is a real warning, not an error)
- Customer count: =MAX(0, count_formula)
- Churn rate: =MAX(0, MIN(1, churn_formula)) — can't churn more than 100%

3. **CIRCULAR REFERENCE DETECTION**
Check for any circular references in the model. If found, list them and suggest how to break the cycle.

4. **VALIDATION CHECKS TAB**
Add a new tab called "Checks" with these validation rows:
| Check | Formula | Result | Status |
- Balance sheet balances (A = L + E, within $1)
- Cash flow reconciliation (beginning + cash flows = ending)
- Revenue = sum of all revenue line items
- Total headcount = sum of department headcounts
- No negative customer counts
- Churn rate between 0% and 20% (flag if outside range)

Use conditional formatting: green "PASS" or red "FAIL" for each check.

5. **ERROR HIGHLIGHTING**
Add conditional formatting to the entire model: any cell containing #REF!, #DIV/0!, #N/A, or #VALUE! should be highlighted bright red with bold text.

I'm facing a decision: ${decision}

Build a scenario model that evaluates this decision across three outcomes:

**OPTION A: ${optionA}**
Assumptions: ${optionAAssumptions}

**OPTION B: ${optionB}**
Assumptions: ${optionBAssumptions}

**OPTION C: ${optionC} (status quo)**
Assumptions: ${optionCAssumptions}

For each option, model:
- 12-month P&L impact
- Cash impact (upfront cost + ongoing)
- Revenue impact (if applicable)
- Break-even point (when does the investment pay for itself?)

Then build a DECISION MATRIX:
| Criteria | Weight | Option A | Option B | Option C |
- Financial return (NPV or ROI)
- Cash outlay required
- Time to value
- Risk level
- Reversibility (can we undo this if it doesn't work?)

Weighted score at the bottom.

Finally: "Based on the financial analysis, Option X is recommended because..." (2-3 sentences). But flag any non-financial considerations that might override the financial answer.