An inventory management system is not just about keeping records of the movement of stock in and out of a store or warehouse in an Excel spreadsheet. A simple inventory management system should be able to identify how many merchandise are left in the store, what products require a reorder, and where, when and what merchandise has entered and left the warehouse. Therefore, it is important to generate accurate reports. But to be able to do this, the data must first be organized in a systematic way.

Using the details, we could set up a pivot table to summarize the stock going in and out of the store. It will allow us to organize the report so that we can identify the level of stocks by product groups, product name and the locations to which they have been moved. The records could also be grouped so that we can track the stock movements by month. In our report below (see the row before the grand total), we can quickly establish that there is a net increase of 19 units of adhesives in the month of October, a net decrease of 3 units in the month of November, another drop of 4 units. in December, resulting in 12 units of adhesive left in the store.

Using the same report, we could drill down to see individual product movements

in the Adhesive group per month.

Alternatively, we could also present the amount of stock left in the store by changing the report settings. In our example below, we can know that the store keeps 4 units of “3M Command ADH Large Hook” in November and 1 unit in December. If we sort the report in descending order, we could immediately list the products we have to reload quickly to avoid an out-of-stock situation.

And if this report isn’t too relevant yet, we could even show the stock movements (the ins and 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 the accuracy of the data records, we also shared that we could set up a drop-down list that depends on the selection made by the user using another drop-down list.

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