Lean Six Sigma

Rick Orr, Finance Manager Public Works

Reducing Street Light Inventory

Project Objectives

Work Towards Achieving Mayor Richards City Goals

-Safe City

-Quality jobs

-Improved customer service - B.E.S.T.

Demonstrate how Lean Six Sigma Improves Customer Service and Saves ResourcesImprove Customer Service by Reducing Capital Investment in Street Light Inventory

Systematic approach to reducing process defects that produce undesired outcomes - in our case, improving the decision making regarding inventory purchases

DMAIC Define, Measure, Analyze, Improve, Control

Team focus to problem solving - each of us are experts in certain areas of the inventory process and each have specialized knowledge of portions of the process

What Is Lean Six Sigma?

Street light inventory seems excessive relative to usage

Project Description

Reduce inventory to optimum level

Problem Statement:

Objective:

A year ago, my division manager, Greg Meszaros, asked me to investigate reducing the level of inventory at our Street Light Warehouse. Since empirical data was sketchy, it was his opinion that our level of inventory seemed excessive relative to usage.

My project goal was to reduce our level of inventory to some sort of optimum level while following the 6 sigma roadmap.

Of course, if our funds are not tied up in inventory, the funds can be used elsewhere.

External Customers

Citizens

Carrying excessive inventory ties up capital that can be used elsewhere

Lost capital opportunities cause unnecessary high tax rates

Internal Customers

City Staff

Uncertain ordering schedules makes it difficult to anticipate ordering needs

Inaccurate inventory records

Inaccurate damage recoveries

Inaccurate materials billing

Cost of Poor Quality

Frees capital funds to be redirected towards other use and helps maintain low taxes

Benefits

The Y: the total value of street light inventory, measured monthly

Y = f(x1,x2,x3,,xk)

The Y

Minimizing Inventory:

Increases flexibility in asset management

Makes it easier to control

Reduces the need for space

Makes it easier to count

Reduces aged inventory

Inventory is an asset, but it is a non-productive asset.

It earns no interest but costs City in handling, shrinkage, and space.

Why Minimize Inventory?

Y = f(x1,x2,x3,,xk)

The Project Plan: examine the factors that drive inventory levels on various items and appropriately reduce the level of individual street light items

The Goal: Reach optimal levels of inventory to reduce the invested capital

The Defect: excessive street light inventory

The Y: the total value of street light inventory, measured monthly

Definition of the Y

The 6 sigma approach is to uncover the various factors that drive a process - the x's in this formula - reduce the variation of these factors, and thereby improve overall process output and decrease process variability.

The process outcome Im trying to positively affect is the dollar value of total inventory carried, over time.

Champion: Greg Meszaros

Assisting: Michele Hill, Roger Hirt

Team Members:

Rick Orr, Project Leader/Black Belt

Dave Pepper, St Light Warehouse

Nate Parker, St Light Warehouse

Lori Dekoninck, St Light Warehouse

Phyllis Davis, St Light Engineering Admin

Steve Davis, Assistant Traffic Engineer

Tracy Neumeier, Internal Audit/Black Belt

Project Team

It is important to mention that a 6 sigma project should be a team effort.

Upper management support is critical.

It is also critical that the people working within the process be involved too since they are our process experts. Their involvement also helps insure buy-in as the project progresses and at project completion.

DefineMarch April 2003

Measure May Sept 2003

AnalyzeOct March 2004

Improve Apr Jun 2004

ControlJun 2004 +

Project Schedule

Number of Street Lights (Approx) 27,500

Number of Alley Lights (Approx) 3,100

Energy Expense, 2003 $453,367

Department Expense, 2003 $2,743,285

Estimated Value of Network $8,500,000

Street Lighting System

Ive included this slide in order to give a general impression of the size of our street lighting network.

We maintain approximately 30,000 lights, and budget nearly 3 million/year for the operation of the St Lighting Dept.

The book value of the street lighting network is estimated at $8,500,000

Material Needs Determined

Materials Ordered

Materials Delivered

Materials Stored

Materials Depleted

Process Map

At a 50,000 foot level, our process is fairly straight forward we determine our material needs, buy the materials, store the materials, and finally use the materials.

Obviously, the process is more complex than this, but a simple process map is an excellent tool to help frame the project, and it is a good starting point used to begin figuring out the most important factors that drive the process.

Cause and effect matrix:

Important Factors: demand, lead time, order interval, level of safety stock

Cause and Effect Matrix

C&E Matrix (2)

Rating of Importance to Customer99710109326

123456789101112131415

Process StepProcess InputsGel TimeViscosityCleanlinessColorHomogeneityConsistencyDigets TimeTemperatureSolidsTotal

1CompoundingScales Accuracy982119118

2CompoundingPreheating DICY TK111111111

3CompoundingDMF Load Accuracy381118138

4CompoundingDMF Cleanliness114212111

5CompoundingDMF Raw Materials111112111

6CompoundingDICY Load Accuracy971119112

7CompoundingDICY Envir. Factors853118112

80

90

100

110

120

130

140

150

160

170

180

190

200

0

Total2882799180703512118138000000

Lower Spec

Target

Upper Spec

&A

Page &P

Cause and Effect Matrix

This table provides the initial input to the FMEA. When each of the output variables (requirements) are not correct, that represents potential "EFFECTS". When each input variable is not correct, that represents "Failure Modes".

1. List the Key Process Output Variables2. Rate each variable on a 1-to-10 scale to importantance to the customer3. List Key Process Input Variables4. Rate each variables relationship to each output variable on a 1-to-10 scale 5. Select the top input variables to start the FMEA process; Determine how each selected input varable can "go wrong" and place that in the Failure Mode column of the FMEA.

C&E Matrix

Rating of Importance to Customer10864

126789101112131415

Process StepProcess Inputsminimize total inventory investment levelcost effective purchasesresponsiveness (maintenance, construction, lights out)pleasing aestheticsTotal

1needs determinedbudget availability510510195needs determined

2materials deliveredtime, order to delivery105100190materials delivered

3materials depletedmaterials requested by maintenance crews10555185materials depleted

4materials depletedmaterials requested by construction contractors51051159materials depleted

5needs determinedpast usage considerations10510145needs determined

6needs determinedstaff inventory experience10510145needs determined

7cip5555135

8available space11055135

9council selections15510115

10knockdowns5155103

11vendor1151083

12estimating expertise (eng)510578

13time, po issuance to po receipt505075

14vendor selected (beyond project scope)requisition0

15potential bidders0

16purchasing workload0

17state purchasing statutes0

18process schedules0

190

20materials deliveredmanpower to unload and stock155075

21shipping method151159

22unanticipated arterial road projects115147

23proactive replacement program115147

24light out contacts115043

25materials storedfacilities upgrade program101535

26contractor cuts105035

270

28materials depleted0

290

300

310

320

330

340

350

360

370

Total800608504256000000000

Lower Spec

Target

Upper Spec

&A

Page &P

Cause and Effect Matrix

This table provides the initial input to the FMEA. When each of the output variables (requirements) are not correct, that represents potential "EFFECTS". When each input variable is not correct, that represents "Failure Modes".

1. List the Key Process Output Variables2. Rate each variable on a 1-to-10 scale to importantance to the customer3. List Key Process Input Variables4. Rate each variables relationship to each output variable on a 1-to-10 scale 5. Select the top input variables to start the FMEA process; Determine how each selected input varable can "go wrong" and place that in the Failure Mode column of the FMEA.

A tool used to narrow the process variables is called the cause and effect matrix. The team ranked the process inputs (shown in yellow) against various process outputs (shown in green) in order to determine the most important factors believed to drive the process. This slide shows the 6 most important factors identified.

How can our process fail?

As ranked with FMEA, failures can result if:

historical usage data is not maintained and monitored

inventory usage is not recorded by maintenance crews

material usage is not recorded on work order tickets

expensive in-stock items are substituted for out of

stock items

vendor states inaccurate delivery time on bid

poor analysis done in budgeting cycle

How Can Our Processes Fail?

Budget vs actual: 2000 - 2003

In May of 2003, the inventory budget was reduced by $100,000 in anticipation of project success. Approximately $80k less was spent on materials than modified budget would have allowed for 03.

Estimated savings to date (March 04), $180,000.

Budget Vs. Actual Costs 2000-2003

Chart1

837669832395

668400636865

633274584287

522585450538.86

5003002004

budget

expenditure

Sheet1

yearbudgetactual

2000837669832395

2001668400636865

2002633274584287inventory 10/20 ($)639709165

"new" items ($)2658151

year20002001200220032004

budget$837,669$668,400$633,274$522,585$500,300

expenditure$832,395$636,865$584,287$450,539

Sheet1

budget

expenditure

Sheet2

Sheet3

This slide shows the the level of funds expended on street light inventory over the last few years. The 03 budget was originally approved for $100,000 more that is shown here since In anticipation of project success, the budget was decreased by $100,000.

Updated from esum on 2-9-04, still had $25,164.96 encumbered and included in chart above.

Actual material expense 2001 $636,865

Actual material expense 2002 $584,287

Actual material expense 2003 thru 9-30 $320,199

Total $1,541,351

Historical usage captured

Jan 01 Sept 03, valued at $966,547

Current inventory value as of

Sept 30, 2003 $630,806

Has all data been captured?

*Note that recorded usage does not total the amount expended

Has All the Data Been Captured?

Has all data been captured?

All recorded historical usage was collected

Historical inventory values were not kept. It can not be determined if some usage was not recorded or if the differences shown on the previous slide are attributable to changes in the value of inventory on January 1, 2001 as compared to the value of inventory on September 30, 2003.

What can be done to insure data integrity, going forward?

Work orders Re-lamping lists Proactive maintenance files Capital project files

Has All the Data Been Captured?

Low hanging fruit data source

Implementation of an inventory tracking database Material usage recorded as it leaves warehouse Information readily available to all staff Facilitates data collection going forward Improves accuracy of recorded usage

Accomplished without adding any additional tasks not already being performed by warehouse personnel

Data base implementation should help address 2 factors identified in the C&E matrix: availability of historical data and reliance on staff experience

Low Hanging Fruit-Data Source

