Industry considered — Food Supply Chain (Meat and Seafood)
Key Performance Indicators (KPI) addressed — Wastage and Availability
( Checklist —Fewer expired products, Higher sales, lower markdowns, Higher inventory turnover )
Model Developed with — Microsoft Excel
Functions predominantly used — Pivot table, Vlookup, Nested If function, Relative Reference cells
Benefit: With this Excel model created, the user will be able to optimize the inventory by releasing the frozen capital internally and replenish the business locations using their own overstocks, instead of making extra replenishment orders from the suppliers. In other words, the business can minimize lost sales and increase inventory productivity by moving products from slower hubs to locations where the same product has been selling at a faster pace.
“I don’t look at the numbers from a finance perspective. Instead, I look at what the customer is trying to tell us” -Peter Georgacopoulos
Introduction: If you find yourself having to purge your refrigerator’s crisper bin every few weeks, imagine what goes on at a grocery store or at a replenishment center in case of an e-business. According to an article published by the Indian news website, The Wire, in Jan 2021, an estimated 161,000 tonnes of India’s avoidable food loss and waste (meat and fish) occur during the retail phase of the supply chain. This waste has usually been sent to anaerobic digestion facilities or industrial composting or disposed of in landfills. As we all know, letting food go to waste like this, is a frivolous use of natural resources that drives up costs, inflates food prices, and weakens the food supply chain. The plethora of reasons behind this act of food wastage is as follows.
- Lack of protocols or networks to enable food rescue and redistribution
- Inaccurate forecasting and poor inventory management leading to oversupply
- Withdrawal of products approaching or exceeding date labels
- Inadequate storage on-site
- Rejection of produce that does not meet visual quality standards
- Goods damaged upon receipt
As waste production surveys have identified that a large proportion of these arisings originate from meat and poultry, in this blog, the study has been done, focusing on a specific problem faced by an e-commerce business selling freshly packed meat products (expiry = 2 days), addressing the first reason listed above — redistribution of products across the hubs.
Consider a food supply chain operated by an e-commerce business, predominantly supplying meat and seafood, follows a push system for distribution of its products across hubs in different locations. The primary products delivered and the hub locations are as below.
*Hub location is mentioned as store location here and in all the figures.
The area considered for the study is Chennai, located in the southern part of India. There are 13 hubs in total which receive meat products on a daily basis. From these hubs, the products will be delivered directly to customers through delivery partners. The hubs are geographically distributed across the city and considering the administrative and logistical constraints, the hubs are divided into ‘k’ clusters (k=4 here). This is done using the k-means cluster in python. The main objective of the k-Means algorithm is to minimize the sum of distances between the points and their respective cluster centroid.
This means, excess products will be distributed to the hubs within the specific cluster only, and thus, the inter-cluster transfer is not allowed. Also, one of the hubs in every cluster is selected as the designated primary hub. This primary hub will have extra storage facilities which aid inter-hub transfers. For instance, cluster 1 consists of three hubs located at Korattur (KOR), Mogappair (MOG), and Athipet (ATH), and the primary hub is ATH.
Hubs in this cluster often encounter the situation in which they are out of stock of a particular item (or “SKU”) while other hubs have surplus stock for the same item/SKU. Such situations can lead to either loss of a sale or markdown or dumping of the products. Now with this idea of inter-hub transfer, the products will be moved to the primary hub and then to the other hubs in the cluster.
Moreover, the business considered in this study delivers fresh meat products which have a shelf life of 2 days. Thus, inventory transfer between hubs is necessary to avoid possible food wastage and increase sales in turn profitability.
For ease of understanding, Inter hub transfer at Cluster 1 (KOR-ATH-MOG) is taken and discussed further.
The inter-hub transfer model generates the number of units that can be transferred for every single SKU at each location given different input data as follows. Here, ‘x’ refers to the time at which the data is extracted and run on the transfer day.
- Inventory data @T day x pm
- Sales data till T day x pm
- Expected Dump @T day
- Pre-order Sales data @T+1 day
- Sales Forecast @T+1 day
- Sale Factor
- Data Mapping
1. Inventory data @T day x pm:
This table contains the data of the list of SKUs and the available quantities for sale at each hub. The columns include the key, Category, SKU ID, SKU name, city, Store ID (hub ID), Store location (hub location), and Stock units. A glimpse of the data is shown below in fig.5. (Store indicates hubs here)
In Fig.6, the pivot table function is used to get the summarized data for each key
2. Sales data till T day x pm:
As with the previous table, this table contains the same columns with just a change in the stock units column. Here, ‘Stock units’ is replaced by the ‘Units sold’. A glimpse of the data is shown in fig.7.
3. Expected Dump @T day:
This table contains the number of products expected to be thrown off due to the expiration. The data is reported at the SKU level at each hub and is based on the historical sales to date at different hubs.
4. Pre-order Sales data@T+1 day:
Occasionally, the hubs will receive the order for the list of products beforehand for the next business day. This table shown in Fig.10 contains the data related to the same.
5. Sales Forecast @T+1 day:
Based on the buying patterns and historical sales data, unit sales for the future would be predicted and used as input here for this model. This varies for each day of the week. For instance, meat sales will be more on weekends compared to weekdays. Thus, based on the seasonality and trend, forecast values will differ. A glimpse of the data is shown in fig.12.
6. Sale Factor:
The table on the left contains the Point of Sale data collected and combined from all the hubs in the city to date at specific time intervals. Using the Pivot table function in excel, the percentage of sales on Sunday is calculated as shown in Fig.14. It shows that 24.74% of the Chicken would have been sold by 9 am, 77.99% by 1 pm across all hubs. This data is also used for arriving at the transfer data.
7. Data Mapping:
The priority of the hub is one important factor in Inter hub transfer. If each hub demands the same amount of product from the primary hub, the priority determines the hub which needs to be served first with the available products, which might be lesser than what actually demanded. Hence, this table is used for nothing but to extract information regarding each hub like its Index, its corresponding primary replenishment center/primary hub, the priority of the hub, etc as shown in fig.15.
Excel model logic:
Before jumping into the explanation of the model in detail, there is something that needs to be known called the Carry forward percentage (CFP). As it is known that the products will expire in 2 days, firstly, if products are remaining unsold at the end of day 1, they will be saved for the next day’s sale. Secondly, at the end of the next day, the leftovers from the saved lot would be dumped if not sold. Thus, the CFP as in cell B1 indicates the % of products moved to the next day as fixed by the business, which is 25% here. Also, the model is run every day, so logic =TEXT(TODAY()+1,”dddd”) is included on top as in cell C1. Here, the model is run on Saturday for execution on the next business day morning which is ‘Sunday’ here.
A screenshot of the entire model is displayed here in fig.16.
Column A — Key:
A key is created as a combination of SKU ID, Hub, and Priority. This unlocks access to other tables. The logic used here is =CONCAT(H3,I3,C3).
Column B — Category:
This column represents the category that the SKU falls into. It can be Chicken, lamb or Seafood.
Column C — SKU ID:
This unique ID indicates the SKU item and it differs with every item. For Chicken Drumstick, it's ‘DRU-CH-05’ where DRU indicates the actual item, CH indicates the Chennai city, and 05 shows that it is the fifth item code in the category chicken as set by the business.
Column D — SKU name:
SKU name is the actual name of the item. Different SKUs available are shown in fig.2.
Column E — City:
This column refers to the area in which the business is happening. It’s Chennai in this case and it will vary with respect to the operating location. As and when business is expanding to different cities, new records can be added to the same model.
Column F — Store ID:
This column is an acronym of the hub location present in column G. The logic used here =UPPER(LEFT(G3,3)).
Column G — Store location:
This signifies the location of the hub in the corresponding city.
Column H — Priority:
Priority signifies the precedence of hubs in serving the inventory involved in this transfer. The value ranges from A to Z and beyond (AA, AB..) where hubs falling under A will be given more priority. The values are fetched from the ‘Data mapping’ table using the Vlookup function as shown below in fig.17.
Column I — Hub:
Hub is the store that acts as the ‘Replenishment center’ for the given cluster. For cluster 1, it is ATH and is fetched from the Mapping table using the logic =VLOOKUP(G4,’Data Mapping’!B:F,4,0).
Column J — Sale till T-Day 1pm:
The sales recorded across the hubs till 1 pm on the business day are fetched from the respective sheet using the indexes SKU ID and Store ID (hub ID) via the Vlookup function. A screenshot of the same is displayed here in fig. 18.
Column K — Inventory @ T-Day 1 pm:
It is nothing but a snapshot of the number of units that are currently present in hand across different hubs. The data is brought in from the corresponding sheet using the Vlookup function as shown below in fig.19.
Column L — Sale factor:
It is a factor calculated based on the historic sales recorded under each category, per se chicken, lamb, or seafood. The data is fetched from the Sale factor sheet using the logic =IFERROR(VLOOKUP(Solution!B5,’Sale Factor’!G:L,3,0),0).
Column M — Expected dump:
It indicates the number of units expected to be thrown off due to the product expiration. As shown in fig.20, this value is fetched from the respective sheet using excel functions and used here for further calculations.
Column N — Closing inventory:
Based on the sales data, inventory data, and the sale factor, closing inventory is calculated. If 66.25% of the sales leads 86 units, 100% of it at the end of the day would be around 130 units. Thus, the remaining units that would be sold for the rest of the day are 44 units. As this being fulfilled from the current inventory (140), the closing inventory expected would be 96 units (140–96). The same is shown in fig.21 and fig.22.
Column O — Carry forward:
This value is simply the number of units expected to be in the closing inventory lot minus the expected dump in the given day. The same logic is shown in fig. 23 and fig.24.
Column P — Surplus units:
As per the business rule, the number of units under the ‘carry forward’ category should be 25% of the forecast value. The excess units are considered as Surplus units. Here, for the hub ‘MOG’, the forecast for the next day is 31, and a quarter of that value is approximately 8. Thus, the ‘carry forward’ (94 units) minus 25% of the forecast (0.25*31) gives 86 units and is depicted in fig.25 and 26.
Column Q — Final surplus units:
This is just the difference between the Surplus units calculated earlier and the Pre-order (or) Expected Sales data @T+1 day. The logic being =ROUND(MAX(0,P5-R5),0).
Column R— Expected sale @T+1 day:
As said earlier, the hubs will receive the order for the list of products beforehand for the next business day through the mobile app or website. Hence, this value will be fetched from the respective table for further inter-transfer unit calculation. The excel logic used is =IFERROR(VLOOKUP(C5&F5,’Expected sale @ T+1 day’!J:K,2,0),0).
As the number of excesses at each hub that can be transferred is known now, the next step would be the decision of where and how much to move. It has two business logic, Priority A and Priority B.
Priority A — Hubs that didn’t meet the next day’s pre-order units.
Priority B — Hubs that didn’t carry forward 25% of the units to the next day.
The model will first try to fulfill the units in Priority A followed by priority B. If more units are present in the hub even after fulfilling both the priorities, the remaining units will be split and delivered across all the hubs based on the ‘forecast ratio’ (T+1) at each hub.
Column S — Slack priority A:
As shown in fig.28 and fig.29, this indicates the number of units short and ought to be delivered to each hub when considered under priority A.
Column U — Units in primary hub after Priority A transfer:
This indicates the number of units present in the hub once after if priority A is satisfied. It is simply the total sum of ‘Final surplus units’ minus the ‘slack priority A’ units across all hubs. Here 127–11= 116 units will be present at the primary hub/replenishment center at ATH after replenishing the 11 units to the hub at KOR.
Column T — Slack Priority B:
As shown in fig.31 and fig.32, this indicates the number of units short and ought to be delivered to each hub when considered under priority B.
Column V — Units in primary hub after Priority B transfer:
This indicates the number of units present in the hub once after if priority B is satisfied. It’s just the ‘Units in the hub after Priority A transfer’ minus the ‘slack priority B’ units across all hubs. Here 116 –10= 106 units will be present at the hub/replenishment center at ATH after replenishing the 10 units (priority B) to the hub at KOR.
Column W — Priority A transfer:
This column indicates the number of units that will be actually moved under priority A based on the excesses present as ‘final surplus units’. The logic is built as follows.
- As said earlier, this column will be populated based on the hub’s priority value (A followed by B and C).
- If the priority of the hub is A and the total sum of units in ‘Slack Priority A’ (say 10) is less than the ‘final surplus units’ (say 15), it (10<15 = 10) will be populated in this column. But if the value is more (say, 20), only the item that could be fulfilled using surplus units will be used (here 15).
- If the hub priority is B, the same rule is followed only after fulfilling the units demanded at hub priority A in the previous step. The same applies to hub priority C also.
In our case here in fig.34, the surplus available is 127 and the total priority combining all hubs is 11units to KOR.
Column X— Priority B transfer:
As with the priority A transfer, the same logic goes here except that the total units present for this transfer are whatever left in ‘Column V — Units in the hub after priority A transfer’. Units are calculated based on the hub priority order A followed by B and C.
In our case here in fig.35, the units available after priority A transfer is 116 and the total priority combining all hubs is 10 units to KOR. The end value is 106 and this matches with the value in Column V also.
Column Y— Forecast:
This is just the data fetched from the forecast sheet using the logic =MAX(IFERROR(VLOOKUP(C4&F4&$C$1,’Sales Forecast’!A:I,9,0),1),1).
Column Z — Forecast Ratio:
As said earlier, when more units are left even after both the calculated priority transfers, it will split across all the hubs based on their forecast ratio. This is the forecast value of each hub divided by the total value as shown in fig.36.
Column AA— Surplus transfer:
The end units present in the primary hub after Priority B transfer is 106. The forecast ratio at each hub multiplied with the end units gives the surplus transfer units at each hub as depicted in fig.37.
Column AB — Store to hub: (other hubs to primary hub)
All the above overwhelming calculations using excel functions are to arrive at the values that are about to be discussed here. Also, the main idea behind all these calculations till now is to divide the final surplus units of 127 among each hub based on business logic.
- Hub ATH — The final surplus units present were 57 (column Q). The units needed at this hub are Pr.A+Pr.B+Sur.transfer = 0+0+37 = 37 units. Thus the excess of 20 units is moved to the primary hub storage at the location (internal movement).
- Hub KOR — The units needed at this hub are Pr.A+Pr.B+Sur.transfer =11+10+38 = 59 units. As the final surplus units here are 0, no units are moved from here to the primary hub storage at ATH.
- Hub MOG — The units needed at this hub are Pr.A+Pr.B+Sur.transfer =0+0+31 = 31 units. As the final surplus units present here were 70 (column Q), the excess of 39 units is moved to the primary hub storage at the ATH. This is done through delivery partners.
Column AC — From hub to store: (primary hub to other hubs)
The total number of units shifted to the hub is 59. As the requirement at the second hub, KOR is 59, it is directly fulfilled from hub ATH through a delivery partner. The logic is depicted in fig.39 below. Hub indicates the primary hub which has extra storage and store indicates the normal hub.
To recapitulate, the inter-hub inventory transfer model developed has been explained so far, for example, a cluster consisting of 3 hubs and for a single product ‘Chicken Drumstick’. This resulted in a notable shift of 59 units across the hub network to a possible demand point. Once when the complete data is loaded, the result will significantly scale up and list the transfer for 16 products across 13 hubs. At this point, the usage of this model will be significant as it can save more products from just being dumped because of expiration. Also, the current business area is only a single city -Chennai. When the business is opened up in new locations, additional hubs and cities can also be added to the model, as the model is created to work flexibly.
Thus this model recommends the optimal inter-hub transfer schedule to move products from hubs where it is collecting dust on the shelf to hubs where the same products are in high demand PROACTIVELY.
In this way, this model helps to
- Increase comparative hub sales by a notable percent.
- Reduce inventory costs.
- Avoid unnecessary markdowns, and gives a second chance to sell at full price.
- Reduce possible wastage.
“It’s not the organizations that are competing. It’s the Supply Chains that are competing.”
I heartily thank you for taking your best interest in reading this blog. Please feel free to relay your valuable comments, suggestions, or corrections. Let’s learn together!