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:
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!
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/22 | 14/01/22 | 21/01/22 | 28/01/22 | 04/02/22 | 11/02/22 | 18/02/22 | 25/02/22 | 04/03/22 | 11/03/22 | 18/03/22 | 25/03/22 | 01/04/22 | 08/04/22 | 15/04/22 | 22/04/22 | 29/04/22 | 06/05/22 | 13/05/22 | 20/05/22 | 27/05/22 | 03/06/22 | 10/06/22 | 17/06/22 | 24/06/22 | 01/07/22 | 08/07/22 | 15/07/22 | 22/07/22 | 29/07/22 | 05/08/22 | 12/08/22 | 19/08/22 | 26/08/22 | 02/09/22 | 09/09/22 | 16/09/22 | 23/09/22 | 30/09/22 | 07/10/22 | 14/10/22 | 21/10/22 | 28/10/22 | 04/11/22 | 11/11/22 | 18/11/22 | 25/11/22 | 02/12/22 | 09/12/22 | 16/12/22 | 23/12/22 | 30/12/22 | 06/01/23 | 13/01/23 | 20/01/23 | 27/01/23 | 03/02/23 | 10/02/23 | 17/02/23 | 24/02/23 | 03/03/23 | 10/03/23 | 17/03/23 | 24/03/23 | 31/03/23 | |
.ETS | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 100 | 14 | 14 | 14 | 15 | 15 | 15 | 15 | 15 | 16 | 16 |
.LINEAR | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 100 | 7 | 7 | 8 | 8 | 8 | 8 | 8 | 9 | 9 | 9 |
Expected | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 100 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
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!