Inventory Management System

Project Report On SPARE PARTS INVENTORY MANAGEMENT SYSTEM Undertaken At COLGATE PALMOLIVE INDIA LIMITED EXECUTIVE SUMMARY The main problem faced by the production & stores is the poor availability of spare parts of various machines. The non-availability often leads to loss in production time. The objective of the current project is to design a user friendly inventory management system that would minimize stock outs and at the same time help in maintaining optimum inventory levels according to the criticality of the material.

For the identified electrical spares, classification has been done on the basis of criticality. For this, three parameters have been considered viz. , lead time, cost and effect on operations. For A category items line wise data has been collected to know the actual installed quantity of each item. All A category items would be identified with a 10 digit material code (e-code). Also, the manufacturer? s catalogue number has been mentioned for every item. This catalogue number has been hyperlinked with a soft copy of the specification sheet of the item.

This specification sheet has been taken from the website of the manufacturer. After identifying a preferred make with the item, the discount rates have been negotiated with 13 vendors. The negotiation happened in two stages and the discount rate at the end of second stage has been confirmed. For B & C category items for which e-code is not created a catalogue format has been prepared that is compatible with eBuy. The consumption for the spares for a period of ten months (Jan 2009 to Oct 2009) has been determined.

The inventory policy has been decided on the basis of classification of the material and at the same time due importance was given to the demand and installed quantity of the item. For A & B category items EOQ, (S-1,S) policies have been applied. For C category items the Reorder Quantity is selected in such a way that it would satisfy atleast 6 months demand. To monitor the A category items closely a Microsoft Excel based reordering tool has been designed that would help in monitoring the A category items inventory levels. A brief outline of the methodology adopted in the project is: ? ? ? ? ? ? ?

Study of the previous work done on spare parts management Data collection on spare consumption pattern Cataloguing of regularly procured spares Collection & Negotiation of rates with the vendors Inclusion of rates in the catalogue Designing an appropriate inventory policy Designing a system that would generate a report on spares to be procured Key Benefits of the project are: ? ? ? ? ? Visual control of inventory levels. Providing comprehensive information about the spares. Location wise tracking of critical spares. Weekly / bi-weekly updates on spares to be controlled. Reduced risk of stock outs Table of Contents 1.

COLGATE PALMOLIVE INDIA Ltd. ………………………………………………………………………………… 1 1. 1 ABOUT THE PLANT ……………………………………………………………………………………………………… 1 1. 2 PLANT LAYOUT …………………………………………………………………………………………………………… 2 1. 3 PLANT PROCESS LAYOUT ……………………………………………………………………………………………. 2 1. 4 PRODUCT PORTFOLIO OF COLGATE PALMOLIVE (I) LIMITED………………………………………. 2. PROBLEM DEFINITION ……………………………………………………………………………………………………. 5 2. 1 OBJECTIVE ………………………………………………………………………………………………………………….. 5 2. 2 SCOPE OF THE PROJECT ………………………………………………………………………………………………. 5 2. 3 NEED FOR THE PROJECT ……………………………………………………………………………………………… 2. 4 METHODOLOGY ………………………………………………………………………………………………………….. 6 2. 5 TOOLS USED ……………………………………………………………………………………………………………….. 7 3. LITERATURE REVIEW……………………………………………………………………………………………………… 8 3. 1 INVENTORY MANAGEMENT ………………………………………………………………………………………… 3. 2 SPARE PARTS MANAGEMENT ……………………………………………………………………………………… 9 4. MODELING …………………………………………………………………………………………………………………….. 12 4. 1 IDENTIFYING THE MATERIALS ………………………………………………………………………………….. 13 4. 2 MATERIAL CLASSIFICATION ……………………………………………………………………………………… 14 4. COMPONENT CRITICALITY ANALYSIS ………………………………………………………………………… 17 4. 4 DEFINING THE MATERIALS ……………………………………………………………………………………….. 18 4. 4. 1 Additional Information about the product ……………………………………………………………….. 18 4. 4. 2 Saving the Specification Sheet ………………………………………………………………………………… 18 4. 4. 3 Relating the specification sheet with the master ist………………………………………………….. 19 4. 4. 4 Creating Material Code for A Category Items ……………………………………………………………. 19 4. 4. 5 Existing Codes and creation on new codes ……………………………………………………………….. 20 4. 4. 6 Confirmation of creation of e-codes ………………………………………………………………………… 21 NITIE, Mumbai 1 4. 4. 7 Purchasing Non-Inventoried Items ………………………………………………………………………….. 2 4. 4. 8 Negotiation of Discount Rates ………………………………………………………………………………… 23 5. ANALYSIS ………………………………………………………………………………………………………………………. 25 5. 1. 1 Inventory Policy for A category items ………………………………………………………………………. 28 5. 1. 2 Parameters for calculating the EOQ’s ………………………………………………………………………. 29 5. . 3 Ordering Cost ……………………………………………………………………………………………………….. 29 5. 1. 4 Inventory Holding Cost ………………………………………………………………………………………….. 30 5. 1. 5 Policy Illustration…………………………………………………………………………………………………… 31 5. 2 REPORT GENERATION SYSTEM …………………………………………………………………………………. 31 6.

LIMITATIONS & FUTURE SCOPE ………………………………………………………………….. ……………….. 34 7. ACADEMIC CONTRIBUTION …………………………………………………………………………………………… 34 8. REFERENCES………………………………………………………………………………………………………………….. 35 9. APPENDIX ……………………………………………………………………………………………………………………… 37 9. INTRODUCTION TO CROSTON’S METHOD ………………………………………………………………….. 37 9. 2 INVENTORY POLICY…………………………………………………………………………………………………… 38 9. 3 EOQ PARAMETERS…………………………………………………………………………………………………….. 38 NITIE, Mumbai 2 1. COLGATE PALMOLIVE INDIA Ltd. 1. 1 ABOUT THE PLANT In the year 1991 a decision was taken by the management to Colgate Palmolive (I) Ltd to set up a manufacturing site in Goa.

