My 9-Step Financial Modeling Process
A step-by-step approach to building financial models that I have developed over the past decade.
I want to start by making something abundantly clear. Financial modeling is not the same as Excel modeling. While building your financial model in Excel is a large part of the exercise, we must take other crucial steps to create robust and, most importantly, useful financial models.
Now that this is out of the way, let's dive straight into the steps I always take when working on a model.
Step 1: Establish model purpose with the client
Obviously, every financial modeling project starts with a chat with the client. And when I say client, this can be an external business hiring you or someone within your organization that requires the model.
However, most people talk with clients with the goal in mind to ask them what they want the model to show. And in my experience, people have no idea what they want the model to look like. But they know what they want it to do. At least most of the time.
So, this is my main goal in my initial call/meeting. I want to understand the exact purpose of the model. Will they use it to apply for bank debt? Should it follow some narrative to be presented to potential investors? Is it supposed to be the internal roadmap against which the team's performance will be benchmarked? In theory, all those should be the same. But they rarely are.
So, 2 main things to figure out here:
Who will use the model? (stakeholders)
What will they use it for? (purpose)
Step 2: Understand business processes
We then move to holding interviews with anyone who's someone within the company. Department heads, process owners, salespeople, business strategy people, and anyone who can impact the company's financial performance.
This is something that I adopted from my years as an external auditor. The only way to build a great financial model is to understand how the business operates and how functions and departments interact within the company. And the best way to do that is to talk to people.
The only way to truly understand what's happening with a business is to talk to the people working there. You can spend as much time as you want digging through the financials (and it's essential), but you should spend equally as much time talking. And by talking, I mean listening.
This is why this step requires you to essentially hold interviews with all key employees, especially process owners. In an ideal situation, after you've spoken to everyone, you should have flow charts for how each process works and, hopefully, an overall flow chart of how the entire business works. This will help you a lot in the following steps.
Step 3: Discuss key drivers & assumptions
Armed with our detailed understanding of the business, we can move to the fun part. We will review and discuss the company's key drivers, focusing on what levers move the needle the most.
I would usually spend some time trying to figure this out on my own based on all the information gathered so far. On one hand, this helps me be more effective when discussing assumptions with the client. On the other hand, it makes me look smarter and solidifies my expertise, as I pick up on what they're saying much faster and have much more detailed and applicable ideas to share. Oh, and most importantly, entering these discussions as prepared as possible allows you to better manage and steer the conversation.
Step 4: Gather data
This is pretty self-explanatory, but I want to specify something here.
It would be best to never let the client figure out what to send you. Never agree to 'I'll send you everything I have.' They won't. Not maliciously, they just don't know what you need.
So, better to play it safe like the big consulting firms and send them an RFI (a Request For Information), outlining every little breakdown and piece of data you need to build the model.
Then, track when data is received in the RFI file itself. Not only will this help you actually track what you have and don't have, but it will help you cover yourself when the entire financial modeling project inevitably gets pushed another few weeks because the client has not yet provided everything you need in terms of data.
Step 5: Create a model structure
I know this one sounds like we're finally opening Excel, but there's actually one more thing we have to do. Before we spend time playing around in Excel (which, let's face it, is the best part of the whole thing), we will do one more thing.
Grab a piece of paper, open a new PowerPoint file, grab a whiteboard, or whatever works for you, and start outlining how your model will work once it's done. I usually go for a flow chart style, identifying and noting down the main elements of my model and the main ways these relay data between them.
I've found that having such a high-level diagram helps me stay focused on the bigger picture and allows me to plan for subsequent features in advance.
Step 6: Build the model
We are finally here! Time to roll our sleeves and do what we came here for, right ;)
I won't spend time going into the nitty gritty details here. Essentially, you take everything you learned, gathered, and prepared in the previous steps and use it all to build a model that is so amazing it makes you genuinely proud of yourself.
Are you interested in learning this whole Excel modeling part step-by-step? If that's the case, I have a free course on YouTube that takes you from a blank Excel page all the way to a dynamic assumptions-driven financial model.
And the model we are building follows the exact same structure I used for a US-based startup doing $100m+ in annual revenue that used the model to raise over $50m.
You can check out the YouTube playlist here:
Clean link: https://www.youtube.com/playlist?list=PLAwVMydA44DFAKG1Ow71sUobV9b1_iLKF
Step 7: Discuss assumptions and prepare scenarios
We're done. Well, not really, given that we said '9 Steps' on the box.
Our Excel file is finished and polished, and everything flows dynamically. We can't help but chuckle when we think about the exquisite solutions we thought of when building the model functionality and dealing with some of the complexity around business operations.
It's now time to return to Earth and rejoin the regular humans who don't have your Excel superpowers.
I know, it sucks. But it's inevitable, and you have to do it.
It's essential to sit down with the users of the financial model and discuss all assumptions made in building it. I'm not talking about structural assumptions (we try to avoid changing those unless something is really messed up). What I mean by assumptions is all the growth %'s, receivable days, interest %'s, salary increases, etc., that you have in your list of assumptions.
Management, department heads, and process owners must review these and ensure they feel comfortable with them.
Over the years, I stopped walking people through the model in detail, trying to explain what each specific assumption is and its impact. I found it not to work very well.
Therefore, I started preparing an Assumptions Guide Book for each model I work on. It's essentially a Word file that goes over each piece of the model, outlining the assumptions, what they do, and what they are currently set at.
It takes a few hours to prepare, which is not ideal. Still, in my experience, it helps stakeholders understand much more about how the model works, as written narratives are far less intimidating for people (at least for the regular ones; I prefer Excel files).
So, at the end of the day, it probably saves time. However, most importantly, it makes discussing the assumptions and adjusting them to levels stakeholders are personally comfortable with much easier to get to.
Step 8: Run stress tests
Okay, we're almost there. This is a simple step. After we have built the entire model and reviewed and dialed down all the assumptions, it's time to ensure our model holds up to scrutiny.
Here, I'd add sensitivity tables, do some edge-case scenarios, and see if the model logic holds up in more stressful situations. What happens if growth is negative? What happens if interest jumps by 5%? Essentially, I push the assumptions to the extreme to see how the model behaves.
Step 9: Build required outputs
And, for dessert, we get to do some more Excel work.
Remember when we discussed the purpose of the model? This is where this comes into play. We now have a fully functional model that calculates the future projections. However, we need to build the exact views to show the relevant stakeholders what they need to see.
I would usually build the ones we discussed initially and throw a few on top for good measure. My favorite one to do is a revenue step-up chart that outlines where revenue growth comes from. I still haven't met a client to ask for it. And I still haven't met anyone who asked me to remove it from their model. It's my favorite chart, to be honest. 😻
We're finally done. Good job!
I hope the above made sense and that you have taken a few ideas for your workflow from how I prefer to organize my financial modeling gigs.
This is the method I've gradually developed over the years. It's been pretty steady for the past few years, but I am 100% sure it will continue to evolve as my skills and knowledge evolve.
That's all I have for today, and if you want to learn more about the fantastic topic of financial modeling, I'll see you in my course on YouTube: https://www.youtube.com/playlist?list=PLAwVMydA44DFAKG1Ow71sUobV9b1_iLKF
Thanks for reading!
Best,
Dobri