This tutorial is everything about creating a fully automated Stock and Inventory Management system that will help you keep track of the stock of items in the inventory with a click of a button!
This guide collectively teaches you to maintain sale and purchase records, create a real-time stock management database integrated with these records that helps in keeping track of the current stock of items in the inventory- all without VBA coding!
This article is Part 2 is about integrating the Stock Summary with the purchase and sale records. The item stock status will update based on the latest purchase or sale transaction made.
Also read: Automated Stock and Inventory Management System in Excel [No VBA] PART 1
Let’s get started!
How does a Smart Stock Management System Work?
An integrated stock summary keeps track of the latest purchase and sale transactions and informs the users of the current stock of an item in the inventory.
Additionally, it is expected to maintain a unique record of every item that is bought or sold any number of times.
For instance, 20 pens were bought on the 1st of a month, so, we added a purchase entry for 20 pens. The stock summary displays an entry of 20 pens in stock.
On the 5th, a customer purchased 3 pens from the store, so we created a different entry this time, that is a sales entry in the records. The stock summary still shows a single entry of pens but displays 17 pens in stock now.
This is how a smartly integrated stock management system works. Fortunately, this is possible to do in Excel and this is what we are going to learn in this Part 2 guide.
Integrating sale/purchase records with the stock summary
Now you know how a smart stock management system works. Let us create one in Excel using the Advanced Filter tool.
- Go to the Stock sheet.
- Type Item in a cell.
- Click on the cell below and type =”<>”
- Press ENTER.
This text is a criterion for the advanced filter to start working.
The next step is to create a Refresh button that will update the stock summary and display the current stock of an item.
Before we begin, we will start recording a macro.
- Go to the View tab.
- Click Macro and click Start Recording.
- Once the recording starts, click on a blank cell in the sheet.
- Go to the Data tab.
- Click on Advanced.
- Choose Copy to another location.
- In the List range field, go to the Purchase sheet.
- Select the Item column from the header till the last item entry in your table.
- Click in the Criteria Range field and go back to the Stock sheet.
- Remove the existing range in this field (if any).
- Select the “Item” and “<>” cells.
- In the Copy to field, select the Item header in the stock table.
- Check Unique records only.
- Click OK.
- Stop the recording from the status bar below.
Hide the column with “Item” and “<>” entries.
- Go to the copy the Sale or Purchase button.
- Paste it into the Stock sheet.
- Rename it as Refresh or Refresh Summary.
- Right-click on the button.
- Click on Assign Macro.
- Select This Workbook below.
- And select the Refresh macro.
- Click OK.
- Adjust the button aesthetics by pressing CTRL and adjusting the colors from the home tab.
Now go to the Billing Interface and enter two or more transaction information of the same item with slight differences. Then go to the Stock summary and press the Refresh button.
You can see that there are two entries of an item in the purchase records but only reflects in the stock sheet.
The further steps are about integrating the total purchase and sale quantity and displaying the current stock status of an item in the stock sheet. We will also learn to track the last bill number in the billing interface.
Fully Automated Stock and Inventory Management System in Excel [No VBA] PART 3
Conclusion
This article was Part 2 guide to creating a fully automated stock and inventory management system in Excel. We learned to integrate multiple entries of items in the purchase and sale records as a single entry in the stock summary. We also learned to create a refresh button that updates the stock summary.
Part 3 is about integrating the quantities of items in the purchase and sale records with the stock summary and display the current stock of items.
We will learn to create more buttons to navigate to the Purchase, Sale, or Stock Summary records from the billing interface and learn to track the last bill number in the interface. Stay tuned for more incredible content!