Business

A complete inventory management system built with Excel

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 products are left in the store, which products require a reorder, and where, when, and which products entered and left the warehouse. Therefore, it is important to generate accurate reports. But in order to do this, the data must first be organized systematically.

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 stock by product groups, the name of the product and the locations to which they have been moved. Records could also be grouped so that we can track stock movements by month. In our report below (see the row above 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 remaining in the store.

Using the same report, we could drill down to see individual product movements
in the Adhesive group by 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 kept 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 month’s stock movements.

To make it easier to capture the details and improve the accuracy of the data records, we also shared that we could configure a dropdown list that depends on the selection made by the user using another dropdown list.

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

Leave a Reply

Your email address will not be published. Required fields are marked *