Inventory
Christine
November 17, 2016
TwoYr2$Date <- as.Date(substring(TwoYr2$Date, 2), "%m.%d.%Y")TwoYr2 (3850,4)
| Item_Code | Description | Date | Inventory_Used |
|---|---|---|---|
| SS02CLR3030 | 3/32 CLEAR 30X30 | 2015-01-01 | 1 |
| SS02CLR3232 | 3/32 CLEAR 32X32 | 2015-02-01 | 0 |
| SS02CLR3240 | 3/32 CLEAR 32X40 | 2015-03-01 | 0 |
| SS02CLR3636 | 3/32 CLEAR 36X36 | 2015-04-01 | 0 |
| SS02CLR3660 | 3/32 CLEAR 36X60 | 2015-05-01 | 0 |
| SS02CLR4884 | 3/32 CLEAR 48X84 | 2015-06-01 | 0 |
meanItems (30,7) Highest monthly averages
| Item_Code | Monthly_Average | StDev_Monthly | Av_Demand_during_L | Safety_Stock | Reorder_Level | Order_Quantity |
|---|---|---|---|---|---|---|
| SS06CLR96130 | 2305 | 411 | 493 | 390 | 883 | 68 |
| SS03CLR7284 | 955 | 892 | 204 | 846 | 1051 | 44 |
| SS02CLR7284 | 931 | 330 | 199 | 313 | 512 | 43 |
| SS06S6T100144 | 512 | 172 | 110 | 163 | 273 | 32 |
| SS05CLR96130 | 452 | 153 | 97 | 145 | 242 | 30 |
| SS12CLR102130 | 440 | 108 | 94 | 102 | 197 | 30 |
newestCombo (660, 3) Z score of monthly averages highest
| Date | Item_Code | Monthly_Inventory_Z_Score |
|---|---|---|
| 2015-03-01 | SS06CLR96130 | -0.72 |
| 2015-02-01 | SS06CLR96130 | -1.91 |
| 2015-01-01 | SS06CLR96130 | -1.48 |
| 2016-10-01 | SS06CLR96130 | -0.56 |
| 2016-09-01 | SS06CLR96130 | -1.41 |
| 2016-08-01 | SS06CLR96130 | -0.29 |
NewestCombo (22,31) Average Inv by month in Z
| Date | SS02CLR7284 | SS02LOE7284 | SS03AHT7284 | SS03CLR7284 | SS03CLR96130 | SS03LOE7284 | SS03S6A7284 | SS03S6T7284 | SS04CLR96130 | SS04LOE96130 | SS05CLR96130 | SS05LOE96130 | SS05S6T96130 | SS06BRN96130 | SS06CLR96130 | SS06GRY96130 | SS06LCL4080 | SS06LOE96130 | SS06MCL96130 | SS06S6A100144 | SS06S6T100144 | SS06S7T100144 | SS06STC96130 | SS06STP96130 | SS10CLR96130 | SS10CLV96130 | SS10SHG96130 | SS10STP96130 | SS12CLR102130 | SS12STP96130 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2015-01-01 | 1.36 | 0.53 | -0.36 | -1.07 | 1.51 | -1.09 | 0.58 | -0.45 | -1.28 | -0.94 | 0.44 | -0.74 | -0.57 | -0.81 | -1.48 | -0.44 | -1.93 | 0.56 | 3.40 | -1.27 | -0.56 | -0.93 | -0.48 | -0.60 | 1.99 | 0.01 | 0.75 | 0.03 | 0.54 | 1.78 |
| 2015-02-01 | 1.36 | 0.83 | 0.39 | -1.07 | 1.17 | -1.39 | -0.25 | -0.12 | 0.75 | -1.28 | 2.07 | -1.77 | 0.16 | -1.36 | -1.91 | -0.46 | -0.21 | 1.01 | 0.68 | -1.28 | -0.56 | -1.03 | 0.17 | -1.29 | 0.74 | 0.35 | 0.27 | -0.67 | 1.69 | -0.14 |
| 2015-03-01 | -0.04 | 0.83 | -0.61 | -1.07 | 0.63 | 0.53 | -0.68 | -0.12 | 1.53 | 0.03 | 1.79 | -1.82 | 0.40 | 0.72 | -0.72 | 0.28 | -0.21 | -0.58 | 0.48 | 0.32 | -1.59 | 0.31 | -0.13 | -0.40 | -0.28 | 0.27 | -0.16 | -1.52 | -0.10 | 0.15 |
| 2015-04-01 | -0.34 | -0.06 | 0.19 | -0.98 | 0.88 | 1.06 | -1.13 | -0.81 | 0.75 | 1.68 | 0.26 | -0.20 | -0.50 | -1.70 | 0.20 | -0.58 | 2.18 | 0.55 | -0.12 | 2.06 | -1.11 | -0.85 | -0.76 | 0.78 | 0.47 | 2.32 | -0.26 | -1.67 | -0.10 | -0.58 |
| 2015-05-01 | -1.10 | -0.02 | 0.19 | 0.20 | 0.28 | 1.64 | -1.71 | -0.68 | 0.75 | 0.89 | 1.65 | 0.29 | -0.50 | -0.08 | 0.85 | -0.70 | 0.35 | 0.89 | -0.66 | 0.71 | -1.27 | -1.25 | 0.17 | 0.69 | -0.13 | 1.99 | 0.69 | -1.72 | -0.51 | -0.40 |
| 2015-06-01 | -1.92 | -1.34 | -0.46 | 0.87 | -0.75 | 0.23 | -1.22 | -2.01 | 1.02 | 0.41 | -0.21 | 0.24 | -1.12 | -0.51 | 2.04 | 0.33 | -1.32 | 0.89 | 0.40 | 0.52 | -1.23 | -1.25 | 0.86 | 0.37 | -1.54 | 1.69 | 1.15 | -0.97 | -0.46 | -0.69 |
TwoYr222 (660,11) All calculations, all months, top 30
| Item_Code | Description | Date | Inventory_Used | Monthly_Average | StDev_Monthly | Av_Demand_during_L | Safety_Stock | Reorder_Level | Order_Quantity | Z_Score |
|---|---|---|---|---|---|---|---|---|---|---|
| SS02CLR7284 | 3/32 CLEAR 72 X 84 | 2015-07-01 | 347 | 930.77 | 330.27 | 199.19 | 313.20 | 512.39 | 43.15 | -1.77 |
| SS02LOE7284 | 3/32 ENEG ADV LOW-E 72X84 | 2015-08-01 | 64 | 196.14 | 73.74 | 41.97 | 69.93 | 111.91 | 19.81 | -1.79 |
| SS03AHT7284 | 1/8 ACCLIMATE HT 72X84 | 2015-10-01 | 19 | 80.45 | 40.10 | 17.22 | 38.03 | 55.25 | 12.68 | -1.53 |
| SS03CLR7284 | 1/8 CLEAR 72X84 | 2016-05-01 | 782 | 954.86 | 892.32 | 204.34 | 846.22 | 1050.56 | 43.70 | -0.19 |
| SS03CLR96130 | 1/8 CLEAR 96X130 | 2016-06-01 | 0 | 355.91 | 454.36 | 76.16 | 430.89 | 507.05 | 26.68 | -0.78 |
| SS03LOE7284 | 1/8 ENEG ADV LOW-E 72 X 84 | 2015-02-01 | 185 | 339.41 | 111.02 | 72.63 | 105.29 | 177.92 | 26.05 | -1.39 |
Problem Definition
An inventory system that prevents stockouts.
*Safety stock
*Forecasting
Decide on a horizon. A month?
*Prediction probability? Looking to prepare for a 20% increase in sales.
*This is time series forecast, we are not looking into factors which may affect the SKU behavior.
Data Gathering
How did we decide what data to pull?
*Join 2015 and 2016 monthly inventory data
*This narrowed our data ser to 175 SKUs
*Calculate the Monthly_Average monthly use of each SKU
*Chose the top 30 SKUs with the highest Monthly_Average
Preliminary Analysis
Graphing the data
Are there consistent patterns?
Is there a significant trend?
Is seasonality important?
Is there evidence of the presence of business cycles? Are there any outliers in the data that need to be explained by those with expert knowledge?
How strong are the relationships among the variables available for analysis?
Find the highest volume SKUs and do inventory management calculations based on historical data.
Equations based on the following assumptions:
Continuous Review Policy
consistent lead time of 6 days and a service level of 98%.
Service level of 98%.
Average Monthly Demand:\[\bar{x}_d\]
Standard Deviation of Monthly Demand:\[\sigma_d\]
Lead Time:\[L = .214\]
Service Level:\[z = 2.05\]
Carrying Cost (negligible):\[k = 1\]
Holding Cost (negligible):\[h = 1\]
Average demand during lead time: \[\bar{x}_L = \bar{x}_d * L\]
Safety Stock:\[z*\sigma_d*\sqrt{L}\]
Reorder Level:\[\bar{x}_L+z*\sigma_d*\sqrt{L}\]
Order Quantity:\[Q = \sqrt\frac{2k*\bar{x}_d}{h}\ \]
Which is the best way to show historical data?
Or Big Time Series with a bizarre trend line?
TwoYr2 %>% filter(Item_Code == "SS06CLR96130") %>% ggvis(~Date, ~Inventory_Used) %>% layer_lines(stroke = ~factor(Item_Code))
Individual giant time series and smooth
## Warning in bind_rows_(x, .id): Unequal factor levels: coercing to character
FourOne %>% ggvis(~factor(Item_Code), ~Z_Score) %>% layer_boxplots()## Warning in bind_rows_(x, .id): Unequal factor levels: coercing to character