inventory optimization tracker n.
Skip this Video
Download Presentation

Loading in 2 Seconds...

play fullscreen
1 / 29


  • Uploaded on

INVENTORY OPTIMIZATION TRACKER. USER’S GUIDE By David Guo, MBA Business Analyst 2012 706-254-6781 Contact me if you need further explanation. Inventory Analysis Timeline . Recommended Schedule: Maintenance departments, warehouse personnel, and procurement

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'INVENTORY OPTIMIZATION TRACKER' - harlow

Download Now An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
inventory optimization tracker



By David Guo, MBA Business Analyst 2012


Contact me if you need further explanation


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


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.


Third & Fourth Week

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

are analyzed.


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. 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


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.

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”.


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.


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

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


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.


Click on “Load Data”

Click on the “Select Data” button to select

the data range.


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


Click the “Copy” button to copy

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”

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”

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.