This article provides guideline on making inventory management sheet in Google Sheets. This sheet can be used to keep track of in and out of the stock and the balance stock will keep on automatically adjusting when you enter the details of purchase and sales.
Table of Contents
How to Make Guideline
Get Readymade Google Sheets Template
Video Tutorial
How To Make Inventory Management Sheet
Follow below given steps to make inventory management sheet.
- In A1 cell, write the title of sheet i.e “Inventory In Out and Balance Tracker”.
- In A3 Cell, write Stock In or Purchase.
- Below A4 cell write column headers of Date, Item Name and Quantity.
- In D3 cell, write Stock Out or Sales and below it write column headers of Date, Item Name and Quantity.
- In G3 cell, write Stock Balance and below it write column headers of Item Name and Stock Balance.
- Merge and Center the stock in out and balance headers above the sub categories (like date, item name and quantity)
- In Item Name column of stock balance section, list out all the inventory items that you have in your warehouse.
- In Item name column of stock in and out section, create a drop down selection menu by linking the item list created in stock balance section.
- Validate the date column by taking “is valid date” option in the data validation. Which enables the date picker calendar when double clicked in the cell.
- Enter few dummy transactions in the in and out of stock before proceding to use formula in the stock balance quantity column.
- Use the SUMIF function to dynamically get the total stock in and out details for each item in the stock balance section. The required formula is at the end of this list.
- Test whether the stock balance is calculated correctly.
- Freeze the column headers part by going to data freeze option in the view tab.
Formula:
=SUMIF($B$5:$B$16,G5,$C$5:$C$16)-SUMIF($E$5:$E$16,G5,$F$5:$F$16)Readymade Inventory Management Template
If you want to use the readymade inventory management template, get it from below link.
https://docs.google.com/spreadsheets/d/1d9qMG3leLNWPcWAeyoA2b950DSetKToFB0pco1BysYU/copy
After opening this template, for the editing access, you have to click on “File” tab, then “Make a Copy”. (In the Google Sheets Window)
Video Tutorial
If you want to make it from scratch by watching video tutorial, below you have the YouTube video. Just play it and follow the step by step guideline.