The first major improvement attributable to this project was the implementation and use of an inventory tracking data base, effective October 12th, 2003.

With this new tool, inventory data (historical and current item status) will be readily available to warehouse personnel, clerical staff, managers, and engineers. This improvement was accomplished without adding any additional tasks that were not already being performed by staff.

With an inventory system in place, it is now possible to track material usage as materials leave the warehouse, rather than by reconstructing material usage based on reported work activities. This should greatly facilitate ease of data collection and insure the accuracy of recorded usage as this project moves forward

The data base should also decrease reliance on staff experience as the order triggering mechanism.

Modified Microsoft Office Template:

In-house expertise without added cost

Key problem poor record keeping

Key Problem-Poor Record Keeping

Here is a screen shot of a portion of the database. Although the Microsoft template is not highly sophisticated, it should work very well for our purposes.

Prior to using this data base, inventory levels were simply tracked with a spreadsheet. As item amounts changed, the spreadsheet was over-written, and previous copies were not saved as separate files. This made historical data collection very difficult.

Inventory Turn: A common method of measuring inventory

management

Calculated by dividing the average inventory level ($) into the annual inventory usage ($)

2003 material usage $450,539

2003 average inventory value $682,441

*For 2003, Street light inventory turned only .66 times

*For 2004, Street light inventory turned 1.124 times

Inventory Turn-Annual Inventory Use

A very common inventory metric is called inventory turns. Regarding street light inventory, this metric was not previously computed or known prior to this project.

Although inventory turns varies across industries, .66 seems extremely low. Over the long term, this metric should increase substantially, and will be useful to measure process efficiency.

Not in TQM presentation: Begin to track inventory turns, with the goal of increasing the ratio of usage to average inventory value. Danger of a too fast turn rate is running out of materials

.66 seems very low. Begin to track inventory turns, with the goal of increasing the ratio of usage to average inventory value. Danger of a too fast turn rate is running out of materials

At the start of this project, 165 items were identified with specific item numbers

Shortly after implementation of database, an additional 88 inventory numbers were assigned to materials not previously carried on the books

*Value of items not previously accounted for totaled $26,581 or 4% of inventory on hand as of Oct 21, 2003

Inventory Records-Inventory Accuracy

Chart3

639709

26581

Sheet1

yearbudgetactual

2000837669832395

2001668400636865

2002633274584287inventory 10/20 ($)639709165

"new" items ($)2658151

year20002001200220032004

budget$837,669$668,400$633,274$522,585$500,300

expenditure$832,395$636,865$584,287

Sheet1

budget

expenditure

Sheet2

Sheet3

At the beginning of this project, a total of 165 items kept in inventory were identified with a specific inventory item number. Upon introduction of a data base and after demonstrating the resulting ease of inventory tracking, an additional 51 inventory numbers were assigned to materials not previously carried on the books.

As of October 21st, 2003, the value of these items that are now accounted for totaled $26,581, or 4% of the value of all inventory on hand as of that same date.

Inventory accuracy -

Accuracy Benefits

Enhance Customer ServiceReduce Stock OutsProduction is not jeopardized

Inventory Accuracy

Historically, a physical inventory count has was conducted once per year, generally in December, resulting in the reconciliation of records to counted stock on hand. Accuracy statistics were not maintained, and the existing stock record was over-written with updated counts. Identifying causes of errors was impossible, since errors could have occurred at any time over the 12 month period.

Class A consists of 22 item categories

Class B consists of 28 item categories

Class C consists of 203 item categories

Reclassified A, B, C items by looking at new data, Oct 1 03 through Sept 15 04

Now we have:

30 A items 9% of items, 80% of $

44 B items, 14% of items, 15% of $

239 C items 77% items, 5% $

322 total items 100%

Inventory Accuracy

Past: Historically, a physical inventory count was conducted once per year. Accuracy statistics were not maintained, and the existing stock record was over-written with updated counts.

Effective 2004, implemented Cycle Counting

Current: Inventory items are now differentiated and counted multiple times per year, depending on usage-value (inventory classification)

Class A items, count 6 times/year 80% of $ spent over 33 months

Class B items, count 2 times/year 15% of $ spent over 33 months

Class C items, count 1 time /year 5% of $ spent over 33 months

Inventory accuracy rates

After annual 2003 inventory count, error rates were established. An error occurs whenever an item count differs from the inventory record, while considering +/- 5% as an acceptable tolerance.

Class A items 27.3% error rate

Class B items 35.7% error rate

Class C items 26.1% error rate

All items 27.3% error rate, 12-31-03

Error rates will be tracked with control charts, going forward. If the use of the inventory data base and the implementation of cycle counting fail to improve this error rate, this problem could be investigated further as a Green Belt project.

Inventory Accuracy Rates

Talked with Dave Pepper. 2 problems cited after Feb 29 count: maintenance crews used 2 poles from yard, charged to a work order, but did not inform warehouse personnel. Maintenance crews also returned some fixtures without informing warehouse personnel. Maintenance crew used a 35 aluminum pole, but incorrectly recorded usage as a 30 aluminum pole. Thought we had 42 300v photo cells on hand, but after counting we discovered we only have 24.

Apr 04 count: 14-105 defective (says 50, we have 60 Pepper, no idea why), 14-120 def (says 129, we have 120, no idea why), 16-210 def (says 3, we have 4, no idea why). In general, Pepper claims that Ed not writing down usage. According to Pepper, all the maintenance guys hit Ed up at the same time, which causes him problems. Pepper mentioned Ed repairs stuff, puts back on shelf, doesnt record. I think problem is too much access to stuff by non-city personnel

Although within the allowable 5% tolerance, bulb count was off by 26

3 yrs of expense, 165 item numbers

Most of the project effort and analysis will be directed at the 22 items comprising 80% of the expenditures. These top 22 items are designated as class A items.

Show Me the Money!

3777.txt

Pareto: code8KJPareto: code;; HMF V1.24 TEXT;; (Microsoft Win32 Intel x86) HOOPS 5.00-34 I.M. 3.00-34(Selectability "windows=off,geometry=on")(Visibility "on")(Color_By_Index "Geometry,Face Contrast" 1)(Color_By_Index "Window" 0)(Window_Frame "off")(Window -1 1 -1 1)(Camera (0 0 -5) (0 0 0) (0 1 0) 2 2 "Stretched")

