Using Excel For Your Inventory

The magic word is inventory management and a few experts believe in 'just in time' inventory management. There are many software programs that have been created for the sole purpose of inventory management and they are either 'over kill' or very expensive. Small business can easily use Excel to manage their inventory and since most people use either MS-Office or Open Office in their offices, there are no additional investments required.

Inventory management is important for any business - small or big. Because if you don't have the goods customers are looking for, why did you start the business in the first pace and if you have too many of goods that no one wants to buy you will end up making a huge loss. So how do we ensure that we have the goods that the customer is looking for and avoid storing too much of the products.

Let's assume for a moment that you have a store and one of the items you sell or have been selling is red silk skirts. How many have you sold on an average in a period (day, week or month)? What is the maximum sales of this item that you can expect realistically? What is the time lag between placing the order and receipt of the goods? You also need to take into account a safety margin: the supplier doesn't have the items in stock, the transport is on strike, there is an accident, etc.

Depending on the average sales per period and the maximum expected sales you can calculate the variance. The safety margin can be taken as twice the variance as a thumb rule. The sum of the safety margin and the time taken to deliver the goods gives us a good approximation of our re-order level. Now based on the quantity in hand and the calculated re-order we can easily calculate the re-order quantity.

You can use the Excel worksheet cells with the following headers to create an inventory management for each of your items: Item ID, Item Name, Description, Unit Price, Quantity in Stock, Reorder Level, Quantity on Reorder, Max sales per day, Average sales per day, Variance, Time to deliver goods. Based on the above discussion and using standard Excel formulas, you can calculate the variance, safety margin, reorder level and finally the reorder quantity!

With time and with a better understanding of your business including seasonal variations in sales you can tweak this simple inventory system to achieve a more comfortable handle on your inventory!