Automating DCF Models with AI: A Step-by-Step Guide for Analysts

The Discounted Cash Flow (DCF) model is the fundamental tool of valuation, but for many analysts, it’s a source of immense repetitive work. Setting up the “skeleton”—linking the three statements, calculating Free Cash Flow (FCF) for each year, determining the Terminal Value, and discounting it all back—is a process that invites fat-finger errors and broken links.

In 2026, building a DCF doesn’t start with an empty Sheet1. It starts with a prompt in o11. Here is how you can automate a professional-grade DCF model.

Step 1: Architecting the Skeleton

Instead of manually typing years across a header and setting up line items, you can give o11 a high-level architectural prompt.

• Prompt: “Build a 5-year DCF model structure. Include lines for Revenue, COGS, OpEx, Depreciation, Taxes, and CapEx. Include a separate section for the WACC calculation and a Terminal Value calculation using the Gordon Growth Method.”
• The Result: o11 builds the tabs, labels the cells, and formats the structure to professional institutional standards.

Step 2: Intelligent Data Ingestion

A model is only as good as its inputs. Normally, you’d have a PDF of a 10-K open on one screen and your Excel sheet on the other.

• Prompt: “Populate the historical data for the last 3 years from the attached Annual Report. Ensure the EBITDA matches the ‘Adjusted EBITDA’ reconciliation on page 42.”
• The Result: o11 extracts the relevant figures and places them in your model with a citation link, so you can hover over the cell and see the source snippet.

Step 3: Generative Projections

This is where the analyst’s judgment comes in. o11 handles the math, you handle the assumptions.

• Prompt: “Project Revenue at a 15% CAGR for the first 3 years, step-down to 10% for the final 2 years. Keep Gross Margin constant at 40% but assume 100bps of OpEx leverage year-over-year as the company scales.”
• The Result: o11 writes the formulas across the 5 projection years based on your logic. These are standard Excel formulas, not “AI black box” code, meaning you can audit them yourself.

Step 4: Sensitivity Analysis & Output

Once the base case is set, the job is to stress-test it.

• Prompt: “Create a sensitivity table for the Final Share Price based on WACC (increments of 0.25%) vs. Terminal Growth Rate (increments of 0.1%).”
• The Result: o11 builds the Data Table instantly.

The M365 Advantage: Excel to PowerPoint

The biggest bottleneck in a deal process is taking that Excel model and putting it into a pitchbook.

• The Integration: You can ask o11: “Take this DCF summary and the sensitivity table and insert them as formatted tables in our Valuation deck in PowerPoint.”

Verdict

By using o11 to handle the structural and formula-heavy lifting of a DCF, analysts can shift their focus from building the model to defending the assumptions. This results in faster turnarounds for clients and more robust, error-free valuations.