AI can transform financial modelling in Excel by automating formula writing, scenario testing, and data cleaning. Start by using Copilot in Excel or ChatGPT to generate formulas from plain English. Feed the model with clean inputs and use AI for sensitivity analysis, then keep human review on assumptions. The biggest gains come from offloading the tedious parts so you spend time on judgement and logic.

For business owners and analysts, this matters because a financial model is rarely about the formulas. It is about the assumptions underneath. AI lets you test more scenarios, catch more errors, and update faster when reality shifts.

Why AI Financial Modelling Matters for Business

Most financial models in Excel break for the same reason: nobody has time to maintain them. Assumptions go stale, scenarios multiply, and the model becomes a snapshot of last quarter instead of a live decision tool.

AI changes the cost of building and maintaining these models. Tasks that used to take an afternoon, like building a three-statement model, building a discount rate calculator, or running a Monte Carlo on revenue, can now happen in minutes. That shift means your finance team stops being spreadsheet custodians and starts being strategic advisors.

Three areas where this hits hardest:

• Forecasting revenue under multiple assumptions without rebuilding the model every time
• Cleaning and standardising messy transaction data pulled from banking portals or accounting systems
• Drafting investor or board updates where the numbers need to match the story

If you operate a mid-market business and your model lives in a single Excel file that one person understands, AI is the fastest way to make that model scalable.

What AI Can and Cannot Do in a Financial Model

Before walking through the steps, it helps to set expectations. AI is excellent at pattern-based tasks inside Excel. It is poor at open-ended judgement calls.

What AI handles well:

• Writing DAX, Power Query M, and Excel formulas from natural language
• Generating VBA macros for repetitive cleanup tasks
• Explaining what a broken formula is doing
• Building scenario tables and data tables
• Translating between Excel and Google Sheets syntax
• Summarising a finished model into plain English commentary

What AI still cannot do reliably:

• Validate whether your discount rate is correct for your industry
• Spot a unit error buried in row 200 of a complex workbook
• Replace a qualified finance reviewer for final sign-off
• Pull live data from your accounting system without an API connector

Treat AI as a junior analyst who types fast and never sleeps. You still own the logic, the assumptions, and the review.

Step 1: Set Up a Clean Source Workbook

AI works best when your inputs are tidy. Before asking for any modelling help, build a source workbook with three rules.

First, one tab per logical table. Inputs, assumptions, calculations, and outputs should live separately. AI tools read structure, and structure lets them target the right range.

Second, named ranges for every assumption. If your growth rate lives in cell B7 and your margin in B8, the model is fragile. Turn them into named ranges like RevenueGrowth and GrossMargin. When you prompt AI to build a scenario, it can reference these names instead of guessing at cell positions.

Third, a documentation tab. List every assumption, the source, and the date it was last reviewed. This is the boring part nobody does, but it is what separates a working model from a model that lies to you six months later.

Once your source workbook follows these rules, AI can start adding value immediately.

Step 2: Use Copilot or ChatGPT to Write Formulas

If you have Microsoft 365 Copilot, you can prompt it inside Excel directly. Type something like “add a column that calculates the year-over-year revenue change for the table on this sheet” and it generates the formula in the active cell.

If you do not have Copilot, ChatGPT or Claude work just as well for one-off formulas. Copy a sample of your data, describe the columns, and ask for the formula. For example:

“I have a table where column A is the month, column B is the forecast revenue, and column C is the actual revenue. I need a formula in column D that flags any month where actuals are more than 10 percent below forecast.”

The output will usually be a working IF statement you can paste straight in. Test it on one row before dragging it down.

A practical tip: when you ask for a formula, include the sheet name, the cell references, and the data type. AI hallucination drops sharply when you give it concrete anchors.

Step 3: Build Scenarios with AI Instead of Manual Tables

Scenario analysis is where AI earns its keep. The traditional approach involves copying a sheet three times, renaming them Best Case, Base Case, and Worst Case, then manually changing the assumption inputs.

Instead, ask AI to build a data table or a scenario manager that references your named ranges. In Excel, the What-If Analysis tools cover most of this, but AI can wrap them in a cleaner interface.

A useful prompt: “Using my named ranges RevenueGrowth, GrossMargin, and ChurnRate, build a scenario summary table that shows net revenue across three scenarios: Conservative, Base, and Aggressive. Include a 5 percent flex either side of the base case.”

The output is a structured table you can paste into a new tab. For more advanced work, ask AI to generate the VBA that swaps assumption values based on a dropdown selection. This turns a static model into something the leadership team can drive themselves.

