[Spreadsheet]AI-Built Financial Models & Sensitivity

Summary: Use AI to help build a simple financial model, then run sensitivity analysis with a Data Table.

1. Pain Point

When it comes to valuation and budgeting, the hardest part isn't the "calculation" — it's the "building."

You've probably been there: your boss says "value this company," and you start from a blank spreadsheet — manually write five years of free cash flow, compute the discount, compute the terminal value, then add sensitivities (sensitivity analysis — in plain terms, "see how much the result shifts when a key assumption changes"). Dozens of formulas — get one cell wrong and you redo everything. Even worse, the boss asks again: "What if the discount rate goes up two points to 12%? Growth rate cut to 2%?" — and you can only change them one by one, and copy the results one by one.

So many people pin their hopes on AI, thinking: "Just let Copilot build the model for me and we're done."

Let me set expectations up front, and set the tone: M365 Copilot in Excel is a "copilot," not "autopilot." It can help you write formulas, explain formulas, and give layout and suggestions — but it will not figure out the whole financial model's structure, assumptions, and articulation logic (articulation logic — in plain terms, "the logic that makes one table tie out to another") for you. That "thinking work" is still on you. What this article does is put AI where it actually shines, then nail the model down with manual but reliable operations, and finally use Excel's native Data Table to run the sensitivity analysis with one click.

2. What You'll Build

After this article, you'll have a working simple DCF (DCF — Discounted Cash Flow valuation; in plain terms, "add up all the money the company will earn in the future, discounted for risk") valuation model, looking like this:

  • A set of assumption cells named with Name Manager (the tool in Excel that gives cells aliases) — discount rate, perpetual growth rate, base-period free cash flow, and so on — where the formulas read like plain language: =FCF1/(1+DiscountRate)^1 instead of =$C$10/(1+$B$2)^1.
  • A complete calculation chain: a five-year free cash flow forecast + terminal value (Terminal Value — in plain terms, "how much the company is still worth after year 5") + enterprise value (Enterprise Value — in plain terms, "sum up all that discounted money").
  • A two-dimensional Data Table: growth rate on the horizontal axis, discount rate on the vertical axis; every cell automatically computes the corresponding valuation — when the boss asks "12% discount rate, 2% growth," you just point at the number in the cell.

Supporting skill: you'll clearly know what to hand to Copilot and what you must click yourself.

Note: This article uses "simple DCF valuation" as the main example. If you want a model linking the three main statements — income statement / balance sheet / cash flow statement — the idea is exactly the same: solidify the "assumptions" with Name Manager, write the calculation chain clearly, and finish with a Data Table for sensitivity. A three-statement model just has more line items; the skeleton stays the same.

3. Hands-On

Let's go step by step. First sketch the model skeleton, then let Copilot help write the formulas, and finally run the sensitivity with a Data Table.

Step 1: Frame the "assumptions" and "result slots" (manual, ~2 min)

Open a blank worksheet and put the assumption area on the left (no AI for this step — pure manual entry, to keep the anchors clear):

Cell Content Value
B2 Discount Rate DiscountRate 10%
B3 Perpetual Growth Rate GrowthRate 3%
B4 Base Free Cash Flow BaseFCF 100
B5 Forecast-Period Cash Flow Growth FCFGrowth 15%
B6 Number of Forecast Years NYears 5

Screenshot tip: capture the left B2:B6 assumption area and label it "these are the model's knobs."

Step 2: Use Name Manager to name these cells (manual, key step)

Name Manager is a tool in Excel that "gives cells aliases" — in plain terms: you call B2 DiscountRate, and later writing DiscountRate in a formula equals writing B2, but it's ten times more readable and won't break references when you move things around.

Path: Formulas tab → Name Manager → New. Define each of the five cells above as a name (use absolute references):

  • DiscountRate = =Sheet1!$B$2
  • GrowthRate = =Sheet1!$B$3
  • BaseFCF = =Sheet1!$B$4
  • FCFGrowth = =Sheet1!$B$5
  • NYears = =Sheet1!$B$6

Key point: the name can be worksheet-level or workbook-level — either works; don't put spaces in the name, use camelCase or underscores. After defining, type =DiscountRate in any blank cell and press Enter — it should show 0.1, confirming the binding worked.

Step 3: Let Copilot help you think / write the calculation columns (AI-assisted, but verify)

Now it's M365 Copilot's turn. Open the Copilot pane on the right side of Excel, and you can ask like this (note: put the real cell addresses into the prompt — the more specific, the better):

Prompt example 1 (generate forecast cash flow): "In C10:C14 generate the next 5 years of free cash flow. C10 equals BaseFCF, and each year after = previous year × (1+FCFGrowth). Use the names from Name Manager, no hardcoding."

