Simple steps for effectively forecasting sales
Forecasting what your fourth quarter might look like takes a little work and a lot of initiative, says Scott Wheeler, owner of Automotive Consultants Group. But he says it’s well worth the effort. Sales forecasting helps ensure you know how much money the shop is bringing in on a monthly and annual basis and can be used to develop financial plans, benchmarks and goals. “In the automotive industry, typically you can ‘make hay’ during the summer months,” says Wheeler. “But what about handling the cyclical ups and downs we all experience? You need to forecast and plan ahead.” Wheeler frequently works with shop owners to calculate their sales forecast and discusses the numbers needed to create a regression equation to project numbers into the future.
There’s a simple tool used in forecasting and financial analysis. The math behind it is called a “linear regression tool.” While it is recommended to use Microsoft Excel to perform this, it’s important that you understand the mechanics of it first.
At the center of the regression is the relationship of two variables, called the dependent and interdependent variables. For instance, suppose you want to forecast your repair shop’s sales variables, and you’ve obviously concluded that as car count increases, sales goes up. But what about the efforts of your marketing and advertising? Your sales forecasting would be the dependent variable, as it is “dependent” on your advertising. And the advertising would be the interdependent variable. As the advertising increases, the car count should go up. One is dependent on the other.
The relationship between two variables is called covariance. If one variable increases and the other variable tends to also increase, then the covariance would tend to be positive. If one variable goes up and the other goes down, then the covariance would tend to be negative. Simply put, correlation coefficient is an attempt to standardize the relationship between the two variables. If you increase your marketing/advertising by 1 percent, will you always get a 1 percent plus increase in sales? In essence, this is a “cause-and-effect” relationship.
From here, Microsoft Excel will allow you to develop a regression equation. Using Excel, click on the “Tools” drop down menu, select “Data Analysis,” and then “Regression.” You will get a pop-up box, which you will need to populate. At minimum, you should be tracking the following numbers that will be needed for the sales forecast: • Car count • Daily gross sales • Month-to-date (MTD) gross sales • MTD gross sales to target • Daily labor revenues with gross profit margin percentage • Daily parts revenues with gross profit margin percentage • Other profit centers and their gross profit margin percentages • Marketing and advertising dollars spent, and the return on those dollars
As previously mentioned, you need to establish a baseline of performance. If last year’s profit and loss (P&L) statement was a relatively standard year, then this tool can help you to establish your baseline. However, the caveat is that if a business remains flat, then it is, in fact, dying. The reason for this is simple—increased costs of doing business and inflation. Therefore, your forecast should include at minimum a 10 percent increase in gross sales. In theory, this should keep you ahead of the curve. However, it’s more complex than that. Forecasting sales will allow you to more closely execute and control your budget.
Let’s look at an example. You want to start by entering your annual targets (Example 1). Again, I get all of these numbers from the profit and loss statement and then add 10 percent.
Then, you always want to measure what the variance is compared to the target. I suggest measuring all of the above on a daily basis. It just takes 3–5 minutes at the end of the day to pull these reports. In Example 2, you can see how the above targets interact with the data on a daily basis. You can see when you’re up and down, especially in comparison to the annual target.
I also recommend looking at this on a quarterly basis. That will give you a higher level view of where you are overall.
By now you should understand the importance of sales forecasting. It’s a look into the future. You should be experienced at goal setting and attainment. You need to look back at your previous performance and come to some solid conclusions. You need to be realistic about how much work your shop can handle. You need to understand how to use Excel. You need to understand why you have ups and downs in your business. You need to be properly staffed, and at the right times during the year. You need to budget and plan ahead for major purchases.