Latest Month of Actuals
One of the key things to include in your model is your actuals.
When you include actuals in your model, you unlock a ton of benefits compared to if you only show your projections.
But including actuals in your model means that you also need to set up your formulas in a special way…
especially as it relates to the formulas on your Balance Sheet.
Let’s dive into it
Why do you Need to Include Actuals in your Model?
There are a number of reasons for why you need to include actuals in your model.
Let’s dive into a few…
1. The past informs the future
I know I know…you’re about to 10x. The past is the past, and the future outlook is much much brighter.
That’s fine…go nuts.
Prepare a budget showing a crazy ROI, and hold your team accountable for it…
but don’t make the mistake of showing that 10x growth in your very first month of projections.
Comparing your last month of actuals to your first month of projections is as great way to spot check your assumptions - the values should show a gradual transition, and not a crazy spike.
This is one of the most common ways that I spot check my projections with my clients.
2. Investors often will ask for your actuals
A common reason for why you create a financial model is to showcase your use of proceeds to investors.
But investors don’t want to just where you’re GOING…
they want to understand where you’ve BEEN.
They want to see how much revenue you’re currently doing each month…
They want to see how much cash you have in the bank…
They want to understand what liabilities you have…
and including actuals in your model gives them this wholistic view without having to marry separate exports from different platforms.
3. Your model should be refreshed with actuals to compare against your projections
OK…this is probably the biggest one.
If you are just building a model to showcase your projections, you are missing out on a valuable opportunity to validate your assumptions.
The best way to validate your assumptions is to compare against what actually happened.
That’s why a budget vs actuals report is my favorite report.
When Do You Need to Set Up a Custom Formula for Actuals vs Projections?
OK…so now that we know just how crucial it is to include actuals in your budget…
how will this affect our formulas?
Well…for some formulas, it won’t really matter.
For example, we may have a projected expense for office supplies based off of the trailing 6 month average.
So regardless of where we are with our projections, the formula will always be the same:
=average(prior-6-months)
But things get much trickier with the Balance Sheet…since those values are CUMULATIVE.
Let’s go over an example with Deferred Revenue.
Example with Deferred Revenue
The formula for Deferred Revenue is:
Beginning Balance
+ New Invoices
- Revenue Recognized
= Ending Deferred Revenue Balance
If we utilize this formula across all of our projections…
then our projected balance will eventually be incorrect…
because it doesn’t take into account our actual ending balance as of the last date of actuals.
In order to correctly project out movements in your Deferred Revenue balance, you need to start with the balance as of your last month of actuals.
The same holds true for Accounts Receivable…Inventory..Capex…
Pretty much any Balance Sheet Account.
How Do We Set Up This Dynamic Formula?
First, I have a section in my model that always shows what the latest month of actuals is in my drivers tab.
This is a key cell that is referred to by countless formulas
I take this one step further by converting this cell to a variable, making it easy to reference in my formulas:
In order to utilize in my formulas, I can simply write out a formulas as such:
=If(currentMonth≤LatestMonthofActuals, pull from balance sheet, calculate formula)
Here’s how I use it for Deferred Revenue:
In this case, Drivers!Z77 is my actuals balance for my Deferred Revenue on my balance sheet, which allows me to use this as my opening balance in Jan 23 before I start projecting things.
This topic may be confusing….
but it’s a really important one.
To summarize…
you need to include a flag in your model calculations to pull from your Balance Sheet whenever you are in a month of historicals…
allowing you to have the correct starting point for your Balance Sheet projections.
The same can hold true for other examples, such as setting up waterfalls…
where you only want the values to start generating for your first month of projections.