Excel VBA inventory management template
Excel VBA inventory management template
This article provides details of Excel VBA inventory management template that you can download now.
inventory management, also known as inventory control, is used to show how much of your inventory is at a certain point in time and how to track it.
It applies to any item you use to make a product or provide a service, from raw materials to finished goods. This covers the inventory at each stage of the production process, from purchasing and delivery to using and replenishing the inventory.
Effective inventory control allows you to keep the right amount of inventory in the right place at the right time. This ensures that capital is not tied up unnecessarily and protects production if problems arise with the supply chain.
Microsoft Excel software under a Windows environment is required to use this template
These Excel VBA inventory management templates 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 three Excel VBA inventory management template
1. Economic Order Quantity (EOQ)
The prime objective of inventory management is to find out and maintain optimum level of investment in inventory to minimize the total costs associated with it. The EOQ is the optimum size of the order for a particular item of inventory calculated at a point where the total inventory costs are at a minimum for that particular stock item. It is an optimum size of either a normal outside purchase order or an internal production order that minimizes total annual holding and ordering costs of inventory. Stock-out costs are difficult to incorporate in this model, since they are based on qualitative and subjective judgment. The ordering costs are the costs of placing a separate order multiplied by the number of separate orders placed in the period. The carrying costs can be calculated based on the assumption that annual cost of carrying a particular stock item on average, half the stock is on hand all the time in addition to the safety or buffer stock. The fewer the orders, the lower costs of ordering, but the greater the size of the order the greater the costs of carrying. The safety or buffer stock has no bearing on the EOQ, only on the timing of orders. The economic order quantity (EOQ) is an optimum quantity of materials to be ordered after consideration of the following three categories of costs:
Ordering Costs: The costs of ordering inventory include the following:
- Preparation of purchase order
- Costs of receiving goods
- Documentation processing costs
- Transport costs
- Intermittent costs of chasing orders, rejecting faulty goods
- Additional costs of frequent or small quantity orders
- Where goods are manufactured internally, the set-up and tooling costs associated with each production run.
Carrying Costs: The carrying costs of inventory include the following:
- Storage costs (rent, lighting, heating, refrigeration, air-conditioning etc.)
- Stores staffing, equipment maintenance and running costs.
- Handling costs.
- Audit, stock taking or perpetual inventory costs.
- Required rate of return on investment in current assets.
- Obsolescence and deterioration costs.
- Insurance and security costs.
- Costs of money tied up in inventory.
- Pilferage and damage costs.
Stock-out Costs: The stock-out costs are associated with running out of stock which includes the following:
- Lost contribution through the lost sales caused by the stock-out.
- Loss of future sales because customers go elsewhere.
- Loss of customer goodwill.
- Cost of production stoppages caused by stock-outs of WIP or raw material.
- Labour frustration.
- Over stoppages.
- Extra costs associated with urgent replenishment purchases of small quantities.
Assumptions of EOQ
To be able to calculate a basic EOQ certain assumptions are necessary:
- That there is a known, constant stockholding cost.
- That there is a known, constant ordering cost.
- Those rates of demand are known and constant.
- That there is a known, constant price per unit, i.e., there are no price discounts.
- That replenishment is made instantaneously, i.e., the whole batch delivered at once.
The following formula is used in calculation of EOQ: EOQ = √2QO
Q = Annual consumption C = Cost per unit
O = Cost of placing an order S = Storage and other inventory carrying cost.
EOQ WITH DISCOUNTS
A particularly unrealistic assumption with the basic EOQ calculation is that the price per item remains constant. Usually some form of discount can be obtained by ordering increasing quantities. Such price discounts can be incorporated into the EOQ formula, but it becomes much more complicated. A similar approach is to consider the costs associated with the normal EOQ and compare these costs with the costs at each succeeding discount point and then ascertain the best quantity to order. Price discounts for quantity purchase have three financial effects, two of which are beneficial and one adverse.
Beneficial effects - Savings will come from:
- Lower price per item, and
- The large order quantity means that fewer orders need to be placed and hence, ordering costs are reduced.
Adverse effects - Increased costs arise from the extra stockholding costs caused by the average stock level being higher due to the larger order quantity.
2. ABC Analysis
In this technique, the items of inventory are classified according to value of usage. The higher value items have lower safety stocks, because the cost of production is very high in respect of higher value items. The lower value items carry higher safety stocks. ABC analysis divides the total inventory list into three classes A, B, and C using the rupee volume, as follows:
- Items in class 'A' constitute the most important class of inventories so far as the proportion in the total value of inventory. The 'A' items consists of approximately 15% of the total items, accounts for 80% of the total material usage.
- Items in class B' constitute an intermediate position, which constitute approximately 35%of the total items, accounts for approximately 15% of the total material consumption.
- Items in class 'C’ are quite negligible. It consists remaining 50% items, accounting only 5% of the monetary value of total material usage.
The numbers are just indicative and actual break-up will vary from situation to situation. The above categorization is represented in the table given below:
Class of items
The ABC analysis of inventory class 'A' is made up of inventory items which are either very expensive or used in massive quantities. Thus these items, though few in number contribute a high proportion of the value of inventories. Class 'B' items are not so few in number, but also they are not too many either. Value wise also, they are neither very expensive nor very cheap. Moreover, they are used in moderate quantities. Class 'C contains a relatively large number of items. But they are either very inexpensive items or used in very small quantities so that they do not constitute more than a negligible fraction of the total value of inventories. The control of inventory through ABC analysis is exercised as follows:
- 'A' class items merit a tightly controlled inventory system with constant attention by the purchase and stores management. A larger effort per item on only a few items w ill cost only moderately, but the effort can result in large savings.
- 'B' class items merit a formalized inventory system and periodic attention by the purchase and stores management.
- For 'C class items still relaxed inventory procedures are used.
The table given below shows how an organisation treats the various classes of items according to their consumption value. For 'A' class items, the inventory policy, i.e. order quantity and re-order point should be carefully determined and the close control over the usage of materials is desirable. For 'B' class items, the economic order quantities and reorder level calculations can be done and larger stocks can be maintained. The review of these items may be done quarterly or half-yearly. In case of 'C class items, generally one year supply can be maintained. Periodic review once a year may be sufficient.
'A' Class Items (High consumption value)
‘B’ Class Items (Moderate consumption value)
'C Class Items (Low consumption value)
1. Very strict control.
1. Moderate control.
1. Loose control.
2. No safety stocks or very low safety stocks.
2. Low safety stocks.
2. High safety stocks.
3. Maximum follow-up and expediting.
3. Periodic follow-up
3. Follow-up and expediting in exceptional cases.
4. Rigorous value analysis.
4. Moderate value an
4. Minimum value analysis.
5. Must be handled by
5. Can be handled by
5. Can be fully delegated.
The technique tries to analyze the distribution of any characteristics by stock values of importance in order to determine its priority. This technique can be applied in all facets of organisation. Many organisations are applying this technique in materials management and spare parts management to identify the contribution made by the materials/spares in the total inventory value. On the basis of stock value, materials procurement strategy and consumption strategy is decided.
3. VED Analysis
VED analysis divides items into three categories in the descending order of their critically as follows:
- V’ stands for vital items and their stock analysis requires more attention, because out-of- stock situation will result in stoppage of production. Thus, 'V items must be stored adequately to ensure smooth operation of the plant.
- 'E' means essential items. Such items are considered essential for efficient running but without these items the system would not fail. Care must be taken to see that they are always in stock.
- 'D’ stands for desirable items which do not affect the production immediately but availability of such items will lead to more efficiency and less fatigue.
VED analysis can be very useful to capital intensive process industries. As it analyses items based on their critically, it can be used for those special raw materials which are difficult to procure.
4. FNSD Analysis
Age of inventory indicates duration of inventory in organisation. It shows moving position of inventory during the year. If age of inventory is minimum it means, the turnover position of that particular item of inventory is satisfactory. If the age of any particular item of inventory, it indicates the slow moving of stock which may be due to lower demand for the product, inefficiency in shocking policy, excessive stocking etc. The excessive investment in stocks means, high investment is locked-up in inventory leads to lower profitability of the firm due to excess carrying costs. FNSD analysis divides the items into four categories in the descending order of their usage rate as follows:
- 'F' stands for fast moving items and stocks of such items are consumed in a short span of time. Stocks of fast moving items must be observed constantly and replenishment orders be placed in time to avoid stock-out situations.
- 'N' means normal moving items and such items are exhausted over a period of a war or so. The order levels and quantities for such items should be on the basis of a new estimate of future demand to minimize the risks of a surplus stock.
- 'S' indicates slow moving items, existing stock of which would last for two years or more at the current rate of usage but it is still expected to be used up. Slow moving stock must be reviewed very carefully before any replenishment orders are placed.
- 'D' stands for dead stock and for its existing stock no further demand can be foreseen. Dead stock figures in the inventory represents money spent that cannot be realized but it occupies useful space. Hence, once such items are identified, efforts must be made to find all alternative uses for it. Otherwise, it must be disposed off.