In this series we are examining how to measure and analyze the revenue stream, if you didn’t catch the first or second part I encourage you to start there first. In this section we are going to convert our revenue of our fictitious company into a three-month running average and show you a few things to look for and once again a few pitfalls to avoid.
A three-month running average is a different kind of analysis tool than what we’ve seen so far. Straight revenue charts and revenue percentage charts do okay when looking at one point in time against a previous, and gives a very basic feel of how things are doing. Unfortunately, it doesn’t give a true feel if you’re making progress, year over year (YoY) and a sense of traction in your efforts. Enter the three-month running average with a year over year percentage, which combines a bunch of stuff to create one of the oddest looking charts of the series.
Here you can see an overview of the data and the accompanying chart. First of all, you can see the revenue is now a column, which is fairly easy to understand, and then there is a bunch of white space, which really throws a lot of folks. Don’t worry, it’s right. Let me show you how it works.
The three-month running average is an average of the previous 3 months, which is done in Excel like this:
The next cell down does the next, previous three months:
This just continues down the column producing the running average. In stocks it’s called the fast moving average and with its partner slow moving average they are used in trend analysis denoting when to buy and sell stocks. Since you cannot just buy and sell your business at a whim, we just use the running average by itself. You might ask why we pick three months instead of two or four months. There is no technical reason, but we like it because it aligns with the quarters and it’s easy to look down the column and see the average revenue of any given quarter. So in our example, we could easily say we averaged $117.2k per month in the first quarter of Year 1.
Then we take our average from one year ago, subtract it from our current average, and do a percentage. We will include our sheets when we are done with the series, but it you are just following along make sure you put the () around the subtraction (C17-C5) and make sure you divide by the previous year, not the other way around.
Then that too is copied down the column. Excel makes this very easy, and once you set it up you only need to update it monthly to keep up with the trend.
After a little dragging and whip up a quick chart here’s what we getThen you get a really weird looking chart that shows a bunch of…something?
Let’s start with the blue line. It’s revenue and we scale that against the numbers on the left, so it easy to see in any given month how you did; it’s money you made. The red line is the three-month running average, which just smooths out your revenue over a three-month period. It will always look like it’s lagging the blue line and is also scaled on the left. The green line compares the two and difference between the two is represented by the percentages on the right scale.
To read this, look at the first green point (Y2 Mar which is about 5%) so we can say, that the last three months did 5% better than the same three-month period last year. The next point reads we did 2% worse than the same three-month period last year.
This chart is measuring your growth, and compares it against the previous year. I assume you want your company to grow, so anytime the green line drops below zero, you are losing growth (shrinking) as measured against the previous year. That doesn’t mean you will not make your projections, it just gives you a visual indicator of what’s going on. A big point to understand with loss is understanding that a loss can be more detrimental than a corresponding gain. If you have a loss of 2% and a gain of 2%, it usually doesn’t net out to zero. Mathematically it does, but in real life, you and your team will remember the lost 2% much more and for much longer than the gain and it will have a much bigger impact on moral. That’s why I say, you don’t have to gain, just don’t give up what you got.
The next point I want you to see is “bounce.” If you look at the middle of Year 3, you will see astronomical growth as compared to the previous year, but then it crashes straight to a negative growth, and then shoots right back up and then back down. If you see this you will need to take a look at what’s causing it. It either was really good one month last year, was really bad one month this year, or both. In our case Dec-Y2 was very strong, and Dec-Y3 was just average, so it makes this year look very bad. It could be an opportunity to figure out what you did last year to make it as good.
Finally, the last thing I look at is smoothness. Of course our example looks pretty erratic, but if our growth target is 10% and we are all over the place, hitting that 10% is a lot harder than you think. This falls back to cash flows, and budgeting, that little rollercoaster we’re on is whipping the other departments all over the place. This is a time when you can throttle back some of the seasonal sales and adjust them to the other seasons to smooth things out a little. I have seen the “make hay when you can” sales/marketing strategy enough times to know that it’s a tough road to travel and leads to a lot of heartache. So I like to see a plus/minus of only a few points—say a 7%–13% range on a 10% growth goal—anything greater than that is showing a lack of understanding in the sales/marketing department and they are just trying anything to get ahead. Positive consistency is the key to long term success.
Using year over year running averages takes some time to get used to, like anything, the more complex the tool, the longer it takes to get a handle on it. In this case the results are worth the work because of the richness of information it shows. I don’t know of any other single chart that is as useful in telling a story as this one when it pertains to growth and consistency. As many of these charts it has more of a qualitative value, because although you can look at the numbers all day long, those three lines just tell the story so much better.
Next in our series of measure and analyze the revenue stream we will be doing a little statistics by introducing Z-Score and show how that tells a qualitative and a quantitative story about your revenue and the benefits and pitfalls of that method.