AI financial modeling: building the model from your ERP and books
AI financial modeling is the use of software to build and update a company’s financial model, the linked forecast of sales, costs, cash and the balance sheet, from the records in its ERP and accounting system instead of retyped exports. Financial modeling software runs from Excel with AI features to dedicated AI FP&A tools, where FP&A means financial planning and analysis. For a business that makes, moves or sells physical goods, the model also has to carry volumes, product mix, inventory and payment terms. This guide covers what AI changes in the model, the data it needs, how to start with one question, and the limits.
What a financial model is
ICAEW, the Institute of Chartered Accountants in England and Wales, defines a financial model in its Financial Modelling Code as “a time-based set of financial calculations within a spreadsheet workbook which aims to create a financial forecast based on one or more input set of variables.” The usual form is a three-statement model, in which the income statement, the balance sheet and the cash flow statement are linked, so a change in one flows through to the other two.
The inputs are called drivers. For a company that sells goods, the drivers that matter are units sold by product family, price and discount, product mix, material cost per unit, labour hours and rates, overhead, inventory days, the days customers take to pay, the days you take to pay suppliers, and spending on equipment. A distributor’s model leans on purchase prices, freight and rebates. A manufacturer’s also needs bills of materials, machine hours and capacity. A food producer adds yield, spoilage and seasonal volume.
The Code describes a model as an engine that alternative scenarios and sensitivities can be passed through, and that is what owners use one for. Typical questions are what happens to cash if a large customer pays 30 days later, and whether the business can afford a second production line next year.
How one driver moves the three statements
A small example shows why the statements have to be linked. Take a distributor with $12 million of annual sales, a 28% gross margin, customers who pay in 45 days on average, and 60 days of inventory. The numbers are made up, and each change below is tested on its own.
| Change | Income statement | Balance sheet | Cash |
|---|---|---|---|
| Customers pay in 52 days instead of 45 | No change to profit | Receivables up about $230,000 | Down about $230,000 |
| Prices up 3%, units down 2% | Gross profit up about $285,600, to a margin of about 30.1% | Receivables rise slightly with sales | Up as the extra profit is collected |
| Inventory held 75 days instead of 60 | No change to profit | Inventory up about $355,000 | Down about $355,000 |
The first and third changes leave profit unchanged and still take cash out of the business. A budget that tracks only profit would miss both, and a model that links the three statements shows them the month they start.
How the model is built and updated by hand
Where the model is a workbook, the monthly update follows the same steps. Someone exports the trial balance, pastes it into an actuals tab, maps any new accounts to the model’s lines, updates the assumptions, and rolls the forecast forward a month. Copies travel by email, and the current version is whichever one the controller saved last.
Each hand-off is a chance for an error. The ICAEW Code warns that “Even an expert builder can make errors of logic, typos and so on,” and it asks for checks built into each section, such as an equality check that the balance sheet balances. It also warns against hardcoding, meaning fixed values typed inside a formula, and says hardcoding “should never be used for values that could foreseeably change during the life of the model.”
The forecast itself is often a judgment typed into a cell. Excel has statistical forecasting built in: Microsoft’s forecasting functions, available since Excel 2016, “use advanced machine learning algorithms, such as Exponential Triple Smoothing (ETS).” Using them takes a clean monthly history of units for each product family, and a trial balance holds dollars by account with no units at all.
What AI changes in the model
AI financial modeling changes four things: where the actuals come from, where the drivers come from, how people ask the model questions, and how the model gets reviewed.
Actuals that load from the source
The first change is plumbing. Software reads the general ledger and the ERP directly, maps each account to a line in the model, and reconciles the result to the trial balance before anyone looks at it. When the chart of accounts changes, a language model can propose a mapping for the new accounts from their names and the transactions in them, and the controller approves it. If the totals do not tie to the statements to the dollar, the load stops and says why.
Drivers from your own history
The second change is where the drivers come from. Units by product family can be forecast from the order history, which AI demand forecasting covers. Price, discount and mix come from invoice lines, as in margin analysis. Material and labour cost per unit come from purchase receipts, bills of materials and routings, the cost build-up described in cost-plus pricing. Inventory days follow from the reorder settings in inventory optimization. Each driver then carries its source: the history it was estimated from and the person who last changed it.
Working capital gets the same treatment. Customer payment days come from the dates on past invoices and payments, customer by customer, and supplier payment days from your own payment history. In a model built this way, if one large customer drifts from 38 to 52 days, the cash forecast shows it the following month instead of at year-end.
Scenarios asked in plain language
The third change is how people use the model. A controller can ask for a scenario in plain language, such as steel up 12% from March and volume down 5%, and the AI translates it into changes to the input cells, runs the model, and lists what moved. Microsoft describes Copilot in Excel as built for this kind of work: “When you’re updating budgets, creating financial models, or analyzing data, Copilot uses Excel tools like tables, charts, PivotTables, and formulas to complete your requests.” It adds that “Your content stays editable, and you’re in control of everything that’s modified.”
That design suits a financial model. When the AI writes formulas into the workbook, the spreadsheet does the arithmetic and anyone can audit it, and the AI’s job is to propose the change, which is the part a person can check. In Gartner’s 2025 AI in Finance Survey, the AI use case that finance leaders ranked highest for impact was code generation, “by a significant margin.”
Reviewing the model
The fourth change is review. AI can read a workbook the way a reviewer would and list numbers hardcoded inside formulas, formulas that differ from their neighbours in the same row, links to other workbooks, and checks that fail. Those are the issues the ICAEW Code addresses under consistency, error reduction and calculation techniques. A person decides what to fix. Explaining each month’s gap between the model and actual results is covered in AI in finance.
Financial modeling software and AI FP&A tools
Financial modeling software falls into four groups. The table describes categories, not ranked products, and every vendor describes its own features differently.
| Category | What it is | Worth checking |
|---|---|---|
| Spreadsheet with AI | Excel or another spreadsheet, with an assistant that writes formulas and runs statistics in the workbook | Where calculations run. Microsoft says Python in Excel calculations “run in the Microsoft Cloud” |
| FP&A platform | Budgeting and forecasting software that connects to the general ledger and stores versions and scenarios | Which ERPs and accounting systems it connects to, and whether it reads item-level data or only account totals |
| ERP planning module | Budgets, demand planning or forecasting inside the ERP you already run | Whether it plans by item and location, and how it handles a new product with no history |
| Custom model on your data | A model in code or a database, fed from the ERP and the ledger and built around your products | Who maintains it, and whether finance can still read and change every assumption |
Whichever you consider, ask how it is charged, for example per user, per entity or by quote, and check the vendor’s own pricing page. Ask too where your data is processed and stored, and whether the vendor keeps a copy. Private AI for business lists the questions to put in writing.
Find out what your books can support
Tell Derik which ERP and accounting system you run and which decision the model has to answer. He will tell you what your data can support today.
Start a conversationWhat data the model needs
- Trial balance by month. Two to three years, with the chart of accounts and a record of any changes to it.
- Sales lines. Item, customer, quantity, price, discount and date, from the ERP.
- Costs. Purchase receipts at the price paid, standard costs, and for a plant the bills of materials and routings.
- Inventory. Month-end quantities and values by item or category.
- Receivables and payables. Invoice, due and payment dates, which drive the payment-day assumptions.
- Payroll by department. Headcount and cost totals, without individual pay records.
- Capital and debt. Planned equipment purchases, loans and their payment schedules.
- The current model. The workbook you use now, which holds the structure and the assumptions people trust.
The trial balance alone cannot drive a goods business’s model, because it holds totals by account and no units. Volume and product data has to come from the ERP. AI for ERP covers the ways to read it, including older systems installed on your own server.
How to start: one model and one question
- Choose the question. For example, a 12-month forecast of gross margin and cash by product family, or the effect of a price change on next year’s cash.
- Load the actuals and reconcile them. The model’s actuals must match the financial statements to the dollar before anyone trusts a forecast built on them.
- Test on last year. Forecast the last 12 months from the history before them and compare with what happened. AI evals explains how to test on past results.
- Run it beside the current model for two or three months, and write down where the two disagree and why.
- Name the owner of each assumption. The controller or CFO signs off on every driver change. Human in the loop covers the approval step.
Build in stages, starting with the question that matters most. Cost of AI explains how each part of a build is charged.
Risks and limits
Keep arithmetic out of the language model. OpenAI’s help centre says ChatGPT “can produce incorrect or misleading outputs,” and it lists its code tool as the one that “enables accurate calculations.” In a financial model, the AI writes the formula or the code, and the spreadsheet or program does the math.
- Traceable assumptions. Each driver shows where it came from and who last changed it.
- Checks in the model. The balance sheet balances, cash ties to the bank, and actuals tie to the statements, with a visible flag when any check fails, as the ICAEW Code recommends.
- Limits of history. A forecast built from history cannot know about a contract you signed or lost last week, so the owner of each assumption adjusts for it.
- Data location. Ledgers and payroll totals are sensitive, so know where each step processes them. Python in Excel, for example, sends its calculations to the Microsoft Cloud.
- One current version. A model that several tools and people edit needs one place where the current version lives, and a log of what changed.
How ThriveAI helps
ThriveAI is an AI engineering company in Ottawa that builds private AI systems on a company’s own data, for businesses that make, move or sell physical goods. For financial modeling, that means connecting the ledger and the ERP, reconciling them to the statements, and building the model around your products and customers, with every driver traceable to its source and a named person approving changes. Every answer shows where it came from.
ThriveAI’s systems read your ERP and accounting system as they are, and every connection only reads data. They are designed to keep each client’s data on its own server in Canada. You choose a model on that server or a hosted model under a written zero data retention agreement, and a hosted model may process requests outside Canada. Derik Lawlis, the founder, leads every project and stays close to the build. About ThriveAI covers the company.