How to Forecast the Balance Sheet
Updated: Jun 13, 2024
Forecasting the Balance Sheet is one of the toughest parts to get right in a forecast…and it’s also one of the most valuable.
It may seem daunting, but there’s actually not much to getting this right once you understand a few key aspects of a Balance Sheet.
Grab a cup of coffee, cuz we’re going to have some fun!
What We're Going to Talk About
Why You Need to Forecast the Balance Sheet
90% of the time when I see a forecast from a new client, it’s missing a Balance Sheet.
Somehow…the idea that the Balance Sheet is not as important as the Profit & Loss has spread, and few people understand just how powerful including a Balance Sheet in a forecast can be.
Here are a few reasons why you NEED to include a Balance Sheet in your forecast:
1. Most companies report on the ACCRUAL basis
As a refresher, the Accrual basis of accounting reports income when it is EARNED, and expenses when they are INCURRED.
This differs from the Cash basis, which reports income when cash is received, and expenses when payments are made.
For more details on Cash vs Accrual, see this video here.
2. It’s Really Simple to Forecast Cash When You Have Both a Profit & Loss and a Balance Sheet
Creating a dynamic cash flow statement is really easy - you just need to take the values from your Profit & Loss, and Balance Sheet.
This to me may be the most important reason why you need to include your Balance Sheet…it helps you forecast cash with very minimal effort…and cash is king 👑.
For more information on how to create a cash flow with your P&L and Balance Sheet, see this video here.
3. The Balance Sheet Provides CONTEXT
This is the number 1 reason why I feel the Balance Sheet reigns supreme over all other statements - it’s the only one that gives you CONTEXT.
The context on what was invested into the company, and how the profits compare against those amounts invested (called Return on Equity).
For more information on how to read a Balance Sheet, see this video here.
OK, now that we know why your forecast needs to include a Balance Sheet, let’s jump into how you can forecast your Balance Sheet using the BASE framework.
The BASE Framework
Recall a key difference with the Balance Sheet vs the Profit & Loss…
its values get shown CUMULATIVELY.
The Profit & Loss and Statement of Cash Flows showcase information on an INCREMENTAL basis…each period doesn’t depend on the last and is shown independently of one another.
The Balance Sheet on the other hand showcases its balances each period with the following structure:
Every Balance Sheet account follows this format and is what we are going to use in order to create our Balance Sheet forecast. Here are some of the most common accounts you’ll be forecasting:
Latest Month of Actuals
One last important note on forecasting the Balance Sheet.
Recall from the sections above that each Balance Sheet account starts with analyzing the balance at the beginning of the period…
Well, that’s just a fancy way of saying the balance at the end of the last period.
But it’s not as simple as just referring to the ending balance on your forecast from the last month of actuals…you’ll want to pull in the ACTUAL value from your Balance Sheet as of the latest month of actuals.
See…the best forecasts include both historicals & projections…
this gives you the ability to analyze where the business has BEEN, as well as where the business is GOING. This consolidates your financial reporting and makes it a breeze to identify trends while calling out unusual spikes between your actuals and your projections.
So to forecast a Balance Sheet account’s value in the first period of projections, you’ll need to first collect the ending value as of your latest month of actuals.
I like to set a named range in my forecast for the date in which I have the latest month of actuals set - this makes it really easy to refer to in formulas, which I have everywhere on my Balance Sheet projections:
This means that if you have a forecast that showcases actuals through April 2024, and projections starting in May 2024…your Beginning Balance for all Balance Sheet accounts needs to match the ending Balance as of April 2024.
If you don’t do this, your ending balance will cause a huge swing in comparison to the true starting point, causing your cash flows to incorrectly calculate.
What is a Waterfall Table?
Recall from earlier where we discussed the structure for forecasting each Balance Sheet account using the BASE framework:
What this really means is we need 2 inputs to get to our ending balance… ADDITIONS...and SUBTRACTIONS.
Let’s start with Additions, which is the easiest of two to update.
Additions can represent anything that makes your balance sheet account balance go down.
Here are examples of the way each account we spoke about earlier goes up:
To forecast additions to these accounts, there isn’t so much to it. Or maybe there is…but it’s certainly simpler than forecasting the subtractions (which we’ll talk about soon).
Here are some ways I’ve forecasted these line items in the past:
The general idea with these accounts is that there’s a lot more flexibility with how you forecast the additions to these accounts when compared to forecasting what makes these accounts go down.
See… as we spoke about earlier, the Balance Sheet shows all of its values CUMULATIVELY…
Which means that any account balance that goes up should have a set schedule for how that balance will eventually go DOWN…or as we call it, get amortized.
Introducing the Waterfall Table
OK, so we’re ready to discuss the logic behind forecasting our first Balance Sheet account - Deferred Revenue. Let’s assume we are a SaaS platform that sells quarterly licenses to customers.
The formula for Deferred Revenue is:
Beginning Balance
Additions - Gross Sales
Subtractions - Amounts recognized in Revenue
Ending Balance
To get to gross sales (additions), this can come from anywhere…the key is to have a robust Revenue Forecast that showcases the amount of sales you expect to generate each period.
But to get revenue (subtractions)? That one is a bit trickier.
Here, we’ll need to take the amount that we generated in sales and divide things by 3.
We then need to layer in each cohort’s recognizable revenue amount, and sum up the values from those cohorts like such:
To do this, we’ll need to create what’s known as a Waterfall table.
A waterfall table allows you to take an amount (like sales), and AMORTIZE that amount over a set of periods (like revenue).
Here are the key requirements for a waterfall
How to Set Up a Deferred Revenue Waterfall Table
Let’s revisit each of the steps mentioned above in the context of Deferred Revenue:
1. Show your gross amounts going across horizontally (in this case, sales)
This is going to be the amount that we want to amortize. In this case, it’ll be the gross sales amount. Because we are recording things with accrual accounting, we now need to showcase the actual revenue amount separately.
2. Create a vertical sequence of dates that matches your horizontal set of dates
To amortize each booking, we need to understand what cohort we are in. For example, we know that we did $90,000 in sales in January - that will be the vertical date we show. The horizontal dates will showcase the revenue to recognize in each period from that cohort. There are a few ways to create vertical dates quickly. The quickest way is to use the transpose function, which is a spill function that will convert your horizontal range to a vertical range as such:
3. Pull in Gross Amounts Using a Lookup Function
Now we need to get our gross amounts for each cohort. To do that, you’ll use a lookup function. Here’s an example using XLOOKUP:
4. Populate assumptions for how your gross amount will be recognized
Now we need to set up the rules for how we will amortize revenue for each gross sales amount.
In this case, we’re selling quarterly deals, so we’ll amortize over 3 months.
Add a new section for your assumptions, and enter a 3 as such:
Let’s take things to the next level by changing “3” to “3 Months”. Because are are going to refer to this cell, we can’t actually add text after the 3 or we’ll get a scary #VALUE! error.
Instead, we will keep the cell’s identity as 3, but utilize custom formatting to add the text “ Months” after whatever number we add. This will change the way our cell appears while maintaining its identity as the number 3.
To do this, hit CTRL 1 to open the format cells dialogue box, click Custom, and type in your value as such:
Notice how the value changes, but Months still get added afterward.
5. Populate a Formula to Amortize the Gross Amount
OK, now comes the formula to amortize revenue. And the logic is pretty simple…
We have 2 conditions:
If our horizontal month is greater than or equal to our vertical month…
And if we are less than X amount of periods into the future it’s less than dividing our gross amount by the (where X is our amortization period)…
THEN divide our gross sales by our amortization period. Otherwise, return 0.
For this, we can use the AND function, and the EOMONTH function, as such:
Now all that’s left is to sum up the values from each cohort:
When you’re done, your Deferred Revenue balance should be summarized as such:
Understand Accrual Accounting: Know the difference between accrual and cash accounting.
Include Both Statements: Use both the P&L and Balance Sheet for a complete financial picture.
Context Matters: Use the Balance Sheet to provide context for your financial data.
Use Latest Actuals: Always start with the most recent actual data for accurate forecasts.
The BASE Framework: how you forecast all items on the Balance Sheet, which leaves additions & subtractions as the only items to update.
Deferred Revenue: Additions are taken by adding the gross sales amount, and subtractions are taken by amortizing gross sales into revenue.
To Amortize Revenue: create a waterfall table that showcases dates going across vertically, and pull in each gross sale amount. Add an assumption for your amortization period and use custom formatting to add a relevant text to your value. Populate your formula to amortize revenue based off of the amortization assumption
By following these steps, you can create a reliable and insightful Balance Sheet forecast.
Josh (Your CFO Guy)
Thanks for reading my article . My goal is to help you grow in your career with proper Finance and Accounting.
If we're not already connected on Social media, let's get connected and send me a message to say hi. I promise to respond