For checking the performance of your business, you might have needed to maintain store inventory. It’s quite difficult to handle such type of report manually. Rather, you can do it in Excel efficiently. In this article, I’ll show the step-by-step guide on how to maintain store inventory in Excel with proper explanation.
At the end of this guiding session, you’ll be able to make the following store inventory report.
However, you can also download the template for your convenience.
Download Practice Workbook
3 Steps to Maintain Store Inventory in Excel
Let’s say, you have some Fruit Items with their Product ID in the Main Stock sheet.
Now, you need to prepare a store inventory report based on the Incoming Stock and Outgoing Stock sheets.
Step 01: Deal with Incoming Stock List
In the Incoming Stock sheet, you might have Date, Product ID, Fruit Items, and Quantity fields.
So, you need to create a drop-down list for choosing the Product ID. For doing that, do the following tasks.
- Firstly, select the C5:C16 cell range.
- Then, go to the Data tab > Choose Data Validation option.
- In the Data Validation box, choose the List from the drop-down list under the Allow option.
- Lastly, specify the Source as =’Main Stock’!$B$5:$B$16 and press OK.
Eventually, you’ll get a drop-down list of Product IDs as shown below.
Next, you need to use the following formula to return the name of Fruit Items based on the C5 cell.
Here, C5 is the look-up value, ‘Main Stock’!$B$4:$G$16 is the table_array, 2 is the col_index as Fruit Items is located at the 2nd column based on the Product ID field in the table, and lastly FALSE is for approximate matching.
⧬ Formula Explanation:
So, if you pick any Product ID e.g. 1001-P2 from the drop-down list, you’ll get Apples in the D5 cell.
Step 02: Deal with Outgoing Stock List
In addition, create the same fields in the Outgoing Stock sheet.
Next, generate a drop-down list in the C5 cell as done in the Incoming Stock sheet. Also, copy the VLOOKUP formula (by pressing CTRL + C) from the sheet and paste it (by pressing CTRL + V) into the D5 cell of the current sheet.
Step 03: Maintain Overall Store Inventory Report in Excel
Then, you have to prepare the Main Stock sheet which will be updated automatically based on the entry of the Incoming and Outgoing Stock sheets.
For example, you may use the SUMIF function, a popular function for calculating the sum value from different aspects depending on criteria also. So, the formula will be like the following in the D5 cell.
=SUMIF('Incoming Stock'!$D$5:$D$16,'Main Stock'!C5,'Incoming Stock'!$E$5:$E$16)
Here, $D$5:$D$16 and $E$5:$E$16 are the cell range representing Fruit Items and Quantity of the Incoming Stock sheet respectively. And, the C5 is the starting cell of Fruit Items in the active sheet.
⧬ Formula Explanation:
In the above formula, ‘Incoming Stock’!$D$5:$D$16 acts as the range argument, ‘Main Stock’!C5 is the criteria argument to specify which cells to add, and ‘Incoming Stock’!$E$5:$E$16 is the sum_range [optional] argument. Therefore, the SUMIF function sums up the Quantity if the criteria match the range.
Likewise, create a formula utilizing the SUMIF function for updating the Outgoing Stock Quantity in the active sheet based on the Outgoing Stock sheet.
=SUMIF('Outgoing Stock'!$D$5:$D$16,'Main Stock'!C5,'Outgoing Stock'!$E$5:$E$16)
Later, subtract the Outgoing Stock Quantity from Incoming Stock Quantity to get the Quantity in Stock.
Here, D5 and E5 are the starting cell of Incoming Stock Quantity and Outgoing Stock Quantity respectively.
Subsequently, you may apply the IF logical function to get an alert message whether you need to update the stock now or not.
=IF(F5<=10,"Update Stock","Not Needed")
Here, F5 is starting cell of Quantity in Stock.
⧬ Formula Explanation:
In the above IF formula, the function will show “Update Stock” if the F5 is less than or equal to 10, else it will return “Not Needed”. I used the minimum quantity as 10, however, you can change it if you want.
Lastly, fill up the cells of the Incoming Stock sheet.
And, do the same task for the Outgoing Stock sheet.
Now, it’s time to check whether your created store inventory report is working perfectly or not.
- Just drag down the cursor in the case of the D5, E5, F5, and G5 cells.
Eventually, you’ll get that your Main Stock sheet is updated automatically!
Now, change the entry in the Incoming Stock and Outgoing Stock sheet and check the performance of your business. And, let me know if this Store Inventory Report is working perfectly or not.
This is how you can maintain store inventory in Excel efficiently. I firmly believe this article will be highly beneficial for you. Anyway, don’t forget to share your thoughts in the comments section below.