Right then sales tax benefit was provided by the Central Government. Toothpaste was figured in the reserved list of industries which was popularly known as Small Scale Industries (SSI). The plant was set up in an 8000 sq meter land area under the name of Lumena Home Products Pvt. Limited. The manufacturing activity commenced in the year 1992 with 1 DOPP mixer and 1 each of GAB & GAN line with speed of 50 & 90 Tubes/ min respectively. In 1995 another manufacturing unit was set in the adjoining area in the name of Sterling Home Products Pvt. Limited.

Beside the Sales Tax benefits the central government also announced income tax benefit. In November 2002 Lumena Home Products Pvt. Ltd. and Sterling Home Products Pvt. Ltd. were renamed as Advanced Oral Care Products Pvt. Ltd. (AOC) and Professional Oral Care Products Pvt. Ltd. (POC). Both the plants were operated as separate legal identities in order that the benefits were availed smoothly. In the year 2002 Government relaxes the norm of SSI. In the subsequent year toothpaste was de-reserved from SSI. In June 2007, new project Flex was started.

On September 30th 2008, Commercial production was started at Flex FF. Manufacturing reached 1000 + MT from Flex FF in March 2009. On 1st July 2008, amalgamation of both the companies announced for operational synergy. AOC was amalgamated with POC on 1st July, 2009. In Year 2009 Goa Facility crossed 30000MT and 520 MM tubes in a calendar year. On Feb 1st 2010, POC Company became 100% subsidiary company of CP India. An application was filed in High Court, Goa on 17th Feb 2010 of company? s amalgamation with CP and on 1st May 2010 the amalgamation was complete. NITIE, Mumbai 1. 2 PLANT LAYOUT Fig: Top View of Colgate Palmolive (I) Ltd, Goa Facility 1. 3 PLANT PROCESS LAYOUT Manufacturing Process Weighing and mixing ? After transporting the raw materials into the factory, the ingredients are weighed. This ensures accuracy in the ingredients’ proportions. Then the ingredients are mixed together. ? All the ingredients are mixed together in the mixing vat (also known as mixer). The temperature and humidity of mixer are watched closely. This is important to ensuring that the mix comes together correctly. NITIE, Mumbai 2 Filling the tubes ?

Before tubes are filled with toothpaste, the tube itself passes under a blower and a vacuum to ensure cleanliness. Dust and particles are blown out in this step. The tube is capped, and the opposite end is opened so the filling machine can load the paste. ? After the ingredients are mixed together, the tubes are filled by the filling machine. To make sure the tube is aligned correctly, an optical device rotates the tube. Then the tube is filled by a descending pump. After it is filled, the end is sealed (or crimped) closed. The tube also gets a code stamped on it indicating where and when it was manufactured.

Packaging and shipment ? ? ? After tubes are filled, they are inserted into open paperboard boxes. Usually a dozen boxes are bundled together by a bundler. The boxes are cased and shipped to warehouses and stores. Fig: Process Flow Layout of Tooth Paste Manufacturing 1. 4 PRODUCT PORTFOLIO OF COLGATE PALMOLIVE (I) LIMITED The products of Colgate Palmolive (I) Ltd. can be classified in to Oral Care, Personal Care, Household Care and From the Dentist. The following screenshot would give a brief illustration of the products. NITIE, Mumbai 3 NITIE, Mumbai 4 2. PROBLEM DEFINITION . 1 OBJECTIVE Strengthen current spare parts management system by defining stock levels, reordering process and location of storage keeping in mind the procurement lead time and end use of spares. 2. 2 SCOPE OF THE PROJECT The scope of the project includes visual inventory control of electrical spares used in three focus factories viz. , AOC (Advanced Oral Care), POC (Professional Oral Care) & FLEX and utilities area. 2. 3 NEED FOR THE PROJECT The project takes its roots from the strong need to reduce the machine down time due to non availability or poor quality of spares.

For this to be accomplished a visual control system should be developed to monitor the stock levels. An attempt has been made to capture the loss time (in minutes) due to non-availability of spares. Loss time was defined as the time taken to fetch the spare part from any other focus factory or the time taken to repair and replace the spare part. With this definition, the loss time was captured for the month of March 2010 from all three focus factories. The figure shown below illustrates the same. NITIE, Mumbai 5 Fig: Excel file to capture the loss time due to non-availability of spares

There was 2225 minutes of loss time due to non-availability of spares in the stores. This would be around 37 hours. 2. 4 METHODOLOGY A brief outline of the methodology adopted in the project is given below Study of the previous work done on spare parts management ? ? ? ? ? ? Data collection on spare consumption pattern Cataloguing of regularly procured spares Collection & Negotiation of rates with the vendors Inclusion of rates in the catalogue Designing an appropriate inventory policy Designing a system that would generate a report on spares to be procured NITIE, Mumbai 6 2. 5 TOOLS USED ? ? Inventory Policies (EOQ, (S-1,S) ) Pivot Chart & Pivot Table MS Excel Macro NITIE, Mumbai 7 3. LITERATURE REVIEW Inventory is a list for goods and materials, or those goods and materials themselves, held available in stock by a business. 3. 1 INVENTORY MANAGEMENT Inventory management is primarily about specifying the size and placement of stocked goods. Inventory management is required at different locations within a facility or within multiple locations of a supply network to protect the regular and planned course of production against the random disturbance of running out of materials or goods.

