Because some field service industries -- especially HVAC -- tend to be seasonal in nature (or at least have more business in summer and winter), there’s sometimes a financial confusion that takes place. Managers assume growth (perhaps from an increased effort in marketing a field service business) when, in fact, it wasn’t growth -- it was a seasonal abnormality that led to higher-than-expected revenues.
Managers at all types of seasonal businesses have been trying to solve this problem for years. Can it be solved? Yes. (Within reason.) Here’s the approach -- with some photos to boot!
Step 1: Using data
This always seems to be Step 1 in any 2017 business process, right? Here’s what you need to do here. If you don’t have this data specifically on hand, well, that’s even more reason to have a integrated field service management (FSM) software program. What you’ll need is:
- Annual sales data for the past 3-5+ years (5+ years is preferable)
- Monthly sales data for the same period you choose to do annually
How you get this will be based on how you track sales and customer information. If you use FSM software, it’s all right at your fingertips and can be pulled up in under two minutes. From there, just copy and paste it into excel. Get your data to look something like this:
It’s not important to break month and year apart. That’s up to you. We like to break it out to filter by year and create different graphs when necessary. It’s always handy to have each individual piece of data in a column of its own.
Right now, if you were to graph the sales data it would look like this:
You can see sales go up and down based on the month. Obviously, this makes it hard to know if you’re actually doing well one month or if it’s just a month you always have high sales in.
Step 2: Calculate the centered moving average
If you’re working within Excel or another spreadsheet program, you’ll want to go six months into your sales data and type this:
=average(average(select first year of monthly sales),average(select first year of monthly sales starting with second month))
Drag that formula all the way down to six months before the end of the data.
Here’s what it should look like:
What’s happening here? What is a centered moving average? See, many data series -- especially field service sales -- have an upward trend. You need to find a centered moving average to understand where your data really falls; otherwise November/December totals will almost always be higher than January/February totals just because of routine growth. Why does it stop 6 months from the present date? Because it’s the middle average. Or, the number it is producing is always in the middle of the data.
Step 3: Division
Here you’re going to find the ratio of your monthly sales compared to the average sales of the moving average of the year. Take monthly sales and divide that by the centered moving average calculated above. See here:
Step 4: More Division
Now that we have the ratio of what each month was in comparison to the year, we need to average each individual month to the same month of the other years. Average together each Ratio for “July” with the other Ratios of “July”. Drag the formula through one year, but make sure it isn’t trying to average data you don’t have at the bottom of the spreadsheet. In our example, we had to remove the last value for “June” because we didn’t have that data. Once these numbers are calculated though, you no longer need the formula. Copy just the numbers all the way down your spreadsheet (all the way to the current month). These numbers will keep on repeating because they represent an average of the amount of your sales in a year that come from a specific month.
Step 5: A Little More Division
The last step is simple. Take your monthly sales and divide that by the Average Monthly Ratio.
That results in data without seasonality. Since we calculated the average percentage of annual sales based on month, we can use these numbers to figure out our seasonality all the way to present date. Keep in mind, the more data you have the more accurate this will be.
Lay all this data out in graphs and see what other trends arise. See the graph below compared to before:
What does this graph mean? The sales line represents what sales actually were for that month, while the deseasonalized line shows what your sales were without the “seasonal” trends. What the data really shows us are the longer term trends. In our example, the company appears to have had a rough couple years, but then has been growing pretty steadily since then. Without the Deseasonalized data, you may look at the data from March 2015 to December 2015 and think the company was going downhill again, but if you look at it with the seasonal trends considered, the company stays relatively consistent in sales throughout that period. Of course, if we had even more data we may be able to reveal a cyclic trend.
We made the above relatively simple, but if you haven’t worked with centered moving average or Excel before, the first time may be a little bit challenging. No worries. You will get it.
The overall idea here is to figure out whether your company is growing, or whether seasonal effects are the reason for revenue shifts. Just because sales in December are up from November doesn’t necessarily mean you’re having a good month. In fact, you may be down in comparison to your average sales for December. In short? You want good, clean data as opposed to “data that proves your point.” This isn’t just for fun (probably wasn’t fun at all, to be honest), but it can give you a real look at whether your company is growing, or whether your company is shrinking. Managing cash flow for field service can be difficult if you don't consider the seasonality ups and downs of your business.
If you’re looking at your numbers and seeing the kind of seasonality that our example portrayed, you’re probably thinking about how despite your sales going up and down, your employee costs stay the same. What do you do with your techs when they have seasonal downtime? We wrote an eBook that covers that topic, and other topics regarding productivity, here. Of course, within the eBook we discuss our field service software solutions. Check it out, and let us know if you have any questions below in the comments (or send us an e-mail here).