Oct 12 2021 03:53 AM
Hi, I am trying to do a sales FC based on the historical daily sales. I have used the FORECAST.EST formula, however, the results give negatives or very low FC in some instances (weekends), how would I adjust/remove negatives so that it is more accurate? Or should I use another method?
The workbook attached contains no sensitive information. Sheets: Data dump has actual sales, FC data converted data to a usable table, FC has the FORECAST.EST and then 2 test sheets that I used to test a previous months' accuracy.
Thank you for your help :)
Oct 12 2021 06:46 AM - edited Oct 12 2021 07:07 AM
@JJX_88 wrote: ``the results give negatives or very low FC in some instances (weekends), how would I adjust/remove negatives so that it is more accurate?``
=MAX(0, FORECAST.ETS(A114,$B$2:$B$109,$A$2:$A$109,1,1)) can be used to clip the results at zero.
But I don't think that really solves your problem, which is "very low (estimated) FC in some instances".
You could replace zero in MAX(0,...) with some other large value. But that begs the question: how to decide what that lower limit should be?
I would use the InterQuartile Range (IQR) method to determine a "lower fence". See the wikipage for details.
Caveat: I would use a multiplier of 1.7, 2 or 2.5, not 1.5 as per the wikipage. In a normal distribution (not saying your data is), 1.7, 2 and 2.5 correspond to 3sd, 3.4sd and 4sd, whereas 1.5 corresponds to 2.7sd -- much too limited, IMHO.
But clipping data is always risky, especially in your case where the lower fence is likely to be so-very-far above the actual forecasted value. Experiment.
Second thoughts.... OTOH, I'm surprised at the sudden drops in the estimated data. I might study the behavior of the data and the ETS algorithm to try to understand what is causing that. Perhaps "correcting" the data instead of the forecast is the better way to go, again using the IQR method to determine lower (and upper?) "fences". Food for thought.
-----
@JJX_88 wrote: ``Or should I use another method?``
That's a completely different (and should be separate) question. It would require a much deeper dive into your data for a "proper" answer. Too lazy!
Also, forecasting is as much "art" as it is science. The "correctness" is in the eye of the beholder.
Off-hand, your data seems to fit the cyclical ("seasonality") pattern that ETS is designed for. OTOH, I have no experience with it.
Oct 12 2021 07:04 AM