The scope of inventory management also concerns the fine lines between replenishment lead time, carrying costs of inventory, asset management, inventory forecasting, inventory valuation, inventory visibility, future inventory price forecasting, physical inventory, available physical space for inventory, quality management, replenishment, returns and defective goods and demand forecasting. Balancing these competing requirements leads to optimal inventory levels, which is an on-going process as the business needs shift and react to the wider environment.

Other definitions of inventory management from across the web: ? ? ? Involves a retailer seeking to acquire and maintain a proper merchandise assortment while ordering, shipping, handling, and related costs are kept in check. Systems and processes that identify inventory requirements, set targets, provide replenishment techniques and report actual and projected inventory status. Handles all functions related to the tracking and management of material. This would include the monitoring of material moved into and out of stockroom locations and the reconciling of the inventory balances.

Also may include ABC analysis, lot tracking, cycle counting support etc. Management of the inventories, with the primary objective of determining/controlling stock levels within the physical distribution function to balance the need for product availability against the need for minimizing stock holding and handling costs. NITIE, Mumbai 8 Business inventory The reasons for keeping stock There are three basic reasons for keeping an inventory: ? ? ? Time – The time lags present in the upply chain, from supplier to user at every stage, requires that you maintain certain amount of inventory to use in this “lead time” Uncertainty – Inventories are maintained as buffers to meet uncertainties in demand, supply and movements of goods. Economies of scale – Ideal condition of “one unit at a time at a place where user needs it, when he needs it” principle tends to incur lots of costs in terms of logistics. So bulk buying, movement and storing brings in economies of scale, thus inventory. 3. 2 SPARE PARTS MANAGEMENT

Spare parts refer to the part requirements for keeping owned equipment in healthy operating condition by meeting repair and replacement needs imposed by breakdown, preventive and predictive maintenance. The spare part management function is critical from an operational perspective especially in asset intensive industries such as refineries, chemical plants, paper mills, etc as well as organizations owning and operating costly assets such as airlines, logistics companies, etc. “Service parts” on the other hand would refer to the demand for parts imposed by service requirements for equipment owned and operated by customers.

The service function is one that is outsourced to a third party aftermarket service provider or one managed by the manufacturer itself. Given the profitability of the spare parts business, manufacturers are looking towards the spares management function as a vital part of their operations, both from an internal operational perspective as well as from the service perspective. In this paper, we use the terms “Spare part” to connote both spare parts and service parts as applied to a manufacturer handling both internal and external spare and service needs.

Differentiating Spares From Production Parts A question that often comes up is “Why treat service and spares differently from regular production parts? ” The forecasting of spares and service parts as well as the inventory management function is a more complex task because of the following characteristics: NITIE, Mumbai 9 Production Parts Spare Parts The demand for spare parts largely depends on the output of preventive and predictive maintenance activities, and is typically based on MTTF (mean time to failure) calculations.

Sudden breakdowns due to factors such as wrong operation or failure to perform a routine maintenance activity lead to demand with no assignable predictable causes, thereby imposing a need to maintain buffer inventory. The demand of spares is based on the equipment life cycle and follows the inverted bathtub curve The sparse nature of usage/ consumption data makes it difficult to generate statistically valid forecasts for spares. The existence of part alternates and common parts across equipment makes inventory management more complex.

Spares are often procured locally and development of indigenous suppliers makes the analysis of failure dependent on a large set of factors. Managing multiple sources of supply for the same part imposes a need for greater rigor and analysis on service maintenance. Often the demand for a spare arises from an alternate source of failure. To elaborate, a failure of a key on a gear shaft may cause the gear shaft to be replaced as well as the other meshing parts like gears. Likewise, the demand of spares is a function of the equipment life cycle.

Non-availability of spares impacts the throughput and directly translates into costly machine downtime. The demand for production parts is a derived or a dependant demand generated from the production plan and hence is predictable. Production parts have a demand based the existence of market demand which is easier to predict Easier to forecast because of more predictable movement patterns. Incidence of alternate parts and common parts is handled through substitution relationships in a Bill Of materials. The component/ part relationships with the supplier are inherently better defined apart from commodity raw materials.

The demand for a part or a component is in most cases a derived demand based on the customer demand for the end product; which is relatively simple and based on orders received or forecasted. Production parts are typically the input or output of a production process. Nonavailability of input parts can constrain the throughput Complexities In Spare Parts Management The processes described under Spare Parts Management have complexities that make automation and information management critical to their successful execution. To illustrate, NITIE, Mumbai 10 let? examine the enormity of data and the complexity of analysis involved in typical decisions such as: How do we predict demand? Demand of spares cannot be easily gauged because the consumption of spares can be event based or probabilistic in nature. The events themselves can be scheduled or planned events or unplanned. Scheduled maintenance, capital overhauls etc. , would fall in the first category whereas breakdowns would be in the second category. Historical records on breakdowns could be inaccurate particularly due to lack of system support in this area.

The intermittent nature of spare part demand due to breakdowns makes it impossible to apply conventional time series based algorithms. The choice of the right model becomes vital and that would depend on the usage rates and the usage patterns. What inventory do we carry and where? How do we allocate inventory in a multi – echelon logistics network? The problem statement here would vary depending on the organization. For a typical manufacturing plant, the inventory is held at two levels – at a shop level and at the plant level. For a service organization, inventory holding is done as a part of a multi-echelon service etwork – this could range from a centralized national depot to regional depots and down to city depots as well as mobile inventory in the form of service trucks that service installations along specific routes. Hence the problem statement here is larger and more complex and includes elements of route optimization, network optimization (how many echelons do we keep? ), etc. The allocation of inventory in such a case becomes a function of the geography, operational conditions (An Air Conditioning system in a very hot climate needs more service attention than one in moderate climates), and service response needs, etc.

