Live Learning Center. Solution-Driven Integrated Learning Paths. Make the Most of Your Educational Experience

(1)

Solution-Driven Integrated

Learning Paths

Educational Sessions

Lean

Global Supply Chain

Basics of Operation Management

Demand Management, Forecasting, and S & OP

Professional Advancement

Special Interest Topics

Plant Tours

Networking Events/Peer Interaction

Materials for Sale at the APICS Bookstore

Make the Most of Your

Educational Experience

Develop a Learning Plan

Assess your learning needs

Use teamwork

Prepare to learn

Create your own Action Plan

Live Learning Center

Sync-to-Slide

(2)

Steve Cimorelli, CFPIM, is an independent consultant and owner of SCC Inventory Consulting. He holds a degree in

engineering and has 30 years of experience in aerospace, industrial equipment,

commercial manufacturing, distribution, and supply chain management. From 2005 to 2008, Cimorelli served as Director, Global Inventory Planning for Cummins Filtration. He can be reached at (321) 269-3407 or [email protected].

Excel Tools and

Techniques

For Better Inventory

Management

Steve Cimorelli, CFPIM

President, SCC Inventory Consulting, LLC

Introduction

NASA: Engineer, space shuttle program

McDonnnell Douglas: Mfg. engineer, MRP manager

Learjet: Manufacturing systems manager

Square D: Operations manager, staff analyst

Cummins: Director, global inventory planning

Current: President, SCC Inventory Consulting, LLC

Published Author

Kanban for the supply chain; 2005

Handbook of manufacturing engineering; 1995

(3)

Learning Path / Session Type

Global Supply Chain

Demand Mgt., Forecasting, S&OP

Logistics and Distribution

Lean

Basics of Operations Management

Green for Good Business

Legend: Primary Secondary Global Advanced Emerging Issues Specific Issues Mini Workshop Audience Level: Intermediate Case Study

Six Learning Paths Six Session Types

Ground Rules & Expectations

Learn something useful… Apply

immediately (next week) on-the-job

Participate

Learn from each other

Ask questions

Mutual respect

Have fun!

Others… ?

Course Take-Aways

Learning Objectives

Learn and practice Excel techniques for analyzing inventory data.

Understand techniques for analyzing demand pattern statistics.

Develop interactive chart; graphically portray demand data over time.

Analyze inventory savings opportunities; summarize by vendor, ABC, planner, etc.

(4)

Course Outline

Excel Skills (Demo)

Importing data from text files

Formatting data

Formulas & functions

Sorting

Inventory Analysis (Demo)

ABC classification

EOQ calculation (Appendix) Excel Skills (Intermediate)

Joining worksheets

More Functions

Interactive Graphs BREAK

Variability Review

Course Outline (continue)

Inventory Analysis (Intermediate)

Part usage stability

Kanban or MRP?

Demand through lead time

Safety stock Excel Skills (Advanced)

Analyzing data

Decision making Inventory Analysis (Advanced)

Lead time opportunities

Lot size opportunities

Pivot Table Summary Time Permitting…

OP, Avg. Inventory, Overstock

Dashboards

Setup

Equipment

Laptop computer with USB or CD reader

Assume Microsoft Windows XP

Handout

Quick-Reference Guide (Cheat Sheet)

Source Data

USB Flash Drive

Data CDs

¾

Confirm data on

laptops now.

(5)

ABC Classification

Pareto Principle

80% value is in 20% of the parts

Applying this to ABC classification

yields the following typical breakdown

ABC % Items % Annual Cost

A 20 80

B 30 15

C 50 5

100% 100%

Excel Demo (ABC Analysis)

Import data from ERP system

Basic formatting

Merge data using Vlookup

Simple calculations

Sorting

ABC assignment

Open workbook

“Item Master (Solution1).xls”

… Follow Demo

(6)

Review: Import Data Wizard

Review: Formatting

Sizing rows and columns

Alignment

Freeze panes

Numeric formatting (comma,

decimal, etc.)

Currency formatting

Review: Formulas & Functions

Simple math (= / + / - / x /

÷

)

Fixed vs. Variable cell references

Paste_Special_Values

Sorting Data

Data_Sort Sort Icons

Group discussion.

Reorganize seating

as needed…

(7)

Intermediate Excel Skills

Functions:

Sum

Average

Std. Deviation

Concatenate

Min

Max

Count

Countif

Vlookup with match function

Graphing data

Exercise: Demand Analysis

Open “FG Demand Exercise.xls”

“Daily Demand” Tab

Create functions for blank columns:

Follow

Demo…

PN and warehouse Total demand Average day Std. deviation Min day Max day

No. days with demand

(8)

Exercise: Interactive Graph

Continue “FG Demand Exercise.xls”

“Daily Demand” tab

Create drop-down menu

Select part number and warehouse

Create Vlookup table for graph

Embedded match function

Create interactive graph

Follow

Demo…

(9)

Normal Distribution:

Bell Curve

Daily demand for a product

0 20 40 60 80 100 120 140 160 1 3 5 7 9 11 13 15 17 19 21 23 25 Mean + 1 SD - 1 SD - 2 SD - 3 SD + 2 SD + 3 SD

