Hello, In this blog I will be writing about the inventory management system. This blog will help Excel learners, inventory managers and warehouse supervisors to track if the inventory is below threshold. This blog contains all the information on how to develop Excel sheet, which can be accessed anytime. This blog will help you know how much stock is available at warehouse.
If the stock is less, an automated email will be sent to the user or owner of company. I have learned Macros, Pivot Table, formulas such as IF, SUMIF, COUNTIF, IFERROR, HYPERLINK in MS Excel. I promise that you can learn about concepts which are important while making such a software.
Topics to discuss in this blog:
Developing Dashboard to interact with user
Creating sheet for Product Details
Creating sheet to enter Products in inventory
Creating sheet to enter Sales in sold out sheet
Creating Stock Available sheet
1.Developing Dashboard to interact with user:
Developing the dashboard is important as it will the first step of all calculations. This step will decide what product is selected by the user and accordingly output will change in all sheets. In the above image I have created the "Add in inventory" (Green button) and "Sold Out" (Blue button) buttons using the shape command. In the same way I have created the three buttons shown in light blue colour. To take the input from the User, I have created space to input the values in cell "Bill No.", "Date", "Item", "Qty".
Understanding values entered in cells by user:
Bill No: Numeric datatype bill number
Date: Should be entered in DD/MM/YYYY format
Type of Item: This cell uses data validation cell. This cell will select the type of product from list of products.
Item: Once the product is selected, based on product selected it filters the
product and displays in "Item" cell.
Qty: In this cell user has to enter the quantity he has sold or going to enter in
the stock.
2. Creating Product details sheet:
This sheet is created to have a raw data from where we can extract the products. This sheet has all products listed according to their weights. We can see Nut Butters, 1 Oz, 2 Oz, 6 Oz, 1 Lb and chocolate bars.This is the sheet from where all the products are taken in the dashboard sheet.
To filter the products, I used simple nested IF formula in the Product details sheet:
=IF(Home!$F$6=$B$3,$B$4:$B$23,IF(Home!$F$6=$C$3,$C$4:$C$23,IF(Home!$F$6=$D$3,$D$4:$D$23,IF(Home!$F$6=$E$3,$E$4:$E$23,IF(Home!$F$6=$F$3,F4:$F$23,IF(Home!$F$6=$G$3,$G$4:$G$23," "))))))
This formula looks for the cell in home sheet, what is the weight of the product. Then this formula filters the products listed in the product details sheet shown below.
3. Creating sheet to Enter Products in inventory:
Once we have selected the product, we can enter the product to inventory sheet. I have recorded a Macro for this. Whenever any one enters the details about the product to enter. And when we click on Green button, the saved macro for this button will automatically run the macro and paste the information in the 'Add in Inventory' (Green button) sheet. The green button shown below has the saved macro.
4. Creating sheet to enter Sales in sold out sheet:
Similarly to above step, the 'Sold Out' button (Blue Button) has macros which will transfer the details from 'Home' sheet to 'Sold Out' sheet. For this I have used the same method, that is macros. In the above figure, the Sold Out button in blue colour will run the macro.
5. Creating Stock Available sheet:
This is the important sheet in the whole inventory management, because it calculates and gives the exact stock available. We can that, how it looks like in the below picture.
There is Item, Produced/Added in inventory (Column C), Sale/Sold Out (Column D), available stock (Column E) and Email Link (Column G). Now we will see one by one the formulas to calculate the stock available and Email link. If the sale is less than 75, it will show a link for sending email. Also, I have used conditional formatting to display the inventory which is less in red colour in Column E.
In this sheet I have added Refresh Button (Red button), as you can see in the below picture. When you click on the button it will make use of advanced filter option and show the updated inventory like displayed in the above picture. Now, we will see in detail how the I have used the macro to refresh the Stock and calculated available inventory.
To filter the produced quantity from the 'Added in Inventory' sheet, I have used SUMIF function in Excel. I used the below formula. This formula sums the quantity from 'Add in Inventory' sheet based on quantity in B4 cell.
=SUMIF(Table_22[[#All],[Item]],[@Item],Table_22[[#All],[Qty:]])
To filter Sale Quantity from 'Sold Out' sheet, I have used SUMIF function to calculate the inventory and display the sum of all products. This will also sum the quantities that are sold out from warehouse.
=SUMIF(Table_2[[#All],[Item:]],[@Item],Table_2[[#All],[Qty:]])
To calculate the Stock Available I have subtracted the Sold Quantity from Produced Quantity. Formula for calculating stock is:
=[@[Produced Qty]]-[@[Sale Qty]]
Now, we will se how to send an alert to the user if the inventory is low. I have used IF, HYPERLINK formula from cells G4 to G29.
=IF([@[InStock<75,HYPERLINK("mailto:"&$L$1&"subject="&$J$1&"&cc="&$J$2&"&body="&F4,"Send Email"),"")
=HYPERLINK(Link_name, friendly_name)
We will see how I have applied the IF formula which is shown above. The formula basically applies IF formula. If the 'Stock Available' is less than 75 products, it applies HYPERLINK formula, otherwise blank.
HYPERLINK uses mailto keyword which takes the email ID from L1 cell, to whom the alert will be sent. Subject is used, which has the subject at J1 cell. Also, the body is taken from F4 cell. When the inventory is less then, we can see that there is an option to Send Email. This option appears if inventory is below threshold, that is 75 Nos.
Once, we click on the link, an email window pops up and we just have to click on button to send the email. I have shown in the below image how the email window looks like.
Thank You!
Soham Sanjay Shinde