Implement Forecast at Monthly level

chat new comments ask submit login

2 points by s40racer 6 months | flag | 4 comments

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)

s40racer 6 months | flag

I can also use some guidance on how to change from a weekly to a daily implementation from a coding perspective.

bperraudin 6 months | flag

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.


///Create necessary tables
table WeekDays = extend.range(7)
WeekDays.DayNum = WeekDays.N - 1 //DayNum between 0 and 6
table GroupsWeekDays = cross(Groups,WeekDays) //Groups being an existing table with 1 line per seasonality group

Sales.DayNum = Sales.Date - monday(Sales.Date)
GroupsWeekDays.DemandQty = sum(Sales.DeliveryQty) by [Items.SeasonalityGroup,Sales.DayNum] at [Groups.SeasonalityGroup,WeekDays.DayNum]
GroupsWeekDays.WeightDay = GroupsWeekDays.DemandQty /. sum(GroupsWeekDays.DemandQty) by GroupsWeekDays.SeasonalityGroup

///Compute daily forecast
table ItemsDay = cross(Items,Day)
Day.DayNum = Day.Date - monday(Day.Date)
ItemsDay.Baseline = ItemsWeek.Baseline * single(GroupsWeekDays.WeightDay) by [Groups.SeasonalityGroup,WeekDays.DayNum] at [Items.SeasonalityGroup,Day.DayNum]
ItemsDay.DemandQty = sum(Sales.DeliveryQty)

///Compute monthly forecast
table ItemsMonth = cross(Items,Month)
ItemsMonth.DemandQty = sum(Sales.DeliveryQty)
ItemsMonth.Baseline = sum(ItemsDay.Baseline) //mind partial months when analyzing the results

vermorel 6 months | flag

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,

s40racer 6 months | flag

Thank you.
Would you mind elaborating a bit more on the day-of-week multiplier concept you mentioned above?