Z-Score

Z Prob < Z 0.00 50% 0.67 75% 1.28 90% 1.64 95% 1.88 97% 2.05 98% 2.33 99% 2.58 99.5% 3.09 99.9%

Excel: Normsinv() function

Example:

Normsinv(99%) = 2.33

Z = Number of Std. Deviations to the right of mean.

Z = 2.05 means that 98% of days will have demand less than mean + 2.05 SD pieces.

Statistical Safety Stock

Formula: SS = SF x SD x Sqrt(LT)

SS = Safety stock (for a PN)

SF = Safety factor (Z-Score)

Represents the probability that demand will be less than a certain value; i.e. SS will provide X% part availability

SD = Std. deviation of daily demand

LT = Lead time (square root of LT)… Caution: use same time units as demand (days, weeks, etc.)

(10)

Statistical Safety Stock

Formula: SS = SF x SD x Sqrt(LT)

Example

SF = 2.33 (represents 99% service) SD = 150 pieces LT = 20 days SS = 2.33 x 150 x Sqrt(20) = 1563 pieces

Exercise: SS Calculations

Re-Open “FG Demand Exercise.xls”

“Planning” tab

Calculate statistical safety stock…

Add lead time and safety factor columns

Vlookup lead time

Safety factor function (NORMSINV)

Calculate safety stock

Follow

Demo…

(11)

Planning Methods

MRP – ERP default method

Kanban/pull

Stable demand patterns (low variability)

Frequent, repeatable demand

Daily level load (continuous flow)

Highest volume products

Very stable demand

Very frequent, repeatable demand

Planning Methods – Excel

Techniques & Formulas

Kanban/pull and level

load statistics

Stability: Coefficient of variation (CoV) CoV = Std. dev / mean Frequency: Number days

with demand Low CoV Med. CoV High CoV Stable demand Normal demand Erratic demand

Excel pivot table

Analyze natural breakpoints in data

Exercise: Inventory Planning

Re-open “FG Demand Exercise.xls”

“Planning” tab

Determine planning method rules…

Add CoV column

Use Pivot Tables to determine “natural break points”

Establish rules for Level Load, Kanban &

MRP

Follow

(12)

Review: Inventory Planning

Select planning method for each product

Nested “If()” Statement

And() & Or() Functions

Calculate safety stock for each product

Inventory Analysis - Advanced

Challenge

: Analyze inventory savings potential

Which vendors & PNs hold the greatest savings opportunity?

Establish lead time & lot size Targets by ABC Calculate inventory savings potential by PN Summarize inventory savings potential by vendor &

ABC

Use Pareto analysis (80/20 rule) to focus on “big bang for the buck”

(13)

Exercise: Inventory Savings

Re-Open “Item Master Solution1.xls”

Add new tab: “Control Table”

Create control table as follows:

Follow

Demo…

ABC Lead Time (Days) Lot Size (Days) A 5 5 B 10 20 C 20 60

Calculate target lot size

Calculate inventory savings by PN

Summarize savings by vendor & ABC

Save-As: “Item Master Solution2.xls”

Saw Tooth: Average Inventory

Avg Inv = SS + ½ Lot size

SS LT LS Quantity Time DLT OP

Depicts Inventory Usage & Replenishment over time.

Avg Inv

Inventory Analysis

-Dashboard

Challenge: Are current inventory levels

properly balanced?

Objective: Create an inventory

dashboard

Concepts Needed:

Reorder Point (aka Order Point)

Average Inventory

(14)

Order Point & Average Inventory

Avg DLT = Avg. Daily usage X Lead time

Avg Inv = SS + ½ Lot size

OP = SS + DLT SS LT LS Quantity Time DLT OP

Depicts Inventory Usage & Replenishment over time.

Avg Inv

Overstock

Overstock Threshold = OP + LS

SS LT Quantity Time OP

Theoretical Max Inv = OP + Lot Size Any Inventory above Max is considered “Overstock”.

No customer demand for several days after breaking OP. Replenishment occurs on-time within LT.

1 2 3

Exercise: Inventory Status

Open “Item Master Solution2.xls”

Add columns for SS, OP, DLT, Avg Inv, overstock and shortage

Create formulas for new columns

Import available inventory data

Calculate $ value of Avg inventory, overstock & product short

Pivot table: $ values by planner & vendor

Follow

Demo…

(15)

Inventory Dashboard

Data Dept/ Vendor Sum of Theoretical Target $ Sum of Actual $ Sum of Overstock $ Sum of Shortage $ AAA $ 59 $ 22 $ 17 $ (22) CSTG $ 13,638 $ 12,225 $ - $ -DDD $ 6 $ 23 $ 11 $ -KKK $ 139 $ 184 $ - $ -NNN $ 25 $ 40 $ - $ -PPP $ 665 $ 651 $ 11 $ -SSS $ 12,646 $ 21,921 $ 6,129 $ (2,940) STMP $ 1,634 $ 3,028 $ - $ -Grand Total $ 28,811 $ 38,093 $ 6,170 $ (2,962)