;; (Driver_Options "no backing storeno borderno control areadisable input,no do;; uble-bufferingno double bufferingno first color,no force black-and-whiteno f;; orce black and whiteno gamma correctionno landscape orientation,light scalin;; g=0,no locater transform,no number of colors,no special eventsno subscreen c;; reatingno subscreen movingno subscreen resizingsubscreen stretchingno output;; format,no update interrupts,no use colormap id")(Edge_Pattern "---")(Edge_Weight 1)(Face_Pattern "solid")(Heuristics "no related selection limit")(Line_Pattern "---")(Line_Weight 1)(Marker_Size 0.421875)(Marker_Symbol ".")(Text_Font "name=arial-gdi-vector,no transforms,rotation=follow path")(User_Options "mtb aspect ratio=0.671554,graphicsversion=6,worksheettitle=\"for pareto top items.MTW\",optiplot=0,builtin=0,statguideid=10051,toplayer=0,angle=0,arrowdir=0,arrowstyle=0,polygon=0,isdata=0,textfollowpath=1,ldfill=0,solidfill=0,3d=0,usebitmap=0,canbrush=0,brushrows=0,columnlengthx=2,columnlengthy=2,columnlengthz=0,light scaling=0.00000,sessionline=63")(Segment "include" ())(Front ((Segment "figure1" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=0") (Front ((Segment "region" ((Front ((Segment "figure box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 0) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "solid") (Line_Pattern "---") (Line_Weight 1) (User_Options "solidfill=1") (Segment "" ( (Polygon ((-0.99995 -0.99995 0) (0.99995 -0.99995 0) (0.99995 0.99995 0) (-0.99995 0.99995 0) (-0.99995 -0.99995 0))))))) (Segment "data box" ( (Visibility "faces=off") (Color_By_Index "Face" 1) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "/") (Line_Pattern "---") (Line_Weight 1) (User_Options "ldfill=1,solidfill=1") (Segment "" ( (Polygon ((-0.699965 -0.39998 0) (0.699965 -0.39998 0) (0.699965 0.699965 0) (-0.699965 0.699965 0) (-0.699965 -0.39998 0)))))) ) (Segment "legend box" ()) (Segment "legend" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=1") (Front ((Segment "bar1" ()))))))))) (Segment "object" ((Front ((Segment "frame" ( (Window_Pattern "clear") (Window -1 1 -1 1) (Front ((Segment "tick" ((Front ((Segment "set1" ( (Color_By_Index "Face Contrast,Line,Text,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1) (Text_Alignment "**") (Text_Font "name=arial-gdi-vector,size=0.02362 sru") (Text_Path 0.86602 0.499997 0) (Segment "" ()) (Segment "" ()) (Segment "major" ( (Segment "" ((Polyline ((0.339983 -0.39998 0) (0.339983 -0.439978 0) )))) (Segment "" ((Polyline ((-0.339983 -0.39998 0) (-0.339983 -0.439978 0))))))) (Segment "minor" ()))) (Segment "set2" ( (Color_By_Index "Face Contrast,Line,Text,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1) (Text_Alignment "*>") (Text_Font "name=arial-gdi-vector,size=0.03125 sru") (Segment "" ( (Selectability "polygons=on!,text=off") (Visibility "polygons=off") (Text_Alignment "v") (Text_Font "name=arial-gdi-vector,size=0.03374 sru") (Segment "" ()) (Segment "" ( (Polyline ((-0.679966 -0.39998 0) (0.679966 -0.39998 0))) )))) (Segment "set3" ( (Color_By_Index "Face Contrast,Line,Text,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1) (Text_Alignment "*>") (Text_Font "name=arial-gdi-vector,size=0.03374 sru") (Segment "" ()) (Segment "" ( (Polyline ((-0.679966 -0.39998 0) (0.679966 -0.39998 0))) )))) (Segment "set4" ( (Color_By_Index "Face Contrast,Line,Text,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1) (Text_Alignment "*>") (Text_Font "name=arial-gdi-vector,size=0.03374 sru") (Segment "" ()) (Segment "" ( (Polyline ((-0.679966 -0.39998 0) (0.679966 -0.39998 0))) )))) (Segment "set5" ( (Color_By_Index "Face Contrast,Line,Text,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1) (Text_Alignment "*") (Text_Path 0 1 0) (User_Options "angle=90,polygon=3,linect=1,charct=7") (Polygon ((0.829815 -3.24122e-3 0) (0.829815 0.287968 0) (0.922904 0.287968 0) (0.922904 -3.24122e-3 0))) (Renumber (Text 0.899632 -3.24122e-3 0 "Percent") 1 "L") (Segment "raw" ((Visibility "off")(Renumber (Text 0 0 0 "Percent") 1 "L"))))) (Segment "" ( (Polyline ((0.699965 -0.379981 0) (0.699965 0.679966 0))) )))) (Segment "set6" ( (Color_By_Index "Face Contrast,Line,Text,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1) (Text_Alignment "*>") (Text_Font "name=arial-gdi-vector,size=0.04218 sru") (Text_Path 6.12303e-17 1 0) (Segment "" ( (Selectability "polygons=on!,text=off") (Visibility "polygons=off") (Text_Alignment "v>") (Text_Path 0 1 0) (User_Options "angle=90,polygon=3,linect=1,charct=5") (Polygon ((-0.929952 0.0319236 0) (-0.929952 0.252803 0) (-0.836863 0.252803 0) (-0.836863 0.0319236 0))) (Renumber (Text -0.860135 0.0319236 0 "Count") 1 "L") (Segment "raw" ((Visibility "off")(Renumber (Text 0 0 0 "Count") 1 "L"))))) (Segment "" ( (Polyline ((-0.699965 -0.379981 0) (-0.699965 0.679966 0))))))))))))))) (Segment "data" ( (Window_Pattern "clear") (Window -0.68 0.68 -0.38 0.68) (User_Options "isdata=1,viewinfigurecoord=1") (Front ((Segment "bar1" ((Visibility "faces=on")(Color_By_Index "Face Contrast,Line,Edge" 7)(Color_By_Index "Face" 4)(Edge_Weight 2)(Face_Pattern "solid")(Line_Weight 2)(User_Options "ldfill=0")(Segment "" ( (Visibility "faces=on") (Color_By_Index "Face Contrast,Line,Edge" 7) (Color_By_Index "Face" 4) (Edge_Weight 2) (Face_Pattern "solid") (Line_Weight 2) (User_Options "ldfill=0,solidfill=1") (Segment "" ( (Polygon ((0 -0.99995 0) (0.99995 -0.99995 0) (0.99995 -0.745994 0) (0 -0.745994 0) (0 -0.99995 0)))))))(Segment "" ( (Visibility "faces=on") (Color_By_Index "Face Contrast,Line,Edge" 7) (Color_By_Index "Face" 4) (Edge_Weight 2) (Face_Pattern "solid") (Line_Weight 2) (User_Options "ldfill=0,solidfill=1") (Segment "" ( (Polygon ((-0.99995 -0.99995 0) (0 -0.99995 0) (0 0.650761 0) (-0.99995 0.650761 0) (-0.99995 -0.99995 0))))))))))) )))))) (Segment "labels" ((Window_Pattern "clear")(Window -1 1 -1 1))) (Segment "annotation" ((Window_Pattern "clear")(Window -1 1 -1 1))))))) (Segment "figure2" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=0") (Front ((Segment "region" ((Front ((Segment "figure box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 0) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "solid") (Line_Pattern "---") (Line_Weight 1) (User_Options "solidfill=1") (Segment "" ( (Polygon ((-0.99995 -0.99995 0) (0.99995 -0.99995 0) (0.99995 0.99995 0) (-0.99995 0.99995 0) (-0.99995 -0.99995 0))))))) (Segment "data box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 1) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "/") (Line_Pattern "---") (Line_Weight 1) (User_Options "ldfill=1,solidfill=1") (Segment "" ( (Polygon ((-0.699965 -0.39998 0) (0.699965 -0.39998 0) (0.699965 0.699965 0) (-0.699965 0.699965 0) (-0.699965 -0.39998 0)))))) ) (Segment "legend box" ()) (Segment "legend" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=1") (Front ((Segment "symbol1" ()) (Segment "connect1" ()))))))))) (Segment "object" ((Front ((Segment "frame" ( (Window_Pattern "clear") (Window -1 1 -1 1) (Front ((Segment "tick" ()) (Segment "grid" ()) (Segment "reference" ()) (Segment "axis" ()))))) (Segment "data" ( (Window_Pattern "clear") (Window -0.68 0.68 -0.38 0.68) (User_Options "isdata=1,viewinfigurecoord=1") (Front ((Segment "symbol1" ((Segment "points" ( (Color_By_Index "Marker" 1) (Marker_Size 0.421875) (Marker_Symbol "@") (Segment "" ( (Color_By_Index "Marker" 2) (Marker_Size 0.210938) (Marker_Symbol "@") (Marker 0.499975 0.904717 0))) (Segment "" ( (Color_By_Index "Marker" 2) (Marker_Size 0.210938) (Marker_Symbol "@") (Marker -0.499975 0.650761 0))))))) (Segment "connect1" ((Color_By_Index "Face Contrast,Line,Edge" 2)(Edge_Pattern "---")(Edge_Weight 2)(Line_Pattern "---")(Line_Weight 2)(Segment "" ( (Polyline ((-0.499975 0.650761 0) (0.499975 0.904717 0))))))) )))))))) (Segment "labels" ((Window_Pattern "clear")(Window -1 1 -1 1))) (Segment "annotation" ((Window_Pattern "clear")(Window -1 1 -1 1))))))) (Segment "annotation" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "toplayer=1") (Front ((Segment "text1" ((Color_By_Index "Text" 1)(Text_Alignment "^*")(Text_Font "name=arial-gdi-vector,size=0.05061 sru")(Segment "" ( (Selectability "polygons=on!,text=off") (Visibility "polygons=off") (Text_Alignment "v

Ranked listing of high expense items (class A) Jan 01-Sept 03

Sheet1

33 month expenseitem #description% total costcumulative %

$206,919.7214-120100w HPS Town & Country fixture21.41%21.41%

$85,283.3714-105150w cobrahead fixture8.82%30.23%

$77,538.3416-20030' embedded aluminum pole8.02%38.25%

$49,688.5213-503100w HPS bulb5.14%43.39%

$47,504.4016-40016' black metal pole4.91%48.31%

$42,803.2014-151100w alley fixture4.43%52.74%

$40,236.5617-205#6 3 conductor uf 600v tray cable4.16%56.90%

$31,433.0413-504150w HPS bulb3.25%60.15%

$27,884.2216-20930' aluminum bolt down pole2.88%63.04%

$19,740.0016-21035' aluminum pole single bracket2.04%65.08%

$16,926.9018-1161 1/2" pe tubing1.75%66.83%

$16,213.0014-122250w HPS Town & Country fixture1.68%68.51%

$15,702.5716-10035' wood pole1.62%70.13%

$12,751.8314-203250w HPS power door1.32%71.45%

$12,324.0014-106250w cobrahead fixture1.28%72.73%

$12,060.0016-410Fort Wayne standard post1.25%73.98%

$12,056.6514-131100w PMA fixture1.25%75.22%

$11,711.1016-291transformer base, small1.21%76.43%

$11,546.0014-205750w power door1.19%77.63%

$10,873.5014-107400w HPS fixture with photo cell1.12%78.75%

$10,831.5916-21335' bronze painted aluminum pole1.12%79.87%

$9,088.0014-500300v photo cell0.94%80.82%

$8,531.0016-21230' big top pole0.88%81.70%

$8,246.0016-413Broadway post0.85%82.55%

$7,820.0015-2016' mast arm aluminium pole0.81%83.36%

$7,573.5314-110400w HPS decashield luminaire0.78%84.14%

$6,687.1213-505250w HPS bulb0.69%84.84%

$6,652.6015-10612' arm 2" (wood pole) SF0.69%85.52%

$5,964.0016-21135' aluminium pole double bracket0.62%86.14%

$5,902.0014-204400w HPS power door0.61%86.75%

$5,880.0014-54360amp MR relay (South Bend controller)0.61%87.36%

$5,791.8017-204#4 3 conductor tray cable0.60%87.96%

$5,763.6817-1002 conductor aluminium wire service drop0.60%88.56%

$5,202.0014-103400w HPS cutoff fixture0.54%89.09%

$5,120.0015-2028' arm aluminium for aluminium pole0.53%89.62%

$5,070.0014-5410.52%90.15%

$4,925.9413-5060.51%90.66%

$4,472.2815-1020.46%91.12%

$4,213.8216-2060.44%91.56%

$3,979.1916-9110.41%91.97%

$3,858.0016-4120.40%92.37%

$3,713.4915-2030.38%92.75%

$3,332.0014-2120.34%93.10%

$2,964.0014-4510.31%93.40%

$2,720.1014-2020.28%93.68%

$2,689.0514-4070.28%93.96%

$2,588.7516-2040.27%94.23%

$2,460.0014-5450.25%94.48%

$2,441.6014-5440.25%94.74%

$2,171.6815-1040.22%94.96%

$1,938.0014-1020.20%95.16%

$1,922.2016-4020.20%95.36%

$1,824.8417-1040.19%95.55%

$1,788.0017-1020.18%95.73%

$1,677.9514-1180.17%95.91%

$1,445.0016-2200.15%96.06%

$1,398.4014-2000.14%96.20%

$1,391.9420-2400.14%96.35%

$1,384.0014-5420.14%96.49%

$1,253.2815-2060.13%96.62%

$1,247.3416-2050.13%96.75%

$1,187.5015-1010.12%96.87%

$1,185.4513-3200.12%96.99%

$1,135.4014-1520.12%97.11%

$1,127.8818-1020.12%97.23%

$1,048.0014-1450.11%97.34%

$1,021.9314-1040.11%97.44%

$1,019.1615-2040.11%97.55%

$1,000.5813-3190.10%97.65%

$988.0014-7000.10%97.75%

$980.0014-1420.10%97.86%

$910.0014-4500.09%97.95%

$864.8213-7040.09%98.04%

$836.0016-2140.09%98.13%

$776.0016-1030.08%98.21%

$720.0016-4110.07%98.28%

$710.2214-1230.07%98.35%

$695.0014-1600.07%98.43%

$692.5014-2180.07%98.50%

$663.9518-1120.07%98.57%

$614.7913-5070.06%98.63%

$613.0016-2920.06%98.69%

$587.5217-4020.06%98.75%

$574.0014-1440.06%98.81%

$566.9314-7010.06%98.87%

$482.4214-4130.05%98.92%

$480.0015-1000.05%98.97%

$474.2514-5200.05%99.02%

$436.8014-2110.05%99.07%

$431.2019-6000.04%99.11%

$411.6013-3220.04%99.15%

$395.6014-5900.04%99.19%

$380.0014-1460.04%99.23%

$367.0814-5020.04%99.27%

$344.3718-4330.04%99.31%

$324.4519-6010.03%99.34%

$321.9017-5060.03%99.37%

$305.3716-4010.03%99.40%

$288.3217-1080.03%99.43%

$286.6016-1020.03%99.46%

$275.0114-5570.03%99.49%

$268.7813-3180.03%99.52%

$267.8117-3510.03%99.55%

$251.3217-5050.03%99.57%

$250.8017-2020.03%99.60%

$231.2817-5030.02%99.62%

$224.4817-1070.02%99.65%

$208.8914-5050.02%99.67%

$200.2017-4100.02%99.69%

$175.6820-2210.02%99.71%

$173.8817-5000.02%99.73%

$164.4818-5250.02%99.74%

$158.0818-7050.02%99.76%

$123.9017-4010.01%99.77%

$108.8914-1190.01%99.78%

$107.0117-3700.01%99.79%

$100.5017-4090.01%99.80%

$97.7920-2010.01%99.82%

$97.6214-4550.01%99.83%

$95.7917-3330.01%99.84%

$90.600.01%99.84%

$88.4013-3170.01%99.85%

$84.1318-7200.01%99.86%

$83.5820-2020.01%99.87%

$77.1917-3320.01%99.88%

$75.0013-7050.01%99.89%

$73.2817-2070.01%99.89%

$66.7018-7060.01%99.90%

$64.9617-3000.01%99.91%

$60.6917-1050.01%99.91%

$57.6217-3050.01%99.92%

$57.3215-2050.01%99.93%

$54.0017-2030.01%99.93%

$42.2417-5070.00%99.94%

$40.4018-2020.00%99.94%

$40.0417-3010.00%99.94%

$36.0014-5530.00%99.95%

$35.8818-2250.00%99.95%

$35.0320-2150.00%99.96%

$34.4017-3060.00%99.96%

$33.2817-3630.00%99.96%

$32.7817-3620.00%99.97%

$32.0513-3210.00%99.97%

$28.7017-4030.00%99.97%

$26.5114-5550.00%99.97%

$25.9217-3350.00%99.98%

$23.1217-3710.00%99.98%

$22.1417-3340.00%99.98%

$20.7920-2000.00%99.98%

$19.6017-3070.00%99.99%

$18.6314-5560.00%99.99%

$17.7617-3090.00%99.99%

$13.3217-3020.00%99.99%

$11.9816-9030.00%99.99%

$11.8416-9040.00%99.99%

$10.7818-4140.00%100.00%

$10.6617-3310.00%100.00%

$10.2017-3400.00%100.00%

$7.7017-3720.00%100.00%

$7.0317-3080.00%100.00%

$5.6017-2080.00%100.00%

$3.5717-3040.00%100.00%

$1.5014-5860.00%100.00%

$1.0514-5540.00%100.00%

$0.7017-3390.00%100.00%

$966,546.54total material expense

Sheet2

Sheet3

Poles used: Jan 2001 Sept 2003

Poles Used: Jan 2001-Sept 2003

Sheet1

$ ranking 33 months33 month expensedescription% of 33 month expense33 month usage maintenance33 month usage capitalquantity on hand 10-1-03

3$77,538.3430' embedded aluminum pole8.02%13294152

5$47,504.4016' black metal pole4.91%43425444

9$27,884.2230' aluminum bolt down pole2.88%334513

10$19,740.0035' aluminum pole single bracket2.04%281918

13$15,702.5735' wood pole1.62%457277

16$12,060.00Fort Wayne standard post1.25%10827

18$11,711.10transformer base, small1.21%9436

21$10,831.5935' bronze painted aluminum pole1.12%21029

23$8,531.0030' big top pole0.88%20011

24$8,246.00Broadway post0.85%705

29$5,964.0035' aluminium pole double bracket0.62%12018

39$4,213.8220' aluminum bolt down (Tower Heights)0.44%13013

41$3,858.00S Calhoun pole0.40%307

47$2,588.7512' aluminum bolt down0.27%1907

52$1,922.2022' fiberglass embedded0.20%14056

56$1,445.0050' aluminum 2-piece0.15%102

61$1,247.348' arm 4'upsweep wood pole0.13%6019

74$836.0035' alum box fixture0.09%2048

75$776.0024' alum bolt down0.08%205

76$720.00casing for FW standard0.07%800

82$613.00T base large0.06%106

98$305.3716' fiberglass silver0.03%3027

100$286.6040' wood pole0.03%106

$0.0035' alum big top0.00%003

Sheet2

Sheet3

Fixtures used: Jan 2001 Sept 2003

Fixtures Used: Jan 2001-Sept 2003

Sheet1

$ ranking 33 months33 month expensedescription% of 33 month expense33 month usage maintenance33 month usage capitalquantity on hand 10-1-03

1$206,919.72100w HPS Town & Country fixture21.41%988425240

2$85,283.37150w cobrahead fixture8.82%633240119

6$42,803.20100w alley fixture4.43%59315108

12$16,213.00250w HPS T/C (discontinue)1.68%2502

15$12,324.00250w cobrahead fixture1.28%592096

17$12,056.65100w PMA fixture1.25%6703

20$10,873.50400w HPS fixture with photo cell1.12%363084

26$7,573.53400w HPS box fixture0.78%37023

34$5,202.00400w HPS cutoff fixture0.54%151911

51$1,938.00150w cutoff0.20%19017

55$1,677.95250w T/C0.17%5024

64$1,135.40100w HPS downtown fixture0.12%205

66$1,048.00250 W MH fixture S Calhoun0.11%204

67$1,021.93250w cutoff fixture0.11%7027

71$980.00150w HPS wallmount fixture0.10%7011

77$710.22250w HPS box fixture0.07%3018

78$695.00bollards for mall0.07%103

84$574.00150w HPS downtown fixture0.06%1010

93$380.00150w HPS ornamental fixture0.04%1012

115$108.89special fixture type 5 t/c0.01%100

$0.00250w Hadco W Central0.00%000

$0.00250w wall mount0.00%009

$0.00175w MH Allen Co fixture0.00%001

$0.00welcome marker fixtures0.00%007

City of Fort Wayne:30 on order

City of Fort Wayne:250 on order

Sheet2

Sheet3

Even though there were 240 14-120 T/C fixtures on hand, 305 were ordered in November, to be used for maintenance activities. I would estimate that 240 fixtures is a 6 month supply

Even though there were 119 14-105 cobra head fixtures on hand, 200 were ordered in November, to be used for maintenance activities. I would estimate that 119 fixtures is a 6 month supply

Presented this information to George Cook, who discussed further with his staff. George let the order stand because Houser convinced him that the quantities would be needed before 04 price agreements would be in place. It should be interesting to look at actual usage from the order date to the new 04 pc date, and see if they would have run out or not

As of 1-14-04, they have 192 of 14-105 cobra head fixtures in stock, and 04 price agreements are not yet established. So, they would have run out if order had not been placed, and my estimate of historical usage is too low.

Bulbs used: Jan 2001 Sept 2003

Bulbs Used: Jan 2001-Sept 2003

Sheet1

$ ranking 33 months33 month expensedescription% of 33 month expense33 month usage maintenance33 month usage capitalquantity on hand 10-1-03

4$49,688.52100w HPS bulb5.14%50123332173

8$31,433.04150w HPS bulb3.25%351602028

27$6,687.12250w HPS bulb0.69%74801378

37$4,925.94400w HPS lamp0.51%5510645

63$1,185.45250w MH0.12%1030137

69$1,000.58175w MH0.10%9408

73$864.82750w HPS lamp0.09%22022

81$614.791000w HPS lamp0.06%23027

91$411.60400w MH0.04%42036

102$268.78189w Edison base0.03%129080

122$88.4069w Edison bulb0.01%121067

126$75.00special bulb 3100.01%1018

143$32.05150w MH lamp0.00%4031

$0.001000w MH lamp0.00%0011

$0.0070w MH lamp0.00%0013

$0.00100w MH lamp0.00%0024

City of Fort Wayne:48 on order

City of Fort Wayne:48 on order

Sheet2

Sheet3

I included this slide to show bad decision making in the extreme. Even though enough inventory existed, additional bulbs were purchased anyhow, mainly due to the unavailability of good information.

Not in TQM: Asked Dave why 96 bulbs ordered, and he said because Ted told him too. Dave (our inventory specialist) did not feel the order was necessary at this time. In Daves words, too many Chiefs and not enough Indians.

Differences in usage values and dollars spent each month could mean that not all material usage was recorded or more inventory is being purchased than is being used.

Total $ value of materials used = $1,034,998

Total $ expended = $1,577,055

*34 months examined

Purchase Decisions Made On Usage

Chart1

42709.3632400.36

26277.3322103.76

20126.3592683.16

18686.3364644.69

25531.7336980.32

68213.3249952.63

41541.32116859.47

29571.0631353.12

27760.8924431.22

32057.0190295.63

25867.8140519.12

25335.9834641.08

29887.714182.63

23834.510479.4

15354.5959152.05

23155.7973513.53

18750.77153476.72

20763.2968655.23

62642.9425277.76

42413.8340937.07

27331.3588622.22

20173.089540.46

14874.422411.34

15349.4548074.38

144815410.34

17355.27114966.55

25161.0828564.24

29952.2441534.12

38792.358888.44

37582.0418167.89

34879.225087.12

28990.386449.58

47179.5641100.36

62415.0835698.73

material usage

invoices paid

Sheet1

monthmat used (y)spending (x)year

jan42709.3632400.362001

feb26277.3322103.762001

mar20126.3592683.162001

apr18686.3364644.692001

may25531.7336980.322001

jun68213.3249952.632001

jul41541.32116859.472001

aug29571.0631353.122001

sep27760.8924431.222001

oct32057.0190295.632001

nov25867.8140519.122001

dec25335.9834641.082001

jan29887.714182.632002

feb23834.510479.42002

mar15354.5959152.052002

apr23155.7973513.532002

may18750.77153476.722002

jun20763.2968655.232002

jul62642.9425277.762002

aug42413.8340937.072002

sep27331.3588622.222002

oct20173.089540.462002

nov14874.422411.342002

dec15349.4548074.382002

jan144815410.342003

feb17355.27114966.552003

mar25161.0828564.242003

apr29952.2441534.122003

may38792.358888.442003

jun37582.0418167.892003

jul34879.225087.122003

aug28990.386449.582003

sep47179.5641100.362003

oct62415.0835698.732003

nov63953.052003

1034998.381577054.72

Sheet1

material usage

invoices paid

Sheet2

Sheet3

* With monthly measurements, there does not appear to be a significant linear correlation between material usage and the amount of funds spent for inventory acquisition.

Correlation of Funds and Usage

3783.txt

Fitted Line Plot: mat used (y) versus spending (x)YXFitted Line Plot: mat used (y) versus spending (x);; HMF V1.24 TEXT;; (Microsoft Win32 Intel x86) HOOPS 5.00-34 I.M. 3.00-34(Selectability "windows=off,geometry=on")(Visibility "on")(Color_By_Index "Geometry,Face Contrast" 1)(Color_By_Index "Window" 0)(Window_Frame "off")(Window -1 1 -1 1)(Camera (0 0 -5) (0 0 0) (0 1 0) 2 2 "Stretched")

;; (Driver_Options "no backing storeno borderno control areano double-buffering;; no double bufferingno force black-and-whiteno force black and whiteno gamma ;; correctionno text preference,light scaling=0,no color consolidation,no pen s;; peed,no special eventssubscreen=(-0.999902,0.324318,-0.99987,0.185028),no su;; bscreen creatingno subscreen movingno subscreen resizingsubscreen stretching;; no output format,no update interrupts,no use colormap id,no write mask,no ha;; rdcopy resolution")(Edge_Pattern "---")(Edge_Weight 1)(Face_Pattern "solid")(Heuristics "no related selection limit")(Line_Pattern "---")(Line_Weight 1)(Marker_Size 0.421875)(Marker_Symbol ".")(Text_Font "name=arial-gdi-vector,no transforms,rotation=follow path")(User_Options "mtb aspect ratio=0.671554,graphicsversion=6,worksheettitle=\"mat use vs expend.MTW\",optiplot=0,builtin=0,statguideid=2151,toplayer=0,angle=0,arrowdir=0,arrowstyle=0,polygon=0,isdata=0,textfollowpath=1,ldfill=0,solidfill=0,3d=0,usebitmap=0,canbrush=0,brushrows=34,columnlengthx=4,columnlengthy=4,columnlengthz=0,light scaling=0.00000,sessionline=2562")(Segment "include" ())(Front ((Segment "figure1" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=0") (Front ((Segment "region" ((Front ((Segment "figure box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 0) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "solid") (Line_Pattern "---") (Line_Weight 1) (User_Options "solidfill=1") (Segment "" ( (Polygon ((-0.99995 -0.99995 0) (0.99995 -0.99995 0) (0.99995 0.99995 0) (-0.99995 0.99995 0) (-0.99995 -0.99995 0))))))) (Segment "data box" ( (Visibility "faces=off") (Color_By_Index "Face" 1) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "/") (Line_Pattern "---") (Line_Weight 1) (User_Options "ldfill=1,solidfill=1") (Segment "" ( (Polygon ((-0.649967 -0.59997 0) (0.499975 -0.59997 0) (0.499975 0.59997 0) (-0.649967 0.59997 0) (-0.649967 -0.59997 0))))))) (Segment "legend box" ()) (Segment "legend" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=1") (Front ((Segment "symbol1" ()))))))))) (Segment "object" ((Front ((Segment "frame" ( (Window_Pattern "clear") (Window -1 1 -1 1) (Front ((Segment "tick" ((Front ((Segment "set1" ( (Color_By_Index "Face Contrast,Line,Text,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1) (Text_Alignment "^*") (Text_Font "name=arial-gdi-vector,size=0.02362 sru") (Segment "" ( (Text 0.434068 -0.669966 0 "150000"))) (Segment "" ( (Text 0.0808259 -0.669966 0 "100000"))) (Segment "" ( (Text -0.272417 -0.669966 0 " 50000"))) (Segment "" ( (Text -0.625659 -0.669966 0 " 0"))) (Segment "major" ( (Segment "" ((Polyline ((0.434068 -0.59997 0) (0.434068 -0.639968 0) )))) (Segment "" ((Polyline ((0.0808259 -0.59997 0) (0.0808259 -0.639968 0))))) (Segment "" ((Polyline ((-0.272417 -0.59997 0) (-0.272417 -0.639968 0))))) (Segment "" ((Polyline ((-0.625659 -0.59997 0) (-0.625659 -0.639968 0))))))))) (Segment "set2" ( (Color_By_Index "Face Contrast,Line,Text,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1) (Text_Alignment "*>") (Text_Font "name=arial-gdi-vector,size=0.02362 sru") (Segment "" ( (Text -0.719964 0.579971 0 "70000"))) (Segment "" ( (Text -0.719964 0.388009 0 "60000"))) (Segment "" ( (Text -0.719964 0.196047 0 "50000"))) (Segment "" ( (Text -0.719964 4.08502e-3 0 "40000"))) (Segment "" ( (Text -0.719964 -0.187877 0 "30000"))) (Segment "" ( (Text -0.719964 -0.379839 0 "20000"))) (Segment "" ( (Text -0.719964 -0.571801 0 "10000"))) (Segment "major" ( (Segment "" ((Polyline ((-0.649967 0.579971 0) (-0.689965 0.579971 0 ))))) (Segment "" ((Polyline ((-0.649967 0.388009 0) (-0.689965 0.388009 0 ))))) (Segment "" ((Polyline ((-0.649967 0.196047 0) (-0.689965 0.196047 0 ))))) (Segment "" ((Polyline ((-0.649967 4.08502e-3 0) (-0.689965 4.08502e-3 0))))) (Segment "" ((Polyline ((-0.649967 -0.187877 0) (-0.689965 -0.187877 0))))) (Segment "" ((Polyline ((-0.649967 -0.379839 0) (-0.689965 -0.379839 0))))) (Segment "" ((Polyline ((-0.649967 -0.571801 0) (-0.689965 -0.571801 0))))))))))))) (Segment "grid" ()) (Segment "reference" ()) (Segment "axis" ((Front ((Segment "set1" ( (Color_By_Index "Face Contrast,Line,Text,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1) (Text_Alignment "^*") (Text_Font "name=arial-gdi-vector,size=0.04218 sru") (Segment "" ( (Selectability "polygons=on!,text=off") (Visibility "polygons=off") (Text_Alignment "v") (Text_Font "name=arial-gdi-vector,size=0.04218 sru") (Text_Path 6.12303e-17 1 0) (Segment "" ( (Selectability "polygons=on!,text=off") (Visibility "polygons=off") (Text_Alignment "v>") (Text_Path 0 1 0) (User_Options "angle=90,polygon=3,linect=1,charct=18") (Polygon ((-0.917652 -0.23675 0) (-0.917652 0.428571 0) ( -0.847835 0.428571 0) (-0.847835 -0.23675 0))) (Renumber (Text -0.859471 -0.23675 0 "material usage ($)") 1 "L") (Segment "raw" ((Visibility "off")(Renumber (Text 0 0 0 "material usage ($)") 1 "L"))))) (Segment "" ( (Polyline ((-0.649967 -0.579971 0) (-0.649967 0.579971 0))))))))))))))) (Segment "data" ( (Window_Pattern "clear") (Window -0.63 0.48 -0.58 0.58) (User_Options "isdata=1,viewinfigurecoord=1") (Front ((Segment "symbol1" ((Segment "points" ( (Color_By_Index "Marker" 1) (Marker_Size 0.421875) (Marker_Symbol "@") (User_Options "canbrush=1,brushsetup=0,grouping=0") (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 34) (Marker -0.53776 0.748913 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 33) (Marker -0.469 0.244664 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 32) (Marker -0.910086 -0.357341 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 31) (Marker -0.927429 -0.162439 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 30) (Marker -0.760918 -0.0729838 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 29) (Marker -0.242567 -0.032928 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 28) (Marker -0.463479 -0.325507 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 27) (Marker -0.628578 -0.484079 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 26) (Marker 0.471277 -0.742427 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 25) (Marker -0.923315 -0.837557 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 24) (Marker -0.380225 -0.808813 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 23) (Marker -0.96149 -0.824536 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 22) (Marker -0.87074 -0.649166 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 21) (Marker 0.135928 -0.41225 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 20) (Marker -0.471079 0.0869334 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 19) (Marker -0.670413 0.756454 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 18) (Marker -0.118241 -0.629632 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 17) (Marker 0.96149 -0.69624 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 16) (Marker -0.0563979 -0.550448 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 15) (Marker -0.239212 -0.808643 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 14) (Marker -0.858788 -0.527985 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 13) (Marker -0.938943 -0.327642 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 12) (Marker -0.551223 -0.47829 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 11) (Marker -0.476399 -0.460688 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 10) (Marker 0.157229 -0.255845 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 9) (Marker -0.681189 -0.398033 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 8) (Marker -0.593077 -0.338122 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 7) (Marker 0.495373 0.058056 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 6) (Marker -0.356315 0.940816 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 5) (Marker -0.521446 -0.471812 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 4) (Marker -0.169293 -0.698373 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 3) (Marker 0.187621 -0.650713 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 2) (Marker -0.710816 -0.447135 0))) (Segment "" ( (Marker_Size 0.210938) (Marker_Symbol "@") (User_Value 1) (Marker -0.579746 0.0967145 0))))))))))))))) (Segment "labels" ((Window_Pattern "clear")(Window -1 1 -1 1))) (Segment "annotation" ((Window_Pattern "clear")(Window -1 1 -1 1)(Front ((Segment "line1" ( (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 2) (Line_Pattern "---") (Line_Weight 2) (Segment "" ( (Polyline ((-0.608623 -0.14457 0) (-0.565933 -0.149358 0) (-0.523243 -0.154145 0) (-0.480553 -0.158932 0) (-0.437863 -0.16372 0) (-0.395173 -0.168507 0) (-0.352482 -0.173295 0) (-0.309792 -0.178082 0) (-0.267102 -0.18287 0) (-0.224412 -0.187657 0) (-0.181722 -0.192445 0) (-0.139032 -0.197232 0) ( -0.0963413 -0.20202 0) (-0.0536512 -0.206807 0) (-0.010961 -0.211595 0) (0.0317292 -0.216382 0) (0.0744194 -0.22117 0) (0.11711 -0.225957 0) (0.1598 -0.230745 0) (0.20249 -0.235532 0) (0.24518 -0.24032 0) (0.28787 -0.245107 0) (0.33056 -0.249895 0) (0.373251 -0.254682 0) (0.415941 -0.25947 0) (0.458631 -0.264257 0))))))) (Segment "line2" ( (Color_By_Index "Face Contrast,Line,Edge" 2) (Edge_Pattern "...") (Edge_Weight 2) (Line_Pattern "...") (Line_Weight 2) (Segment "" ( (Polyline ((-0.608623 2.97027e-3 0) (-0.565933 -0.0136276 0) ( -0.523243 -0.0293622 0) (-0.480553 -0.0439868 0) (-0.437863 -0.0571932 0) (-0.395173 -0.0686224 0) (-0.352482 -0.0779022 0 ) (-0.309792 -0.0847221 0) (-0.267102 -0.0889221 0) (-0.224412 -0.0905497 0) (-0.181722 -0.0898425 0) (-0.139032 -0.0871495 0 ) (-0.0963413 -0.0828439 0) (-0.0536512 -0.077265 0) (-0.010961 -0.0706935 0) (0.0317292 -0.0633503 0) (0.0744194 -0.0554047 0) (0.11711 -0.0469853 0) (0.1598 -0.0381895 0) (0.20249 -0.0290915 0) (0.24518 -0.0197485 0) (0.28787 -0.0102047 0) (0.33056 -4.94697e-4 0) (0.373251 9.35417e-3 0) (0.415941 0.01932 0) (0.458631 0.0293852 0))))))) (Segment "line3" ( (Color_By_Index "Face Contrast,Line,Edge" 2) (Edge_Pattern "...") (Edge_Weight 2) (Line_Pattern "...") (Line_Weight 2) (Segment "" ( (Polyline ((-0.608623 -0.29211 0) (-0.565933 -0.285087 0) (-0.523243 -0.278928 0) (-0.480553 -0.273878 0) (-0.437863 -0.270247 0) (-0.395173 -0.268393 0) (-0.352482 -0.268688 0) ( -0.309792 -0.271443 0) (-0.267102 -0.276818 0) (-0.224412 -0.284765 0) (-0.181722 -0.295047 0) (-0.139032 -0.307315 0) ( -0.0963413 -0.321196 0) (-0.0536512 -0.33635 0) (-0.010961 -0.352496 0) (0.0317292 -0.369414 0) (0.0744194 -0.386935 0) ( 0.11711 -0.404929 0) (0.1598 -0.4233 0) (0.20249 -0.441973 0) (0.24518 -0.460891 0) (0.28787 -0.48001 0) (0.33056 -0.499295 0 ) (0.373251 -0.518719 0) (0.415941 -0.538259 0) (0.458631 -0.5579 0))))))) (Segment "line4" ( (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1))) (Segment "line5" ( (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Line_Pattern "---") (Line_Weight 1))))))))))) (Segment "figure2" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=0") (Front ((Segment "region" ((Front ((Segment "figure box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 0) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "solid") (Line_Pattern "---") (Line_Weight 1) (User_Options "solidfill=1") (Segment "" ( (Polygon ((-0.99995 -0.99995 0) (0.99995 -0.99995 0) (0.99995 0.99995 0) (-0.99995 0.99995 0) (-0.99995 -0.99995 0))))))) (Segment "data box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 1) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "/") (Line_Pattern "---") (Line_Weight 1) (User_Options "ldfill=1,solidfill=1") (Segment "" ( (Polygon ((-0.299985 0.559972 0) (0.299985 0.559972 0) (0.299985 0.839958 0) (-0.299985 0.839958 0) (-0.299985 0.559972 0)))))) ) (Segment "legend box" ()) (Segment "legend" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=1") (Front ((Segment "symbol1" ()))))))))) (Segment "object" ((Front ((Segment "frame" ( (Window_Pattern "clear") (Window -1 1 -1 1) (Front ((Segment "tick" ()) (Segment "grid" ()) (Segment "reference" ()) (Segment "axis" ()))))) (Segment "data" ( (Window_Pattern "clear") (Window -0.28 0.28 0.58 0.82) (User_Options "isdata=1,viewinfigurecoord=1") (Front ((Segment "symbol1" ((Segment "points" ( (Color_By_Index "Marker" 1) (Marker_Size 0.421875) (Marker_Symbol "@") (Segment "" ()) (Segment "" ()))))))))))))) (Segment "labels" ((Window_Pattern "clear")(Window -1 1 -1 1)(Front ((Segment "symbol1" ( (Text_Alignment "v*") (Text_Font "size=0.02531 sru") (User_Options "isdata=0") (Segment "" ( (Text 0 0.668966 0 "S = 13907.1 R-Sq = 1.2 % R-Sq(adj) = 0.0 %"))) (Segment "" ( (Text 0 0.780961 0 "mat used (y) = 32355.5 - 0.0412731 spending (x)"))))))))) (Segment "annotation" ((Window_Pattern "clear")(Window -1 1 -1 1))))))) (Segment "figure3" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=0") (Front ((Segment "region" ((Front ((Segment "figure box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 0) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "solid") (Line_Pattern "---") (Line_Weight 1) (User_Options "solidfill=1") (Segment "" ( (Polygon ((-0.99995 -0.99995 0) (0.99995 -0.99995 0) (0.99995 0.99995 0) (-0.99995 0.99995 0) (-0.99995 -0.99995 0))))))) (Segment "data box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 1) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "/") (Line_Pattern "---") (Line_Weight 1) (User_Options "ldfill=1,solidfill=1") (Segment "" ( (Polygon ((0.499975 -0.79996 0) (0.99995 -0.79996 0) (0.99995 0 0 ) (0.499975 0 0) (0.499975 -0.79996 0))))))) (Segment "legend box" ()) (Segment "legend" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=1") (Front ((Segment "symbol1" ()))))))))) (Segment "object" ((Front ((Segment "frame" ( (Window_Pattern "clear") (Window -1 1 -1 1) (Front ((Segment "tick" ()) (Segment "grid" ()) (Segment "reference" ()) (Segment "axis" ()))))) (Segment "data" ( (Window_Pattern "clear") (Window 0.52 0.98 -0.78 -0.02) (User_Options "isdata=1,viewinfigurecoord=1") (Front ((Segment "symbol1" ((Segment "points" ( (Color_By_Index "Marker" 1) (Marker_Size 0.421875) (Marker_Symbol "@") (Segment "" ()) (Segment "" ()))))))))))))) (Segment "labels" ((Window_Pattern "clear")(Window -1 1 -1 1)(Front ((Segment "symbol1" ( (Text_Alignment "v*") (Text_Font "size=0.02531 sru") (User_Options "isdata=0") (Segment "" ( (Text 0.80746 -0.469977 0 "95% CI"))) (Segment "" ( (Text 0.813209 -0.374981 0 "Regression"))))))))) (Segment "annotation" ((Window_Pattern "clear")(Window -1 1 -1 1))))))) (Segment "figure4" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=0") (Front ((Segment "region" ((Front ((Segment "figure box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 0) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "solid") (Line_Pattern "---") (Line_Weight 1) (User_Options "solidfill=1") (Segment "" ( (Polygon ((-0.99995 -0.99995 0) (0.99995 -0.99995 0) (0.99995 0.99995 0) (-0.99995 0.99995 0) (-0.99995 -0.99995 0))))))) (Segment "data box" ( (Visibility "polygons=off,lines=off") (Color_By_Index "Face" 1) (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 1) (Face_Pattern "/") (Line_Pattern "---") (Line_Weight 1) (User_Options "ldfill=1,solidfill=1") (Segment "" ( (Polygon ((0.499975 -0.79996 0) (0.99995 -0.79996 0) (0.99995 0 0 ) (0.499975 0 0) (0.499975 -0.79996 0))))))) (Segment "legend box" ()) (Segment "legend" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "viewinfigurecoord=1"))))))) (Segment "object" ((Front ((Segment "frame" ( (Window_Pattern "clear") (Window -1 1 -1 1) (Front ((Segment "tick" ()) (Segment "grid" ()) (Segment "reference" ()) (Segment "axis" ()))))) (Segment "data" ( (Window_Pattern "clear") (Window 0.52 0.98 -0.78 -0.02) (User_Options "isdata=1,viewinfigurecoord=1") (Front ((Segment "symbol1" ((Segment "points" ( (Color_By_Index "Marker" 1) (Marker_Size 0.421875) (Marker_Symbol "@") (Segment "" ()) (Segment "" ()) (Segment "" ()) (Segment "" ()))))) (Segment "connect1" ((Color_By_Index "Face Contrast,Line,Edge" 1)(Edge_Pattern "---")(Edge_Weight 1)(Line_Pattern "---")(Line_Weight 1)(Front ((Segment "group1" ( (Color_By_Index "Face Contrast,Line,Edge" 1) (Edge_Pattern "---") (Edge_Weight 2) (Line_Pattern "---") (Line_Weight 2) (Segment "" ( (Polyline ((-0.79996 0.099995 0) (-0.299985 0.099995 0))) )))) (Segment "group2" ( (Color_By_Index "Face Contrast,Line,Edge" 2) (Edge_Pattern "...") (Edge_Weight 2) (Line_Pattern "...") (Line_Weight 2) (Segment "" ( (Polyline ((-0.79996 -0.149992 0) (-0.299985 -0.149992 0))))))))))))))))))) (Segment "labels" ((Window_Pattern "clear")(Window -1 1 -1 1))) (Segment "annotation" ((Window_Pattern "clear")(Window -1 1 -1 1))))))) (Segment "annotation" ( (Window_Pattern "clear") (Window -1 1 -1 1) (User_Options "toplayer=1") (Front ((Segment "text1" ((Color_By_Index "Text" 1)(Text_Alignment "^*")(Text_Font "name=arial-gdi-vector,size=0.05061 sru")(Segment "" ( (Text 0 0.979951 0 "Regression Plot")))))))))))

