Building a model in 7 easy steps
I’ve been fortunate to build over 100 financial models in my career…
and each time, I learn something new.
But the general process is fairly consistent across each one…
and can be summarized in just 7 easy steps
Connecting your Data
Connecting your data is the starting point in your financial model.
The foundation here is importing your existing P&L and Balance sheet from your Accounting Software.
When your financials are imported to your model in their native format, you make it easy to refresh, and allowing you to create your cash flows statement (see next step)…
Other data to connect would be your existing hires, and any information about your existing / pipeline customers.
If you are building a projections only model, this step can be skipped
Link the Financial Statements
Now that your financials are imported, it’s time to link them.
The concept is pretty simple…
P&L connects to the Balance sheet via Net Income & Retained Earnings.
The Cash Flows gets generated by taking the difference in each Balance Sheet account, other than net income, Depreciation, and Amortization, which pulls from the P&L.
For Assets, take LAST MONTHS value - THIS MONTHS value
for Liabilities + Owners Equity, take THIS MONTHS value - LAST MONTHS value
Forecast Revenue
Here comes the biggest part in you forecast…your revenue
Here, you would start by analyzing the activity from your existing customers, and customers who are warm in your pipeline.
You would then develop a revenue build for new customers utilizing the A∙R∙S∙R framework:
How you Acquire those customers (Acquire)
How long you Retain those customers (Retain)
How you generate Sales to those customers (Sell)
How you record revenue, and any other P&L / Balance sheet items (Record)
Forecast Headcount
Now comes your biggest opex account…your headcount
You would start by reviewing the existing headcount that you imported in step 1…
then add in projected hires.
Things to consider are:
Hire date
Salary
Department
Employee vs Consultant (for payroll tax & health benefits projections)
Forecasting Opex
Now that we have our headcount projections out of the way, it’s time to forecast out the rest of our Opex.
Since we’re building all of this in excel, we have full control over how we want our forecast to look.
I like to forecast Opex using any of these drivers:
6 month historical average + buffer
Based off of headcount
Based off of revenue
Prior month
Forecast Balance Sheet
Now we get to the last section of our forecast…the Balance Sheet
Here, we need to analyze the balance of key accounts, and plan how those balances will change in the future.
Each account generally follows this structure:
Beginning Balance
[+] new amounts added
[-] amounts recognized
Ending Balance
A whole email in and of itself can be written about each of these areas, but the general principles are the same…
A common approach is to use a waterfall table for each of these items to get the proper balances, which will then flow into your Statement of Cash Flows.
For all accounts with minimal balances, I like to just set that to the prior months balance so that there is a 0 cash impact.
Present with Pretty Design
Now we get to my favorite part…presentation 🤩
It’s here that we take all of our hard work, and present it in a manner that will woo the crowd…
whether that be the CEO, management, investors, the board of directors…anyone
Here we utilize the principles from my CFO Dashboards course, allowing us to create dynamic and appealing dashboards that drive insights, and make us feel happy to stare at 😊