AI has changed financial modeling from a specialized skill requiring deep Excel expertise to something accessible with good judgment and clear prompts. Here’s how finance professionals are using AI in their modeling work.

What AI Does Well in Financial Modeling

  • Generating Excel formula structures for complex calculations
  • Building model templates from descriptions
  • Writing VBA macros for automation
  • Explaining model assumptions and their implications
  • Error-checking logic and identifying gaps
  • Converting model logic between Excel, Python, and Google Sheets

What AI Does Poorly

  • Guaranteeing numerical accuracy (always verify critical numbers)
  • Understanding company-specific accounting quirks
  • Replacing domain expertise on assumptions
  • Auditing complex models it hasn’t seen in full

Core Prompting Framework

The key to good AI financial modeling prompts is specificity about:

  1. Model type — DCF, LBO, 3-statement, comps, etc.
  2. Data inputs — what’s available, what’s assumed
  3. Output format — what the model needs to produce
  4. Use case — who uses this and for what decision

Building a 3-Statement Model

Step 1: Model Structure

Build me a 3-statement financial model structure for a SaaS company.

Assumptions:
- Revenue: ARR-based with MRR growth rate driver
- Revenue recognition: subscription (monthly)
- COGS: hosting + support + CS headcount
- Operating expenses: S&M, R&D, G&A
- Headcount: key cost driver linked to departmental headcount table
- Capex: minimal (software company)
- Working capital: simple (no inventory)

Output format:
- Income Statement (monthly for year 1-2, quarterly for year 3-5)
- Balance Sheet (quarterly)
- Cash Flow Statement (quarterly)
- Key metrics dashboard: ARR, MRR, NRR, CAC, LTV, Rule of 40

List all required input tabs and output tabs.

Step 2: Formula Templates

Write Excel formulas for the SaaS 3-statement model:

1. ARR calculation:
   - Opening ARR + New ARR + Expansion ARR - Churn ARR = Closing ARR
   - Use named ranges: ARR_Opening, ARR_New, ARR_Expansion, ARR_Churn

2. Revenue recognition from ARR:
   - Monthly Revenue = Average of Opening and Closing ARR / 12
   
3. NRR (Net Revenue Retention):
   - NRR = (Opening ARR + Expansion - Churn) / Opening ARR
   - Rolling 12-month calculation

4. Rule of 40:
   - Rule40 = Revenue Growth YoY % + EBITDA Margin %
   - Include both trailing and forward-looking versions

Show formulas in Excel syntax with comments explaining assumptions.

DCF Valuation Template

Create a DCF valuation model template.

Company type: [SaaS / Manufacturing / Services]
Projection period: 5 years explicit + terminal value
Terminal value method: Exit multiple and Gordon Growth Model (both)

Required inputs:
- Revenue: [enter by year]
- EBITDA margin: [enter by year]
- Tax rate, D&A schedule, capex schedule, working capital changes
- WACC components: risk-free rate, equity risk premium, beta, debt cost, capital structure
- Exit multiples: EV/EBITDA range

Output:
- Free Cash Flow by year
- Discount factors
- PV of FCFs
- Terminal value (both methods)
- Enterprise Value and equity bridge
- Sensitivity table: WACC vs Exit Multiple (9-cell grid)
- Football field chart data

Write all Excel formulas. Flag cells that need manual input in yellow (note this in formula comments).

Scenario Analysis Automation

I have a financial model with these key assumptions in cells B5:B15:
[describe your inputs]

Write a VBA macro that:
1. Creates a scenario manager with 3 scenarios: Bear, Base, Bull
2. Stores current values as Base
3. Prompts user to enter Bear/Bull assumptions for each key driver
4. Adds a dropdown to switch between scenarios
5. Highlights changed cells in each scenario

Also create a sensitivity table formula for:
- Revenue growth (rows): 5%, 10%, 15%, 20%, 25%
- EBITDA margin (columns): 10%, 15%, 20%, 25%, 30%
- Output: Enterprise Value

Converting Between Excel and Python

I have this Excel financial model logic:
[describe or paste key formula structure]

Convert this to Python (pandas) for automated report generation.
Use:
- pandas for calculations
- openpyxl for Excel output
- Keep column names identical to original model
- Add docstrings for each function
- Include error handling for missing inputs

Comparable Company Analysis (Comps)

Build a comparable company analysis template for [industry].

Structure:
- Company info: name, ticker, market cap, enterprise value
- Income metrics: revenue (LTM), EBITDA (LTM), EBIT, EPS
- Growth metrics: revenue growth (1yr, 3yr CAGR)
- Margin metrics: EBITDA%, EBIT%, Net Income%
- Valuation multiples: EV/Revenue, EV/EBITDA, P/E, EV/EBIT

Calculated fields:
- Mean, Median, 25th percentile, 75th percentile for each multiple
- Implied value range for target company at each percentile
- Color-coded: green for above median, red for below

Write formulas and conditional formatting logic.

Error Checking Prompts

Review this financial model logic for errors:
[paste key formulas or describe model structure]

Check for:
1. Circular references (describe any found)
2. Balance sheet equation: Assets = Liabilities + Equity (at each period)
3. Cash flow: Net Income → working capital changes → FCF reconciliation
4. Hard-coded numbers that should be formula-driven
5. Logic errors in year-over-year growth calculations
6. Sign convention consistency (cash outflows negative?)
7. Missing or inconsistent tax shield calculations

For each issue found: describe the error, the cell/formula, and the fix.

Model Documentation

Write documentation for this financial model:
[describe model]

Include:
1. Model purpose and intended use
2. Key assumptions and their sources
3. Sensitivity analysis guidance (which assumptions matter most)
4. Limitations and what this model cannot do
5. Update instructions (what to change quarterly)
6. Tab guide: what each tab contains and how they link

Format: internal model documentation, 1-2 pages.

Practical Advice

Always verify: AI-generated formulas are starting points. Verify logic against known values before using in presentations.

Use AI for structures, not numbers: AI excels at model architecture. Your judgment drives assumptions.

Iterative building: Build in sections — have AI generate one module, verify it, then continue.

Prompt with constraints: “The balance sheet must balance” is a useful constraint to include in modeling prompts.