Additional bidding expectations were requested of vendors bidding on poles, mast arms, and fixtures

Informed all bidders of our goal to minimize inventory carrying costsRequired bidders to list best price at minimum quantity levels, price at lesser quantity order levels, and worst price if only 1 unit orderedRequired vendors to list the length of time between order placement and order delivery (lead time)

*This information will be critical in determining optimal inventory levels and reorder points

Changes to Bidding Specifications

Each year, the City does blanket bids for poles, fixtures, and bulbs. For 04, we made sure that all bidders were aware or our goal of minimizing inventory, required that they list multiple prices based on order quantities, and required that they inform us of the amount of time we could expect between order placement and order receipt. These requirements were not previously detailed in the bid specs.

Beginning in 2000, Street Light Engineering began testing the longevity of various bulb manufacturers

Purchase Decision: What Bulb is the Most Cost Effective to Purchase?

Sheet1

ORIGINAL COST1 YR2 YR3 YR4 YR

G. E.$ 8.97$ 8.97$ 9.72$ 10.84$ 16.07

PHILIPS$ 8.95$ 9.18$ 10.10$ 11.70$ 12.62

SYLVANIA$ 10.15$ 10.15$ 10.67$ 10.93$ 11.19

Sheet1

G. E.

PHILIPS

SYLVANIA

