Generated Skill

EXCEL
=IF(Scenario="Base", Revenue_Base, IF(Scenario="Bull", Revenue_Bull, Revenue_Bear))
=Revenue * (1 + Growth_Rate_Assumption)
=RAND() * (Upper_Bound - Lower_Bound) + Lower_Bound

Create these named ranges first: Scenario, Revenue_Base, Revenue_Bull, Revenue_Bear

Setup (Tabs: Assumptions, Model, Scenarios, Sensitivity, Monte Carlo)

Progress:

• Build assumptions dashboard with scenario toggles
• Create 3-statement model with dynamic formulas
• Set up scenario comparison table
• Build sensitivity analysis grid
• Add Monte Carlo simulation engine

Step 1: Assumptions Table

EXCEL
// Tab: Assumptions
A1: Scenario Selector | B1: [Data Validation: Base/Bull/Bear]
A3: Revenue Growth
B3: Base | C3: =IF($B$1="Base",0.15,IF($B$1="Bull",0.25,0.05))
A4: Gross Margin % | B4: =INDEX(Margin_Table,MATCH($B$1,Scenarios,0))
A5: Working Capital % of Rev | B5: [Similar lookup]

Step 2: 3-Statement Model Structure

EXCEL
// Income Statement
Revenue (t+1) = Revenue(t) * (1 + Growth_Rate)
COGS = Revenue * (1 - Gross_Margin%)
EBITDA = Gross_Profit - OpEx
Interest = Average_Debt * Interest_Rate
Tax = (EBITDA - Depreciation - Interest) * Tax_Rate

// Balance Sheet  
Cash(t+1) = Cash(t) + Cash_Flow_from_Operations + Financing - CapEx
Working_Capital = Revenue * WC_%
PP&E_Net = PP&E_Gross - Accumulated_Depreciation
Total_Debt = Previous_Debt + New_Borrowing - Repayments

// Cash Flow
CFO = EBITDA - Tax_Paid - Working_Capital_Change
CFI = -CapEx
CFF = Debt_Issuance - Debt_Repayment - Dividends

Step 3: Scenario Framework

Create scenario comparison table:

EXCEL
           Base    Bull    Bear
Revenue    100     125      75
EBITDA      15      22       5
FCF         10      18      -2

Step 4: Sensitivity Analysis Grid

EXCEL
=OFFSET(Base_FCF, ROW()-ROW($B$1), COLUMN()-COLUMN($B:$B)) * 
(1 + Revenue_Sensitivity * Growth_Delta + Margin_Sensitivity * Margin_Delta)

Step 5: Monte Carlo Engine

EXCEL
// Random Variables
Growth_Rate = NORM.INV(RAND(), Mean_Growth, StdDev_Growth)
Margin = NORM.INV(RAND(), Mean_Margin, StdDev_Margin)

// Probability Weights
Base_Probability = 0.5
Bull_Probability = 0.3  
Bear_Probability = 0.2
Expected_Value = Base_FCF*0.5 + Bull_FCF*0.3 + Bear_FCF*0.2

Example 1: Input: SaaS company, $10M ARR, 30% growth base case Output:

• Base: 30% growth, 75% gross margin, $2M FCF by Y3
• Bull: 45% growth, 78% gross margin, $5M FCF by Y3
• Bear: 15% growth, 72% gross margin, $0.5M FCF by Y3
• Sensitivity shows FCF most sensitive to churn rate (-2x impact vs growth)

Example 2: Input: Manufacturing company, $50M revenue, cyclical industry Output:

• Working capital swings from 15% (bull) to 25% (bear) of revenue
• CapEx scales with capacity utilization (60%-90% range)
• Monte Carlo shows 70% probability of positive FCF, 30% probability of cash shortfall

Model Architecture:

• Use INDIRECT() for dynamic sheet references across scenarios
• Name all key ranges for formula clarity
• Separate assumptions from calculations completely
• Build checks: Assets = Liabilities + Equity on every row

Scenario Design:

• Base case = most likely, not middle of Bull/Bear
• Bull/Bear should be realistic extremes (90th/10th percentile)
• Include qualitative scenario descriptions alongside numbers
• Test model with extreme inputs (10x growth, negative margins)

Monte Carlo Setup:

• Run 1000+ iterations minimum
• Use triangular distribution for bounded variables
• Normal distribution for continuous variables
• Correlate related variables (revenue growth + margins)

Performance:

• Use manual calculation mode for large models
• Minimize volatile functions (RAND, NOW, INDIRECT)
• Pre-calculate lookup tables vs nested IFs

Don't:

• Hardcode numbers in formulas - use cell references
• Build circular references without enabling iterative calculation
• Forget to stress-test debt capacity in bear case
• Use percentage changes from different bases across scenarios
• Ignore working capital seasonality in monthly models
• Mix scenario assumptions (bull revenue with bear margins)

Model Breaks:

• Negative cash without revolver facility
• Debt/EBITDA covenants violated
• Working capital that doesn't scale properly
• CapEx that creates immediate depreciation mismatches