Excel for FP&A: Using AI to Build PERFECT Financial Models

Profit Playbook
12 followers
Most people think finance professionals spend hours each day creating complex
Excel formulas. And this was true for a long time. But AI changed everything. Today,
with tools like JetGPT, you can now build robust, error-free financial models in less than 30
minutes. So in today's video, I'll build a real Excel model for FP&A using AI, breaking down
each step of the process. This is the same framework that's helping finance teams save
hours of work while creating more accurate models. So let's dive in. Here's the context.
You're a finance manager at a software company. Your CFO asks you to create a weekly pacing model
that calculates how the business is doing versus its monthly goal. The model must be as automated
as possible and allow for on-the-fly changes to dates so we can compare different scenarios.
This is a critical project and you want to build something robust and professional. You want to
use AI to help you structure and build this model efficiently. So let me walk you through the model
we are going to build and then we'll look at the prompt for JetGPT. So our goal is to build a
pacing model that takes monthly forecasts and compares that to the actuals we have achieved
until a certain date in the month. So what we have here is a simple P&L from revenue over gross
profit, marketing expenses, general and administrative costs, all the way to operating
income. And then we have the monthly forecast already given from January through December.
So for example, $111,000 is the revenue we forecast for the month of January. And then
down here we track daily actuals. So each day we get another column here with actual results.
And so for example, on January 1st, we had a revenue of $3,438. And then down here,
this is the model we are building now together. And the idea is that we have the actuals summed
up to a given date. Then we have the corresponding forecast for this month displayed here.
We compare actuals to forecasts. And then we calculate the percentage of actuals that have
already been achieved as a percent of the monthly goal. And then we compare that to how much time
has already passed. And then we can use this to interpret company performance and how we're doing
against the January forecast. For example, if half of the month has already passed, but we only
realized a third of the revenue, then probably there is an issue and we have to do something
to catch up. So here's the prompt we'll start with. I'm a finance manager at a software company.
I need to build a weekly pacing report in Excel. The model should help us analyze how actual results
are tracking against our monthly forecast. I need your help with writing the Excel formulas.
Please provide formulas I can paste directly into Excel with short explanations of how each
component of the formula works. Ensure that the formulas used are as simple as possible,
making them easier to audit and verify that they exactly fulfill the intended purpose.
This is a high visibility project that was given to me. And I would like to impress our chief
financial officer. I don't work with them often. And I want this model to help me differentiate
myself from other analysts and making it as robust, user friendly and automated as possible.
I have actual revenue by day in row 22. The first formula I need your help with should
sum up the actual revenue up to a date specified in cell E35. I need to be able to change this date
and the model automatically shows the corresponding sum of actual sales in cell F38.
Okay, before we move forward with pasting this prompt into chat GPT,
I want to break down why I wrote the prompt the way I did. You can apply the structure
to any prompt for any AI chatbot doesn't have to be chat GPT. We start with providing the goal
with context. That's the part where I say I'm a finance manager at a software company,
and I need to build a weekly pacing report in Excel. The model should help us analyze how
actual results are tracking against our monthly forecast. See how specific I am there. Then you
need to specify the return format. I need your help with writing the Excel formulas,
please provide formulas I can paste directly into Excel, etc. Then you should also include
warnings to make sure that the output the chatbot gives you is exactly what you're looking for.
So I say ensure that the formulas used are as simple as possible, make them easier to audit
and verify that they exactly fulfill the intended purpose. And then I recommend that you do something
that maybe you haven't done before when you're prompting AI, but it can make a big difference,
especially as these tools become more and more powerful. And that is to provide a context dump.
This is the part where I say this is a high visibility project that was given to me and I
would like to impress our chief financial officer and so on. This context dump helps give the AI
more background and makes it so that it can tailor its answers exactly to what you're looking for.
And with that done, I can provide the first task. That's where I say I have actual revenue by day
in row 22. The first formula I need to help with should sum up the actual revenue and so on.
Start by telling AI what I have already have, where it's located in the Excel sheet and where
the cell with the data is, because that way the formula gives me exactly what I need and it can
just copy paste. Now this prompting framework comes from Greg Brockman. He's the co-founder
of OpenAI, the company behind ChatGPT. You notice that the prompt is fairly detailed and that's
crucial. When I train corporate finance teams and they ask me to help with a complex formula,
I don't teach Excel anymore. Instead, I ask them to ask their AI chatbot of choice and then I help
them with crafting the prompt. While most finance teams have access to a company-sanctioned AI tool
now, people are still struggling with prompts. The mistake I most often see is that they treat AI
like a traditional search engine. They input three or four words and then they are surprised
that the output produced by AI is subpar. But if you follow this prompting framework, you will see
the immense capabilities these tools already have. Before we hop into ChatGPT and build out our model
in Excel, if you want to take your FP&A skills to the next level, I'm currently giving away my 10
most popular one-pager infographics for free. These contain my best frameworks and strategies
for finance pros, so click that first link below to get them for free. Now, let me show you how to
create the actual model using ChatGPT and Excel. Okay, so now we are in ChatGPT, but this works
with any of the modern chatbots. So, whether that's Microsoft Copilot, Gemini from Google,
Cloud from Anthropic or Grok from XAI, this will work similarly. I'll paste the prompt here and
then let's see what we get. Okay, so it gives me the formula and then it outlines an explanation
of how the different components of the formula works and it gives me, you know, a reasoning for
why it shows that formula. So, let's see if it works. I'll copy this, clicking on copy here, and bring it
into the Excel model. So, now I paste it here in the cell and let's see. This looks good. So, if I
click in the formula cell or click F2, I can see what it's doing. So, it's pulling revenue here
from the daily actuals, gives me the dates, it links to the cell and it gives me, is the sum
correct? Yes, it's 47498, the same number we have here because this is summing
up. So, this seems to be correct. Let's check if we can also change the date here and the number changes.
So, let's change this to January 11th. Okay, it gives us a different number. That's good. Let's see
if the number is correct. Yes, 42862. So, that's the formula and now I can take this formula
and copy it down. First, I have to fix the cell references by using F4. Okay, and now I can copy it
down. If you have any questions about how I'm doing any of these steps, by the way,
you can also ask that question to ChatGPT. But for now, I'm just building out this P&L.
Gross margin is revenue minus cost of goods sold divided by revenue. Gross profit,
take revenue less cost of goods sold. Then we do the ratios as a percentage
and my operating income is gross profit minus marketing expenses and general and administrative
cost. Okay, so now we want to calculate the forecast according to the date that we have here.
We set that to the date that we had there before and for that we use a different prompt. So, now we
don't need to explain and provide all the background anymore because ChatGPT already
knows what it needs to do and what we're looking for. So, we can jump straight to explaining what
we want. So, I'm pasting this here. I like to start with giving it quick feedback just so it
knows that we want the next output to be similar to how it did it before. So, just say perfect.
Then I say I have forecasted revenue by month in row 6 with January in column F. The month labels
are in row 5. The second formula should look up the monthly forecast according to the current month
which is indicated by date in cell 35. I need to be able to change the state and the model
automatically shows the corresponding forecast in cell G38. Let's see what we're getting here.
Okay, so now here it made a few assumptions and actually I really like that the assumptions
are spelled out here. And it assumed that the month in row 5 is labeled as Jan, Feb, March,
etc. Not full names like January. But actually that's not the case. So, if we go back to the
Excel model we see that in row 5 the month is actually provided as a number. So, let's go back
and ask it to make that correction. Actually the month in row 5 is provided as a number. So,
for example one is for January, two is for February, and so on. So, now it updates the formula
and now it aligns with how the model is set up. So, we can now again take this and paste it into
Excel. Of course, when you do this at home I recommend you also go through the explanation
because ideally you'll learn something from the AI while you do this. Now we copy the formula
and we bring it over into our Excel model. Okay, 111,373. Does it pick up the right rows? It does.
Let's compare it to what we have here. Yes, the January number is correct. Let's change that to
a date in February for example. Does it pick up the correct number? It does. Okay, so let's change
it back to January. Now we can do the same thing. We add the fixed cell references here so we can
copy paste this down and then we can take the other calculations from the actual column because
they are the same. Okay, so now actuals versus forecast is straightforward. We just subtract
one from the other so we see the difference here and then we can also calculate actuals as a percent
of the forecast. That's also super straightforward. We don't need the AI support here. That's a simple
formula and copy that down to the relevant cells and now we can compare how much time has passed.
This is where it gets interesting because we want to compare how much time has passed in the month
up to the date to how much have we already incurred in terms of revenue or how much we
have already spent in terms of expenses. This formula is tricky. When I teach this,
people often struggle to come up with how this formula works so let's ask AI to help us with
that again. The prompt I would use for this is next formula cell J38 should calculate the time
elapsed in a given month up to the date in cell E35 displayed as a percentage. Let's see what we
get. It gives us a formula here and here I definitely recommend reading the explanation
to see how it works because it's not super straightforward but let's see if this works.
Okay it calculated 39 percent. Let's double check if this is actually correct. January has 31 days
so we can say 12 divided by 31 is 38.7. Is it 38.7? It is. So this is how you build a model quickly
in Excel using JetGPT. The next step here would be to interpret the numbers. How is the company
doing against the January forecast? Well we're tracking slightly ahead in terms of revenue.
That's where you compare those two metrics. We are a bit behind when it comes to marketing expenses
but that could simply be phasing. We might be spending the money later in the month but
you learn the most from a pacing model like this when you look at revenue. Okay so that's exactly
how you can use AI to build robust Excel models much faster. Now as I mentioned before I'm currently
giving away my top 10 FP&A infographics for free. You can click the first link in the description
to get them and if you do you'll also join my newsletter where I share actionable FP&A tips
and strategies every single week. So click the link below to get all that completely for free
and if you want to learn the new way to create financial presentations then watch this video next.
