top of page

Latest Month of Actuals


As a business owner, you need to check your business finances every month. This will help you see how your business is doing, and what you can do to make it better.


But how do you check your business finances effectively?


The answer is simple: you need to use the latest month of actuals.


This is a report that shows how much money you made and spent in the last month, compared to your plan and the month before. It also shows your important financial numbers, such as gross margin, net margin, and cash flow.


The latest month of actuals is a powerful tool that can help you:

  • See how close you are to your financial goals

  • See how profitable and efficient your business is

  • See how much cash you have and need

  • Change your plan and forecast if needed

  • Make smart decisions for your business



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.


Actuals model - latest month of actuals

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.


budget vs actuals - latest month of actuals


 

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)


formulas - latest month of actuals

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

Deferred revenue - latest month of actuals

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.

latest month of actuals

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:

latest month of actuals

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:


how I use the formula for Deferred Revenue - latest month of actuals

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.


After all, the final step is to share your latest month of actuals report with your people, such as your partners, investors, lenders, employees, or customers. You should:


  • Sum up your main findings and results

  • Show your successes and challenges

  • Tell your plans and actions for the future

  • Give clear and useful data and charts

  • Use easy and short language

  • Ask for feedback and ideas




52 views

Related Posts

See All

Comments


bottom of page