Step 4: Use AI for Sensitivity Analysis

Sensitivity tables in Excel are powerful but tedious to set up by hand. AI can generate both one-variable and two-variable data tables in seconds.

Prompt example: “Build a two-variable data table showing net margin as a function of price (rows from $40 to $60 in $5 increments) and cost per unit (columns from $20 to $35 in $2.50 increments). Use the named ranges PricePerUnit and CostPerUnit as inputs and reference the Margin cell for the output.”

Paste the result into your model, then ask AI to interpret it. A second prompt like “what does this sensitivity table tell me about the break-even point” gives you a written summary you can drop into a board pack.

For more sophisticated work, AI can also write the formulas for a tornado chart, which shows which assumption has the biggest impact on your output. This is one of the highest-leverage visualisations in financial modelling and most teams never build it because the setup is fiddly. AI removes the friction.

Step 5: Automate Data Cleanup with Power Query and AI

Most financial models choke on dirty data, not on the math. If you are pulling transactions from Stripe, Xero, QuickBooks, or a banking API, the data will arrive with inconsistent naming, mixed date formats, and duplicated rows.

Power Query inside Excel is the standard cleanup tool. AI can write the M code for you. Describe the problem in plain English:

“I have a CSV where dates appear in three formats (DD/MM/YYYY, MM-DD-YY, and YYYY-MM-DD), the customer name column has trailing whitespace, and there are duplicate rows based on transaction ID. Write Power Query M code to clean this.”

The output is a query you can paste into the Advanced Editor. This is one of the highest-return uses of AI in financial modelling because the cleanup step usually eats hours every month.

For larger datasets, consider pairing Excel with a tool like SQLBI or a dedicated data warehouse. AI can write the SQL or DAX to summarise the cleaned data before it lands in your model.

Step 6: Generate Written Commentary from the Numbers

Once your model produces numbers, AI can draft the narrative. Feed it a summary of the outputs and ask for a 200-word executive summary suitable for a board pack.

For example: “Based on the following financial summary, write a board update covering revenue performance, margin trends, and cash position. Keep the tone measured and flag any metric that moved more than 10 percent from the prior period.”

This works for monthly management reports, investor updates, and budget variance commentary. The output still needs a human review pass, but the starting draft is often 70 percent of the way there. That saves 30 to 45 minutes per report, which compounds across a finance team’s workload.

Common Mistakes When Using AI for Financial Modelling

The tools are easy to start using and easy to misuse. These are the failure modes I see most often.

Treating AI output as audited. AI does not know your business. It will happily produce a discount rate that looks reasonable but has no grounding in your actual risk profile. Every output needs review by someone who understands the model.

Letting AI touch the structure without a map. If you let AI rewrite formulas across a whole workbook, you can break dependent calculations in ways that are hard to trace. Always work in a copy and document every change.

Ignoring data security. If your model contains commercially sensitive data, do not paste it into a public AI tool. Use enterprise versions with data residency guarantees, or strip the data to aggregates before prompting.

Building scenarios that are not actually different. AI will generate three scenarios that all sit within 5 percent of each other if you do not push for spread. Define the spread explicitly. Conservative should assume the worst plausible quarter, not a slightly slower version of the base case.

Skipping the documentation tab. AI makes it tempting to skip writing things down because the model “works.” Document anyway. The day someone else opens the file, they need a roadmap.

Over-relying on AI for assumptions. The most dangerous part of any model is the assumption set. AI can help you brainstorm, but the final numbers should come from your operating knowledge, your customer conversations, and your unit economics.

Putting It All Together

A practical workflow looks like this. You maintain a clean source workbook with named ranges and a documentation tab. You use AI to generate formulas, scenarios, sensitivity tables, and cleanup queries. You keep human review on every assumption and every output. You use AI again to draft the narrative around the numbers.

This is not a futuristic setup. It works today in Microsoft 365 with Copilot, plus ChatGPT or Claude for one-off tasks, plus Power Query for the data plumbing. The cost is modest and the time savings are real.

The bigger shift is cultural. Once your finance team stops handcrafting every formula, they have time to ask better questions. Why is churn rising in this segment? What happens to cash if we delay the second product launch by two quarters? AI does not answer those questions, but it gives your team the room to ask them.

Free download: The AI Operating Layer We put together a practical guide covering this and more. Download it here.

For a structured walkthrough of building this into your operations, book a 60-min Omni Audit — https://calendly.com/sam-mckay/discovery-call?utm_source=edna-landing&utm_medium=blog&utm_campaign=product-keywords