Test Time

Cost per Lamp

Sheet2

Sheet3

Sheet1

STREET LAMP FAILURE RATE TEST PROJECT

FAILURES AND RATES

TESTFAILEDFAILEDFAILEDFAILED

MFGRSAMPLEBY YR 1BY YR 2BY YR 3BY YR 4

G.E.2400%28.3%520.8%1979.2%

PHILIPS3912.60%512.8%1230.8%1641.0%

SYLVANIA3900%25.1%37.7%410.3%

COST PER LAMP TO MAINTAIN ORIGINAL SAMPLE SIZE

ORIG $1 ST YR $2 ND YR $3 RD YR $4 TH YR $

MFGRPER LPPER LPPER LPPER LPPER LP

G.E.$ 8.97$ 8.97$ 9.72$ 10.84$ 16.07

PHILIPS$ 8.95$ 9.18$ 10.10$ 11.70$ 12.62

SYLVANIA$ 10.15$ 10.15$ 10.67$ 10.93$ 11.19

FORMULA:(ORIG SAMPLE SIZE + NUMBER REPLACED) X ORIG COST PER LAMP

ORIG SAMPLE SIZE

NOTE:DOES NOT INCLUDE RELAMPING LABOR COST

Figure C

Sheet2

Sheet3

Sylvania bulbs are the most cost effective for the City