Ref: “Item Master Solution3.xls”

Course Review

Excel Skills (Basic)

Importing data from text files

Formatting data

Formulas & functions

Sorting

Inventory Analysis (Basic)

ABC classification

EOQ calculation Excel Skills (Intermediate)

Joining worksheets

More functions

Graphs BREAK

Variability exercise

Inventory Analysis (Intermediate)

Part usage stability

Kanban or MRP?

Demand through lead time

Safety stock Excel Skills (Advanced)

Analyzing data

Decision making Inventory Analysis (Advanced)

Lead time opportunities

Lot size opportunities

Pivot table summary

OP, Avg. inventory, overstock

(16)

ABC Analysis – Sample Data

26 Part Numbers

Annual Demand from 33 to 6410

Costs from $0.55 to $125

Part Number Annual Qty Cost Annual Cost A 249$ 53.20 $ 13,246.80 B 409 5.44 2,223.31 C 204 3.40 693.60 D 352 1.50 528.00 E 270 2.30 621.00 F 6410 1.70 10,897.00 G 264 3.40 897.60 H 57 7.44 424.02 I 77 6.70 515.90 J 428 53.75 23,005.00 K 247 0.69 170.49 L 337 5.50 1,853.50 M 282 2.30 648.60 N 535 1.12 599.20 O 573 6.00 3,438.00 P 35 6.18 216.44 Q 270 55.05 14,862.84 R 4034 0.55 2,218.70 S 450 4.56 2,052.00 T 529 5.44 2,876.44 U 750 0.58 435.00 V 54 5.87 316.86 W 145 3.51 508.84 X 33 125.00 4,125.00 Y 2010 1.25 2,512.50 Z 1819 20.16 36,668.21

Lowest annual cost, but not lowest cost or quantity. Highest annual cost,

but not highest cost or quantity.

ABC Analysis: Step-by-Step

Part Number Annual Cost Cum Cost Cum % ABC %Cnt

Z $ 36,668.21 $ 36,668.21 29% A J 23,005.00 59,673.21 47% A Q 14,862.84 74,536.05 59% A A 13,246.80 87,782.85 69% A F 10,897.00 98,679.85 78% A 19% X 4,125.00 102,804.85 81% B O 3,438.00 106,242.85 84% B T 2,876.44 109,119.29 86% B Y 2,512.50 111,631.79 88% B B 2,223.31 113,855.10 90% B R 2,218.70 116,073.80 92% B S 2,052.00 118,125.80 93% B L 1,853.50 119,979.30 95% B 31% G 897.60 120,876.90 96% C C 693.60 121,570.50 96% C M 648.60 122,219.10 97% C E 621.00 122,840.10 97% C N 599.20 123,439.30 98% C D 528.00 123,967.30 98% C I 515.90 124,483.20 98% C W 508.84 124,992.03 99% C U 435.00 125,427.03 99% C H 424.02 125,851.05 99% C V 316.86 126,167.91 100% C P 216.44 126,384.36 100% C K 170.49 126,554.85 100% C 50% Sort by descending annual cost

Calculate Cum Cost & Cum Cost %

Set ABC Break Points

Calculate % Count Break Points as Sanity Check

(17)

Economic Order Qty (EOQ)

Part cost = Cost per part, considering setup and unit cost

For Purchased Parts: Setup = ordering cost

For make parts: Setup = Setup time X $/hour

¾The greater the lot size, the smaller the part cost per unit

Carrying cost = Cost of carrying parts in inventory

¾The greater the lot size, the greater the carrying cost. C a rr ying C ost P ar t C ost T ota l C ost L ot Size E O Q = M inim u m T otal C ost $ C a rr ying C ost P ar t C ost T ota l C ost L ot Size E O Q = M inim u m T otal C ost $

EOQ Formula

A = Annual usage (Units / Year)

S = Setup or ordering cost ($)

I = Inventory carrying cost (Annual %)

C = Unit cost ($ / Unit); (excluding setup)

2AS IC EOQ = 2 x 10,000 x 100 0.18 x 50 EOQ = = 471 pieces Example: A = 10,000 pieces/year S = $100 I = 18% /year C = $50

EOQ – Excel Exercise

Open “EOQ Exercise.xls”

Import setup & run times for make parts

Create Excel file: “Routing Data.xls”

Use “if” statement to calculate setup cost

Fixed $50 for “buy” parts (Note: Reality may be more complex!)

Assume $50/hour for “Make” parts

Calculate EOQ for all parts

Round to nearest 10, 100, 1000, container Qty

Follow along with

instructor demo…

(18)

Course Take-Aways

Learning Objectives

Learn and practice Excel techniques for analyzing inventory data.

Understand techniques for analyzing demand pattern statistics.

Develop interactive chart; graphically portray demand data over time.

Analyze inventory savings opportunities; summarize by vendor, ABC, planner, etc.

Create an inventory management dashboard

Presenter Contact Information

President, SCC Inventory Consulting

Office: 321-269-3407

Cell: 321-431-0195