A Quick Guide to Inventory Management

Standard Inventory Management Terms

First, it is important to understand frequently used inventory terminology and definitions:

Cycle Count: A periodic count of all supplies and inventories.

Reorder Point (ROP): The inventory level at which a new order should be placed.

Economic Order Quantity (EOQ): The quantity to order once the reorder point is reached.

Minimum Order Quantity: The absolute minimum purchase tender implemented by a vendor.

Inventory Turnover: The ratio showing how many times inventory is sold and reordered.

5S: A Japanese workplace organization methodology, the 5S’s are Sorting, Setting in Order, Sweeping, Standardizing and Sustaining.

Reorder Points and Excel as a Tool

Most small businesses utilize some type of inventory records and/or cycle count logs. The critical component is what you do with the collected information. The good news is that you do not have to have a green belt in Six Sigma® to learn how to utilize technology such as Microsoft Excel to create a sophisticated recording system. A good idea is to enter cycle counts into an Excel spreadsheet so that the data can be used to identify trends in your inventory.

The first aspect of materials management is establishing a reorder point that eliminates excessive inventory and prevents you from paying for express freight. To establish a reorder point, you need to know your historic and projected material usage. This is another case in which an Excel spreadsheet, including all inventory counts, proves quite useful. Be sure to include every item in your inventory, and label the counts by date. Once you have a few months’ worth of data, you can begin to develop usage patterns. For example, let’s assume that you have a flavoring with SKU number 1234, and that you take inventory counts once per week. You may log this information into Excel as illustrated:

With this information, you can easily determine on average how many units of product 1234 you are using on a weekly basis. In this example, it is easy to determine that product 1234 has an average usage level of 5 cases per week. With that average, you can determine reorder points by using the following formula:

Usage per week x lead time in weeks + safety stock

The safety stock is stock that you want to keep just in case you see fluctuation in the usage that may deplete your inventory faster than you anticipate. Let’s assume that we want a safety stock of 3 cases of product 1234—a stock to prepare us for unusually high usage.

Using data from product 1234 above, supposing a 1-week lead time, we determine the reorder point is 8 cases (5 cases x 1 week + 3 safety stock).

Economic Order Quantity

The next basic concept of material management is called economic order quantity (EOQ). The economic order quantity indicates how many units to purchase each time you reach the reorder point (ROP). There are complicated formulas that can be used to compute EOQ, but those may be excessive for a small business. An appropriate small-business approach would be to give consideration to the following underlying assumptions:

1. At what order quantity can you achieve a price break?
2. At what order quantity can you avoid shipping, handling or freight charges?

Let’s assume that the minimum order quantity of product 1234 is 4 cases. Given that the average usage of product 1234 is 5 units per week, the reorder point is 8 cases, and the minimum order quantity is 4 cases, an ROP of either 4 or 8 cases would be most appropriate. So, each time you reach the ROP of 8 cases, an order for 4 additional cases is placed with the vendor. As demand fluctuates, you may want to reconsider your ROP and your EOQ.

Inventory Turnover as a Key to Profitability

Another consideration is inventory turnover, which is critical for businesses that want to maintain a higher positive cash flow while controlling costs. Inventory turnover is a measure of how many times per period inventory is sold, determined by a simple calculation:

Cost of Goods Sold/Average Inventory

The cost of goods sold is your cost of inventory that has been sold. For example, let’s say that each unit of product 1234 costs $2, and each unit makes 2 widgets (common term for manufactured goods). The cost of goods sold would equal $1 per unit. Next, assume we sold 25 widgets in a week. Our cost of goods sold for the week is $25.

To compute average inventory, you need to choose a period of time. For an example, let’s consider product 1234:

The average inventory is calculated to be 9 units (the sum of the values divided by number of values).

We now have the numbers to use in our inventory turnover ratio:

25/9

Our inventory turnover is 2.78. This indicates that our inventory was sold and replenished 2.78 times during the week.

Once you begin to develop solid data, that data can be used to establish goals and benchmarks for your business. A key to profitability is increasing the inventory turnover ratio. A high inventory ratio means that you are selling inventory quickly, which means that the cost of holding your inventory has decreased, and may make a noticeable difference in your cash flow.

As an example, let’s set an inventory turnover goal for our widget. The ratio is currently 2.78, and our goal is to see that metric increase to 3. There are several ways to accomplish this. The first way would be to increase sales. But what if, because of the economic climate in your region, sales remain flat? An alternative method to increase your inventory turnover ratio would be to order supplies in smaller lots. If you are already placing orders that only meet minimum order quantity requirements, consider asking your supplier for flexible shipping options.

Don’t Forget the 5S’s

And last of all, let’s discuss the 5S concept. Organization and cleanliness are important concepts in any business without regard to size. The 5S’s are:

• Sorting. Sorting involves grouping materials in a logical manner, such as by product family, numerically or alphabetically.
• Setting in order. There is an old adage, “A place for everything and everything in its place.” It is easy to access tools, equipment and materials when they are always in the same location, every time. It is always a good idea to label storage locations for easy identification and retrieval.
• Sweeping. Another adage says, “Cleanliness is next to godliness.” If you are involved in the food industry, a clean business place not only enhances the look of your store, it also
  leaves a positive impression with your customers.
• Standardizing. “How do I do this?” or “What do I do when…?” These questions are often repeated to managers on a regular basis. To ensure consistency, it is ideal to have written policies and procedures. Room for errors is significantly reduced and accountability can be enforced when policies are properly conveyed.
• Sustaining. Diligence in the maintenance of systems, strategies and policies will keep your business as efficient and profitable as possible.

To learn more ways to successfully manage your business utilizing Microsoft Excel spreadsheets to develop usage history and other pertinent data:

• Excel Forum

http://www.excelforum.com/

• Excel Help and How To

http://office.microsoft.com/en-us/excel-help/

Investing time and energy in inventory management is an important step to effectively streamline your business. Having an understanding of the product you produce and turnover can cuts costs, help you better evaluate and manage your business, and discern consumer trends. Knowing the intricate details of your business is your business, and it will ultimately aid in its overall success.