Forecasting sales removing negatives from forecast

[JJX_88]

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 :)

[#2]

@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.

[#3]

Thank you Joe, I will have a look at IQR. In the meantime I used moving average on a 30 day cycle and then applied the EST on those values that seemed to sort the problem. Also with that my FC tests only have 200K variance vs actuals which is good enough :) in the eye of the beholder! I agree. Thanks again!