Build Financial Models with AI: DCF, Forecasting & Scenario Analysis
Learn how to build DCF models, revenue forecasts, and scenario analyses with AI automation. Step-by-step guide for finance professionals.
Andrew Grosser
April 17, 2026 • 11 min read
You just spent six hours building a three-statement financial model in Excel. The revenue formulas link to a separate assumptions tab. Your DCF valuation pulls from a discount rate sheet. Then your boss asks for a sensitivity table showing five scenarios across three variables. That's another two hours of manual work, and one wrong cell reference breaks the entire model.
Financial modeling doesn't have to be this painful. AI can automate the formula writing, link assumptions across sheets, generate sensitivity tables, and build scenario analyses in seconds. This guide shows you how to build professional-grade DCF models, revenue forecasts, and scenario analyses using AI—with the same rigor investment banks and FP&A teams demand.
Sourcetable's AI data analyst is free to try. Sign up here.
What Is AI Financial Modeling?
AI financial modeling uses natural language processing to automate the construction of valuation models, forecasts, and scenario analyses. Instead of manually writing hundreds of Excel formulas, you describe what you want—"Build a five-year DCF with 10% WACC" or "Create a revenue sensitivity table varying growth rate and price"—and AI generates the complete model structure, formulas, and formatting.
Traditional financial modeling requires deep Excel knowledge. You need to understand absolute versus relative cell references, nested IF statements, INDEX-MATCH lookups, and circular reference handling for debt schedules. A typical three-statement model contains 300-500 formulas. A DCF adds another 100-150 calculations for terminal value, present value discounting, and enterprise value waterfalls.
AI collapses this complexity. You provide historical financials and assumptions. The AI builds the income statement, balance sheet, cash flow statement, links them correctly, calculates free cash flow, applies discount rates, and outputs valuation ranges. What took six hours now takes six minutes. More importantly, the AI documents every assumption and makes models auditable—critical for investor presentations and board reviews.
Building a DCF Model: Manual Method vs AI
A discounted cash flow model values a company by projecting future free cash flows and discounting them to present value. Here's the standard five-step process investment banks use:
- Project revenue: Historical growth rate × (1 + growth assumption) for each forecast year
- Build income statement: Revenue × margin assumptions = EBITDA, subtract D&A and taxes = NOPAT
- Calculate free cash flow: NOPAT + D&A - CapEx - Change in NWC = Unlevered FCF
- Determine terminal value: Final year FCF × (1 + perpetual growth) / (WACC - perpetual growth)
- Discount to present value: Each FCF / (1 + WACC)^year, sum all periods + terminal value
Let's work through a real example. You're valuing a SaaS company with $50M revenue, 25% EBITDA margin, $5M CapEx, and 15% revenue growth. WACC is 12%, terminal growth is 3%.
| Year | Revenue | EBITDA | FCF | PV Factor | PV of FCF |
|---|---|---|---|---|---|
| Year 1 | $57.5M | $14.4M | $9.4M | 0.893 | $8.4M |
| Year 2 | $66.1M | $16.5M | $11.5M | 0.797 | $9.2M |
| Year 3 | $76.0M | $19.0M | $14.0M | 0.712 | $10.0M |
| Year 4 | $87.4M | $21.9M | $16.9M | 0.636 | $10.7M |
| Year 5 | $100.5M | $25.1M | $20.1M | 0.567 | $11.4M |
| Terminal Value (Year 5 FCF × 1.03 / 0.09) | 0.567 | $130.2M | |||
| Enterprise Value | $179.9M | ||||
Building this manually in Excel requires 40+ formulas across multiple sheets. You need revenue growth formulas that reference an assumptions tab, EBITDA calculations that multiply revenue by margin, FCF formulas that subtract CapEx and working capital changes, discount factors using exponentiation, and terminal value calculations that handle the perpetuity formula correctly. One misplaced dollar sign in a cell reference breaks the entire model.
With Sourcetable's AI, you describe the model in plain English: "Build a five-year DCF for a company with $50M revenue growing at 15%, 25% EBITDA margin, $5M annual CapEx, 12% WACC, and 3% terminal growth." The AI generates the complete model in 30 seconds—revenue projections, income statement, free cash flow calculations, discount factors, terminal value, and enterprise value output. It formats the table, labels every row, and creates an assumptions section you can adjust.
Revenue Forecasting with AI: From Historical Data to Projections
Revenue forecasting combines historical trend analysis with forward-looking assumptions. The standard approach uses compound annual growth rate (CAGR) as the baseline, then adjusts for market conditions, product launches, and seasonality.
Calculate CAGR manually: ((Ending Value / Beginning Value)^(1 / Number of Years)) - 1. For a company that grew from $30M to $50M over three years, that's (($50M / $30M)^(1/3)) - 1 = 18.6% CAGR. Apply this to Year 1: $50M × 1.186 = $59.3M. Repeat for each forecast year.
Real forecasts layer in complexity. SaaS companies model new customer acquisition, expansion revenue from existing accounts, and churn. The formula becomes: (Beginning ARR × (1 - Churn Rate)) + (New Customers × Average Contract Value) + (Expansion Revenue). If you start with $50M ARR, lose 8% to churn, add $12M in new logos, and expand existing accounts by $5M, Year 1 revenue is ($50M × 0.92) + $12M + $5M = $63M.
| Component | Year 0 | Year 1 | Year 2 | Year 3 |
|---|---|---|---|---|
| Beginning ARR | $50.0M | $63.0M | $78.4M | $96.3M |
| Retained (92%) | — | $46.0M | $58.0M | $72.1M |
| New Logos | — | $12.0M | $14.4M | $17.3M |
| Expansion | — | $5.0M | $6.0M | $6.9M |
| Total ARR | $50.0M | $63.0M | $78.4M | $96.3M |
| Growth Rate | — | 26.0% | 24.4% | 22.8% |
This three-component model requires linking assumptions across multiple sheets. Churn rate lives in one tab, new customer acquisition in another, expansion assumptions in a third. Each year's calculation references the prior year's ending ARR. Miss one link and your entire forecast compounds the error.
AI handles this complexity automatically. Upload your historical revenue data and describe your business model: "I have $50M ARR with 8% annual churn. New customer bookings grow 20% per year starting at $12M. Existing customers expand at 10% annually." The AI builds the cohort model, links all assumptions, and generates a five-year forecast with monthly granularity if needed. Change churn from 8% to 10%? The entire model recalculates instantly.
Scenario Analysis: Modeling Best Case, Base Case, and Worst Case
Scenario analysis tests how different assumptions affect outcomes. Investment committees want to see three scenarios: base case (most likely), upside case (optimistic but achievable), and downside case (conservative but realistic). Each scenario uses different growth rates, margins, and market conditions.
Build scenarios by creating three columns in your model. Base case uses 15% revenue growth and 25% EBITDA margin. Upside assumes 20% growth and 28% margin (successful product launch, market expansion). Downside models 10% growth and 22% margin (increased competition, economic slowdown).
| Metric | Downside | Base Case | Upside |
|---|---|---|---|
| Revenue Growth | 10% | 15% | 20% |
| EBITDA Margin | 22% | 25% | 28% |
| Year 5 Revenue | $80.5M | $100.5M | $124.4M |
| Year 5 EBITDA | $17.7M | $25.1M | $34.8M |
| Year 5 FCF | $12.7M | $20.1M | $29.8M |
| Enterprise Value | $125.3M | $179.9M | $258.1M |
Building three scenarios manually means duplicating your entire model three times or using complex IF statements that check a scenario flag cell. The formula becomes: =IF($A$1="Base", Revenue*1.15, IF($A$1="Upside", Revenue*1.20, Revenue*1.10)). Multiply this across 300 formulas and you've created a maintenance nightmare.
Sourcetable's AI builds all three scenarios simultaneously. Describe your assumptions: "Base case: 15% growth, 25% margin. Upside: 20% growth, 28% margin. Downside: 10% growth, 22% margin." The AI creates a scenario comparison table showing Year 5 revenue, EBITDA, FCF, and valuation across all three cases. Want to add a fourth scenario testing recession conditions? Add one line of description and the model updates.
Sensitivity Tables: Testing Multiple Variables Simultaneously
Sensitivity analysis shows how changes in two variables affect a key output. Investment bankers use two-way data tables to test combinations of revenue growth and EBITDA margin, or WACC and terminal growth rate. The output is a grid showing enterprise value at every combination.
Excel's Data Table function creates these grids, but the setup is finicky. You need a formula in the top-left cell, row inputs across the top, column inputs down the left side, then select the entire range and apply Data > What-If Analysis > Data Table. Specify which cells the row and column values should substitute into. Miss one step and you get #REF errors.
Here's a sensitivity table testing revenue growth (10%-20%) versus EBITDA margin (20%-30%) for our SaaS company:
| Enterprise Value ($M) | 20% Margin | 22% Margin | 25% Margin | 28% Margin | 30% Margin |
|---|---|---|---|---|---|
| 10% Growth | $98.2 | $110.5 | $128.7 | $146.9 | $159.2 |
| 12% Growth | $108.4 | $121.9 | $142.0 | $162.1 | $175.6 |
| 15% Growth | $123.1 | $138.5 | $161.3 | $184.1 | $199.5 |
| 18% Growth | $141.7 | $159.4 | $185.7 | $212.0 | $229.7 |
| 20% Growth | $158.3 | $178.1 | $207.5 | $236.9 | $256.7 |
The base case (15% growth, 25% margin, highlighted) shows $161.3M valuation. If growth accelerates to 20% and margin expands to 28%, valuation jumps to $236.9M (+47%). If growth slows to 10% and margin compresses to 22%, valuation drops to $110.5M (-31%). This range gives investors realistic bounds for decision-making.
Building this manually in Excel takes 15-20 minutes. You need to set up the Data Table correctly, ensure absolute and relative references are correct, and format conditional shading to highlight the base case. Add a third variable (like WACC) and you need a three-dimensional sensitivity analysis—impossible to visualize in a standard Excel grid.
Sourcetable generates sensitivity tables from a single prompt: "Create a two-way sensitivity table showing enterprise value with revenue growth from 10% to 20% in 2% increments and EBITDA margin from 20% to 30% in 2% increments." The AI builds the grid, calculates all 25 combinations, and formats the output with conditional shading. Need a three-way analysis? The AI creates multiple tables or interactive visualizations showing all dimensions.
When AI Financial Modeling Fails: Limitations and Gotchas
AI financial modeling works brilliantly for standard structures—DCF valuations, revenue forecasts, sensitivity tables. It struggles with highly customized models that require industry-specific nuances or regulatory constraints. Here's where you'll hit problems:
Circular references in debt schedules: Models with revolving credit facilities create circular logic—interest expense depends on debt balance, but debt balance depends on cash flow, which depends on interest expense. AI can build the structure, but you may need to manually enable iterative calculations in settings and verify convergence.
Complex working capital modeling: If your business has 90-day payment terms, seasonal inventory buildups, and customer deposit timing, the working capital change calculation gets intricate. AI handles simple "NWC = 10% of revenue" assumptions well. Multi-line item balance sheet modeling with days sales outstanding, days inventory outstanding, and days payable outstanding requires more detailed prompting or manual refinement.
Merger models with synergies: M&A models layer in revenue synergies (cross-selling), cost synergies (headcount reduction), and one-time integration costs. AI can build the framework, but accurately estimating synergy realization schedules (20% in Year 1, 60% in Year 2, 100% in Year 3) requires human judgment about integration complexity.
Regulatory capital requirements: Banks and insurance companies have risk-weighted asset calculations and regulatory capital minimums. AI doesn't inherently understand Basel III or Solvency II frameworks. You'll need to provide detailed specifications for these constraints.
The solution isn't avoiding AI—it's using AI for 80% of the mechanical work (structure, formulas, formatting) and applying your expertise to the 20% that requires industry knowledge and judgment. Build the base model with AI in minutes, then refine assumptions, add custom constraints, and validate outputs against comparable company analysis.
Real-World Workflow: Building an Investment Committee Model in 30 Minutes
Let's walk through a complete workflow. You're evaluating a $75M revenue SaaS company for acquisition. The investment committee meets in two hours. You need a full model with DCF valuation, scenario analysis, and sensitivity tables. Here's the 30-minute process:
Minutes 0-5: Data gathering. Pull three years of historical financials from the data room—revenue, EBITDA, CapEx, working capital. Export to CSV or copy into your spreadsheet. Collect management's assumptions: 18% revenue growth, 27% EBITDA margin target, $8M annual CapEx, 11% WACC, 3% terminal growth.
Minutes 5-10: Base model build. In Sourcetable, describe the model: "Build a five-year DCF starting with $75M revenue growing at 18% annually. EBITDA margin is 27%. CapEx is $8M per year. Working capital is 5% of revenue change. WACC is 11%, terminal growth is 3%. Show me enterprise value and equity value after subtracting $20M net debt."
The AI generates the complete three-statement model in 45 seconds. Revenue grows from $75M to $163M by Year 5. EBITDA expands from $20.3M to $44.0M. Free cash flow after CapEx and working capital reaches $35.2M in Year 5. Terminal value is $402M. Present value of cash flows plus terminal value yields $318M enterprise value. Subtract $20M debt for $298M equity value.
Minutes 10-15: Scenario analysis. Ask the AI: "Create three scenarios. Base case uses current assumptions. Upside assumes 22% growth and 29% margin. Downside assumes 14% growth and 25% margin." The AI builds a comparison table showing equity value ranges from $241M (downside) to $298M (base) to $387M (upside). This gives the committee a $146M valuation range.
Minutes 15-25: Sensitivity tables. Request two sensitivity analyses: "First table: revenue growth from 12% to 24% versus EBITDA margin from 23% to 31%. Second table: WACC from 9% to 13% versus terminal growth from 2% to 4%." The AI generates both grids showing how valuation changes with each combination. The first table reveals valuation is more sensitive to margin (±$50M per 2% change) than growth (±$35M per 2% change). The second shows WACC sensitivity is significant (±$45M per 1% change).
Minutes 25-30: Formatting and documentation. Add conditional formatting to highlight the base case in each table. Insert a summary page with key metrics: current revenue multiple (4.0x), EBITDA multiple (14.7x), implied IRR at $280M purchase price (18.2%). Export to PDF for distribution. Total time: 28 minutes. Traditional Excel approach: 6-8 hours.
Advanced Techniques: Monte Carlo Simulation and Probabilistic Forecasting
Deterministic models show one outcome per scenario. Probabilistic models run thousands of simulations with random variation in assumptions, producing a distribution of outcomes. This answers questions like: "What's the probability we achieve at least $300M valuation?" or "What's the 90th percentile downside case?"
Monte Carlo simulation requires defining probability distributions for each assumption. Revenue growth might be normally distributed with mean 18% and standard deviation 4%. EBITDA margin could be triangular with minimum 24%, most likely 27%, maximum 30%. Run 10,000 iterations, each drawing random values from these distributions, calculating enterprise value each time.
The output is a distribution showing: 10th percentile outcome $225M, median $298M, 90th percentile $385M. You can calculate probability of outcomes: 73% chance of achieving $280M+ valuation, 42% chance of $320M+, 12% chance of $360M+. This gives investment committees realistic risk-adjusted expectations.
Building Monte Carlo simulations manually requires add-ins or custom code. Sourcetable's AI handles this natively: "Run a Monte Carlo simulation with 10,000 iterations. Revenue growth is normally distributed, mean 18%, standard deviation 4%. EBITDA margin is triangular, min 24%, mode 27%, max 30%. Show me the distribution of enterprise values and probability of exceeding $300M."
The AI runs the simulation, generates a histogram of outcomes, calculates percentiles, and outputs probability thresholds. You get institutional-grade risk analysis without learning specialized software or writing code. This level of sophistication typically requires training or expensive third-party tools. With AI, it's a single sentence.
Can AI build a three-statement financial model from scratch?
How accurate are AI-generated DCF valuations compared to manual models?
Can I adjust individual assumptions after AI builds the model?
Does AI handle circular references in debt schedules?
How do I build scenario analysis with more than three scenarios?
Can AI create sensitivity tables with three variables instead of two?
What's the difference between scenario analysis and sensitivity analysis?
How do I validate AI-generated financial models?
Can AI build merger models with purchase price allocation and synergies?
What financial modeling tasks should I still do manually?
Sources
References and methodologies used in this article
- CFA Institute - Equity Valuation: Applications and Processes (2025)
- McKinsey & Company - Valuation: Measuring and Managing the Value of Companies, 7th Edition (2024)
- Damodaran, Aswath - Investment Valuation: Tools and Techniques for Determining the Value of Any Asset, 4th Edition (2024)
- Wall Street Prep - Financial Modeling Best Practices Guide (2025)
- FRED Economic Data - WACC and Discount Rate Benchmarks (2026)