Introduction
This page demonstrates how Hedieh and I have used AI to improve financial modelling in a realistic and helpful manner to fix the key pain points of a financial model (Hedieh has done most of the work). She has demonstrated how to use Claude to answer a modelling test from a case and she has done a similar thing with an information memorandum. She has created a set of skills to nicely format a model. She has also used AI to review a model and do detective work on a model. On this page, I review some of how these AI tools work, but with the exception of verifying and reviewing the model, I do not think AI in financial modelling is such a big deal. For example, if you are preparing for an interview exam, you better understand how the model works and you better be able to move around excel very quickly. Most important, you better understand the reason for the various equations. In your actual work I cannot imagine letting AI make models without knowing how to make the models yourself.
I have also seen that AI can create three statement models which is not very interesting (as you may know, the idea of making a three statement model makes me vomit, because creating financial statements is really no big deal compared to things like efficiently structuring a model and understanding nuanced valuating techniques). You are probably too young to remember the world before excel and lotus. But when I reluctantly moved from programming languages to spreadsheets in the 1980’s, the fundamental reason spreadsheets were used in the first place in financial modelling is that unlike code such as python or C++, people can clearly see and follow the input data; all of the equations and easily make changes to outputs and presentation. Doing things like creating timelines, setting-up and InputC page, working through operating cash flows should go really fast. Alternatively, you can use a template model and just fill in the data if you want. Maybe AI can help with these things, but you are going to have to work through assumptions and probably make your own presentation.
.
The meme below may be silly to put on this page, but it empahsizes that abandoning excel with a “shadow model” were you use HTML and you cannot see how formulas work may not be a good idea. The thing people like about excel is you do not have to do programmings. The idea of Hedieh and I is to combine programming with excel and to selectively use AI to accomplish this.
.
.
Dramatic Time Savings – Known for a Long Time
Now to where AI may help with something that is difficult without AI. For a few years I have been having discussions with a modelling company that makes large project finance models. The big issue was the time it takes for running the copy and paste routine in a model and also a goal seek function. If you want to run a lot of scenarios there is no way to do this in the middle of an investment committee meeting or a credit committe meeting. The time it takes to run scenarios in a model is surprising as processing of calculations on a model is so fast with any computer and the calculations for even a large project financial model are very small compared to gaming applications and other scientific applications.
Many years ago I developed the following process to resolve the copy and paste problem: (1) take the data inputs from the excel financal model that creates the circular reference problem; (2) put the data into a programming application (python, C++, html, or VBA), (3) re-do the modelling equations in one of the programming languages; and (4) put the results back in excel. With these four steps, the problem with the timing of the model could be solved. I was able to demonstrate this with large models that had very complicated financing during construction formulas (nuclear plants), with very complicated tax equations that had constraints on the interest charged for shareholder debt and tricky timing issues, and with many different debt issues including equity bridge loans and standby facilities. It was time consuming to replicate the equations but the result in terms of processing time was dramatic. The article attached to the blue button below explains the process in more detail. Below the button I have included a couple of short videos that show examples of the time savings. It is not new that the clugging along with the copy and paste/goal seek can be reduced to seconds as shown on the videos. What is new is how Hedieh and I have made the process much easier to implement.
I understand that somebody else put a financial model into HTML and used a similar process where the model is replicated in another program. The ability to achive a much faster process is not something new. We have known this for a long time as shown in the excerpts below.
Case 1: Large model that had complex tax equations, multiple debt issues, tricky timing issues with political risk and goal seek formulas.
In the first case, the model is large (about 25 MB), with multiple debt issues and complicated tax equations. The copy and paste includes a goal seek. The screenshot below illustrates that running the copy and paste with the goal seek takes about 11 minutes. The method for adding timing to your models so you can evaluate timing is shown at the bottom of this page.
.
.
When the circular reference equations are transferred to code and the equations are wrote back out the excel file, the time it takes to finish the process, including the goal seek is reduced to only three sections. The dramatic time savings that we have illustrated before is shown in the screenshot below. This time of seconds includes reading data into a program (in this case VBA but if you a snob you can use python, HTML, c++ or whatever you want); doing the circular calculations and goal seek caclulations; and writing out data back to excel.
.
.
Case 2: A large model with reconciliation to actual data in the construction period and different generating units.
.
With the goalseek, the time greatly increases as shown below. In this case the time to run the copy and paste together with the goalseek was 1,193 seconds or a massive 19.8 minutes.
.
.
Case 3: A model that consolidates projects into a fund and becomes large with circular references
.
In this case, three methods were added to the hadle circular references during construction in different ways. To model the consolidation carefully, so that the different projects could have different construction start and inservice dates, the models should be monthly. To measure the time it takes to use different options. we have added options to use different ways to resolve the circular refences. The method labeled none cheats and does not make the calculations.
.
.
In this case a template model is copied multiple times and re-calculated using VBA code that changes inputs to the template model and the copies the template model to a new sheet. This takes a long time even if there is no circular reference. The illustrative case has a circular reference related to financing during construction. The 102 seconds is the benchmark to test the other methods for resolving the circular references. The time is more than a minute because the inputs to the template model are changed and then the template model is copied into a new sheet.
.
.
The second case is where the IDC is computed and the iteration button in excel is used to resolve the circular reference. This increases the time to
.
The copy and paste takes a little less time.
.
.
A model that has general InputC and InputS and fully replicates goal seek formulas using a method called bi-sectional calculation techniques.
.
With that long introduction, the objective and the challenge are clear. To implement the process Hedieh and I have developed a few alternatives
- Make sure that anybody can complete the process (with a little training) and do not charge a bunch of money for running the model.
- Do not devolve into arrogance of using fancy terms or complex language (like saying that python is so much better than vba for the application of saying that you have to complete the process outside of excel.
.
Option 1 – Use a Skeleton Approach
Copy Code from Excel to VBA, While Using AI to Debug Programs
Hedieh has developed a method where you can copy some stuff from excel to some generic VBA code. The code you copy is adjusted from the financial model equations and inputs but the initial steps to read the model output and some generic formulas for writing out the results. The process involves copying a list of public variables defined in excel from the inputs and non-circular reference items in the financial model. Second, the variables that need to be read from the excel model that are automatically defined by in the excel file can be copied. Third, some of the circular reference equations such as computing the interest expense as a function of debt are copied. For this section you can use templates and/or write your own equations and/or let AI help with the equations. Fourth, you can create your own customized report structure which will be translated into code that can be copied into VBA code. The files, the screenshots and the videos below illustrate this method with a simple case and a difficult case. The simple case just has circular references associated with interest during construction, up-front fees, and debt sculpting with taxes. The complex case includes multiple debt issues, tax effects of shareholder debt and incorporating a goal seek.
The excel model with completed VBA code and a sheet that translates elements of the financial model to VBA code for the simple model and the more complex model are attached to the two buttons below.
The steps for completing this process and a video explaining the process are shown below.
.
Option 2 – Make Claude Write the Code
Refer to Rodrik. Illustrate with a video. Demonstrate the things that can go wrong.
.
Option 3 – Combine the Skeleton with AI
Refer to Rodrik. Illustrate with a video. Demonstrate the things that can go wrong.
.