Without the cost/lifespan analysis, former procedures would have directed us to purchase Phillips bulbs

The addition of bulb replacement labor costs to the analysis, would further expand the cost differences

Low Price Best Price

Chart2

8.978.9510.15

8.979.1810.15

9.7210.110.67

10.8411.710.93

16.0712.6211.19

G. E.

PHILIPS

SYLVANIA

Test Time

Cost per Lamp

Sheet1

ORIGINAL COST1 YR2 YR3 YR4 YR

G. E.$ 8.97$ 8.97$ 9.72$ 10.84$ 16.07

PHILIPS$ 8.95$ 9.18$ 10.10$ 11.70$ 12.62

SYLVANIA$ 10.15$ 10.15$ 10.67$ 10.93$ 11.19

Sheet1

000

000

000

000

000

G. E.

PHILIPS

SYLVANIA

Test Time

Cost per Lamp

Sheet2

Sheet3

Material ordering procedures were tightened

for all inventory purchases

order form initiated by warehouse personnel or engineers order requires sign-off by department director order requires sign-off by finance manager

Changes to Ordering Procedures

First time the procedure was used, an order of photo cells was reduced from 500 (4-5 month supply) originally requested to 200 ordered

1-13-04 George implemented a new procedure whereby warehouse must route order requests to him, and he and I sign off. Pepper requested to order 500 photo cells, and when I called him, he said he assumes that we need to order a bunch to get some sort of price break from Graybar, but he didnt know where the cutoff occurs for a price break. George and I met with Sales Rep from Graybar, and found that there is no minimum quantity needed to order for the same price. This is an in stock item for Graybar and the warehouse can get the items in a couple of days. George reduced order from 500 (4-5 months of stock) to 200.