Arriving at a good basis for allocations and incorporating that through a system can substantially improve service levels and response time. How do we manage parts substitution and alternates? Spare parts can exhibit characteristics such as substitution and the problem of managing substitution is probably most complex when it comes to service maintenance. The need to keep an equipment running can often lead to bad choices and decisions that can significantly impact future service needs. A substitution could be an exact substitution meaning there is no change in the equipment specs apart from the part itself.

On the other hand, it could be a case NITIE, Mumbai 11 of accommodating some changes such as re-boring to enlarge the bearing seat and use a larger bearing. In which case the original spare becomes defunct and a new spare needs to be set-up. Setting up substitution/ alternates relationships from a forecasting perspective could however help part rationalization and help manage the large number of stock units. Equipment that is no longer manufactured and whose spares are difficult to get could be ideal candidates for setting up alternates. In which case, the decision variable really is twofold – “where use” and “when use”.

When do we phase out a part from the inventory portfolio? Often we see equipment being operated long after they should have been de-activated – there normally is a business need for that. The spare parts management in this case needs to account for the life cycle stage and look at the economics of ordering spares. The decision here could be to deplete and exhaust spare inventory or carry it as before. There could be cases of identifying alternates from other operational equipment and using that for repairs rather than carry original spares.

The equipment history and the operating condition as well as the “Need factor” for the equipment can be the decision criteria on which decisions to obsolete can be based. How do we tackle variability in procurement? The inventory carried is a function of the demand uncertainty and the uncertainty around the procurement lead-time. Imported equipment need original spares that are typically buffered to guard against the longer procurement lead-time. Considerations like economic order quantity, usable shelf life, price discounts etc. would also need to be taken into account.

How do we manage shifting inventory? The equipment and spares may be moved from location to location in a service network – this would call for dynamic updation of the location. Likewise, date sensitive spares would need recording of the “Use date” against actual consumption vis-a-vis the “ procurement date” to ascertain the correct usable life of the spares. 4. MODELING NITIE, Mumbai 12 4. 1 IDENTIFYING THE MATERIALS For identifying the electrical materials four categories were defined viz. , Power Source, Electrical (Control & Protection), Electronic (Control Circuit) and Lighting & Miscellaneous items.

A brief description of the identification process is presented below: Power Source: Power source consists of sources of both AC & DC Power, Energy conversion devices. These include Transformers as a primary source of power input. In case of power outage DG set is used as power source. SMPS (Switched Mode Power Supply) is used as a source of DC supply in the panels for powering the control circuitry. Cables form the power transmission device. Electrical (Control & Protection): This category includes switching devices like Contactors, relays. In relays, Solid state relays, Plug in relays, safety relays, overload relays are considered.

For motor protection and starting, Motor Protection Circuit Breakers are used. These are also known as Manual Motor Starters. Star-Delta Starters and VFD? s are used for starting and control of motors. For making and breaking the circuit MCB (Miniature Circuit Breaker), MCCB (Moulded Case Circuit Breaker), ELCB (Earth Leakage Circuit Breaker), ACB (Air Circuit Breaker), SFU (Swtiched Fuse Units). DIN, HF type fuses are used in switching the SFU? s. Electronic (Control Circuit): Devices that are used to sense a set of conditions or abnormalities and send a control signal to the switching devices are included in this category.

These include timers, sensors, temperature controllers and encoders. Lighting Equipment: It includes lighting and indicating devices like tube lights, CFL? s, tower lights, switches, sockets, push buttons, pilot lights, ballasts. Miscellaneous Items: These include cable accessories like Glands, Lugs, Connectors, Tie Wraps, Markers and Junction Boxes. NITIE, Mumbai 13 Power Source Electrical (Control & Protection) Electronic (Control Circuit) Timers Sensors Encoders Temp. Controllers Lights Ballasts Indicators (Pilot Lights) Tower Lights Switches Sockets Push Buttons Lighting & Misc. Items Transformers Alternators (DG Set) : Switching :: Contactors Relays :: Energy Conversion :: Motors ;; SSR, PLA, Contactor Relay, Safety Relay, Overload Relay :: Power Transmission :: Cables Motor Protection, Starting & Control MPCB? s, S/D Starters, VFD? s Junction Boxes Glands Lugs Connectors :: DC Power :: SMPS :: Make / Break of Power Circuit :: MCB MCCB ELCB ACB SFU ? DIN, HF Fuses Tie Wraps Markers 4. 2 MATERIAL CLASSIFICATION The cues for material classification have been drawn from the existing equipment criticality analysis. The equipment criticality is measured on 4 parameters viz. , EOHS, Quality, Operations and Maintenance.

There are 3 levels for each of the parameters i. e. , High, Medium & Low. Weights assigned to Low, Medium are 1 & 3 respectively. The table given below shows the description of each parameter under the levels. NITIE, Mumbai 14 HIGH (A) MEDIUM (B) 3 Equipment failure that does not meet with specific requirements such as corporate EOHS and Risk Management standards or local permit conditions. Fire protection, emergency lighting systems, etc LOW (C) 1 EOHS Equipment failure leads to substantial civil fines, criminal prosecution, harm to persons or the environment, or major adverse publicity. All other equipment. EOHS Examples

Dust collectors, wastewater treatment equipment, etc. Quality Equipment failure has a major effect on quality due to product contamination or abnormal reactions and produces out-ofspecification product. Process water treatment systems for micro susceptible products, raw material dosing units, post addition skids, etc. Equipment failure produces quality variations that can be corrected by the operator comparatively quickly. Plastic extrusion equipment, some finishing equipment, etc All other equipment that does not apply to either A or B ranking. Quality Examples Labelers performing at edge of specification, etc.

