Editor’s note: This article is Part 2 of a three-part series on financial modelling with AI. Part 1 looks at the limitations of using AI when modelling; and Part 3 looks at the test and implement stages after the model has been built.
Last time, in Part 1 of this series, I addressed the most common questions that are asked surrounding automating the development of a financial model or spreadsheet using artificial intelligence (AI). We concluded it can help, but you need to understand the current limitations, risks, and required checking/auditing procedures you should implement in such instances. It was arguable whether it was quite ready to take over this process yet and whether it would take more time to implement than it would save.
This time I want to consider how AI can help in the stages before you actually build a model. There are in fact six stages of model development:
Three of these, which I look at here, come before the actual build stage:
- Scope: Who/what is the model for? Here, the model’s purpose, its key objectives, and outputs are all agreed. The intention will be to identify what is wrong with existing reporting processes (if any), where the data will come from, who the key stakeholders and end users will be, and so on.
- Plan: What are the key components of the model, and how are they related? Considering the scope in more detail, this stage will confirm what inputs, constants, calculations, outputs, and further analysis should be included — and also identify what is not to be reported. Model specifications and timelines should be agreed at this stage.
- Design: What is the structure of the model, and how will it be built? This stage will agree with stakeholders the high-level relationships between the various elements of the model and how it will complement existing management information systems and reports.
Modellers tend to be process matter experts: They know how to build a model and ensure the spreadsheets conform to best practice standards, are fit for purpose, and are easy to interact with for end users.
Many modellers are not subject matter experts, though. They need to be instructed regarding what the model should do, how key calculations will work, how the business fits together, and so on. This is why the best models are usually those constructed collaboratively with end users or clients, rather than those thrust upon an unsuspecting workforce, as is sometimes the case.
This is where AI can help. Let’s keep it simple. For building models in Excel, the two most common AI tools you are likely to use are ChatGPT and Copilot (in Excel, or in general). Let’s consider some simple assumptions and employ ChatGPT, by simply typing in the following:
When prompted to provide an overview of scope, ChatGPT provided a solid understanding for the model build using the following prompt:
It deduced which financial statements would be needed, including the following line items (extract only shown):
This helps us confirm our process. It is similar to what was developed the last time with similar issues, such as an arguable approach to cash flow modelling, for example, and also suggests considering equity and debt financing.
It should be noted there are subtle and not-so-subtle differences between this response and a generic response to a prompt simply requesting what would be the scope without considering any modelling assumptions. For example, the above notes typical financial instruments that have not been included (eg, debt/equity financing) and also provides more detail on other line items such as Accounts Receivable. Here it recognises the driver is Days Receivable (and not another, such as aging or direct write-off, for example).
However, let’s move away from process matter expertise towards the aforementioned subject matter expertise. Let’s take our simple model and assume we want to pitch for venture capital (VC) funding. In this instance, I work with venture capitalists regularly, but the point here is you may not. The intention is to ask AI for advice and evaluate it.
ChatGPT was able to reproduce a reasonable set of suggestions like carried interest and hurdle rates, clearly aimed at VCs rather than other types of financiers:
Of course, all this should be verified independently: Never “trust” AI solely. Always seek third-party factual corroboration. AI may present false facts (known as hallucinations) and sound convincing, although in this case, I think the details are a good starting point, allowing VCs room to negotiate.
It also included commonly used performance metrics in another section:
You may or may not know these metrics, but the fact is, if you were not aware of them before, you can now proceed to research them and possibly create additional or replacement metrics, too. AI can help develop subject matter expertise.
It can also provide contextual understanding:
You can drill further into scoping, planning, and designing. For instance, consider the following prompt:
From here, it is possible to determine whether the model conceptually is fit for purpose. The response here included the following:
It is true some of the responses here are generic, but arguably, these are the objectives of many models. It can be pushed; this is where AI is particularly good at reconsidering and drilling down on ideas.
Here, several options were provided. These included:
The sales growth rates could be challenged, but previous experience here suggests AI has considered the industrial “standard” rates and certainly terminal growth rates of 2.5% to 4.5% would be in keeping with a more bullish valuer, although possibly acceptable in the current global economy.
AI furnished more ideas, though. Rather than show all the screenshots, in summary, ChatGPT and Copilot offered advanced valuation approaches such as:
- Monte Carlo sampling simulations analysis to simulate multiple financial and operational scenarios using a probabilistic 3-D modelling approach (this seemed quite reasonable if a little complex).
- Comparable company analysis. The idea here was to benchmark valuations and compare key ratios for the industry.
- Exit multiple valuations. A common approach for VCs, this demonstrated entry/exit scenarios using EV/EBITDA multiples. Most VCs would expect this summary — but the would-be modeller might not know this.
Ideas were also provided about what KPIs to use, typical charts, and other types of analysis — but this is more a story for Part 3. The point here is that AI was considering who the key users were, the model’s purpose, and key requirements.
You need to be careful believing AI understands context. It is simply using neural network thinking to identify common patterns from key words it recognises. The better the question is phrased, typically the more usable the response will be.
That is not to say generic questions cannot provide useful insights. We may use them to determine whether the response will be valid for planning and designing a model. For example, consider the prompt:
There is no clue provided in the question, so this is a good test to ask when considering whether to rely on the responses. In this case, ChatGPT and Copilot provided comprehensive answers that were summarised as follows (the following is not an exhaustive list of what was supplied):
- Business owners and founders to assess profitability, cash flow, and long-term sustainability and be able to undertake reasonable strategic and operational decisions.
- Finance and accounting teams to track key financial metrics, plan budgets, forecast, and optimise scarce resources and/or working capital.
- Operations and supply chain teams to manage cash flows, procurement, and capital expenditure.
- Consultants and financial analysts to support strategic business planning, M&A, or restructuring work.
- Board of directors and advisers to guide corporate-level strategy.
- Corporate acquirers and competitors to consider sale options.
- Government and regulatory agencies, where applicable.
It sounds impressive, if a little generic, until you realise venture capitalists have been omitted from the list! The point is it is a good start, as long as you corroborate and then extend the research accordingly.
AI is reasonable at grasping more subtle considerations when in the early stages of model design. For example, the following prompt:
provided an unsolicited but nonetheless succinct summary of key model changes for buyers:
Time and again, I found ChatGPT would go into more detail than Copilot. Whilst Copilot utilises AI models developed by OpenAI, it is focused on productivity gains within Microsoft tools (eg, Excel). Copilot focuses more on that integrated ecosystem, whereas OpenAI’s ChatGPT remit was broader and therefore was open to more ideas in general.
This was particularly clear in the design and planning stages. Copilot tended to provide higher-level task summaries, eg:
In contrast, faced with a similar prompt, ChatGPT would actually start detailing suggested worksheet content for each page of the model, viz.
Drilling down, it became more and more apparent that ChatGPT could explain topics in greater detail as the prompts continued and the conversation deepened.
Consider the following. As model consultants, I asked ChatGPT and Copilot how long we should budget for building the model.
Copilot provided a very high-level summary:
Ten weeks to build a model? I thought AI was supposed to make things faster. We are definitely in “take this with a ton of salt” territory here. I am not sure I will be letting Copilot perform the sales pitch for model development services any time soon.
But what about ChatGPT? As expected, this AI tool was much more detailed:
I am not convinced of the timing of the work here either, but there is more detail that can lead to making better, more informed choices regarding the work required and therefore the budgeted duration.
Indeed, unsolicited, ChatGPT was prepared to become ever more helpful suggesting formulae for the work, too:
The final formula suggested contains both nested IF functions and hard-coded values, neither of which will win any best practice awards, but challenging with further prompts would allow for better second attempts. Remember: I hadn’t actually asked for a step-by-step guide and formulae. The LLM just thought it was a logical next step. I quite like that.
Word to the wise
If you are following along creating your own prompts, it is entirely possible, using the same queries, you will get vastly different responses. That is one of the issues with using these AI tools. However, it should not detract from the fact that both Copilot (in Excel) and ChatGPT, the two AI tools you are most likely to use when building a model in Excel, are helpful in the scoping, planning, and design stages of the model.
As always, responses need to be verified independently, but they can certainly assist where subject matter knowledge is limited. It can give you ideas of what to research next and at least reduce the risk of not knowing what you don’t know.
ChatGPT continually provided better qualitative/quantitative answers. Its solutions aligned with a higher level of awareness compared to Copilot about why someone would approach it with a specific prompt and, therefore, what solutions they may want in return. In particular, as the conversations become more complex, ChatGPT would push suggested solutions that little bit further. However, in conclusion, either tool, when used appropriately, can be a valuable resource in the preliminary stages of model development.
Next time, I will look at post-model production and how AI can assist there, too.
— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling and Continuing Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Oliver Rowe at Oliver.Rowe@aicpa-cima.com.
LEARNING RESOURCE
AI-Powered Excel: Leveraging AI and ChatGPT for Supercharged Productivity
This webcast will have you streamlining your Excel work, research, and documentation, saving you time and effort in your day-to-day tasks.
WEBCAST
MEMBER RESOURCES
Articles
“Using the AI in Power BI to Do Root Cause Analyses”, FM magazine, 27 March 2025
“Excel Modelling: How to Implement 3 Types of Checks”, FM magazine, 25 March 2025
