Build AI Financial Models in Sourcetable
Learn how to build DCF models, revenue forecasts, and sensitivity tables with AI-powered automation for faster, more accurate financial planning.
Andrew Grosser
May 7, 2026 • 11 min read
You spent four hours building a DCF model in Excel. You triple-checked every WACC calculation, every terminal value formula, every discount factor. Then your CFO asked you to run three more scenarios with different growth assumptions. Another six hours gone. Financial modeling doesn't have to be this slow.
Traditional financial modeling requires manual formula construction, error-prone cell references, and repetitive scenario building. Investment bankers spend 60-70% of their time on model mechanics rather than analysis. FP&A analysts rebuild the same revenue waterfall every quarter. The actual thinking—the strategic decisions about assumptions and business drivers—gets buried under spreadsheet plumbing.
Sourcetable's AI data analyst is free to try. Sign up here.
This guide shows you how to build institutional-grade financial models using AI. You'll learn the core methodologies—DCF valuation, revenue forecasting, scenario analysis, and sensitivity tables—then see how AI automation cuts build time from hours to minutes. We'll cover what works, what doesn't, and when you still need manual oversight.
What Is AI Financial Modeling?
AI financial modeling uses natural language processing and automated formula generation to build valuation models, forecasts, and scenario analyses without manual cell-by-cell construction. Instead of writing =NPV(WACC, FCF_Range) + Terminal_Value / (1 + WACC)^5 by hand, you describe what you want: 'Build a 5-year DCF model with 8% WACC and 3% terminal growth.' The AI constructs the entire model structure, formulas, and formatting automatically.
The traditional workflow for a DCF model involves 12-15 distinct steps: historical data collection, revenue driver identification, margin assumption building, working capital forecasting, capex projection, tax rate calculation, WACC computation, free cash flow derivation, terminal value calculation, discount factor application, sensitivity table construction, and scenario modeling. Each step requires 5-20 formulas. A mid-market company DCF typically contains 200-300 formulas across 8-12 sheets.
AI modeling doesn't eliminate financial judgment—it eliminates mechanical execution. You still decide whether to use a 2-stage or 3-stage DCF, whether revenue should grow linearly or exponentially, whether to model customer cohorts or aggregate revenue. The AI handles the formula writing, reference linking, and error checking. It's the difference between thinking about business drivers versus debugging circular references.
| Model Component | Traditional Time | AI Time | Primary Challenge |
|---|---|---|---|
| DCF Model Setup | 3-4 hours | 5 minutes | Formula accuracy, circular refs |
| Revenue Forecast (5yr) | 2-3 hours | 3 minutes | Driver identification, seasonality |
| Sensitivity Table (2-way) | 45-60 minutes | 30 seconds | Data table setup, formatting |
| Scenario Analysis (3 cases) | 1-2 hours | 2 minutes | Assumption tracking, version control |
| Working Capital Schedule | 1-1.5 hours | 2 minutes | Days calculation, balance checks |
Building a DCF Model with AI
A discounted cash flow model values a company by projecting future free cash flows and discounting them to present value using the weighted average cost of capital. The formula is: Enterprise Value = Σ(FCF_t / (1 + WACC)^t) + Terminal Value / (1 + WACC)^n, where FCF is unlevered free cash flow, WACC is the discount rate, and Terminal Value is the perpetuity value of cash flows beyond the projection period.
Manual DCF construction starts with a revenue build. You project revenue for 5-10 years based on historical growth rates, market size, competitive positioning, and management guidance. Then you apply EBITDA margins (historical average or industry benchmark), subtract depreciation and amortization to get EBIT, apply the tax rate to calculate NOPAT (Net Operating Profit After Tax), add back D&A (non-cash), subtract capital expenditures, and subtract changes in net working capital. The result is unlevered free cash flow.
Here's a real example. A SaaS company generated $50M revenue in 2025 with 25% EBITDA margins. Historical growth: 35% (2024), 40% (2023), 45% (2022). You project 30% growth in 2026, declining to 15% by 2030 as the company matures. EBITDA margins expand from 25% to 32% as the business scales. D&A is 5% of revenue, capex is 3%, working capital increases by $2M annually. Tax rate: 25%. WACC: 10% (8% cost of equity weighted 80%, 5% cost of debt weighted 20%, after-tax).
| Year | Revenue ($M) | Growth % | EBITDA ($M) | EBITDA % | D&A ($M) | EBIT ($M) | Tax ($M) | NOPAT ($M) | FCF ($M) |
|---|---|---|---|---|---|---|---|---|---|
| 2026 | 65.0 | 30% | 17.6 | 27% | 3.3 | 14.3 | 3.6 | 10.7 | 8.7 |
| 2027 | 81.3 | 25% | 24.4 | 30% | 4.1 | 20.3 | 5.1 | 15.2 | 14.3 |
| 2028 | 97.5 | 20% | 30.4 | 31% | 4.9 | 25.5 | 6.4 | 19.1 | 18.0 |
| 2029 | 112.1 | 15% | 35.8 | 32% | 5.6 | 30.2 | 7.6 | 22.6 | 21.3 |
| 2030 | 128.9 | 15% | 41.2 | 32% | 6.4 | 34.8 | 8.7 | 26.1 | 24.6 |
Terminal value uses the perpetuity growth method: Terminal Value = FCF_2030 × (1 + g) / (WACC - g), where g is long-term growth (typically 2-3%). With 3% terminal growth: Terminal Value = 24.6 × 1.03 / (0.10 - 0.03) = $363M. Discount all cash flows to present value using (1 + WACC)^t as the denominator. Sum the present values: $8.7/1.1 + $14.3/1.1² + $18.0/1.1³ + $21.3/1.1⁴ + $24.6/1.1⁵ + $363/1.1⁵ = $7.9 + $11.8 + $13.5 + $14.6 + $15.3 + $225.4 = $288.5M enterprise value.
Building this manually in Excel requires 40-50 formulas across revenue projection, margin calculation, cash flow derivation, discount factor construction, and terminal value computation. Each formula needs cell references that update correctly when you insert rows or change assumptions. One misplaced parenthesis or absolute/relative reference error breaks the entire model.
With Sourcetable's AI, you describe the model structure in plain language: 'Build a 5-year DCF model for a SaaS company. 2025 revenue $50M, 25% EBITDA margin. Revenue growth: 30%, 25%, 20%, 15%, 15% for years 2026-2030. EBITDA margins expand to 32% by 2030. D&A 5% of revenue, capex 3%, working capital increases $2M/year. Tax rate 25%, WACC 10%, terminal growth 3%.' The AI generates the complete model—all formulas, formatting, and calculations—in 30 seconds. You review the output, adjust assumptions if needed, and move directly to analysis.
Revenue Forecasting with AI
Revenue forecasting projects future sales based on historical patterns, business drivers, and market conditions. The simplest method is trend extrapolation: fit historical revenue to a linear, exponential, or polynomial curve and project forward. More sophisticated approaches use driver-based models: units sold × average price, customer count × revenue per customer, or market size × market share × penetration rate.
Driver-based forecasting is more accurate because it captures business mechanics. A subscription business forecasts revenue as: (Beginning Customers + New Customers - Churned Customers) × Average Revenue Per User × 12 months. A retail business uses: Store Count × Sales Per Square Foot × Average Store Size. A manufacturing business models: Production Capacity × Utilization Rate × Average Selling Price.
Here's a worked example for a subscription business. January 2026 starts with 10,000 customers at $50/month ARPU. New customer acquisition: 500/month in Q1, ramping to 800/month by Q4. Churn rate: 3% monthly. ARPU increases 5% annually. Revenue for February 2026 = (10,000 beginning + 500 new - 300 churned) × $50 = $510,000. March = (10,200 + 500 - 306) × $50 = $519,700. By December, monthly revenue reaches $612,000 as the customer base grows to 12,240 despite churn.
The Excel formula for month-over-month revenue is: =((Prior_Month_Customers + New_Customers - (Prior_Month_Customers × Churn_Rate)) × ARPU × (1 + Annual_ARPU_Growth)^(Month/12)). You copy this down 60 rows for a 5-year forecast, ensuring cell references update correctly. Then you sum monthly revenue into annual totals, calculate year-over-year growth rates, and build supporting schedules for customer acquisition cost, lifetime value, and cohort retention.
This takes 2-3 hours manually: building the customer count waterfall, applying churn correctly (it's multiplicative, not additive), handling ARPU escalation, and creating summary tables. Common errors include applying churn to new customers in the same month (incorrect—they haven't had time to churn yet), using simple rather than compound growth for ARPU, and forgetting to adjust for seasonality.
Sourcetable's AI builds the entire forecast from a description: 'Create a 5-year monthly revenue forecast for a subscription business. Starting customers: 10,000 at $50/month ARPU. New customers: 500/month in Q1 2026, growing 10% quarterly. Churn rate: 3% monthly. ARPU grows 5% annually. Show monthly detail for year 1, annual summary for years 2-5.' The AI constructs the customer waterfall, applies churn logic correctly, compounds ARPU growth, and generates summary tables. Build time: 3 minutes instead of 3 hours.
Scenario Analysis and Sensitivity Tables
Scenario analysis tests how valuation changes under different assumptions. The standard approach uses three cases: base case (most likely), upside case (optimistic), and downside case (pessimistic). Each case modifies 3-5 key drivers—revenue growth, margins, WACC, terminal growth rate—and recalculates the entire model. This shows the range of possible outcomes and helps quantify risk.
For the SaaS DCF example, the base case assumes 30%/25%/20%/15%/15% revenue growth and terminal value of $363M for total enterprise value of $288.5M. The upside case assumes stronger growth (35%/30%/25%/20%/18%) and higher terminal value ($425M), yielding $342M enterprise value (+18.5%). The downside case assumes weaker growth (25%/20%/15%/12%/10%) and lower terminal value ($298M), yielding $241M enterprise value (-16.5%).
| Scenario | 2026 Growth | Terminal Value ($M) | Enterprise Value ($M) | Change vs Base |
|---|---|---|---|---|
| Downside | 25% | 298 | 241 | -16.5% |
| Base Case | 30% | 363 | 289 | — |
| Upside | 35% | 425 | 342 | +18.5% |
Sensitivity tables show how valuation changes across a range of two variables simultaneously. The most common table varies WACC (rows) and terminal growth rate (columns). For example, WACC from 8% to 12% in 0.5% increments, terminal growth from 2% to 4% in 0.5% increments. This creates a 9×5 grid with 45 valuation outputs.
Building a sensitivity table in Excel requires the Data Table feature: set up the model with input cells for WACC and terminal growth, create a grid with WACC values in the first column and terminal growth values in the first row, reference the valuation output in the top-left corner, select the entire grid, and apply Data > What-If Analysis > Data Table with row input (terminal growth cell) and column input (WACC cell). Excel recalculates the model 45 times and fills the grid.
This process is finicky. The data table references must be exact. If you insert a row above the input cells, the table breaks. If you change the model structure, you rebuild the table from scratch. Formatting the output (color scales, conditional formatting for the base case intersection) takes another 10-15 minutes. Total time: 45-60 minutes for a well-formatted two-way sensitivity table.
With AI, you describe what you want: 'Create a two-way sensitivity table for enterprise value. Vary WACC from 8% to 12% in 0.5% steps and terminal growth from 2% to 4% in 0.5% steps. Highlight the base case (10% WACC, 3% terminal growth) in green.' The AI generates the table, runs all calculations, and applies formatting in 30 seconds. You can immediately see that valuation ranges from $198M (12% WACC, 2% growth) to $412M (8% WACC, 4% growth)—a 108% spread.
When AI Financial Modeling Fails
AI financial modeling works well for standard structures—DCF, comparable company analysis, precedent transactions, LBO models, merger models. It struggles with highly customized models that have unusual business logic, circular references that require iterative calculation (like interest expense depending on debt balance depending on cash flow depending on interest expense), or models that integrate external data sources requiring API calls or web scraping.
Accuracy depends on assumption quality. If you tell the AI to use 50% revenue growth for a mature industrial company, it will build that model—but the output will be garbage. AI doesn't validate whether your assumptions are reasonable. It doesn't know that 50% growth is unrealistic for a steel manufacturer or that 80% EBITDA margins are impossible for a retailer. You still need financial judgment.
Complex working capital models often need manual adjustment. A simple working capital forecast uses days sales outstanding (DSO), days inventory outstanding (DIO), and days payable outstanding (DPO) to project accounts receivable, inventory, and accounts payable. But businesses with multiple product lines, seasonal patterns, or changing payment terms need line-item detail. AI can build the framework, but you'll add nuance manually.
Merger models with synergy realization schedules, integration costs, and pro forma adjustments require careful review. The AI builds the structure—acquirer financials, target financials, purchase price allocation, goodwill calculation, combined entity projections. But synergy timing (20% in year 1, 60% in year 2, 100% in year 3) and cost-to-achieve estimates come from management interviews and due diligence, not formulas. You input those assumptions; AI handles the math.
Circular references need special handling. In a leveraged buyout model, interest expense depends on debt balance, debt balance depends on cash flow available for debt paydown, and cash flow depends on interest expense. Excel resolves this with iterative calculation (File > Options > Formulas > Enable Iterative Calculation). AI can set up the structure, but you verify that the circular reference converges to a stable solution rather than oscillating or diverging.
Building Models Faster with Sourcetable
Sourcetable combines a familiar spreadsheet interface with an AI that understands financial modeling conventions. You describe the model you want—DCF, LBO, merger model, revenue forecast—and the AI builds it. All formulas, all calculations, all formatting. You review the output, adjust assumptions, and run scenarios. The workflow shifts from mechanical construction to strategic thinking.
The AI handles tasks that consume 60-70% of modeling time: writing formulas, linking cell references, building supporting schedules, formatting tables, creating charts. A DCF model that takes 4 hours manually takes 5 minutes with AI. A revenue forecast that takes 3 hours takes 3 minutes. A sensitivity table that takes 1 hour takes 30 seconds. You spend the saved time on analysis—testing assumptions, comparing scenarios, building investment memos.
Sourcetable connects to live data sources, so your models update automatically. Pull revenue data from your accounting system, stock prices from market data APIs, economic indicators from FRED, industry benchmarks from databases. When you refresh the model, all inputs update and all outputs recalculate. No more manual data entry, no more copy-paste errors, no more version control chaos.
The platform includes pre-built templates for common model types: 3-statement models, DCF models, LBO models, merger models, SaaS metrics dashboards, and revenue waterfalls. Start with a template, customize it for your business, and save it for reuse. Your team works from the same structure, ensuring consistency across analyses.
Real-time collaboration means multiple analysts work on the same model simultaneously. The investment banking associate builds the revenue forecast while the analyst constructs the working capital schedule. Changes sync instantly. No more emailing versions back and forth, no more merge conflicts, no more 'Final_v7_FINAL_USE_THIS.xlsx' filenames.
Practical Example: Building a 3-Statement Model
A 3-statement model projects the income statement, balance sheet, and cash flow statement for 5-10 years. The statements link together: net income from the income statement flows to retained earnings on the balance sheet and is the starting point for the cash flow statement. Changes in balance sheet accounts (accounts receivable, inventory, accounts payable) drive cash flow from operations. Capital expenditures reduce cash and increase PP&E on the balance sheet.
Start with the income statement. Project revenue using a growth rate or driver-based model. Apply cost of goods sold as a percentage of revenue (historical average or target margin). Subtract COGS from revenue to get gross profit. Subtract operating expenses (SG&A, R&D) as percentages of revenue. The result is EBIT. Subtract interest expense (calculated from debt balance on the balance sheet) and apply the tax rate to get net income.
Next, build the cash flow statement. Start with net income. Add back non-cash charges (depreciation, amortization). Subtract increases in working capital (if accounts receivable grows by $5M, that's a $5M cash outflow). Subtract capital expenditures. The result is free cash flow. Add financing activities (debt issuance or repayment, equity issuance or buybacks). The net change in cash flows to the balance sheet.
Finally, build the balance sheet. Cash equals prior period cash plus net change in cash from the cash flow statement. Accounts receivable equals revenue × DSO / 365. Inventory equals COGS × DIO / 365. PP&E equals prior period PP&E plus capex minus depreciation. On the liabilities side, accounts payable equals COGS × DPO / 365. Debt equals prior period debt plus new issuances minus repayments. Retained earnings equals prior period retained earnings plus net income minus dividends.
The balance sheet must balance: Assets = Liabilities + Equity. If it doesn't, you have a formula error. Finding that error in a 300-formula model takes 30-60 minutes of cell-by-cell auditing. Common mistakes: forgetting to link net income to retained earnings, applying the wrong sign to working capital changes (increases in current assets are cash outflows, increases in current liabilities are cash inflows), or double-counting depreciation.
Building a 3-statement model manually takes 6-8 hours for an experienced analyst. With Sourcetable, you describe the structure: 'Build a 5-year 3-statement model. 2025 revenue $100M growing 15% annually. Gross margin 60%, EBITDA margin 25%. Depreciation 5% of PP&E, capex 6% of revenue. DSO 45 days, DIO 30 days, DPO 60 days. Tax rate 25%, interest rate 5% on debt. Starting debt $20M, no new issuances.' The AI builds all three statements, links them correctly, and ensures the balance sheet balances. Build time: 8 minutes.
Best Practices for AI-Powered Financial Modeling
Always review AI-generated formulas before using the model for decisions. Check that revenue growth applies correctly (is it compounding or linear?), that margins calculate as percentages of the right base (gross margin as % of revenue, not COGS), and that cash flow signs are correct (capex should be negative, depreciation add-back should be positive). Spot-check 5-10 cells to ensure logic is sound.
Use clear, specific language when describing models to AI. Instead of 'build a valuation model,' say 'build a 5-year DCF model with 12% revenue growth, 30% EBITDA margins, 10% WACC, and 3% terminal growth.' The more specific your inputs, the more accurate the output. Vague descriptions produce generic models that need heavy customization.
Document your assumptions in a separate assumptions sheet. List all key drivers—revenue growth rates, margin assumptions, WACC components, terminal growth rate—with sources and justification. This makes it easy to update assumptions, run scenarios, and explain your model to stakeholders. AI can generate the assumptions sheet from your model automatically.
Version control matters. Save snapshots of your model before making major changes. Label versions clearly: 'DCF_BaseCase_2026-05-07,' 'DCF_UpCase_2026-05-07,' 'DCF_Final_2026-05-08.' This lets you revert if needed and compare scenarios side-by-side. Sourcetable's collaboration features include automatic version history, so you can roll back to any prior state.
Validate outputs against benchmarks. Does your projected EBITDA margin match industry averages? Is your WACC in line with comparable companies? Does your revenue growth assumption make sense given market size and penetration? If your model shows 50% EBITDA margins for a grocery retailer, something's wrong—industry average is 6-8%. Use AI to pull benchmark data from financial databases and compare automatically.
Common Financial Modeling Mistakes AI Prevents
Circular reference errors plague manual models. Interest expense depends on debt balance, debt balance depends on cash available for repayment, cash depends on net income, net income depends on interest expense. Breaking this loop requires careful formula construction and iterative calculation settings. AI detects circular references and structures formulas to resolve correctly, saving hours of debugging.
Inconsistent time periods cause valuation errors. If you discount year 1 cash flow by (1 + WACC)^1 but that cash flow represents mid-year timing, you're over-discounting by half a year. The correct discount factor for mid-year convention is (1 + WACC)^0.5 for year 1, (1 + WACC)^1.5 for year 2, etc. AI applies consistent timing conventions across all formulas.
Sign errors in cash flow statements are common. Capital expenditures should be negative (cash outflow), but depreciation add-back should be positive (non-cash expense). Increases in accounts receivable are negative (cash tied up), but increases in accounts payable are positive (cash preserved). Getting these signs wrong by even one cell throws off free cash flow by millions. AI applies correct signs automatically based on account type.
Hardcoded numbers instead of formulas break scenario analysis. If you type '15%' directly into a growth rate cell instead of referencing an assumptions sheet, you have to find and change that number manually in every scenario. AI builds models with centralized assumptions, so changing one input cell updates the entire model instantly.
Formatting inconsistencies make models hard to read. Numbers should show consistent decimal places (0 for whole numbers, 1 for percentages, 2 for currency). Negative numbers should be red or in parentheses. Headers should be bold. Totals should have top borders. AI applies professional formatting automatically, ensuring your model looks polished without manual cell-by-cell formatting.
How accurate are AI-generated financial models?
Can AI build models with circular references?
How long does it take to build a DCF model with AI?
What types of financial models can AI build?
Do I need to know Excel formulas to use AI modeling?
Can AI pull live data into financial models?
How do I run scenario analysis with AI?
What happens if the AI makes a formula error?
Can multiple people work on the same model simultaneously?
How do I ensure my model assumptions are reasonable?
Does AI replace financial analysts?
Can I customize AI-generated models?
Sources
Research and data sources used in this article
- McKinsey & Company - The State of AI in 2026: Financial Services Applications
- CFA Institute - Discounted Cash Flow Modeling Best Practices (2025)
- Investment Banking Institute - Financial Modeling Standards and Conventions (2024)
- Deloitte - AI in Financial Planning & Analysis: 2026 Survey Results
- Harvard Business Review - How AI is Transforming Corporate Finance (January 2026)
- FRED Economic Data - Federal Reserve Bank of St. Louis (accessed May 2026)
- Bloomberg Terminal - Comparable Company Analysis Methodology (2025)