VIDEO: Discover New Things in Your Financial Numbers

What if I could show you a way to instantly recognize trends in your business, trends spanning anywhere from years to months to days? If you are like most business owners, this newfound ability would forever change the way you manage the financial reporting coming out of your company. It will reduce the time it takes to understand major financial and operational indicators and allow you to communicate that information to key managers and employees without having to explain the first debit or credit.

The tool I cover in this video is one introduced to me by Greg Crabtree in his excellent book, Simple Numbers, Big Profits. It is called rolling 12 or trailing 12 analysis.

When presenting financial data to your team a picture is worth a thousand words or more. We rely heavily on charts and graphs with our clients due to their ability to present massive amounts of information quickly and succinctly. If we imagine a typical line graph of revenue what we are accustomed to seeing is each month’s revenue represented by a single data point on the map. While useful it does not allow us to account for the seasonality of a business when reviewing the current month’s numbers.

This type of chart also fails to lend any insight into the annual numbers or current run rate of the business. Without doing a lot of manual math there is no way to understand whether we are ahead or behind where we were last year.

Rolling 12-month analysis changes this by using each data point on the map to represent 12-months of trailing data. For example, the data point for 6/30/18 would include all revenues from 7/1/17 to 6/30/18. The preceding data point at 5/31/18 would include all revenues from 6/1/17 to 5/31/18. This accomplishes several things.

First, we are able to organize revenues (or any other metric) across years, even decades. This allows us to obtain a more comprehensive picture of where businesses are in relation to where they have been in the past. More than once we have worked with clients to identify historical periods of high growth using this technique. Then we’ve gone on to deconstruct the causes for past growth and replicate those conditions in future periods. Similar lessons can be learned by identifying past periods of decline or stagnation.

Second, we can immediately compare the most recent period to the same period in the prior year. To understand this, consider the previous two data sets we used as an example. When we move from the 5/31/18 data point to the 6/30/18 data point all we have done is replace last June’s data with this June’s data. So, if the line goes up and to the right, we know that this June was better than last June. If the values are included on the chart simple subtraction tells us how much better the most recent period was than the year prior.

Third, we can intuitively examine the existing trend and develop a forecast into the future. It is not difficult to do some back of the envelope regression analysis to draw a line of closest fit for recent periods and extend it out into the future. You can see people do this within seconds of grasping the 12-month trailing concept on the chart in front of them. 

Fourth, by plotting multiple series on the same graph we can identify correlations that may be causal or predictive. This is especially true where the correlation may lag for several periods. For instance, it is easy for anyone to identify and link together an increase in advertising spending with an increase 30-90 days later in revenues.

Finally, using 12-month trailing data to review income statement metrics against monthly balance sheet accounts allows the interdependence of those values to be understood. We see this when increases in 12-month trailing sales drives an increase in accounts receivable.

If you use QuickBooks this video shows how you can quickly generate 12-month trailing spreadsheets for use with charts in excel. It takes a little work, but the insights and understanding you will be able to communicate are well worth the effort.