INVENTORY OPTIMIZATION TRACKER

1. INVENTORY OPTIMIZATION TRACKER USER’S GUIDE By David Guo, MBA Business Analyst 2012 davidxguo@gmail.com 706-254-6781 Contact me if you need further explanation

2. Inventory Analysis Timeline

3. Recommended Schedule: Maintenance departments, warehouse personnel, and procurement personnel should review 30 items each month. First Week 1. After importing the top 360 items, email a copy of the “Inventory Optimization Tracker” worksheet to each department supervisor such as the mechanical supervisor and the electrical & instruments supervisor. Give the department supervisors two weeks to answer the six questions for 30 items off the list. For questions QN3 to QN5, only one of questions should be “YES” because a part can’t be critical and non-critical at the same time

4. First & Second Week While the maintenance departments are answering the questions for the first 30 items, warehouse and procurement personnel should be working through the inventory reduction action checklists. After the first week, check the progress of the maintenance departments and remind them of the two week deadline. Once you get the completed worksheets back from the departments, warehouse personnel should verify the following: a. QN1: If QN1 is “NO” and it was flagged as obsolete, then change the category to “Low Turnover”. The tool only flags items that are possibly obsolete but the final determination is made by the subject matter expert. If QN1 is “YES”, then the item can be marked for investment recovery. b. QN2: If QN2 is “YES”, then probe the subject matter expert further to find out what the substitute is and document the substitute in the system or in a file. c. QN3: If QN3 is “YES”, then the item does not need a high service level. You can stock less of this item since it is non-critical. Consider a 90% service level to meet demand. You should run a simulation to figure out an optimized MIN/MAX that reduces average inventory. If QN3 is “NO”, then move on to QN4. d. QN4: If QN4 is “YES”, then consider a service level of 95% or above. If QN4 is “NO”, then go to QN5. e. QN5: If QN5 is “YES”, then consider a service level of 99% of above. f. QN6: If QN6 is “YES”, then ask for the maintenance or project schedule to determine how far in advance you need to order the item. If the item is seasonal, then you may not need to stock the item for certain months.

5. Third & Fourth Week Warehouse and procurement personnel should continue working the inventory reduction checklists. 2. As warehouse and procurement personnel complete the checklists, they will answer the questions on the “Inventory Optimization Tracker”. For QN1 to QN5, if any are “YES” then you will be able to reduce inventory. For the next month, go through the next 30 items on the list. Continue this process every month until all 360 items are analyzed.

6. Inputs

7. The tool will use the Maximo generated regular inventory file. Ensure worksheet is named “Sheet1” 2. The tool will also use the Maximo generated demand sheet and lead time sheet. It’s one file but separate worksheets. Ensure sheets are named “SKU ISSUES” and “SKU RECEIPTS”

8. Overview

9. 1. Before you begin, please go to GIL and install the latest version of Crystal Ball Pro

10. 1. Open the tool and import the inventory file by clicking the “Import Top 360 Items From Inventory”button, selecting the inventory file, and clicking “Open” button

11. The top 360 inventory items (ranked by bin value) should be imported into the tool. • Now look at the “Category” column highlighted in blue. The tool automatically determines whether an item is high turnover, low turnover, or overstocked. For obsolete items, the tool will flag possible obsolete items but the final determination of obsolescence must be made by subject matter experts (SME). • Now look at the “Actions” column highlighted in pink. Notice the sub-columns marked 1, 2, 3, 4, 5, 6, 7. The numbers identify the actions to reduce inventory. The tool places an “X” to identify which actions should be taken for a specific item. There is a checklist for each action.

12. Warehouse personnel are responsible for completing Action 4 and Action 5. Procurement personnel are responsible for completing Action 1 and Action 7. Action 2 and Action 6 require input for the SMEs. After inputs are received, warehouse personnel can take appropriate actions. Action 3 requires a clean item master. As of AUG2012, Oronite’s Item Master is not clean. 4. Let’s focus on the first part “ELM-V082-0065”.

13. Inventory Reduction Checklists