HIGH (A) Equipment with major effect on production without standby provision whose failure causes previous and subsequent processes to shut down completely with related high cost of failure. Making and transfer equipment, vertical integration equipment planned to work with little or no buffer, electrical substations, etc. MEDIUM (B) 3 LOW (C) 1 Operations Equipment failure causes only partial shutdown, with possibly no impact on overall plant production. Equipment failure has little or no effect on production. Operations Examples Water treatment systems, secondary motor control centers, etc.

Auxiliary equipment with redundancy, etc. NITIE, Mumbai 15 Maintenan ce Equipment takes four or more hours or more than 10% of its monthly maintenance budget to repair. Or equipment fails three or more times per month. Repairs need to be performed externally, failure may involve parts with high cost and/or nonmaintained in stock. Equipment can be repaired in less than four hours at a cost between 2 and 10% of its monthly maintenance budget. Or equipment fails fewer than three times per month. Equipment cost less than 2% of its monthly maintenance budget or can be planned to repair at a later date. Maintenance Examples

Internal capability to perform repairs. Parts avaialable. Deferable maintenance. Calculation ? “A” equipment parameters are given „maximum? points (A) ? “B” equipment parameters are given (3) points ? “C” equipment parameters are given (1) point ? Per equipment parameter, assign a criticality rating ? Add up the values and determine criticality ? (0 – 6) points ? “C” rating ? (7 – 12) points ? “B” Rating ? ( A ) Any parameter with “A” rating ? “A” Rating A EOHS Quality Operations Maintenance A A A A B 3 3 3 3 C 1 1 1 1 Score 0-6 07 – 12 A Rating C B A NITIE, Mumbai 16 4. 3 COMPONENT CRITICALITY ANALYSIS

Similar to the analysis done for equipment criticality a basis for determining the criticality of the component has been developed. Here 3 parameters have been considered viz. , Lead Time, Cost and Effect on Operations. There are 3 levels for each parameter namely High, Medium and Low. Weights assigned for High, Medium and Low are A, 3 and 1 respectively. The calculation stands similar to that of equipment criticality except the score. ? (3 – 5) points ? “C” rating ? (6 – 9) points ? “B” Rating ? (A) Any parameter with “A” rating ? “A” Rating HIGH (A) A Lead Time Imported or more than 3 weeks MEDIUM (B) 3 – 3 weeks LOW (C) 1 Less than one week Operations (In case of nonavailability) Immediate Shut down Line can run, but the component needs to be replaced They have no or little effect on production Cost ; Rs. 30,000 Rs. 30000 to Rs. 5000 less than Rs. 5000 A Delivery Lead Time Cost Operations A A A B 3 3 3 C 1 1 1 Score 3-5 6-9 A Rating C B A With the above description as basis, materials have been classified. Sensors, Temperature Controllers, Motors etc. , have been classified under „A’ category. Push buttons, Indicators, Lugs & Glands of higher dimensions are classified under „B’ Category.

Lights, Lugs & Glands of lower cross section, connectors, switches & sockets, tower lights etc. , were classified under „C’ Category. NITIE, Mumbai 17 4. 4 DEFINING THE MATERIALS The literature related to the material was studied to understand the terminology of the material. This resulted in a set of specifications. But, all of these specifications were not included. Only those parameters were included which would be easy for the trader to understand and would help the trader to identify the material without any ambiguity. Also, the specifications that were used in the previous purchase orders were also examined.

For instance, SMPS had the following specifications: Make, Catalogue No. , Power rating, Voltage rating, current rating. Usually an SMPS is identified by its voltage and current rating. But an ambiguity can exist in terms of preferred make. So the preferred make field was added. To identify the required product among its variants without any ambiguity catalogue number was included in the specifications. 4. 4. 1 Additional Information about the product For the purpose of providing more information about the product the specification sheet was downloaded from the manufacturer? s website.

A soft copy of the specifications sheet will be available on the manufacturer? s website in the form of a pdf document. 4. 4. 2 Saving the Specification Sheet The following figure shows the directory path to store the specification sheet of an SMPS. The first directory contains the folders containing the name of the material. If any directory is selected it would take the user to the next level where in the folders with company name is found. If a particular folder is opened, all models (which could be used in the plant) can be found. Inside every folder the specification sheet downloaded from the manufacturer? website is saved. This would complete the process of saving the specifications sheet. NITIE, Mumbai 18 Fig : Directory path to save the specification sheet 4. 4. 3 Relating the specification sheet with the master list In the figure shown below the catalogue number of every SMPS is hyperlinked (underlined blue font). Clicking on the hyperlink would open the specification sheet from the directory shown in the above figure. Thus the user gets to know the complete details about the material. Fig : Screenshot of the master list of spares 4. 4. 4 Creating Material Code for A Category Items

Nomenclature of the material code Material code, also referred to as e-code, is a unique 10 digit code assigned to the material (Usually A category). Ex: of a material code E16GFLX040. First 3 digits of the material code NITIE, Mumbai 19 define a spare part belonging engineering stores. 4th digit G is also common for all material and it indicates Goa Manufacturing facility. The next 4 digits “FLX” here, define the location of the material i. e. , the usage point. It says that this material is used in FLEX focus factory of the company. The last 3 digits are the serial number in that particular category.

So, upto 1000 material codes can be created for a given location. 4. 4. 5 Existing Codes and creation on new codes Not all A category items were associated with e-codes. So, a set of e-codes were proposed for A category materials which didn? t had one. Why should an A category item be associated with an e-code? The answer lies in the need to monitor the inventory levels. If a material has an e-code then its stock level can be obtained at any point of time from SAP. So, if a material? s inventory is desired to be monitored using any automated system then it is imperative to have material code.

