Tuesday, March 29, 2016

Using Excel 2016 Forecast Tool To Predict Future Sales Revenue

At the rate Microsoft keeps updating Excel with new features, the older versions (Excel 2007 and Excel 2010 especially) will soon be so far behind the new versions will look like entirely new products and not just newer versions of same product.

Today I will be showing you how you can use the forecast tool in Excel 2016.

I have prepared a sample sales data.

It is the daily sales record of a grocery store for this year.  The store has been hit by the current economic downturn and sales are not as good as they used to be. The owner is very worried and wants to see what the future will be like if the trend continues.

This is what the forecast tool in Excel 2016 is built for. And here's how to use it.

Select the historic data, go to Data menu and select "Forecast Sheet".

The forecast dialog box comes up. Set the date you want to forecast to end (Forecast End).

If you are familiar with time series, it is a triple exponential smoothing capable time series tool. You can expand the options and set things as you want.

And for the setting above (default setting), here is the result.

Now the store owner can see that things will get worse if he doesn't do something to halt the downward sales trend.