14. Click the “ACTION #1 CHECKLIST” worksheet tab Each action corresponds to a checklist. Warehouse and procurement personnel should systematically go through the appropriate checklists for each item Copy and paste the “ELM-V082-0065” into the SKU field. Copy and paste the item’s description into the DESCRIPTION field . See below. Now go through each line of the checklist step by step and annotate comments.

15. Once you are complete with Action #1 Checklist, right click on the “Action #1 Checklist “ worksheet tab and click “Create a copy” and move to “(new book)”. Then save the copy to a new folder named after the part, “ELM_V082_0065” and name the copy using the format YEARMONTHDAY and the checklist number. For example “120723ACTION1CHECKLIST.xlsx”. 3. All warehouse and procurement personnel should be aware that all checklists for the part should be stored in the “ELM_V082_0065” folder using the same YEARMONTHDAY format and the corresponding checklist number.

16. As warehouse and procurement personnel complete the checklists, they will answer the questions on the “Inventory Optimization Tracker”. For a given item, if QN1 is “YES” then scroll right on the “Inventory Optimization Tracker” sheet and enter zero for “New Average Inventory” cell to calculate savings. If QN2 is “YES” then run the simulation to optimize the MIN/MAX levels. If QN3 is “YES” then, enter the new price in the “New Price” cell to calculate savings If QN4 and/or QN5 is “YES” then simulation will generate a new average inventory. Enter current average inventory and new average inventory to calculate savings.

17. Crystal Ball MIN/MAX Simulation Optimization

18. Actions 4 and 5 require you to use the “Item Simulation Optimization” worksheet. However, you must first generate the discrete probability distribution for the demand and lead time. Click on the “Demand & Lead Time Data” tab. Then click the “Calculate Probability Distributions” button. Select how many years of data you have and click “OK”. Then select the file that contains the demand and lead time data

19. You should get something similar to the below screenshot. Now click the “Item Simulation Optimization” worksheet tab Place cursor on D2 and right click. Go to “Crystal Ball” then go to “Define Assumption”. If a distribution menu pops up, click “Custom Distribution”. If not, go to next step.

20. Click on “Load Data” Click on the “Select Data” button to select the data range.

21. Click the “Demand & Lead Time Data” worksheet tab Select the issue quantity and issue probability rectangle Type in “$” to make the references absolute Click OK until the pop-up menus disappear

22. Click the “Copy” button to copy the probability distribution 2. Select cells D3:D366 Click “Paste” button to past the probability distribution to cells D3:D366

23. Place cursor on I2 and right click. Go to “Crystal Ball” then go to “Define Assumption”. Repeat the previous three slides. Make sure to select the lead time and lead time probability rectangle not the issue and issue probability rectangle.

24. 1. Click on “OptQuest” Define the mean service level, the probability that a service level is greater than or equal to a defined service level, and the service level you want to greater than or equal to.

25. Define the lower bound, base case, and upper bound for the reorder point and order quantity. 2. Click “Run” and wait two minutes.

26. 1. Click “View” Click “Solution Analysis”. Click “All Solutions” and check both “Feasible” and “Infeasible Solutions” 3. The best solution is “Rank 1”

27. Compare the base case average inventory to the new solution average inventory. Our base case was reorder point and order quantity of 12. The old min/max was 12/24. The optimized min/max is 8/11. We reduced our average inventory from 12.74 to 4.15.

28. Click on the “Inventory Optimization Tracker” worksheet tab and scroll to the right Enter in the new min and max, the current average inventory, and the new average inventory. The “Projected Savings Due to Inventory Reduction” for the item should be calculated automatically.

29. References Slater, Phillip. Smart Inventory Solutions: Improving the Management of Engineering Materials and Spare Parts. 2 ed. New York, NY : Industrial Press, Inc., 2010. Print. Ragsdale, Cliff . Spreadsheet Modeling & Decision Analysis: A Practical Introduction to Management Science. 5 ed. Cincinnati, OH: South-Western College Pub, 2007. Print. Walkenbach, John. Excel 2010 Power Programming with VBA . 1 ed. Hoboken, NJ: Wiley, 2010. Print.