Prompt example 2 (explain a formula, to help you understand it): "Explain in one sentence what the discount factor formula in D10 =1/(1+DiscountRate)^B10 is calculating."

Prompt example 3 (check the articulation): "Check C10:C14 and E10:E14, confirm that each year's present value = cash flow × discount factor, and point out where it might be written wrong."

Copilot capability boundary (must read): At the time of writing, Copilot's most reliable use in Excel is — (a) on data already formatted as a Table (in plain terms, turn a range into a smart table with filter headers via Ctrl+T), "add a column / suggest a formula" — note: the data must first be turned into a Table via Ctrl+T; this is the hard prerequisite for it to add columns and write formulas. On a plain ordinary grid it basically can't do this; (b) in the right-hand pane, explain or rewrite the formula you selected in natural language; (c) answer questions about the table. What it is not suited for and cannot do is: design the model structure from scratch for you, auto-build the Data Table, or auto-build Name Manager. More critically — formulas generated by Copilot must be manually verified by you; it occasionally gets the signs or referenced cells wrong (e.g., writes * instead of ^, or references the wrong row). Treat it as an "intern who writes formulas," not a "partner who audits the books."

Uncertainty note: The button-level operations in the "prompt examples" above will differ depending on your account's Copilot version / license — for example, whether it can directly write the formula into C10:C14, or only gives a suggestion for you to click "Insert." But one thing is certain: the hard prerequisite for Copilot to add columns and write formulas in Excel is that the data has already been turned into a Table via Ctrl+T; on a plain ordinary grid (not a Table) it basically cannot add columns or write formulas. So the prerequisite for Step 3 in this article is always "data becomes a Table first." If it only gives suggestions without modifying the table, just fill in the formula it gives manually — same result.

Step 4: Complete the calculation chain (manual, fill in by reference)

Whatever Copilot helped with, in the end you must confirm these formula rows exist on the worksheet (assuming forecasts are in rows 10–14, years in column B):

B10: 1          C10: =BaseFCF                       D10: =1/(1+DiscountRate)^B10      E10: =C10*D10
B11: 2          C11: =C10*(1+FCFGrowth)             D11: =1/(1+DiscountRate)^B11      E11: =C11*D11
B12: 3          C12: =C11*(1+FCFGrowth)             D12: =1/(1+DiscountRate)^B12      E12: =C12*D12
B13: 4          C13: =C12*(1+FCFGrowth)             D13: =1/(1+DiscountRate)^B13      E13: =C13*D13
B14: 5          C14: =C13*(1+FCFGrowth)             D14: =1/(1+DiscountRate)^B14      E14: =C14*D14

Terminal value and enterprise value (near rows 16 and 18):

C16 (Terminal Value TV):    =C14*(1+GrowthRate)/(DiscountRate-GrowthRate)
E16 (PV of Terminal Value): =C16/(1+DiscountRate)^NYears
C18 (Enterprise Value EV):  =SUM(E10:E14)+E16

Key point: The terminal value uses the Gordon Growth Model — in plain terms, "assume that after year 5 the company's free cash flow grows forever at a fixed growth rate, and compress that 'forever' into a present value with a single fraction." This fraction TV = FCF₅×(1+g)/(r−g) holds on the premise that r must be greater than g, otherwise the denominator is non-positive and the valuation explodes into a negative number or an astronomical figure.

Step 5: Run sensitivity with the Data Table (manual, the hardcore part of this article)

Data Table is Excel's built-in "What-If" tool (What-If — what-if analysis; in plain terms, "what happens if a number becomes X") — in plain terms: you give it a list of "if the discount rate becomes X and the growth rate becomes Y," and it automatically plugs each combination into the model and spits the results into a matrix table. It comes in "one-variable" and "two-variable" flavors; we use two-variable (growth rate on the horizontal axis, discount rate on the vertical axis).

Layout (important, follow it):

  • In G9 enter the anchor formula: =C18 (i.e., referencing the enterprise value EV result). G9 is the top-left corner of the whole table.
  • In H9:L9 (the row to the right of G9) write the growth rate scenarios horizontally: 0.01, 0.02, 0.03, 0.04, 0.05.
  • In G10:G14 (the column below G9) write the discount rate scenarios vertically: 0.08, 0.09, 0.10, 0.11, 0.12.

Operation:

  1. Use the mouse to select the whole range G9:L14 (it must include the top-left =C18 anchor, the top growth rates, the left discount rates, and the blank 5×5 body in the middle).
  2. Click Data tab → What-If Analysis → Data Table.
  3. In the dialog that pops up:
    • Row input cell = B3 (because Excel's rule is that the values in the top row are substituted from the Row input cell; in this example the top row holds growth rates, corresponding to the model's GrowthRate cell B3).
    • Column input cell = B2 (because Excel's rule is that the values in the left column are substituted from the Column input cell; in this example the left column holds discount rates, corresponding to the model's DiscountRate cell B2).
  4. OK. Excel substitutes each combination and recalculates; G10:L14 fills with different valuations instantly.