Prior to this project, quantifiable data was not available. The department director had to rely solely on staff experience and staff recommendations.

Purchase/Replenish Pull System

Implemented a widely recognized inventory system, developed by Toyota Motor Corp, known as Kanban

Kanban is an empirically driven method of both signaling the need for inventory and controlling inventory levels

Kanban Japanese word for sign

Purchase/Replenish Pull System

Partly with information supplied by our Master Black Belt Roger Hirt, and partly as a result of a seminar I attended, I found that there is a widely recognized method of inventory control known as Khan Ban.

Khan ban is a method of signaling when to replenish a depleted item, and is a method to determine how much of item should be ordered.

4 Variables for an Effective Purchase/Pull System

Demand the average monthly usage amount

Lead Time length of time expired between placing order and receiving goods, measured in monthly units

Order Interval how often orders are anticipated, in monthly units

Safety Stock amount of inventory to be held to compensate for demand variability and/or lead time variability

Purchase/Replenish Pull System

For an effective purchase/replenishment pull system, 4 variables must be identified:

Demand the average monthly usage amount

Lead time length of time expired between placing order and receiving goods, measured in monthly units

Order interval how often orders are anticipated, in monthly units

Safety stock amount of inventory to be held to compensate for demand variability and/or lead time variability

Up to this point, most of my analysis has revolved around determining demand.

Historical Demand

Estimate Future Costs By Analyzing Past Material Usage

4 Uses of Materials

Maintenance Repair to Damaged Facilities Re-lamping Activities Based on Light-Out Lists Proactive Replacement of Aged Facilities and/or Bulbs Capital Construction Project

Capital projects are known prior to construction. By meeting minimum requirements, capital materials can be ordered on a project by project basis. On appropriate projects, capital needs will now be segregated from other material needs.

Recall that some of the historical data might be suspect

Historical Demand

An important factor in determining optimal inventory levels and reorder points is demand for each particular item carried in inventory. I intend to estimate future demand by analyzing past material usage.

St Lighting uses materials in 4 ways

Demand analysis

Demand Analysis = Compare means, standard deviations,

and medians for each item

Pre data base implementation Post data base implementation

Demand Analysis

If similar, conclude historical usage was accurately collected

use data collected since January 2001 for a specific item

If different, conclude historical usage was not accurately collected

use data collected since October 2003 for a specific item

Due to various problems with historically recorded usage prior to the data base implementation, I could not be sure of the validity of my recorded data. To estimate demand, I compared the means, medians, and standard deviations of the most important inventory items pre-database and post database, and used the data that resulted in highest level of safety stock.

Ideally, the means and standard deviations for these populations will be similar so that as many data points as possible can be used to establish a mean and standard deviation for each item.

Recorded demand prior to data base implementation will be compared to recorded demand since data base implementation. Ideally, the means and standard deviations for these populations will be similar so that as many data points as possible can be used to establish a mean and standard deviation for each item.

If the demand metrics are not similar enough or if the gathered historical data usage seems understated, average demand will be computed only on recorded usage after data base implementation to avoid under estimating demand.

21.41% of material expense

100 HPS Town & Country Fixture

Mean for periods prior to Oct 03 = 29.9 Mean for periods post Oct 03 = 44.4

Should all data be used to estimate monthly demand?

Difference in means

Difference in medians

Similarity in Standard Deviations

Inconclusive to not under estimate, use data since Oct 1, 2003

100 HPS Town & Country Fixture

(Continued)

8.82% of material expense

150w Cobra Head Fixture

(Continued)

Large difference in means

Large difference in medians

Similar standard deviations

Conclusion Including data prior to Oct 03 might result in under estimation of usage

150w Cobra Head Fixture

Demand Analysis Lots of Variability

100w Alley Fixture

100w alley fixture (continued)

Similar Means

Similar Medians

Similar Standard Deviations

Conclusion Including data back to Jan 01 should not result in under estimated demand

This methodology was used to analyze demand for all class A and class B items

100w Alley Fixture

(Continued)

lead time

Lead Time - Time Expired From Order

Initiation to Receipt of Goods

Lead Time

stated in bid specifications for poles, fixtures, bulbs

include City staff time for requisition preparation and sign-off

Another important variable in the kan ban formula is lead time. Ive not had many opportunities to analyze this variable yet since so few orders have been placed up to this point due to the excessive amount of inventory on hand.

lead time analysis

Lead Time Analysis

Lead time analysis. Even as of 10-12-04, still not enough data points to adequately analyze lead time. This example, for Graybar, considers all items ordered from Graybar. There is considerable variation in lead time, possibly dependant on item ordered.

lead time analysis

Conclusion:

Lead Time Analysis must be done at the item level not the vendor level

Lead Time Analysis

Lead Time on Graybar Items

There is too much variation in lead time between different items

Graybar lead time means, if separated by item number ordered

Order Interval- Frequency of Placing Orders for Each part

If ordering often, can order less quantities per order. But there are overhead and administrative costs for

initiating order, processing requisition, purchase order contacting the vendor and placing the order receiving the goods, re-stocking the shelves processing the payable

