Inventory Modeling and Analysis Made Easy with Supplychainpy¶
The following is taken from the jupyter notebook title ‘0.0.4-Inventory-Modeling-v1’ found here . For a more interactive experience please retrieve this notebook and run with jupyter.
This workbook assumes some familiarity and proficiency in programming with Python. Understanding list comprehensions, conditional logic, loops and functions are a basic prequisite for continuing with this workbook.
Typically, inventory analysis using Excel requires several formulas, manual processes, possibly some pivot tables and in some cases VBA to achieve. Using the supplychainpy library can reduce the time taken and effort made for the same analysis.
from supplychainpy.model_inventory import analyse
from decimal import Decimal
from supplychainpy.sample_data.config import ABS_FILE_PATH
The first two imports are manditory, the second import is for using the
sample data in the supplychainpy
library. When working with a
different file, supply the file path to the file_path
parameter. The
data supplied for analysis can be from a csv
or a database ETL
process.
The sample data is a csv
formatted file:
with open(ABS_FILE_PATH['COMPLETE_CSV_SM'],'r') as raw_data:
for line in raw_data:
print(line)
Sku,jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec,unit cost,lead-time,retail_price,quantity_on_hand,backlog
KR202-209,1509,1855,2665,1841,1231,2598,1988,1988,2927,2707,731,2598,1001,2,5000,1003,10
KR202-210,1006,206,2588,670,2768,2809,1475,1537,919,2525,440,2691,394,2,1300,3224,10
KR202-211,1840,2284,850,983,2737,1264,2002,1980,235,1489,218,525,434,4,1200,390,10
KR202-212,104,2262,350,528,2570,1216,1101,2755,2856,2381,1867,2743,474,3,10,390,10
KR202-213,489,954,1112,199,919,330,561,2372,921,1587,1532,1512,514,1,2000,2095,10
KR202-214,2416,2010,2527,1409,1059,890,2837,276,987,2228,1095,1396,554,2,1800,55,10
KR202-215,403,1737,753,1982,2775,380,1561,1230,1262,2249,824,743,594,1,2500,4308,10
KR202-216,2908,929,684,2618,1477,1508,765,43,2550,2157,937,1201,634,3,3033,34,10
KR202-217,2799,2197,1647,2263,224,2987,2366,588,1140,869,1707,1180,674,3,5433,390,10
KR202-218,1333,402,804,318,1408,830,1028,534,1871,2730,2022,94,714,2,3034,3535,10
KR202-219,813,969,745,1001,2732,1987,717,599,2722,171,639,2108,754,3,5000,334,10
KR202-220,1481,905,1067,2513,861,1670,650,2630,1245,997,1936,2780,794,3,7500,3434,10
KR202-221,771,2941,1360,2714,1801,1744,1428,1660,436,578,1956,1101,834,2,4938,4433,10
KR202-222,2349,4,345,524,340,2698,2137,1164,498,1583,1241,2965,874,2,4922,3435,10
KR202-223,2045,2055,552,81,2780,176,2316,1475,2566,1678,1553,2745,914,1,4894,34533,10
KR202-224,2482,1887,1911,1446,2939,1241,1281,692,119,627,1941,1383,954,2,2942,33,10
KR202-225,2744,2770,2697,1726,1776,2264,332,2420,2722,1161,1986,2587,994,6,8999,2000,10
KR202-226,2509,914,903,877,1859,2263,383,593,236,189,920,1686,1034,3,4342,4344,10
KR202-227,368,2502,2955,2994,1270,2884,2208,699,854,877,2320,160,1074,3,4920,489,10
KR202-228,1468,1109,2464,2799,948,589,2858,1140,501,2691,93,1060,1114,2,15000,9439,10
KR202-229,2114,198,1479,1249,1475,744,407,2280,226,2285,796,1948,1154,2,13000,8939,10
KR202-230,1023,1150,1672,2026,1590,441,2484,2300,2928,1082,2064,2412,1194,2,10000,349,10
KR202-231,482,546,299,2304,2953,1029,1863,2809,454,927,2488,2341,1234,4,9999,3434,10
KR202-232,614,2138,962,2017,2398,2963,2189,1804,414,2016,1350,2464,1274,2,7500,234,10
KR202-233,2395,2521,2157,728,1028,43,138,826,570,2825,181,787,1314,4,6000,349,10
KR202-234,1336,1478,865,533,1562,422,2287,1302,1230,1059,1153,399,1354,2,20000,324,10
KR202-235,2565,2762,2721,1431,845,2163,2413,2227,1753,740,1139,2300,1394,3,59500,850,10
KR202-236,1912,1726,1569,316,71,2082,108,174,1974,609,2896,566,1434,3,2300,4930,10
KR202-237,2153,1112,16,130,590,2619,2576,2390,2567,1531,842,242,1474,2,4500,9483,10
KR202-238,1417,2044,1981,1936,2377,780,1544,1521,51,1056,1876,1356,1514,3,8000,839,10
KR202-239,2717,2186,2300,677,2157,2328,1917,2519,561,281,1162,1146,1554,2,39000,433,10
KR202-240,1015,741,2754,2925,2302,695,2869,440,406,1083,2334,1015,1594,3,3943,390,10
KR202-241,3050,1507,3637,1112,1963,1675,898,1986,2262,3895,1229,2904,769,5,8007,2125,10
KR202-242,1875,2368,830,823,868,1409,1845,3095,3247,1894,2558,3048,1819,1,13225,1253,10
KR202-243,1717,593,3006,2935,3139,2753,3247,3845,1720,3413,3399,2799,1120,3,14682,1128,10
KR202-244,2383,2046,2487,3827,1674,3118,2849,2233,3888,2566,2216,3817,1067,5,11997,1191,10
KR202-245,1115,2694,3038,3366,1058,2724,2863,1930,1787,838,3087,1565,1623,2,12876,611,10
KR202-246,3108,1197,2472,1264,3179,3638,1268,1581,3456,1630,1788,2288,608,2,6548,2192,10
KR202-247,3439,1854,652,1827,1645,2257,2733,1337,2034,2106,877,2409,1578,2,10463,1017,10
It is probable that getting the data to this format will require
‘extracting’ from a database and ‘transforming’ data before ‘loading’
into the analyse
function. This can be achieved with an orm like
slqalchemy or using the driver for the database in question.
Supplychainpy works with Pandas so performing the transformations using
Pandas may be an idea. The DataFrame
or file passed as an argument
must be identical to the format above (future versions of supplychainpy
will be more lenient and attempt to identify if a minimum requirement
has been met).
The ETL process is not covered in this workbook but on the ‘roadmap’ for
supplychainpy
is the automation of this process.
So now that we have the data in the correct format we can proceed with the anlysis.
#%%timeit
analysed_inventory_profile= analyse(file_path=ABS_FILE_PATH['COMPLETE_CSV_SM'],
z_value=Decimal(1.28),
reorder_cost=Decimal(400),
file_type='csv')
The variable analysed_inventory_profile
now contains a collection
(list) of UncertainDemand
objects (one per SKU). Each object
contains the analysis for each SKU. The analysis include the following:
- safety stock
- total_orders
- standard_deviation
- quantity_on_hand’: ‘1003
- economic_order_variable_cost
- sku
- economic_order_quantity
- unit_cost
- demand_variability
- average_orders
- excess_stock
- currency
- ABC_XYZ_Classification
- shortages
- reorder_level
- revenue
- reorder_quantity
- safety_stock
- orders
The listed summary items can be retrieved by calling the method
orders_summary()
on each object. The quickest way to do this is with
a list comprehension.
analysis_summary = [demand.orders_summary() for demand in analysed_inventory_profile]
For the intrepid reader who did not heed the warning about the prerequisites and is now scratching their head wondering “what manner of black magic is this,” here is a quick overview on list comprehensions. In short, the above code is similar to the code below:
analysis_summary =[]
for demand in analysed_inventory_profile:
analysis_summary.append(demand.orders_summary())
The former is much more readable and in truth quite addictive (hence the warning, the love for list comprehensions runs deep).
Exploring the results¶
To make sense of the results we can filter our analysis using standard
python scripting techniques. For example to retrieve the whole
summary
for the SKU KR202-209
we can do something like this:
%%timeit
sku_summary = [demand.orders_summary() for demand in analysed_inventory_profile if demand.orders_summary().get('sku')== 'KR202-209']
#print(sku_summary)
1000 loops, best of 3: 885 µs per loop
The inventory classification ABC XYZ denotes the SKUs contribution to
revenue and demand volatility. AX
SKUs typically exhibit steady
demand and contribute 80% of the revenue value for the period being
analysed. Further explanation on ABC XYZ
analysis can be found here.
As a more traditional way of grouping SKUs by behaviour, it is also likely to be used for generating inventory policies and for further exploration of the inventory profile. To retrive all the summaries for a particular classification, we could do something like this:
ay_classification_summary = [demand.orders_summary() for demand in analysed_inventory_profile if demand.orders_summary().get('ABC_XYZ_Classification')== 'AY']
print(ay_classification_summary)
[{'total_orders': '25185', 'standard_deviation': '721', 'quantity_on_hand': '2000', 'economic_order_variable_cost': '15826.20', 'sku': 'KR202-225', 'economic_order_quantity': '45', 'unit_cost': '994', 'demand_variability': '0.344', 'average_orders': '2098.75', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '10542', 'reorder_level': '7402', 'revenue': '226639815', 'reorder_quantity': '13', 'safety_stock': '2261', 'orders': {'demand': ('2744', '2770', '2697', '1726', '1776', '2264', '332', '2420', '2722', '1161', '1986', '2587')}}, {'total_orders': '15201', 'standard_deviation': '752', 'quantity_on_hand': '8939', 'economic_order_variable_cost': '13248.03', 'sku': 'KR202-229', 'economic_order_quantity': '32', 'unit_cost': '1154', 'demand_variability': '0.594', 'average_orders': '1266.75', 'excess_stock': '3994', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '0', 'reorder_level': '3153', 'revenue': '197613000', 'reorder_quantity': '9', 'safety_stock': '1362', 'orders': {'demand': ('2114', '198', '1479', '1249', '1475', '744', '407', '2280', '226', '2285', '796', '1948')}}, {'total_orders': '21172', 'standard_deviation': '702', 'quantity_on_hand': '349', 'economic_order_variable_cost': '15903.60', 'sku': 'KR202-230', 'economic_order_quantity': '37', 'unit_cost': '1194', 'demand_variability': '0.398', 'average_orders': '1764.3333', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '5913', 'reorder_level': '3767', 'revenue': '211720000', 'reorder_quantity': '11', 'safety_stock': '1271', 'orders': {'demand': ('1023', '1150', '1672', '2026', '1590', '441', '2484', '2300', '2928', '1082', '2064', '2412')}}, {'total_orders': '21329', 'standard_deviation': '749', 'quantity_on_hand': '234', 'economic_order_variable_cost': '16488.55', 'sku': 'KR202-232', 'economic_order_quantity': '36', 'unit_cost': '1274', 'demand_variability': '0.422', 'average_orders': '1777.4167', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '6150', 'reorder_level': '3870', 'revenue': '159967500', 'reorder_quantity': '11', 'safety_stock': '1356', 'orders': {'demand': ('614', '2138', '962', '2017', '2398', '2963', '2189', '1804', '414', '2016', '1350', '2464')}}, {'total_orders': '13626', 'standard_deviation': '516', 'quantity_on_hand': '324', 'economic_order_variable_cost': '13586.45', 'sku': 'KR202-234', 'economic_order_quantity': '28', 'unit_cost': '1354', 'demand_variability': '0.454', 'average_orders': '1135.5', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '3822', 'reorder_level': '2540', 'revenue': '272520000', 'reorder_quantity': '8', 'safety_stock': '934', 'orders': {'demand': ('1336', '1478', '865', '533', '1562', '422', '2287', '1302', '1230', '1059', '1153', '399')}}, {'total_orders': '23059', 'standard_deviation': '691', 'quantity_on_hand': '850', 'economic_order_variable_cost': '17933.46', 'sku': 'KR202-235', 'economic_order_quantity': '36', 'unit_cost': '1394', 'demand_variability': '0.360', 'average_orders': '1921.5833', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '7339', 'reorder_level': '4861', 'revenue': '1372010500', 'reorder_quantity': '11', 'safety_stock': '1532', 'orders': {'demand': ('2565', '2762', '2721', '1431', '845', '2163', '2413', '2227', '1753', '740', '1139', '2300')}}, {'total_orders': '19951', 'standard_deviation': '811', 'quantity_on_hand': '433', 'economic_order_variable_cost': '17612.47', 'sku': 'KR202-239', 'economic_order_quantity': '32', 'unit_cost': '1554', 'demand_variability': '0.488', 'average_orders': '1662.5833', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '5737', 'reorder_level': '3819', 'revenue': '778089000', 'reorder_quantity': '9', 'safety_stock': '1468', 'orders': {'demand': ('2717', '2186', '2300', '677', '2157', '2328', '1917', '2519', '561', '281', '1162', '1146')}}, {'total_orders': '26118', 'standard_deviation': '950', 'quantity_on_hand': '2125', 'economic_order_variable_cost': '14175.73', 'sku': 'KR202-241', 'economic_order_quantity': '52', 'unit_cost': '769', 'demand_variability': '0.437', 'average_orders': '2176.5', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '10328', 'reorder_level': '7586', 'revenue': '209126826', 'reorder_quantity': '15', 'safety_stock': '2719', 'orders': {'demand': ('3050', '1507', '3637', '1112', '1963', '1675', '898', '1986', '2262', '3895', '1229', '2904')}}, {'total_orders': '23860', 'standard_deviation': '853', 'quantity_on_hand': '1253', 'economic_order_variable_cost': '20838.38', 'sku': 'KR202-242', 'economic_order_quantity': '32', 'unit_cost': '1819', 'demand_variability': '0.429', 'average_orders': '1988.3333', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '0', 'reorder_level': '3080', 'revenue': '315548500', 'reorder_quantity': '9', 'safety_stock': '1092', 'orders': {'demand': ('1875', '2368', '830', '823', '868', '1409', '1845', '3095', '3247', '1894', '2558', '3048')}}, {'total_orders': '32566', 'standard_deviation': '882', 'quantity_on_hand': '1128', 'economic_order_variable_cost': '19103.09', 'sku': 'KR202-243', 'economic_order_quantity': '48', 'unit_cost': '1120', 'demand_variability': '0.325', 'average_orders': '2713.8333', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '10227', 'reorder_level': '6655', 'revenue': '478134012', 'reorder_quantity': '14', 'safety_stock': '1954', 'orders': {'demand': ('1717', '593', '3006', '2935', '3139', '2753', '3247', '3845', '1720', '3413', '3399', '2799')}}, {'total_orders': '33104', 'standard_deviation': '718', 'quantity_on_hand': '1191', 'economic_order_variable_cost': '18799.00', 'sku': 'KR202-244', 'economic_order_quantity': '50', 'unit_cost': '1067', 'demand_variability': '0.260', 'average_orders': '2758.6667', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '13200', 'reorder_level': '8223', 'revenue': '397148688', 'reorder_quantity': '14', 'safety_stock': '2054', 'orders': {'demand': ('2383', '2046', '2487', '3827', '1674', '3118', '2849', '2233', '3888', '2566', '2216', '3817')}}, {'total_orders': '26065', 'standard_deviation': '855', 'quantity_on_hand': '611', 'economic_order_variable_cost': '20573.14', 'sku': 'KR202-245', 'economic_order_quantity': '36', 'unit_cost': '1623', 'demand_variability': '0.394', 'average_orders': '2172.0833', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '7081', 'reorder_level': '4620', 'revenue': '335612940', 'reorder_quantity': '10', 'safety_stock': '1548', 'orders': {'demand': ('1115', '2694', '3038', '3366', '1058', '2724', '2863', '1930', '1787', '838', '3087', '1565')}}, {'total_orders': '26869', 'standard_deviation': '872', 'quantity_on_hand': '2192', 'economic_order_variable_cost': '12784.68', 'sku': 'KR202-246', 'economic_order_quantity': '59', 'unit_cost': '608', 'demand_variability': '0.389', 'average_orders': '2239.0833', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '0', 'reorder_level': '4745', 'revenue': '175938212', 'reorder_quantity': '17', 'safety_stock': '1578', 'orders': {'demand': ('3108', '1197', '2472', '1264', '3179', '3638', '1268', '1581', '3456', '1630', '1788', '2288')}}, {'total_orders': '23170', 'standard_deviation': '735', 'quantity_on_hand': '1017', 'economic_order_variable_cost': '19126.21', 'sku': 'KR202-247', 'economic_order_quantity': '34', 'unit_cost': '1578', 'demand_variability': '0.381', 'average_orders': '1930.8333', 'excess_stock': '0', 'currency': 'UNKNOWN', 'ABC_XYZ_Classification': 'AY', 'shortages': '5776', 'reorder_level': '4062', 'revenue': '242427710', 'reorder_quantity': '10', 'safety_stock': '1331', 'orders': {'demand': ('3439', '1854', '652', '1827', '1645', '2257', '2733', '1337', '2034', '2106', '877', '2409')}}]
Using a built-in feature of the library provides a quicker way to filter
the results. For example a quciker way to filter for SKU KR202-209
,
is through the use of Inventory
class in the summarise
module.
from supplychainpy.inventory.summarise import Inventory
filtered_summary = Inventory(analysed_inventory_profile)
%%timeit
sku_summary = [summary for summary in filtered_summary.describe_sku('KR202-209')]
#print(sku_summary)
10000 loops, best of 3: 190 µs per loop
Using the import Inventory specifically built to filter the analysis is
faster and syntactically cleaner for eaier to read and understand code.
The Inventory
summary class also provides a more detailed summary of
the SKU with additional KPIs and metric in context of the whole
inventory profile. The summary ranks and performs some comparative
analysis for more insight.
The descriptive summary includes:
- shortage_rank
- min_orders
- excess_units
- revenue_rank
- excess_rank
- average_orders
- gross_profit_margin
- markup_percentage
- max_order
- shortage_cost
- quantity_on_hand
- inventory_turns
- sku_id
- retail_price
- revenue_rank
- shortage_units
- unit_cost
- classification
- safety_stock_cost
- safety_stock_units
- safety_stock_rank
- percentage_contribution_revenue
- gross_profit_margin
- shortage_rank
- inventory_traffic_light
- unit_cost_rank
- excess_cost
- excess_units
- markup_percentage
- revenue
This is a pretty comprehensive list of descriptors to use for further analysis.
Further summaries can be retrieved, for instance summaries at the inventory classification level of detail can be quite useful when exploring inventory policies:
classification_summary = [summary for summary in filtered_summary.abc_xyz_summary(classification=('AY',), category=('revenue',))]
print(classification_summary)
[{'AY': {'revenue': 5372496600.0}}]
Now we know the total revenue generated by the AY
SKU class. There
is another, slightly more fun way to arrive at this number using
Dash
but more on that latter.
top_10_safety_stock_skus = [summary.get('sku')for summary in filtered_summary.rank_summary(attribute='safety_stock', count=10)]
print(top_10_safety_stock_skus)
['KR202-241', 'KR202-231', 'KR202-233', 'KR202-227', 'KR202-225', 'KR202-212', 'KR202-240', 'KR202-244', 'KR202-236', 'KR202-211', 'KR202-243']
Lets add the safety_stock and create a tuple to see that the results explicitly.
top_10_safety_stock_values = [(summary.get('sku'), summary.get('safety_stock'))for summary in filtered_summary.rank_summary(attribute='safety_stock', count=10)]
print(top_10_safety_stock_values)
[('KR202-241', '2719'), ('KR202-231', '2484'), ('KR202-233', '2472'), ('KR202-227', '2277'), ('KR202-225', '2261'), ('KR202-212', '2164'), ('KR202-240', '2120'), ('KR202-244', '2054'), ('KR202-236', '2045'), ('KR202-211', '2020'), ('KR202-243', '1954')]
We can then pass back the list of top_10_safety_stock_skus
back into
the inventory filter and get their breakdown.
top_10_safety_stock_summary = [summary for summary in filtered_summary.describe_sku(*top_10_safety_stock_skus)]
#print(top_10_safety_stock_summary)
We have only covered a few use cases but we have already achieved a significant amount of analysis with relativley few line of code. The equivalent in Excel would require much more work and many more formulas.