How can I prevent this behaviour from forecast.ets / forecast.linear?

VirtualJr

New Member
Joined
Jan 19, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hi all, thank you for taking the time to read my problem!

I have a large dataset of weekly demand, multiple years over hundreds of lines. I am trying to use the built-in forecasting tools to get a rough idea of forward demand. For the vast majority of lines, I now have something that looks reasonable using the forecast.linear function. But the limitations of the forecast function become visible in lines with very little or sporadic demand. Specifically, if we went numerous weeks with 0 demand and then a single week with some demand, both the .linear and .ets functions populate a forward forecast with demand, while the much more likely case is that the forward demand also remains 0. I hope the below example comes across (can't install the xl2bb plugin on work laptop), where all the data up to 20/01/23 is historic demand, with the final week showing 100, then followed by the forecast formulas:

07/01/2214/01/2221/01/2228/01/2204/02/2211/02/2218/02/2225/02/2204/03/2211/03/2218/03/2225/03/2201/04/2208/04/2215/04/2222/04/2229/04/2206/05/2213/05/2220/05/2227/05/2203/06/2210/06/2217/06/2224/06/2201/07/2208/07/2215/07/2222/07/2229/07/2205/08/2212/08/2219/08/2226/08/2202/09/2209/09/2216/09/2223/09/2230/09/2207/10/2214/10/2221/10/2228/10/2204/11/2211/11/2218/11/2225/11/2202/12/2209/12/2216/12/2223/12/2230/12/2206/01/2313/01/2320/01/2327/01/2303/02/2310/02/2317/02/2324/02/2303/03/2310/03/2317/03/2324/03/2331/03/23
.ETS00000000000000000000000000000000000000000000000000000010014141415151515151616
.LINEAR0000000000000000000000000000000000000000000000000000001007788888999
Expected0000000000000000000000000000000000000000000000000000001000000000000

I think I do understand why the formulas do this, but it is putting "junk" volume into the forward demand which adds up. I have to find one formula that fits best for most cases, but is there something I can do to the forecast formula, or is there a different formula, to take account of outliers, or to see demand spikes as one-offs instead of indicative of future orders? Thank you!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top