INVENTORY OPTIMIZATION TRACKER. USER’S GUIDE By David Guo, MBA Business Analyst 2012 [email protected] 706-254-6781 Contact me if you need further explanation. Inventory Analysis Timeline . Recommended Schedule: Maintenance departments, warehouse personnel, and procurement
Recommended Schedule: Maintenance departments, warehouse personnel, and procurement
personnel should review 30 items each month.
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
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
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.
Warehouse and procurement personnel should continue working the inventory reduction
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
The tool will use the Maximo generated regular inventory file. Ensure worksheet is named
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”
1. Before you begin, please go to GIL and install the latest version of Crystal Ball Pro
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”.
Click the “ACTION #1 CHECKLIST” worksheet tab and Action 5.
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.
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.
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.
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
Select how many years of data you have and click “OK”. Then select the file that
contains the demand and lead time data
You should get something similar to the below screenshot. Optimization” worksheet.
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.
Click on “Load Data” Optimization” worksheet.
Click on the “Select Data” button to select
the data range.
Click the “Demand & Lead Time Data” worksheet tab Optimization” worksheet.
Select the issue quantity and issue probability rectangle
Type in “$” to make the references absolute
Click OK until the pop-up menus disappear
Click the “Copy” button to copy Optimization” worksheet.
the probability distribution
2. Select cells D3:D366
Click “Paste” button to past the
probability distribution to cells
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.
1. Click on “OptQuest” Ball” then go to “Define Assumption”.
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.
Define the lower bound, base case, and upper bound for the reorder point and order
2. Click “Run” and wait two minutes.
1. Click “View” reorder point and order
Click “Solution Analysis”. Click “All Solutions”
and check both “Feasible” and “Infeasible
3. The best solution is “Rank 1”
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.
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
The “Projected Savings Due to Inventory Reduction” for the item should be calculated
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.