How different is the material code created as a part of this project Let? s take for instance Meanwell SMPS with D-60B and E16GFLX040 as catalogue no. and e-code respectively. This e-code will tell the user that it belongs to FLEX focus factory. But this SMPS can be used in other focus factories also. In that case, this e-code is not comprehensive. It would be desirable to have an e-code which would define the material in the code. The proposed e-codes in this project would not just give an identity to the material but also help defining it. The 7th, 8th and 9th characters in the new nomenclature would define the material.

For instance, the proposed e-code for Meanwell SMPS with S-100-24 is E16GSMP101. The characters SMP would define the material as an SMPS. For all other materials the 7th, 8th and 9th digits would be taken from the material name itself. Thus, the material definition and identification has been made easy. NITIE, Mumbai 20 4. 4. 6 Confirmation of creation of e-codes Fig : Screenshot showing the position of proposed e-codes in the inset The above fig shows the positions of Confirmed/Existing e-codes and proposed e-codes. Following is a brief description on converting proposed e-codes to confirmed e-codes.

The proposed e-codes have to be entered into SAP along with description, current inventory level in stores and other details. This would ensure that the e-code is available in SAP. But to put the e-code to use in purchasing, the codes thus created must be replicated in eBuy. As e-Buy is the source of preparing a purchase order the codes need to be replicated in eBuy. After completing this activity the e-codes are moved from Proposed E codes column to the existing e-codes column. With this, monitoring of A category (with e-codes) can be done. NITIE, Mumbai 1 4. 4. 7 Purchasing Non-Inventoried Items The figure below shows the eBuy catalogue for non-inventoried items. The fields of the catalogue Part No: Refers to the catalogue number given by the manufacturer Description: Description of the material as desired by the user. It should be easily comprehended by other users of the catalogue also. The description should necessarily include Make and other details could be dimensions, electrical specifications like voltage, current rating, power consumption and other vital details which are specific to the material.

Price: This is the discounted price offered by the vendor. The negotiation process with the vendors is described in the later section. The vendor who has offered maximum discount for a given material was selected for purchase of that particular material. UOM: unit of measurement of the material I-Code: IMREESUP is the code used for engineering materials Vendor: It refers to the name of the vendor who has quoted maximum discount Fig: eBuy Catalogue for Non-Inventoried Items Usage of eBuy catalogue The catalogue thus prepared in full detail would be uploaded on eBuy.

This would be similar to listing of products on eBay. com or amazon. com. The user can click on the material of his NITIE, Mumbai 22 choice and enter the required quantity. With this selection a purchase order is generated which would be sent to the vendor who is mentioned in the catalogue. A copy of all these material is present in the spares master sheet. 4. 4. 8 Negotiation of Discount Rates With the catalogue for the required material in place a summary of type of material and preferred make was prepared. The following table shows the material against the preferred make.

Material Switches & Sockets Glands_Metal Terminal Blocks Lugs Terminal Blocks Cables HRC Fuses JB’s Glands_PG Push Buttons Contactors HRC Fuses Relays SFU Timers ELCB MCB MCCB MPCB SMPS Push Buttons Push Buttons Relays SMPS Lights Ballasts Terminal Blocks Cables MCCB Contactors HRC Fuses MCCB Push Buttons Relays Preferred Make Anchor Roma Legrand Comet Connectwell Dowell Elmex Finolex GE Hensel Hensel Klockner Moeller L&T L&T L&T L&T L&T Legrand Legrand Legrand Legrand Meanwell MIMIC Moeller Omron Omron Philips Philips Phoenix Polycab Schneider Siemens Siemens Siemens Siemens Siemens

NITIE, Mumbai 23 SFU Push Buttons Tower Lights Push Buttons Push Buttons Temperature Controllers Siemens Standard Gold Target Electricals Teknic Telemecanique Omron Fuji Selectron Sensors Banner Carlo Gavazzi Datalogic Infra International PG Electronics Festo Wenglor NORDEN Omron Pepperl The discount rates for the above materials have been obtained from 13 vendors. The discount rates were consolidated and the maximum discount for each material for the given make was determined. This maximum discount formed the basis for the second round of negotiation with the vendors.

The maximum discount was mentioned and the vendors were asked to quote new discount rates. The discount rates thus obtained were again consolidated and the maximum discount after the second round of negotiation was determined. The maximum discount thus obtained was finalised. The vendors were located in Goa and Mumbai. The top 3 discount rates for a given material were mention in the master list of spares. The latest price list for the materials was obtained from the vendors and it was also included. The discounted prices w. r. t the list price were calculated and included in the master list.

By this the spares master has been supported by the discounted prices also. The next part of the project was to define an appropriate inventory policy for the materials. NITIE, Mumbai 24 5. ANALYSIS 5. 1 INVENTORY POLICY The inventory policy has to be decided on the basis of classification of the materials and at the same time information on annual usage and installed quantity was used. Installed Quantity Now that the demand for the spares would be intermittent in nature it was desired to know the installed quantity of A Category spares in Making, Finishing and Utility locations.

The advantages of obtaining the information on installed quantity are ? ? ? The user gets to know the various makes of the material used and their count. It is needed when one-to-one replacement of a particular spare (especially imported) is desired. At times, if demand for a material is not available for a particular year the safety stock is defined as a percentage of the installed quantity. The installed quantity has been taken for the following items (A Category) Motors, Sensors, SMPS, Temperature Controllers, MCB, MPCB, MCCB, ELCB, SFU, ACB, Contactors, Relays.

NITIE, Mumbai 25 Fig: Screenshot showing the location wise list of motors The above figure presents comprehensive information on all kinds of motors used in the plant. With this it would become easy to locate a motor in any part of the plant along with its make, wattage, phase, voltage, current, rpm, connection, motor no. and frame no. Before actually describing the inventory policy an analysis has to be presented. The figure below shows the snapshot of consumption (Jan 1009 to Oct 2009) of all engineering material arranged in the order of vendors. NITIE, Mumbai 26

