Excel is the Swiss army's knife of any supply chain practitioner. While it is definitely not the most appropriate tool for managing supply chain it is important to be able to convey ideas through it. Lokad tried to build simplified educational version of decisions optimization with probabilistic forecasts in Excel. See the LinkedIn post by the link.
You can ask to receive the file in the comments either here or on LinkedIn under the post.
This is brief summary of my Q&A with one of the users of Excel file. I will provide each question and answer as separate comment.
Q1: How are we creating the probability distributions for the products in this file? If I have historical sales data for a particular SKU, how should I get the probability distribution for that?
A1: We haven't made capabilities to build probabilistic demand forecasts using historical data in this Excel file. This is very hard to implement in Excel due to its limitations. Programming language like Envision is more appropriate for that, but one could also use Python or any other language. How to build Probabilistic forecast with historical data was discussed in this lecture:
This file uses synthetic distributions via built-in Excel functions for normal and negative binomial distributions. Changing parameters you can change the distributions and the ranking of micro level decisions respectively. This is educational tool and the primary goal was to show how having probabilistic demand forecast and economic drivers demand planer can optimize purchasing decisions.
Though, the question of building probabilistic forecast based on historical data still remains valid. It is just not in the scope of this document because it is harder to understand, but also harder to explain and show through Excel.
Q2: After we made and executed purchasing decision there is no more uncertainty, then how would we evaluate whether the predicted probability forecast is the correct one?
A2: Even after we made and executed the decision in reality there is still uncertainty. We don't know when our order will be received because lead time is also probabilistic. Quality of goods can be probabilistic as well (consider fresh food for instance). Also we will not be able to estimate accuracy of our probabilistic demand forecast until the responsibility window (over which we built probabilistic forecast) becomes the past.
With respect to the metrics that are used for probabilistic forecast quality assessment you can check this lecture:
Q3: What goes into engineering economic drivers? How critical it is to get them right from the very beginning or is it more like learning curve?
A3: Decision making using economic drivers in combination with probabilistic forecasts to some extent imitates human level decision making process. Costs and revenues are straightforward, but the stock out cover is not. It is completely context dependent and can be called heuristics. This is where human intelligence lies. Having them properly set enables to automate decision making so that there is no more need to say split 500k SKUs among 50 demand planners to manage 10k SKUs each. All decisions for all SKUs can be generated automatically by the single numerical recipe. Litmus test here is simple - if demand planner sees no insanity in decision recommendations, then it should be concluded that economic drivers are production grade, but for sure can be fine tuned over time.
Q4: Can this approach of decisions prioritization with expected ROI be used for allocating scarce products from DC to stores?
A4: Absolutely.
This file was about purchasing optimization, but the same approach could be applied for DC to stores dispatch problem. ROI ranking perfectly works when there is limited stock at DC and inventory manager wants to make sure that dispatch plan is optimal.
The way it works is the first unit of the first SKU (can be arbitrarily chosen, but can also start from top sellers) and ask in which store (given current stock in each store and probabilistic demand forecast for this SKU for each store) it will have the highest ROI and/or fill rate gain? Then you rinse and repeat for all SKUs at a unit level for all the units (same as with purchasing). This enables to best allocate the existent stock on hand across all the stores in the network. Ranking procedure here is a bit different because at every step we make final decision to which store we send Nth unit of a given SKU. At the end of the process after considering all micro level decisions we chose termination criteria (no more stock at DC or target ROI or target fill rate etc.) and aggregate numbers per store per SKU to get dispatch lists.
Q5: There is a comment in the file recommending using this approach for educational purposes only, because such a forecast will be simplistic and will not account for seasonality or trends etc. How to use this approach beyond educational purposes for real inventory optimization problems?
A5: First, it should be noted that probabilistic forecasts in the file are synthetic probability distributions that are built using Excel functions.
It is possible to build probabilistic forecast that would be based on historical sales data and would take into account all systematic causes of variation like seasonality, trend etc.
We intentionally left it behind the scenes for two reasons:
1) We would have to provide historical sales data that probabilistic forecast would be based on. Here we have a problem that all users are from different industries and got accustomed to see different sales patters. We wanted to avoid "this doesn't look like my sales data" kind of impression.
2) Building production grade probabilistic forecast in Excel is technically speaking a nightmare and an open problem since we've never seen anybody done it so far. Taking into account Excel capability limitations it should be also very slow to rebuild the forecast once you update the data.
At Lokad we use domain specific programming language called Envision where probabilistic forecast can literally be build with several lines of code. Those who are interested about the procedure can play the recording of our past supply chain lecture:
https://tv.lokad.com/journal/2022/3/2/probabilistic-forecasting-for-supply-chain/
This spreadsheet contains a prioritized inventory replenishment logic based on a probabilistic demand forecast. It illustrates how SKUs compete for the same budget when it comes to the improvement of the service levels while keeping the amount of inventory under control. A lot of in-sheet explanations are provided so that the logic can be understood by practitioners.
Excel is the Swiss army's knife of any supply chain practitioner. While it is definitely not the most appropriate tool for managing supply chain it is important to be able to convey ideas through it. Lokad tried to build simplified educational version of decisions optimization with probabilistic forecasts in Excel. See the LinkedIn post by the link.
You can ask to receive the file in the comments either here or on LinkedIn under the post.
This is brief summary of my Q&A with one of the users of Excel file. I will provide each question and answer as separate comment.
Q1: How are we creating the probability distributions for the products in this file? If I have historical sales data for a particular SKU, how should I get the probability distribution for that?
A1: We haven't made capabilities to build probabilistic demand forecasts using historical data in this Excel file. This is very hard to implement in Excel due to its limitations. Programming language like Envision is more appropriate for that, but one could also use Python or any other language. How to build Probabilistic forecast with historical data was discussed in this lecture:
https://tv.lokad.com/journal/2022/3/2/probabilistic-forecasting-for-supply-chain/
This file uses synthetic distributions via built-in Excel functions for normal and negative binomial distributions. Changing parameters you can change the distributions and the ranking of micro level decisions respectively. This is educational tool and the primary goal was to show how having probabilistic demand forecast and economic drivers demand planer can optimize purchasing decisions.
Though, the question of building probabilistic forecast based on historical data still remains valid. It is just not in the scope of this document because it is harder to understand, but also harder to explain and show through Excel.
Q2: After we made and executed purchasing decision there is no more uncertainty, then how would we evaluate whether the predicted probability forecast is the correct one?
A2: Even after we made and executed the decision in reality there is still uncertainty. We don't know when our order will be received because lead time is also probabilistic. Quality of goods can be probabilistic as well (consider fresh food for instance). Also we will not be able to estimate accuracy of our probabilistic demand forecast until the responsibility window (over which we built probabilistic forecast) becomes the past.
With respect to the metrics that are used for probabilistic forecast quality assessment you can check this lecture:
https://tv.lokad.com/journal/2022/3/2/probabilistic-forecasting-for-supply-chain/
Use time stamps to watch the lecture parts where the metrics were discussed.
Q3: What goes into engineering economic drivers? How critical it is to get them right from the very beginning or is it more like learning curve?
A3: Decision making using economic drivers in combination with probabilistic forecasts to some extent imitates human level decision making process. Costs and revenues are straightforward, but the stock out cover is not. It is completely context dependent and can be called heuristics. This is where human intelligence lies. Having them properly set enables to automate decision making so that there is no more need to say split 500k SKUs among 50 demand planners to manage 10k SKUs each. All decisions for all SKUs can be generated automatically by the single numerical recipe. Litmus test here is simple - if demand planner sees no insanity in decision recommendations, then it should be concluded that economic drivers are production grade, but for sure can be fine tuned over time.
Q4: Can this approach of decisions prioritization with expected ROI be used for allocating scarce products from DC to stores?
A4: Absolutely.
This file was about purchasing optimization, but the same approach could be applied for DC to stores dispatch problem. ROI ranking perfectly works when there is limited stock at DC and inventory manager wants to make sure that dispatch plan is optimal.
The way it works is the first unit of the first SKU (can be arbitrarily chosen, but can also start from top sellers) and ask in which store (given current stock in each store and probabilistic demand forecast for this SKU for each store) it will have the highest ROI and/or fill rate gain? Then you rinse and repeat for all SKUs at a unit level for all the units (same as with purchasing). This enables to best allocate the existent stock on hand across all the stores in the network. Ranking procedure here is a bit different because at every step we make final decision to which store we send Nth unit of a given SKU. At the end of the process after considering all micro level decisions we chose termination criteria (no more stock at DC or target ROI or target fill rate etc.) and aggregate numbers per store per SKU to get dispatch lists.
Q5: There is a comment in the file recommending using this approach for educational purposes only, because such a forecast will be simplistic and will not account for seasonality or trends etc. How to use this approach beyond educational purposes for real inventory optimization problems?
A5: First, it should be noted that probabilistic forecasts in the file are synthetic probability distributions that are built using Excel functions.
It is possible to build probabilistic forecast that would be based on historical sales data and would take into account all systematic causes of variation like seasonality, trend etc.
We intentionally left it behind the scenes for two reasons:
1) We would have to provide historical sales data that probabilistic forecast would be based on. Here we have a problem that all users are from different industries and got accustomed to see different sales patters. We wanted to avoid "this doesn't look like my sales data" kind of impression.
2) Building production grade probabilistic forecast in Excel is technically speaking a nightmare and an open problem since we've never seen anybody done it so far. Taking into account Excel capability limitations it should be also very slow to rebuild the forecast once you update the data.
At Lokad we use domain specific programming language called Envision where probabilistic forecast can literally be build with several lines of code. Those who are interested about the procedure can play the recording of our past supply chain lecture:
https://tv.lokad.com/journal/2022/3/2/probabilistic-forecasting-for-supply-chain/
This spreadsheet contains a prioritized inventory replenishment logic based on a probabilistic demand forecast. It illustrates how SKUs compete for the same budget when it comes to the improvement of the service levels while keeping the amount of inventory under control. A lot of in-sheet explanations are provided so that the logic can be understood by practitioners.