Forcasting or trend function in Excel

[dixon6312]

Hi to all who will be able to assist

I have a spreadsheet (attached) with sales per month per year (2017-2020) across (X), and months (Y) Jan-Dec going down. The monthly sales for each year is cumulative. What would be the best way to forecast 2021 sales?

Kind regards

Dixon

[MrShorty]

How much of your question is specific to Excel -- how to program your already chosen regression/forecast algorithm into Excel -- and how much is a more generic data analysis question -- how to choose a suitable regression/forecast algorithm? We are pretty good Excel/spreadsheet programmers, but not many of us have the expertise in data analysis to choose a regression/forecasting algorithm for you.

To illustrate the programming steps, here's how I would do a basic "linear" year over year forecast -- where I am assuming that a straight line can accurately represent the year over year behavior for each month:

1) I will use the TREND() function, though one could also use the FORECAST() function.
2) Enter desired regression function (or sequence of functions) into F5 (and any helper cells that are needed). In this case, the formula is a simple =TREND(B5:E5,$B$4:$E$4,$F$4). Be sure to delete the ?? from F4 so that Excel can see this as a number.

I doubt that such a linear trend will provide a very good forecast, but it should illustrate the basic programming steps for a problem like this.

[dixon6312]

Thanks for reply. Will test it. Would the weighted average method provide a better trend or forecast ? Regards

[MrShorty]

That's a data analysis question that I don't think I can answer. Weighted average might be better or it might not. It depends on exactly how you intend to implement a weighted average and how you intend to measure how good your forecast is or how much uncertainty is in the forecast.

My untrained glance at the data does not see any real quantifiable trends. The only real trend I see is that Jan is the lowest number for each year (but it varies dramatically year over year) and Dec is the highest for each year (though it, too, varies dramatically year over year). Unless there is another trend in these data that the trained eye can see that my untrained eye cannot see, I don't think I can comment on whether one method is better than another.

(My untrained eye wants to say that it might be just as effective -- and maybe more fun -- to set up a dart board and throw darts at it to come up with the forecast. As long as Jan 2021 is the smallest number and the values increase during the year to the largest number in December).