Fig: Snapshot of annual consumption of all engineering materials There were separate tabs for materials with and without e-codes. But this list had to be sorted on the basis of material and irrespective of existence or non-existence of e-codes. The figure shown below is the sorted list of spares in the order of material name. NITIE, Mumbai 27 Fig: Consolidated annual consumption on the basis of material From this list the annual consumption of the materials (given in the above list) were entered in the spares master. Now, there are two parameters to decide the reorder quantities viz. , installed capacity and annual demand.

For C Category items the inventory policy is selected in such a way that the re-order quantity would satisfy 6 months demand. 5. 1. 1 Inventory Policy for A category items Both installed quantity and demand are considered. It has been observed that demand is less than installed quantity. If the demand is > 90% of the installed quantity then safety stock and re-order point are fixed considering the installed quantity. If the demand is less than (< 30%) the installed quantity then the demand has been considered primarily to fix the order quantity since demand would give the failure rate.

If the demand is more than 30 units then EOQ model is used else (S-1, S) model is used. NITIE, Mumbai 28 5. 1. 2 Parameters for calculating the EOQ’s 5. 1. 3 Ordering Cost The table given below illustrates the calculation of Ordering Cost. Time in mins 10 an email to 3 vendors one 5 min call to 3 vendors comparing all three quotes and selecting one quote user has to check all fields of the shopping cart Cost of making a PO Checking all fields of the PO 7 5 7 8 5 Checking material with PO Updating Stock Levels in SAP 10 5 5 10 5 5 Cost in Rs. 41. 67 20. 83 41. 67 20. 3 20. 83 100. 00 29. 17 20. 83 29. 17 33. 33 20. 83 41. 67 20. 83 100. 00 Activity Description Creating of Shopping Cart by the user Detail Sourcing Sourcing Sourcing Sending an email Making a phone call Comparative Study Approval of SC by the user PO Creation PO Approval PO Approval PO Approval By User By Finance By Purchase Manager Checking the soft copy of the PO by the buyer Sending the soft copy of PO to the vendor Receiving Cost Receiving Cost Purchase Order Matching Updating Inventory Records Fixed Transportation Cost Ordering Cost Cost = Rs. 2000 / day 541. 67

All the activities that are involved in making a purchase order are listed against the time taken by them. In brief, the activities can be grouped under 3 categories namely Sourcing, PO Approval and Receiving. The average cost per minute including the employees at all levels is Rs. 2000 / day. The cost at every stage has been calculated considering this cost. NITIE, Mumbai 29 5. 1. 4 Inventory Holding Cost ? ? Cost of Capital: Since the company does not have any debt the cost of capital has to be estimated on the basis of opportunity cost. Thus the cost of capital is 10%.

Obsolescence (or spoilage cost): Though new models would be introduced in the market but for the engineering materials that are used in the plant the effect of change is technology is taken as negligible. ? Handling Cost: For engineering materials no extra person is needed for handling. Also, the engineering spares are lighter and smaller compared to the equipment in which they are used. ? ? Occupancy Cost: No extra space is hired for storage of additional unit of the engineering material. So, the occupancy cost is nil. Miscellaneous Cost: The insurance for the materials in store would be 0. 8%. All factors put together the Inventory Holding Cost would be 10. 08%. In summary, ICC = 10. 08% and Ordering Cost = Rs. 542 The lead time is taken as 3 weeks and the cycle service level is 95% i. e. , a 5% chance of stock out exists of A Category Items (with average annual demand more than 30 units). A quick view of the calculation of ROQ ROQ is calculated using the formula sqrt(2*Annual Demand*Order Cost/(ICC*Unit Cost)) Here, the safety stock has been fixed manually as the value calculated using the formula NORMSINV(CSL)*? L has given the values in the range 0 – 2.

It has been fixed by considering a certain number of units from each focus factory. e. g. , a demand of 2 units from 3 focus factories would give a safety stock of 6 units. The ROP was calculated as the sum of safety stock and demand during lead time. Demand during lead time = (demand * lead time) NITIE, Mumbai 30 5. 1. 5 Policy Illustration In the following section the implementation of inventory policy of few A category items has been presented An Illustration of policies for „A? category items For SMPS The demand is considered to determine the order quantity.

Since the demand is less than 10 units per annum (S-1, S) policy is appropriate. Annual Demand: Temperature Controller, SFU Annual Demand & EOQ Model: DIN type HRC Fuse Link (32A & 63A) For Push Buttons & Indicator: ROP is 20% – 30% of EOQ 1 Pole MCB? s 30% of the order quantity is taken as ROP. 2 Pole MCB? s both EOQ and (S-1,S) Policy is used 3 Pole MCB? s both demand & installed quantities are considered and (S-1, S) model is used. Timers & Starters, Sensors: ROQ is 25% of installed quantity and (S-1,S) Policy etc. , The next part of the project is to design a system that would generate a eport indicating the levels of A category items and the action to be initiated again the stock levels. The next section gives a brief account of the design and working of the Microsoft Excel based Report Generation System. 5. 2 REPORT GENERATION SYSTEM The report generation system is developed using VBA. The requirements for this program are stock count report from SAP and spares master list. The stock count report from SAP should be stored by the name stock_count. xls. By default, this file will be saved in Unicode format. An excel file named Reordering Tool. xls generates the report. NITIE, Mumbai 31

Fig: Screenshot showing the console tab of the reordering tool. xls Upon clicking the Generate Order Status button the macro starts execution. The execution happens in the following steps. ? ? ? ? ? The stock_count. xls is opened and the contents are copied to e1 tab of Reordering tool. xls The stock_count. xls file is closed The spares master file electrical spare. xls is opened and the contents are copied to e2 tab of Reordering tool. xls Electrical spares. xls file is closed All the e-codes present in the first column of the spares master sheet are copied in to updates tab by skipping the blank cells and repetitions (if any) are removed.

