Inventory management Excel template xls
Inventory management Excel template xls
This article provides details of inventory management Excel template xls that you can download now.
Efficient inventory management is vital for any entrepreneur, but it is just as useful at home. Whether you want to inventory insurance, keep track of your personal belongings in a storage unit, or plan the amount of food and drink needed for an event, inventory tracking can help you stay organized .
Companies can run their operations smoothly by managing the stock to avoid a surplus and by making an inventory that allows them to see which products are selling best.
Microsoft Excel software under a Windows environment is required to use this template
These inventory management Excel template xls work on all versions of Excel since 2007.
Examples of a ready-to-use spreadsheet: Download this table in Excel (.xls) format, and complete it with your specific information.
To be able to use these models correctly, you must first activate the macros at startup.
The file to download presents four inventory management Excel template xls
Inventory management is the branch of business management that covers the planning and control of the inventory. In the previous chapters, we have discussed priority and capacity planning and control. Priority planning determines what materials are needed and when they are needed in order to meet customers’ demands. Capacity planning determines the amount of capacity required in each period to execute the priority plans. There are various ways to conduct priority and capacity planning; a “feasible” solution that satisfies a customer’s requirement may not be good enough. We demand a “good” plan that satisfies customers while maintaining the lowest possible total cost.
Different inventory management approaches are required for different manufacturing organizations. The designs of manufacturing organizations for high-mix/low-volume products and low-mix/high-volume products are different. There are two forms of manufacturing organization: flow shop and job shop.
- Flow Shop
In a flow shop, machines and operators perform stable, standard, usually uninterrupted materials management. The flow shop is appropriate for an extremely low-mix/high-volume product, where a single type of product is produced in the production line. Specialized equipment and labor are established to produce the product in large quantities. This is defined as mass production. Different products with very similar design can also be produced in a specialized production line with fixed routing and low or no setups. This is called continuous production. In continuous production, the productive equipment is organized and sequenced according to the steps involved in producing the products, and the material flow is continuous during
the production process. Continuous production system produces continuous products such as steel and chemical. The products in the repetitive production have similar design and need low or no setup cost, the process is also continuous, yet the products are discrete. The repetitive production system produces different products with same process.
- Job Shop
In a job shop, equipments are organized by function. Each job, a certain amount of a product in production, follows a distinct routing through the shop. A routing comprises a number of operations. There are setups between operations. The process is not continuous, and material flows are sometimes interrupted. Each job contains a certain quantity of a product. Mold or fixture changes are required when the production line changes jobs. Machine utilization in a job shop is lower than that in a flow shop. Unless the quantity of a product is large enough to support a specialized facility, continuous production through a flow shop is impossible. This form of manufacturing is defined as intermittent production.
- Throughput, WIP, and cycle time
Throughput, work-in-process inventory, and cycle-time are related indices that are frequently used in flow shops. Throughput is the total volume of production through a facility per unit of time. Cycle time is the length of time that starts when a material enters a facility and ends when it exits. Therefore, work-in-process inventory equals the product of throughput and cycle time. Throughput is the output rate of a facility and is frequently used to measure the performance of a flow shop. In a job shop, since different products are processed through various routings, throughput and cycle times depend on the product mix and are not stable. The standard and actual cycle-times for each job and the planned and actual input/output and work-in-process inventory for each work center are used to monitor the performance of a job shop.
Materials flow from suppliers, through a manufacturing organization, to the customers. The progressive states of a material are classified as raw materials, semi-finished goods, finished goods, and work-in-process (WIP).
- Raw Materials
Purchased items or extracted materials that are converted via the manufacturing process into components and/or products. Raw materials appear in the bottom level of BOM. They are stored in the warehouse and are non-phantom items.
- Semi-finished Goods
Semi-finished goods are items that have been stored uncompleted, awaiting final operations that will adapt them to different uses or customer specifications. Semi-finished goods are made under the instruction of a shop order, using the components issued by a picking order, and stored in the warehouse when finished. They are the items between the top and bottom levels in a management BOM (rather than engineering BOM) and are non-phantoms. Semi-finished goods are not sold to the customers.
- Finished Goods
A finished good is a product sold as a completed item or repair part, i.e., any item subject to a customer order or sales forecast. Finished goods are non-phantoms and are stored in the warehouse before they are shipped.
- Work-In-Process (WIP)
Products in various stages of completion throughout the plant, including all material from raw material that has been released for initial processing up to completely processed material waiting for inspection and acceptance as finished goods. WIP inventory is temporarily stored on the shop floor and appears as a phantom in the BOM.
- Maintenance, Repair, and Operational Supplies (MRO)
Items used in support of general operations and maintenance such as maintenance supplies, spare parts, and consumables used in the manufacturing process and supporting operations. These items are used in production but do not become part of
- Safety Stock
An additional quantity of stock kept in inventory to protect against unexpected fluctuations in demands and/or supply. If demand is greater than forecast or supply is late, a stock shortage will occur. Safety stock is used to protect against these unpredictable events and prevent disruptions in manufacturing. Safety stock is also called buffer stock.
- Lot-size Inventory
In order to take advantage of quantity price discounts, reduce shipping and setup costs, or address similar considerations, items are manufactured or purchased in quantities greater than needed immediately. Since it is more economical to produce or purchase less frequently and in larger quantity, inventory is established to cover needs in periods when items are not replenished. Lot-size inventory depletes gradually as customer orders come in and is replenished cyclically when suppliers’ orders are received.
- De-coupling Stock
Inventory between facilities that process materials at different rates. De-coupling stock de-couples facilities to prevent the disparity in production rates at different facilities from interfering with any one facility’s production. This inventory increases the utilization of facilities.
- Pipeline Inventory
Inventory to fill the transportation network and the distribution system including the flow through intermediate stocking points. This inventory exists because of the time needed to move goods from one location to another. Time factors involve order transmission, order processing, shipping, transportation, receiving, stocking, etc.
- Transportation Inventory
Transportation inventory is part of pipeline inventory. It is inventory in transit between locations. The average amount of inventory in transit is:
I = ( A / 365) * D
Where I is the average annual inventory in transit, A is annual usage, and D is transit time in days. The transit inventory does not depend upon the shipment size but on the transit time and the annual usage. The only way to reduce the inventory in transit is to reduce the transit time.
- Anticipation Inventory
Additional inventory above basic pipeline inventory to cover projected trends of increasing sales, planned sales promotion programs, seasonal fluctuations, plant shut downs, and vacations. Anticipation inventory differs from safety stock in that it is a predictable amount.
- Hedge Inventory
Inventory held to protect against future fluctuations due to a dramatic change in prices, strikes, war, unsettled government, etc. These events are rare, but such occurrences could severely damage a company’s initiatives. Risk and consequences are usually high, and top management approval is often required. Hedge inventory is similar to safety stock except that a hedge has a dimension of timing as well as amount. If the incident does not occur in the predicted time period, the hedge rolls over to the time period.
Conflict of Objectives
All enterprises are pursuing high customer service level, high operating efficiency, and low inventory cost. However, these objectives are conflict.
- Customer Service
Customer service is the ability of a company to deliver a product to the customer at the time the customer specifies. It is a measure of inventory management effectiveness. The customer can be a purchaser, a distributor, another plant in the organization, or the next workstation. Ways to measure customer service include percentage of orders shipped on schedule, percentage of line items shipped on schedule, and order-days out of stock. Inventory (safety stock) helps to improve customer service by protecting against uncertainties in production, but at the price of incurring higher inventory cost.
- Operating Efficiency
Operating efficiency is the ratio of the actual output of a work center, department, or plant to the planned or standard output. Inventory (de-coupling stock) allows operations with different production rates to operate separately and more economically. Inventory (anticipation inventory) can also level the production by building more in the valley periods for sale in the peak period. By doing this, the costs of changing production levels are avoided. Changing production level involves costs such as overtime, hiring and firing, training, subcontracting, etc. Inventory (lot-size inventory) allows longer production runs, which bring lower setup costs, ordering costs, and quantity discounts.
- Inventory Cost
The enhancement of customer service and operating efficiency is by no means free. Inventories have tangible and intangible costs. Item costs (direct material, direct labor, overhead, transportation, custom duty, and insurance), carrying costs, ordering costs, shortage costs, and capacity costs are all tangible inventory costs. Inventories cover the manufacturing problems; the manufacturing problems cause more inventories and deteriorate the manufacturing system. This is an intangible inventory cost.
Inventory Costs related to Lot Sizing
- Ordering Cost
Ordering costs are the costs associated with placing an order with the factory or a supplier. The ordering cost does not depend on the quantity ordered. It is a composite of all costs related to placing purchase orders or preparing shop orders, including
- Work station setups,
- Inspection, scrap, and rework associated with setups,
- Record keeping for work-in-process.
- Carrying Cost
Carrying cost is the total of costs related to maintaining the inventory, including
- Capital cost invested in inventory, or foregone earnings of alternate investment,
- Storage costs for space, equipment, and people,
- Taxes and insurance on inventory,
- Obsolescence caused by market, design, or competitors’ product changes,
- Deterioration from long-term storage and handling,
- Record keeping for
ABC Classification Method
As there are enormous materials in the warehouse, we cannot manage all the materials in the same way. Important parts should be paid more attention to, and those cheaper and less important materials should be managed in a simple way or not managed. Materials are classified into three classes. In planning, order releasing, receiving, storing, counting, and costing, we use sophisticated and precise approach for class A materials, and simpler approach for class C materials.
- Stock Keeping Unit (SKU)
An item may be stocked at many warehouses. An item at a particular geographic location is called an SKU. For example, one item stocked at two plants and four distribution centers would represent six SKUs. In determining the classification of a material, all the SKUs have to be included.
- Application of Pareto’s Law
Inventory control is exercised by controlling individual stock keeping units. Pareto’s Law observes that a small number of items often dominate the results achieved. In inventory control, it is found that the relationship between the percentage of the number of items and the percentage of the annual dollar usage follows a pattern. We can classify items into three classes based on their observed dollar usage:
- Class A: About 10% to 20% of the items account for about 50% to 80% of the dollar usage,
- Class B: About 20% to 30% of the items account for about 15% to 20% of the dollar usage,
- Class C: About 50% to 70% of the items account for about 5% to 10% of the dollar
Steps in ABC classification include:
- Determine the annual usage for each item,
- Calculate the annual dollar usage for each item,
- Sort the items according to their annual dollar usage,
- Calculate the accumulated annual dollar usage, percentage of the accumulated annual usage, and the accumulated percentage of the items,
- Group the items into A, B, and C classes based on the percentage of annual
- Control Approaches
“C” class items account for only a small percentage of total dollar usage but are vital to the entire production process. A shortage of a single C class material could result in interruption of the operations in a plant. Simple control approaches that call for large quantities and high safety stocks are used to ensure that there is an adequate amount of C class materials anytime. A two-bin system is an approach for inventory management of C class items.
“A“ class items are extremely important and deserve the tightest control and the most frequent reviews. Tight control includes absolutely accurate records, regular reviews by management, frequent reviews of demand forecasts, and close follow-up and expediting to reduce the lead times. TPOP (for independent items), MRP (for dependent items), and close following up of purchase and shop orders are types of inventory planning and control used for A class items.
Normal controls with good record keeping are applied to B class items. Both MRP and ROP are commonly applied in planning and controlling B class items.
Simple Inventory Replenishment Methods
Not every item should use a sophisticated approach such as MRP to plan and control purchase or manufacturing orders. For low-value C class items, such as nuts and bolts, simple methods should be used to replenish the inventory.
- Two-bin system
A type of fixed order system in which inventory is carried in two bins. The second bin represents the order point (the inventory level at or below which an order is released), and is not touched until the first bin is used up. A replenishment order is placed when the first bin is empty. When the material is received, the second bin is refilled and the excess is put into the first bin. At this time, stock is again drawn from the first bin until it is exhausted.
There are variations on the two-bin system. In one variation, there need not be exactly two bins. For example, the first “bin” may include five boxes, and the second bin, three boxes of materials. In other versions, there may be no bins. For example, tags are frequently used in a bookstore to replenish the inventories. A tag is placed in a book that is in a stack in a position equivalent to the order point. When that book is sold, the tag automatically informs the clerk to reorder the books.
- Visual Review System
A simple inventory control system where the inventory reordering is based on actual visual inspection of the amount of inventory on-hand. Visual review system periodically checks stocks of items against rough inventory levels where reordering is thought to be necessary, or order points. If this level is reached, replenishing orders are placed.
The visual review system also has variations. The inventories are not necessarily reviewed where they are physically located. In one variation, a card is attached to each case of materials. When a case’s materials are exhausted, the card is placed on a board in the office. The number of cards on the board indicates the consumption of the materials. A red line drawn on the board represents the order point. When the cards touch the red line, a replenishment order is released.
- Min-max System
A type of order point replenishment system where the “min” is the order point, and the “max” is the “order-up-to” inventory level. The order quantity is variable and is the result of max minus available and on-order inventory. An order is placed when the
available and on-order is at or below the min. The min and max levels are usually arbitrarily determined through experience.