In this podcast episode, we discuss how to create a sales forecast. Key points are noted below.

The Need for Sales Forecast Detail

The easy approach that lots of companies use is to take last year’s sales, adjust it by a few percent – usually upwards – and call that the forecast for the next year. That’s not a good idea, because there’s a lot going on underneath that grand total sales figure from last year. You really need to get down into the details.

The Three Parts of a Sales Forecast

At a gross level, the sales forecast can be broken down into three parts. The first one is the basic sales forecast, which is those sales that predictably keep coming back. Sales may not always be from the same old customers, but you can usually rely on a basic sales level from the same old product and service sales, every month. So that’s your base layer. We’ll get back to that in a minute.

The second part is the promotional sales forecast. In this part, sales are directly tied to marketing activities. So if the marketing department runs a coupon promotion that reliably generates an extra $100,000 of sales, then those sales are part of the promotional sales forecast. For this part of the forecast, you need to work with the marketing department to figure out the timing of their promotions, and then put the historical results of those promotions into the forecast on the dates set by marketing.

The third part is new product sales. The forecasting here can be tough, since there’s no sales history to base it on. Usually, the marketing folks derive estimated sales from how similar products have sold in the past. That’s pretty much all you’ve got to work with. A concern here is whether a new product will cannibalize sales from some other existing products, in which case total sales will be less than you expected.

Drivers of the Basic Forecast

Those are the three parts of a sales forecast. Let’s get back to the basic forecast. Even though I’m calling it basic, there’s still a lot going on here. Let’s say that the sales manager is projecting a five percent increase in the basic forecast from last year, because that’s what the actual increase has been for the last couple of years. Is that a viable number? Once again, you have to get down into the weeds to figure it out.

First, let’s say that sales are being driven by a large sales force, and they’re organized into sales regions. Is it reasonable that the sales coming out of each sales region will keep going up? At some point, they won’t. Sales regions do not generate more sales forever. So, take a look at how sales are growing – or not – by salesperson, by region, to figure out when sales are cresting, and maybe when they’re starting to go down. This takes an in-depth analysis to figure out what sales are going to do.

Here’s another example. What if sales are based on contracts that have a specific end date? In this case, you need to identify the termination date of each one of these contracts, and then aggregate them all to figure out when the related sales will end. Then separately compile the sales department’s best estimates of which new contracts it thinks it will get, including the estimated contract start dates and the associated amounts, and layer these figures on top of the information for the existing contracts. In this case in particular, if you just roll forward last year’s sales numbers into next year, you’re probably in for a big surprise. Contract-based sales forecasting requires a lot of detailed analysis.

Now, let’s say that there is no sales force. Instead, you’re operating a bunch of retail stores. In this case, sales can be estimated based on the historical sales per square foot of store space. Of course, it’s not quite this easy. The sales per square foot figure tends to go up over time, so the sales associated with new stores are usually lower than the sales for existing stores. And there might be a declining trend of sales per square foot, too, which should be rolled forward into next year’s forecast. So if you want an accurate sales forecast for retail stores, be prepared to analyze sales at the level of the individual store.

Here’s another one – the Internet store. Again, there aren’t any salespeople, so you’re forecasting based on historical sales levels. In this case, you need to dig in the historical data a bit more. This involves seeing if there’s a long-run trend in the data, as well as any seasonality effect, so that sales are consistently changing during certain months of the year. And also look for the recency effect, which is recent changes in the data. This is not something you can just put on a plot in Excel and visualize. A better approach is to use the exponential smoothing function in Excel. What it does is assign exponentially decreasing weights to older data when it creates a forecast. In other words, more recent historical data are weighted more heavily in deriving a forecast.

This concept of data recency is an important one, especially when you’re trying to figure out when sales are cresting. Cresting is a big deal, because this is when product sales transition from a steep uphill climb to gradually flattening out. Management needs to get this right, so that it doesn’t keep investing in infrastructure to support sales that have stopped increasing.

A good way to watch for the cresting effect is to flag a decline in the rate of sales growth. As soon as this happens, start forecasting much more frequently, because the rate of growth could start dropping off really fast. Also, it can make sense to watch the sales coming from the company’s original core customers. When their purchases start to drop off, it’s a good bet that they’re the leading indicator for a general drop off in sales for customers who came in later. This means that the original markets show cresting sales first, as they become saturated. You can then extrapolate this information to other markets, to predict when sales will crest in each successive market.

Sales Forecast Constraints

Let’s switch over to constraints. When setting up a sales forecast, you can’t just dither around with sales figures. You also have to understand whether the business can even generate the sales from an operational perspective. For example, let’s say that a business sells a very technical product, which requires an extremely well-trained salesperson to make the sale. In this case, the bottleneck in the system is being able to find and train enough qualified salespeople. The market could be enormous, but if you don’t have the sales staff, then you can throw out any massive sales increases.

Or, what if the company is a manufacturer, and its bottleneck operation on the production floor is completely maxed out already? If so, not matter how strong demand may be, the sales forecast is really driven by how fast the capacity of that bottleneck operation can be increased.

Here’s another one. A new retail chain has a great new concept store, and customers love it. The problem is that the company only has the capability to sign leases, train up retail staff, and open new retail stores at the rate of one a month. In this case, the sales forecast is driven by the rate of store openings, not by customer demand.

To summarize, you can’t just sit in your cubicle and dream up a sales forecast by extrapolating out last year’s sales numbers. This requires a major amount of detailed analysis.