Key point & common error: Row/column input cells are easy to swap. First memorize Excel's rule: the data table's top row values → Row input cell; the left column values → Column input cell. In this example the top row is growth rate (B3) and the left column is discount rate (B2), so Row = B3 and Column = B2. If you swap them, the table won't error, but you get a "mirrored wrong version" — embarrassing if you present it.

Screenshot tip: capture the "Data Table dialog" and circle the Row/Column input cell entries.

Determining rule (must read): The Data Table's "row input / column input cell" must be on the same worksheet as the Data Table itself — that is, B2 and B3 must be on the same Sheet as G9:L14. If the input cells are on another sheet, Excel will error out directly with "Input cell reference is not valid," and the Data Table simply cannot be built. This isn't a version difference — it's a hard constraint. This article puts the model and the Data Table both on Sheet1 precisely to satisfy it; just follow along.

4. Principle Summary

Behind the Data Table is Excel's TABLE() array function: after you select the range, Excel remembers "which cell is the result anchor and which two cells are the inputs," then for each scenario value it temporarily plugs the input into the corresponding cell, recalculates, and writes the result back to the corresponding cell of the matrix — so when you change B2 or B3, the whole sensitivity table refreshes with it, but it is one solid array block and you can't edit a single small cell individually. Name Manager is a different matter: it just issues an "alias business card" to addresses like B2, letting formulas break free from specific row/column numbers — more readable and easier to maintain. As for Copilot, in this workflow it plays the role of "formula drafting and explanation assistant"; the model's structure, assumptions, and articulation logic are still defined by you — AI accelerates the "writing," not the "thinking."

5. Pitfall Guide

  1. The Data Table's top-left corner must be a formula, and it must reference the result cell. Many people leave G9 blank or write text, and the whole table comes out blank. G9 must be a formula like =C18 that "points at the result you want to see."
  2. Swapping row/column input cells = a mirrored wrong table. Excel's rule: top row values go to Row input cell, left column values go to Column input cell. In this example the top row is growth rate B3 and the left column is discount rate B2, so Row = B3 and Column = B2. After filling, double-check against the text so you don't swap them.
  3. The discount rate must be greater than the perpetual growth rate. In TV = FCF₅×(1+g)/(r−g), once r−g is ≤ 0, the terminal value blows up (negative or infinite) and the whole valuation table crashes with it. When doing sensitivity, don't set the growth rate scenarios above the discount rate.
  4. The Data Table is "volatile" — don't overuse it on huge tables. It reruns all scenarios on every recalculation; with tens of thousands of rows it makes the file lag. Keep the sensitivity matrix small (5×5 or 10×10 is enough); keep the raw big data elsewhere.
  5. Copilot's formulas must be manually verified before going live. It occasionally gets signs wrong, references the wrong row, or silently changes your assumptions. Treat it as an intern: look at the output once, confirm it's right, then paste it into the model.

6. Advanced Extensions

  • Back to basics B3: If you're not yet comfortable with "how to talk to Copilot in Excel" or "how to turn a range into a Table," go back to B3 first and nail down Copilot's basic interactions and how to work with Tables — Step 3's AI assistance in this article heavily depends on the prerequisite "data becomes a Table first."
  • Going further:
    • Upgrade a single model into three-statement linkage (income statement → cash flow statement → balance sheet, articulated via retained earnings / cash balance), then use a Data Table to do sensitivity on "revenue growth vs. net margin."
    • Want more "random" sensitivity? Go for Monte Carlo simulation (Monte Carlo — in plain terms, "let key assumptions jitter randomly a few thousand times and see the probability distribution of the valuation") — use Excel's RAND() with a Data Table to repeatedly sample, and see which range the valuation falls into, instead of staring at only a few fixed scenarios.
    • Stop hand-pasting raw financial data: use Power Query (Excel's "data-cleaning ETL tool" — in plain terms, "a pipeline that automatically pulls and cleans data from files/databases") to bring the statements in; for heavier multidimensional modeling, go for Power Pivot + DAX (DAX is the formula language in Power Pivot specialized for writing "measures," suited for cross-period, share, and YoY/QoQ aggregation calculations).
    • If you find "clicking the Data Table every time" tedious, check the VBA chapters in this series and use a macro (macro — in plain terms, "a sequence of operations recorded in Excel that can run repeatedly and automatically") to chain "change assumptions → refresh Data Table → export results" into one click.