Now the updates tab contains all confirmed e-codes present in the spares master sheet. ? For every ecode pasted in the updates tab the stock levels for each occurrence is added and the final stock value is pasted against the e-code. Thus the updates sheet contains the stock levels again the e-codes. ? Every row of the updates sheet is pasted in results sheet and reorder point and order quantity corresponding to the e-code are copied from master sheet and pasted in the results sheet and a quick comparison of SAP stock with reorder point is made. ? If the current stock is less or equal to ROP then a message is displayed saying “Place Order”.

Along with this message the specifications of the material are also pasted in the adjoining cells. NITIE, Mumbai 32 ? After completing this looping for all the e-codes in the updates sheet the cells of the results sheet are arranged in the order of order status and material description. Fig: The report generated by the macro in Reordering tool. xls NITIE, Mumbai 33 6. LIMITATIONS & FUTURE SCOPE Though an attempt has been made to fix the inventory levels using EOQ and (S-1, S) models it may not be the accurate method as the consumption follows an intermittent demand pattern.

EOQ model would give accurate results where demand and lead time demand follows normal distribution. There is a need to use a forecasting model to fix the inventory levels of spare parts. The future scope includes forecasting of intermittent demand. One of these methods could be Croston? s Forecasting Method. In this method the demand for spare parts is forecasted. The forecast value changes in accordance with the consumption. A more detailed explanation is given in the appendix. 7. ACADEMIC CONTRIBUTION Management Techniques: Inventory classification has been done which includes cost, effect on operations and lead time.

On this basis the materials have been classified into A, B & C categories. Inventory levels are fixed based on the classification of materials. Techniques like EOQ, (S-1, S) are used. Concepts of database management are used to create the catalogue. Report generation system connects the data from SAP and the inventory levels fixed by the user (materials manager). The data was selected in such a way that material identification happens without any ambiguity. Functional Area: Academic subjects that are covered in the scope of study are Materials Management and DBMS. NITIE, Mumbai 34 8.

REFERENCES Books 1. Supply Chain Management – Strategy, Planning and Operations by Sunil Chopra, Peter Meindl, D. V Kalra Websites The following company websites were visited to extract information about their products Company Name Wesbite Anchor Roma Legrand Comet Connectwell Dowell Elmex Finolex Hensel Klockner Moeller L&T Meanwell Moeller Omron Philips Phoenix Polycab Schneider Siemens Target Electricals Teknic Telemecanique Fuji Selectron www. anchor-world. com/ www. legrand. co. in/ www. cometgland. com/ www. connectwell. com/ www. dowells. co. in/ www. elmex. net/ www. finolex. com/ www. ensel. in/ www. klocknermoeller. com/ www. lntebg. com www. meanwell. com/ www. moeller. net www. omron-ap. co. in/ www. india. philips. com/ www. phoenixcontact. co. in/ www. polycab. com/ www. schneider-electric. com/ www. siemens. co. in/ www. targetel. com/ www. teknic. co. in/ www. schneider-electric. com/ www. coulton. com/Temperature_Controllers_PXR_Fuji_Electric. h tml www. selec. com/ NITIE, Mumbai 35 ? ? ? Manufacturing Process: http://www. enotes. com/how-productsencyclopedia/toothpaste Spare Parts Management: www. infosys. com/… /industries/… /automating-spare-partsmanagement. df Forecasting, Ordering and Stock-Holding for Erratic Demand: www. lums. lancs. ac. uk/files/PDF5. pdf, ISF 2002 –23rdto 26thJune 2002; Andrew Eaves, Lancaster University / Andalus Solutions Limited ? (S-1, S) Policy: http://www3. interscience. wiley. com/journal/113392040/abstract? CRETRY=1&SRET RY=0 ? WACC: http://en. wikipedia. org/wiki/Weighted_average_cost_of_capital NITIE, Mumbai 36 9. APPENDIX 9. 1 INTRODUCTION TO CROSTON’S METHOD Erratic and Slow-Moving Demand Erratic, or intermittent, demand has infrequent transactions with variable demand sizes; often caused by: ? ? ? ? Many small customers and a few large Variations magnified by multi-echelon system Correlation between customer requests Sympathetic replacement of parts Aggregation, or bucketing, of demand Slow-moving demand has infrequent transactions with low demand sizes. Common methods for forecasting and stock-holding less effective if demand not smooth and continuous. Forecasting Erratic Demand Exponential smoothing (ES) often used in reality, but ? ? Forecast highest after a demand As order level broken by a demand occurrence there is a tendency for unnecessarily high stocks.

Croston? s method provides an alternative ? ? Separately applies ES to interval between demand and size of demands Only updates if demand occurs yt= demand for an item at time t pt = mean interval between transactions zt = mean demand size yt= mean demand per period (the forecast) q = time interval since last demand ? = smoothing constant NITIE, Mumbai 37 If yt = 0 then pt = pt-1 zt= zt-1 q= q + 1 Else pt = pt-1 + ? (q -pt-1) zt= zt-1 + ? (yt-zt-1) q=1 Size and interval combine as yt = zt / pt 9. 2 INVENTORY POLICY S-1, S) Policy The one-for-one (S – 1,S) inventory policy, which calls for a replenishment order after each demand equal in magnitude to the size of the demand, is often advocated for controlling the stock levels of expensive, slow-moving items. In particular, this policy has frequently been promoted for use in recoverable-item inventory systems. This includes the important case of unit-sized demands. The inventory system considered is one with continuous review, constant lead times, general inter-arrival and discrete demand distributions, complete backlogging, and linear holding and penalty costs per unit per unit time. 9. 3 EOQ PARAMETERS