Fully Automated Stock and Inventory Management System in Excel [No VBA]: The Final Part

Welcome to Part 3 on 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 part is about integrating the quantities of items in the purchase and sale records with the stock summary and displaying the current stock of items. We will learn to add insufficient stock alerts and many more premium automation techniques!

If you haven’t already read through the previous parts, you can do so by clicking the links below:

Let’s get started!

Integrating the values in the records with the stock summary

Now, it’s time to start displaying the total purchased and sold quantities of an item in the stock summary. Here’s how we can do it using the SUMIF formula in Excel.

Go to the Stock summary sheet.
In the first blank cell under the Purchase Qty header, open the SUMIF formula.
Type =SUMIF(
Now, for the range argument, go to the Purchase sheet.
Select the entire Item header and column in this sheet.
Put a comma and go back to the Stock sheet again.
Select the first cell under the Item header.
Put a comma and go to the Purchase sheet.
Select the entire Qty header and column in this sheet.
Close brackets and hit ENTER.

You can view the total number of an item purchase in the Purchase Qty column in the Stock summary.

Repeat the same steps for the Sales Qty values as well in the stock summary sheet.

Now, to display the current stock, we just have to subtract the total purchase quantities from the total sale quantity in the Stock sheet.

Go to the Stock sheet.
Click on the first blank cell under the Stock heading.
Type = and click on the first value under the Purchase Qty.
Type – click on the first value under the Sales Qty.
Press ENTER.

That’s it! The stock is fully integrated with the purchase and sales records displaying stock items correctly. Try putting a value and check if the stock sheet is properly integrated with the records.

Tracking bill nos. and displaying insufficient stock alerts

Next up, it’s time to start adding the data that we want and tracking it with alerts.

1. Tracking the last bill number precisely

The next step is to track the last bill number separately for sales and purchase transactions in the billing interface. Here’s how we can do it.

Let’s start with tracking the last bill number of the last Purchase transaction.

Click in the Last Bill no. field in the billing sheet.
Type =MAX( and go to the Purchase sheet.
Select the bill number series from the bottom to the top empty cell.
Close the brackets and press ENTER.

The field will display the last bill number by finding out the highest value in the bill number column in the purchase sheet. Make sure the bill number are a series of numbers.

Now, the problem here is, this value does not change on changing the option from the drop-down list above the Last Bill No. field, which is Entry type.

So, let’s apply some logic to the two fields.

The task is very simple. Just change the formula in the last bill number a little bit and insert the IF function before the MAX formula.

Type IF( after the = sign.
Click on the dropdown cell above.
Type =”Purchase”
Put a comma and keep the MAX formula as it is.
Put a comma again and go to the Sales sheet this time.
Select the Bill no. column in the table and close the bracket twice. One for the MAX formula and another for the IF function.

You can see that the Last Bill No. field is tracking the bill number for the purchase and sales records separately.

2. Displaying insufficient stock alerts

The next step is to precisely display an alert whenever a user tries to sell a quantity beyond the stock quantity. These alerts must not be displayed while entering a purchase transaction.

So, let’s do it using the VLOOKUP and IF functions. The VLOOKUP function will look up the item name entered in the interface in the stock sheet. The IF function will display an alert if the stocks are insufficient for sale.

We know that we have 376 rulers in stock. Let’s try to create a sale transaction of 500 rulers.

Go to the Billing sheet.
Enter a random sale transaction of 500 rulers in the billing form.
Click on a blank cell where you want the alert to display.

Type =VLOOKUP(
Click on “Ruler” in the item name.
Put a comma and go to the Stock sheet.
Select the entire stock summary table range.
Then type the column number of the stock value column that is 4 here.
Put a comma and type 0 or FALSE because we want an exact match of the look-up result.
Press ENTER and you will see the stock value displaying in the formerly blank cell in the billing sheet.

Also read: What is VLOOKUP & How to Use VLOOKUP In Excel?

Now, we have to integrate this function with the quantity entered in the form and display an insufficient stock alert. Let us modify the formula slightly to achieve this.

Type IF( before the entire VLOOKUP function.
At the end of the VLOOKUP function, type < and click on the quantity entered in the form.
Put a comma and type “Insufficient items in stock”.
Put a comma type “” and close the bracket.
Hit ENTER.

You can see that the alert message is displaying in the cell correctly.

Now, we must only allow this alert message to display for Sales transactions. Let’s modify the formula again to achieve this.

We will use the IF formula once again.
After = type IF( and click on the cell with the dropdown list above.
Type =”Sale”,
Then type ,””) after the latest ,””)
Hit ENTER.

no purchase formula — The complete formula for reference

This is how your formula should look like before pressing ENTER.

Now, the problem is, whenever we enter a new item name in the form, the N/A error displays.

To fix this, we will modify the formula one last time.

Edit the formula in the formula bar or double on the cell.
After = type IFERROR(
Go to the end of the formula, and type ,””)
Hit ENTER.

Great! Now the error alert doesn’t show up when a unique item name is entered.

Conclusion

The tutorial ends here. The final part was about integrating the quantities of items in the purchase and sale records with the stock summary and displaying the current stock of items. We also learned to add insufficient stock alerts for excess sales transactions and many more premium automation techniques!