I have a few doubts regarding the concept of "Lags" in forecasting.
Let T0, T1, T2... be the time periods, with T0 being the current time period. "Row 2" in the attached Excel gives the forecast generated in time period T0 for the next month onwards, T1, T2...
After time period T0 gets over, and we reach time period T1, the forecast is again generated for time periods T2, T3, and so on. "Row 3" in Excel gives us this.
The "Actual" sales observed in each time period are given by "Row 8", highlighted in Green.
"Lag 1" signifies the forecast for the next immediate Time period. So forecast generated in "T0" for "T1"; forecast generated in "T1" for "T2" and so on. The same is highlighted in a shade of yellow and the successive snapshots are in "Row 10".
"Lag 2" signifies the forecast for 2 Time periods from now. So forecast generated in "T0" for "T2"... and the successive snapshots are in "Row 11" highlighted in light blue.
Likewise for "Lag 3" and "Lag 4".
Let us consider a company, and let us assume "Lag 4" is used for the procurement of Raw Materials.
"Lag 3" is used for Manufacturing.
"Lag 2" is used for dispatching to the DCs.
"Lag 1" is used for replenishing the stores.
So if we are in "T0", Lag 4 forecast = 420 units, and we will procure raw material worth this.
After 1 time period elapses, we are in "T1" and we would manufacture for "410" forecast for the time period "T4" (Lag3). (What would happen to the 10 units worth of Raw Material that will not be manufactured?)
When we come to T2, we will have to dispatch 500 (Lag2), so if we only made 410 in the previous step, how do we get the extra 90 units?
When we come to T3, we have to send 430 (Lag1) to stores. If we got 500 from the previous step what happens to the 70 units? If we only got 410 (as Lag3 was 410 and we assume we manufacture and send the same to the DCs), we still fall short by 20 units.
My question is at every step the forecast for a particular time period ("T4") changes whenever we move from "T0" to "T1", "T1" to "T2". So where do we get the additional units from in each stage if forecast at say Lag2 (500)> Lag3 (410) or conversely what happens to excess material if "Lag 4(420) > Lag3 (410)"
My two cents: in a classical setting, manufacturing would have a frozen horizon period and use the net demand + stock policy to define its procurement and production at T0. Additionally you would have sourced more raw material than your short term demand (again safety stock in its classical sense + lot quantity from the tier1 supplier).
In each cycle the base forecast is converted in net demand for the next node (your excess material / existing stock would be subtracted from the forecast)
Second, put aside Excel entirely for now. It is hindering, not helping, your journey toward a proper understanding. You must be able to reason about your supply chain problem / challenge without Excel; Excel is a technicality.
Third, read your own question aloud. If you struggle to read your own prose, then probably, it needs to be rewritten. Too frequently, I realize, upon reading my own draft that the answer was in front of me once the question is properly (re)phrased.
Back to your question / statement, it seems you are confusing / conflating two distinct concepts:
The forecasting horizon
The lead times (production / dispatch / replenishment)
Then, we have also the lag which is a mathematical concept akin to time-series translation.
Any forecasting process is horizon-dependent, and no matter how you approach the accuracy, the accuracy will also be horizon dependency. The duration of between the time of cut-off and the time of the forecast is frequently referred to as the lag because in order to backtest, you will adding "lag" to your time-series.
Any supply chain decision takes time to come to pass, i.e. there is a lead time involved. Again, in order to factor those delays, it is possible to add "lag" to your time-series to reflect the various delays.
Lagging (aka time-series shift, time-series translation) is just a technicality to factor any kind of delay.
I have a few doubts regarding the concept of "Lags" in forecasting.
Let T0, T1, T2... be the time periods, with T0 being the current time period. "Row 2" in the attached Excel gives the forecast generated in time period T0 for the next month onwards, T1, T2...
After time period T0 gets over, and we reach time period T1, the forecast is again generated for time periods T2, T3, and so on. "Row 3" in Excel gives us this.
The "Actual" sales observed in each time period are given by "Row 8", highlighted in Green.
"Lag 1" signifies the forecast for the next immediate Time period. So forecast generated in "T0" for "T1"; forecast generated in "T1" for "T2" and so on. The same is highlighted in a shade of yellow and the successive snapshots are in "Row 10".
"Lag 2" signifies the forecast for 2 Time periods from now. So forecast generated in "T0" for "T2"... and the successive snapshots are in "Row 11" highlighted in light blue.
Likewise for "Lag 3" and "Lag 4".
Let us consider a company, and let us assume "Lag 4" is used for the procurement of Raw Materials.
"Lag 3" is used for Manufacturing.
"Lag 2" is used for dispatching to the DCs.
"Lag 1" is used for replenishing the stores.
So if we are in "T0", Lag 4 forecast = 420 units, and we will procure raw material worth this.
After 1 time period elapses, we are in "T1" and we would manufacture for "410" forecast for the time period "T4" (Lag3). (What would happen to the 10 units worth of Raw Material that will not be manufactured?)
When we come to T2, we will have to dispatch 500 (Lag2), so if we only made 410 in the previous step, how do we get the extra 90 units?
When we come to T3, we have to send 430 (Lag1) to stores. If we got 500 from the previous step what happens to the 70 units? If we only got 410 (as Lag3 was 410 and we assume we manufacture and send the same to the DCs), we still fall short by 20 units.
My question is at every step the forecast for a particular time period ("T4") changes whenever we move from "T0" to "T1", "T1" to "T2". So where do we get the additional units from in each stage if forecast at say Lag2 (500)> Lag3 (410) or conversely what happens to excess material if "Lag 4(420) > Lag3 (410)"
For each lag we have,
Error = (Forecast-Actuals)
Accuracy = {1-[Abs(Error)/Actuals]}
The same has been computed in the Excel file. Please let me know if my understanding is correct.
My two cents: in a classical setting, manufacturing would have a frozen horizon period and use the net demand + stock policy to define its procurement and production at T0. Additionally you would have sourced more raw material than your short term demand (again safety stock in its classical sense + lot quantity from the tier1 supplier).
In each cycle the base forecast is converted in net demand for the next node (your excess material / existing stock would be subtracted from the forecast)
I have a few tangential remarks, but I firmly believe this is where you should start.
First, what is the problem that you are trying to solve? Here, I see you struggling with the concept of "lag", but what you are trying to achieve in unclear. See also https://www.lokad.com/blog/2019/6/3/fall-in-love-with-the-problem-not-the-solution/
Second, put aside Excel entirely for now. It is hindering, not helping, your journey toward a proper understanding. You must be able to reason about your supply chain problem / challenge without Excel; Excel is a technicality.
Third, read your own question aloud. If you struggle to read your own prose, then probably, it needs to be rewritten. Too frequently, I realize, upon reading my own draft that the answer was in front of me once the question is properly (re)phrased.
Back to your question / statement, it seems you are confusing / conflating two distinct concepts:
Then, we have also the lag which is a mathematical concept akin to time-series translation.
Any forecasting process is horizon-dependent, and no matter how you approach the accuracy, the accuracy will also be horizon dependency. The duration of between the time of cut-off and the time of the forecast is frequently referred to as the lag because in order to backtest, you will adding "lag" to your time-series.
Any supply chain decision takes time to come to pass, i.e. there is a lead time involved. Again, in order to factor those delays, it is possible to add "lag" to your time-series to reflect the various delays.
Lagging (aka time-series shift, time-series translation) is just a technicality to factor any kind of delay.
Hope it helps.
Link for the Excel file
Copy and paste the entire link in a new window (do not click directly on the link as it does not seem to redirect correctly)
https://1drv.ms/x/s!AmdAMe2CGp70kgXFYSnCr0-SHoEV?e=prEmbD