Hello, I am attempting to convert a demand forecast code at the weekly level to month. What are the changes I need to do? I am guessing I need to convert the SLT from weekly to monthly (e.g. the (1/7) to (1/30)), and I need to build a ItemsMonth table by cross (Sku, month). I am sure I am missing others. Please advise.
firstDate = min(Sales.Date)
lastDate = date(2024,01,01)
todayForecast = monday(max(Sales.Date))+7 ///date(2023,07,17)
today = max(Sales.Date)
maxSLT = max(support.max(SLT.SLT))
maxSLTDate = todayForecast + 14 + maxSLT
keep span date = [firstDate .. lastDate]
Items.TotalSales = sum(Sales.DeliveryQty)
Items.Rank = rank() scan Items.TotalSales
table ItemsWeek = cross(Items, Week)
Sales.Monday = monday(Sales.Date)
Sales.MonthA = month(Sales.Date)
ItemsWeek.Monday = monday(ItemsWeek.Week)
//Raw Demand Qty
ItemsWeek.DemandQty = sum(Sales.DeliveryQty) by [Sales.Sku,Sales.Monday] at [Items.Sku,ItemsWeek.Monday]///[@Paul Jan] -> joint with Sku vector
//Smoothed Demand Qty
ItemsWeek.DemandQtyShiftMinus1 = ItemsWeek.DemandQty[-1]
ItemsWeek.DemandQtyShiftPlus1 = ItemsWeek.DemandQty[1]
ItemsWeek.DemandQtyShiftMinus2 = ItemsWeek.DemandQty[-2]
ItemsWeek.DemandQtyShiftPlus2 = ItemsWeek.DemandQty[2]
ItemsWeek.DemandQtyShiftMinus3 = ItemsWeek.DemandQty[-3]
ItemsWeek.DemandQtyShiftPlus3 = ItemsWeek.DemandQty[3]
ItemsWeek.SmoothedDemandQty = if ItemsWeek.Monday == lastDate then
0.7 * ItemsWeek.DemandQty +\
0.2 * ItemsWeek.DemandQtyShiftMinus1 +\
0.1 * ItemsWeek.DemandQtyShiftMinus2
else if ItemsWeek.Monday == lastDate - 7 then
0.4 * ItemsWeek.DemandQty +\
0.2 * ItemsWeek.DemandQtyShiftMinus1 +\
0.3 * ItemsWeek.DemandQtyShiftPlus1 +\
0.1 * ItemsWeek.DemandQtyShiftMinus2
else
0.4 * ItemsWeek.DemandQty +\
0.15 * ItemsWeek.DemandQtyShiftMinus1 +\
0.15 * ItemsWeek.DemandQtyShiftPlus1 +\
0.1 * ItemsWeek.DemandQtyShiftMinus2 +\
0.1 * ItemsWeek.DemandQtyShiftPlus2 +\
0.05 * ItemsWeek.DemandQtyShiftMinus3 +\
0.05 * ItemsWeek.DemandQtyShiftPlus3
///Item life
ItemsWeek.ItemLife = 1
ItemsWeek.CumSumMinusOneExt = 0
where ItemsWeek.Monday >= firstDate
ItemsWeek.CumSumMinusOneExt = (sum(ItemsWeek.ItemLife) by ItemsWeek.Sku scan ItemsWeek.Week) - 1
ItemsWeek.CumSumMinusOneExtMonth = ceiling(ItemsWeek.CumSumMinusOneExt / 8)
ItemsWeek.WeekNum = rank() by Items.Sku scan -monday(Week)
I can also use some guidance on how to change from a weekly to a daily implementation from a coding perspective.
Hello,
You'll find below an example of code that can help you generate a daily and a monthly forecast from an existing weekly forecast. I hope this will help.
The methodology I followed fits what was described in previous comments:
1. Compute the weight of each day of the week in the whole horizon considers for each group of products.
2. Multiply this weight to an already computed weekly baseline to get a daily baseline.
3. Aggregate at month level.
This is only example to be adapted to your specific use case, here are the assumptions I made:
1. The weight might differ on the seasonality group: if not the granularity can be changed or the weight can simply be computed for the whole dataset. For categories with very few sales, this logic might overfit
2. The horizon contains only full weeks or is sufficiently big for considering that having 1 extra occurrence of a given week day is negligible.
3. The weight of the days is constant over the whole horizon. In particular we completely neglect here the impact of events like Black Friday.
Instead of going from weekly to monthly, I would, on the contrary, suggest to go from weekly to daily, and then from daily to monthly. Keep the weekly base structure, and to introduce day-of-week multiplier. This gives you a model at the daily level. Turn this daily model into a monthly forecasting model. Indeed, having 4 or 5 weekends has a significant impact on any given month, and usually to most effective path to capture this pattern consists of operating from the daily level.
Hope it helps,
Thank you.
Would you mind elaborating a bit more on the day-of-week multiplier concept you mentioned above?