Identifying Trends in Excel Sales Forecasting

By Conrad Carlberg

A baseline with a trend generally heads up or down. Trends can make sales forecasting in Excel trickier, as well as the steps you can take to make the forecast more accurate. For the time being, though, understanding what trends are about is a good idea.

3201_seasonal-baseline
This baseline has trend. It wanders some, but you can see that the direction is generally up.

In sales, trends tend to follow changes in customers’ behavior. For good or ill, trends are an economic fact. In looking for trends, bear in mind the following:

  • People stop using certain products or services. There are lots of ways that society encourages people to purchase some products — an upward trend — and discourages them from purchasing other products — a downward trend. Over time, upward trends often turn into downward trends due to changing market conditions. For example, someone who sells tobacco, whether retail or wholesale, probably wants to see an upward trend — but as consumers have more information about the dangers of smoking, fewer of them will buy the product. (Some will always buy, but not in the numbers seen decades ago.)
  • People want the newer, faster versions. Wi-Fi? Cable? Your very own fiber loop? Doesn’t matter. People are impatient and they want to get stuff to and from the Internet faster than they used to. They go from 1,200 bps (yes, people used to send and receive at 1,200 bps, and slower yet) to 56,000 to whatever multimegabit rate your phone or cable company offers. The number of people subscribing to higher-speed communications increases, as a trend, over time. The same is true of many other technological improvements.
  • People spend more dollars, but they may not spend more constant dollars. Here’s the deal: It costs more to buy a car in 2016 than it did in 1996. Blame it on inflation. Or blame it on the bossa nova — the fact is that things cost more than they used to. There are ways to deal with this, such as converting prices to constant dollars, but unless you do so you’re going to be looking at a trend, and a meaningless one at that.
  • People spend more for the things they want. For example, people generally pay whatever gasoline costs, even if the cost rises at a high rate. There are lots of reasons for the increasing cost of gasoline, ranging from South American politics to thirsty SUVs to exploding economies in the Far East. Shrinking or static supply, blended with increased demand, creates upward revenue trends; as we’ve seen in 2015 and 2016, expanding supply, and static or decreased demand, creates downward trends.

One of the problems with a trend is that there’s a mathematical relationship between one figure and the next in the baseline. The two main approaches to forecasting — smoothing and regression — deal with those relationships differently. You tend not to worry about the relationship between one figure and the next if you’re using the regression method of sales forecasting. If you’re using smoothing, you sometimes want to start by removing the trend from the baseline, and reintroducing it later.