Order Interval

Order class A items frequently, and order class C items infrequently

Trade-off between the level of inventory quantities carried per item and the frequency of ordering the item.

Pareto analysis used to establish order frequencies. Class A items are few but are 80% of the dollars in inventory. Class C items are numerous, but only a small part of total inventory value.

The 3rd variable in the khan ban formula is order interval. Again, due to lack of orders, Ive not had much of a chance to adequately analyze this variable.

For the time being, Ive estimated order intervals based on what feels right, recognizing that there are definite costs in ordering to frequently. In general, we intend to order class A items frequently and class C items much less frequently.

Preferred Products: Poles, Mast Arms, Transformer Bases

Poles/Mast Arms: charged a 13 14 % premium for orders totaling less than $11,000 / order, effective 2004. Various types of poles/mast arms can be mixed per minimum $11,000 purchase.

Preferred Products purchases, October 2003 - February 2004 averaged $7,429 per month. To avoid paying an average premium of $1,003 per month (if the interval is 1), the order interval should be at least 2 months.

This results in an inventory that is larger than would be necessary otherwise, for items that are relatively expensive.

But in effect, the excess inventory carried is returning approximately 13.5% in avoided expense.

Recall the Cause & Effect Matrix the process output cost effective purchases was ranked at 8 out of 10 in importance to the customer.

Order Interval

GE Supply Fixtures & Power Doors

Order Requirements: Lots of 25

Orders less than the per fixture price increases by 10%, or on average, $9 more per item.

Again, the result is inventory that is larger than would be necessary otherwise if cost effective purchasing is to be achieved.

But some fixtures used infrequently, anticipate paying premium charge.

Order Interval

Safety Stock

Safety Stock: inventory stock required to guard against

process variability demand variability lead time variability quality variability

Safety Stock Quantity: dependent on desired service level

service level 1, on average no stock outs 84% of the time service level 2, on average no stock outs 98% of the time service level 1, 1 standard deviation of safety stock carried service level 2, 2 standard deviations of safety stock carried

Safety Stock

High Service Levels Need More Inventory/Safety Stock

Safety stock is inventory stock required to guard against variability

One recognized empirical method for calculating safety stock utilizes the standard deviation how widely values are dispersed from the mean.

If a higher service level is desired, more safety stock becomes necessary. Since street lighting is not an absolutely critical service, a service level of 1 has been selected at this point. Once inventory levels are drawn down to more reasonable amounts, this decision can be re-visited.

Materials for capital projects are known in advance and ordered on a project by project basis. Capital projects are not impacted by the service level choice.

For the cause & effect matrix, process outcomes were ranked by the Division Director minimizing total inventory carried ranked at 10 (high) responsiveness to calls, light outs ranked at 6 (medium)

Street lights are not a critical service, so a service level of 1 will be used to establish inventory re-ordering points and optimal inventory levels.

Safety Stock= Standard Deviation * Service Level * (Lead Time ^ .7)

Safety Stock-Level of Service

Safety stock is inventory stock required to guard against variability

One recognized empirical method for calculating safety stock utilizes the standard deviation how widely values are dispersed from the mean

Kanban System

Establish inventory levels and calculate reorder points for each carried stock item.

Inventory Level/Order Triggering Formulas

Kmax = Max on-hand quantity for an item

(lead time * demand) + (order interval * demand) + safety stock

Kmin = Re-ordering trigger point for an item

(lead time * demand) + safety stock

Order more stock when (balance on hand + items on order) is less than the trigger point

Order Quantity = Kmax (balance on hand + items on order)

Once the 4 khan ban variables are adequately known and analyzed, all that is left is to plug the variables into the kanban formulas. Once completed, our staff will have a rational, empirically driven inventory decision making tool.

Inventory fills demand (after considering the acceptable level of risk of running out, i.e., safety stock).

Demand is monitored not controlled. Demand affects inventory level, inventory level does not affect demand.

Modified data base- demand transactions and values are monthly calculated and updated with changes.

Materials for capital projects are bid and supplied by the successful bidder, not by the Citys inventoried stock

Controlling the Xs (Demand)

Means and standard deviations of usage for each item computed on 24 data points only, to capture any changes in demand over time

The database was modified to better capture lead time changes. As orders are filled and the database updated, the received date is recorded and compared to other order dates. The difference in dates is converted to monthly units. The database prints lead time reports that list the average lead time value by item and by vendor to update lead time fields.

Controlling the Xs (Lead Time)

Means and standard deviations of usage for each item computed on 24 data points only, to capture any changes in demand over time

Control Plan Summary

Control Plan Summary

Sheet1

Product:Street Light InventoryCore Team:Dave Pepper, Materials CoordinatorDate (Orig):

Key Contact:Rick Orr, City of Fort WayneMike Houser, Superviosor St Light Operations

Phone:260.427.2788Doug Hilkey, Department DirectorDate (Rev):

Rick Orr, Finance Manager Public Works

ProcessProcess StepOutputInputProcess Specification (LSL, USL, Target)Cpk /DateMeasurement Technique%R&R P/TSample SizeSample FrequencyControl MethodReaction Plan

optimizing inventory levelseliminate need for capital project inventorylower optimal inventory level, reduced demand for stocked inventorychanged project bidding specs (contractor to supply materials)project bid specs require material acquisition by successful contractor for 100% of bid street light projectsIn 2004, materials for all bid projects supplied from inventory. For 2005, no materials from inventory for bid projectsexamine project bid specifications for inclusion of materials as pay items in project bidsreview 1 project for compliance with objectives1st project bid each construction season until procedures are embeddedremind project coordinators to include materials in project bidsreport to Dept Director and Division Director in the event warehouse issues materials for capital projects

optimizing inventory levelscalculate demand for stock itemsaccurate kmax, kmin valuesposted transactions100% of transactions posted daily for accurate kmax, kmin value re-calculations monthly. Demand calculations done for all 'a items' and 'b items' every month. Usage updated daily.demand means and standard deviations automatically calculated and kmax, kmin values automatically adjusted based on new metrics. Dtb auto runs on 1st of monthall usage examined for all itemsmonthlySupervisory oversight to insure timely posting of date. Automated calculations for all items when adequate data exists to make meaningful calculations.

optimizing inventory levelsre-calculate lead-times and update dtblead time re-analysis and determination, necessary for valid kman, kmin calculationslead time dtb summary reportFinance Manager to review lead time summary report and compare new values with values listed on product summary report. Update dtb with any changes. Perform task on a semi-annual basis.comparision of database values to lead time summary reportexamine 100% of 'a items' and 'b items'semi-annuallylead time calculations automated, based on database. Finance Manager's calendar flagged for semi-annual review

optimizing inventory levelsdetermine order intervals, item by itemoptimal order interval established for each inventory itemcomparison of the cost of order processing to the cost of carrying inventorynot yet establishednot yet determinednot yet determined

optimizing inventory levelsservice level determinationservice level determinedanalyze stock out reports for service level decisionstock out frequency rept reviewed by Division Director who makes determination if the costs of stock-outs exceed the costs of carrying more inventory2004 service level = 1Stock out reports sent to PW Finance Manager. Finance Manager compiles information and reports to Director. Stock out occurance rate tracked with control charts (see also material requistion process step)100% of stock out reportsannuallyreview stock outs and affects on operations. Compile stock out event control chartsat Division Director's discretion, service level can be changed

material acquisitionorder materialsupdated material req sheetMaterials Needed?' report, 'Product Summary' reportAt least weekly, run 'Materials Needed?' rept and 'Product Summary' rept to spot any items below kmin values. Process material requisition sheetas of dec '04, not yet initiatedcompare stock out occurances to total number of items ordered on a monthly basis, with stock out event classified as defective occurance. Chart quarterly performance100% of orders placedquarterlysupervisor to run rept to spot items below kmin values, to cross check for timely ordering. PW Finance Manager to monitor control charts.disciplinary action if warehouse staff doesn't order items when they should, as determined by Dept Director.

material acquisitionorder materialsmaterials order processedmaterial requisition sheetsubmit material req list to supervisor for review. Upon return, check dtb for each item, noting price, vendor, and any special ordering considerations. Confirm prices, and order goods. Input order information into the dtb by creating a purchase ordercompare material req sheet to dtb and ascertain completeness of form. Determine if request seems reasonable.random, as determined by supervisorsupervisory feedback to warehouse staffdisciplinary action if warehouse staff ignores job responsibilities, as determined by Dept Director

receive materialsreceiving goodsrestocked shelves, confirmed receipt of materialsorder arrivaloversee deliverly and unloading of materials. Verify reciept of all goods with packing slip. Update dtb.payable clerk to monitor for stock person's signature on packing slip100% of packing slipswhenever payments are processedpayable not to be processed until packing slip signed, report problems to Dept Directordisciplinary action, as determined by Dept Director

inventory record accuracycycle countingreconcile actual inventory to record of inventory, control chartscycle count worksheet, physical counting of selected stock itemsprint cycle counting worksheet, physically count stock, update dtb with correct counts, turn in completed worksheet to office staff for defective computations. Investigate causes for defectives. Update control charts and post to network drivethrough Nov '04, ave defective rate a items = 19.7%, b items = 27.6%, c items not yet computeddifferences between actual count and recorded count considered defective only if difference exceeds 5%100% of each item classification categorya items' count every other month, 'b items' count 2 times/year, 'c items' count once/yearP charts maintained for each of the 3 item classifications. Document and capture reasons cited as to causes for defectivesconsider green belt project to increase cycle counting accuracy rates. Investigate causes of errors, and take corrective actions

inventory record accuracycycle countingitem classifications determined for cycle countingcomparison of dollars spent on each item as compared to total dollars spent for all itemsrun usage report, calculate item classifications, compare new classifications to old classifications and update dtb accordingly (pareto analysis)Pareto items by spending level. Sort and order items according to dollars spent, highest to lowest. Add highest items until reaaching 80% of total dollars spent and designate these items as 'a items'. The next set of items totalling 15% of total dollartotal spent for all items in given time period. Minimum period is 1 yrannuallyPareto analysis conducted by Finance Manager PW. Pareto results turned over to warehouse