Top 3 Excel functions you can use to forecast sales

Well, I didn’t need to predict sales but segment sizes for the upcoming 3 periods of the Markstrat simulation game we were playing as part of the Strategic Marketing class I was taking. 

Still, it works pretty much the same way – you use the numerical historic data you have in order to make a prediction about the future…

It goes without saying, of course, that the more historic data you have as a basis, the more accurate your prediction.

And the functions that help you do this are:

1) Forecast

2) Trend

3) Growth

Now, here’s an important BUT – both Forecast  and Trend were designed to work with linear data (i.e. data that increases/decreases at the same rate over time). Any attempt to use them with data, which is not linear, is highly likely to lead to inaccurate predictions.

Therefore, the first thing you need to establish, is whether your data follows a linear growth pattern. The easiest way to do this would be to map your available time series on a scatter plot and see if a linear pattern can be established.

If that’s the case (as it is here), you are safe with your Forecast/Trend prediction.

 

Another peculiarity you should remember is that Trend () is an array function – i.e. you need to encircle it in curly brackets by pressing Ctrl+Shift+Enter

 

Still, despite the differences in syntax, both Forecast and Trend will return the same results

Growth works very much as Forecast and Trend.

The big difference, however, is that it was designed to use with data  that decreases/increases at an exponential rate.  Visually this would look like a highly skewed curve (usually much steeper than the one below)

No purely numerical prediction, of course, can be 100% accurate.  So, do take the results with a pinch of salt.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.