A complete inventory management system built with Excel

An Inventory Management System is not only about keeping records of the movement of the stock in and out of a store or warehouse in an Excel spreadsheet. A simple inventory management system should be able to identify how much goods are left in the store, which products require re-order and where, when and which goods have moved in and out of the warehouse. Thus, generating accurate reports is important. But to be able to do this, the data must first be organised systematically.

Using the details, we could set up a pivot table to summarise the stocks that move in and out of the store. It will allow us to organise the report so that we could identify the stock level by product groups, product name and the locations they have moved to and from. The records could also be grouped such that we can track the stock movements by month. In our report below (refer to row above grand total), we can quickly establish that there is a net increase of 19 units of adhesives in the month of Oct, a net decrease of 3 units in the month of November, another 4 units drop in Dec, which all resulted in 12 units of adhesive left in the store.

Using the same report, we could drill down to see the movements of individual products in the Adhesive group by month.

ALTernatively, we could also present the quantity of stocks remaining in the store by changing the setting of the report. In our example below, we are able to know that the store is left with 4 units of “3M Command ADH Large Hook” in Nov and 1 unit in Dec. If we sort the report in descending order, we could immediately list down the products we have to top up quickly to prevent an out-of-stock situation.

And if this report is still not too relevant, we could even show the stock movements (the ins and the outs) for each month and then the stock balance for the month to better explain the stock movements for the month.

To make it easier to capture the details and improve on the accuracy of the data records, we also shared on we could set up a dropdown list that is dependent on the selection made by the user using another drop list as shown below:

The complete system helps to improve the data entered into the inventory management system and then prepare reports that help the store manager to make better quality decisions with regards to stock replenishment and stock movement in the store.