Q: Thanks for the Wonderful Book “Fundamentals of Demand Planning & Forecasting”. Is there any best proven and Industry accepted model/algorithm to calculate sales loss? For example, the actual sale duration for an item is from 7 am to 10.30 pm every day. But if an item is sold out at 6 pm, how do we calculate the number of sales that we lost/missed during the remaining time period from 6 pm to 10.30 pm?

I am working as a Demand Planning Manager in the Food & Beverages and Health & Supplement industries. We do demand planning on a daily basis with 3 days in advance (T-3) for Sale day (T) {Freeze Demand plan on Monday for Thursday’s sales}. We need a minimum of 2 days (T-2 & T-1) for procurement, transport, cooking and other operations. Buffer stock/Safety stock is not possible as shelf life of cooked food is max 24 hours. So on any day, our forecasted demand is equal to sum of sales plus wastage.

Often we face difficulties in calculating the correct sales loss percentage due to item stock outs.

Below is the data (imaginary numbers):

Date Stockout @ Demand Sold Wastage Sales Loss
Day 1 No Stockout 100 95 5 –
Day 2 No Stockout 105 98 7 –
Day 3 No Stockout 105 100 5 –
Day 4 21:42:29 95 95 – ?
Day 5 21:34:40 100 100 – ?
Day 6 20:21:47 98 98 – ?
Day 7 20:09:54 100 100 – ?
Day 8 No Stockout 115 105 10 –

Is there any algorithm/method to calculate sales loss for the days 4 to 7 in order to identify the overall potential sales/day?

MAPE = {(Wastage+Sales Loss)/Actual Sales}*100

Without correct sales loss numbers, it is very difficult to get MAPE correct.

Thanks in advance.

 

A: Your problem is very unique, and thus you won’t find any textbook that discusses it. If I were to face this problem, I would take the average sales on days when there was no stockout. The difference between the average demand and actual sales is your loss. For example on day 4 sales would have been 106 but you sold only 95, so lost sales equals 11 units.

 

I hope this helps.

Happy forecasting.

 

Dr. Chaman Jain

St. John’s University