Google Sheets Inventory Management System
![Google Sheets Inventory Management System]()
![google sheets inventory management system 1]()
![google sheets inventory management system 2]()
![google sheets inventory management system 3]()
![google sheets inventory management system 4]()
This tracker lets the user enter inventory transactions into a database in Google Sheets. Each row represents a movement of inventory.
The resulting tables and summaries will then run against the database entries in order to show existing inventory balances by SKU and by location. In theory there is no limit to the number of SKUs and locations that can be tracked as long as G sheets can process it. You simply would just need to drag formulas over and down as much as needed.
The default is 25 locations and 500 SKUs but that can be increased without much effort.
This works great if you have a warehouse or multiple warehouses and then smaller surrounding locations that you want to know the level of inventory by SKU as units move around to different locations. This template automatically shows reports of the current balances for better organization and insight into what is going on with inventory and where things are.
There are also more advanced modules in this. For example, you can enter an expiration date for a given incoming purchase batch and then show reports on how long it has been in inventory based on an input for '# of days until expiry'. There are extra columns for each row entry of the database so that you can mark if a given part of a batch has left.
For safety stock management, there is a tab that shows what items are below acceptable levels. Each SKU will have a user input for the minimum level of units that should exist across all locations. The report tab for that will show all items below the defined level in a separate summary.
There is a way to enter when inventory is actually 'sold' as that is a third dropdown selection in the 'add/deplete' column. This is helpful if you want to know the total items that have left the system. There is a report tab that displays all 'sold' transactions.
The user can run reports by selecting a SKU individually and seeing balances per location or they can go to the 'view all' tab and see the balance of all SKUs across all locations at once.
There is also an inventory valuation aspect of this based on the purchase price of units that enter the system. The user can define the date ranges to use in order to come up with the weighted average cost per unit and that will apply to all ending balances.
This is built in Google Sheets so you need a free Gmail account to access the link. Once you do, just hit File and Make a Copy for your own editable version of the template.
To enter 'starting balances' simply make a database row entry for each SKU and at each location that is an 'add' entry for the amount of inventory currently existing therein.
If you wanted to use this in Excel, it would be possible, but you would need Office 365, be apart of Office Insiders (free) and understand how to adjust the filter functions in Excel. Those functions have a few small differences in how you write out the formulas in Excel, but it is possible to filter on multiple criteria.
This tracker lets the user enter inventory transactions into a database in Google Sheets. Each row represents a movement of inventory.
The resulting tables and summaries will then run against the database entries in order to show existing inventory balances by SKU and by location. In theory there is no limit to the number of SKUs and locations that can be tracked as long as G sheets can process it. You simply would just need to drag formulas over and down as much as needed.
The default is 25 locations and 500 SKUs but that can be increased without much effort.
This works great if you have a warehouse or multiple warehouses and then smaller surrounding locations that you want to know the level of inventory by SKU as units move around to different locations. This template automatically shows reports of the current balances for better organization and insight into what is going on with inventory and where things are.
There are also more advanced modules in this. For example, you can enter an expiration date for a given incoming purchase batch and then show reports on how long it has been in inventory based on an input for '# of days until expiry'. There are extra columns for each row entry of the database so that you can mark if a given part of a batch has left.
For safety stock management, there is a tab that shows what items are below acceptable levels. Each SKU will have a user input for the minimum level of units that should exist across all locations. The report tab for that will show all items below the defined level in a separate summary.
There is a way to enter when inventory is actually 'sold' as that is a third dropdown selection in the 'add/deplete' column. This is helpful if you want to know the total items that have left the system. There is a report tab that displays all 'sold' transactions.
The user can run reports by selecting a SKU individually and seeing balances per location or they can go to the 'view all' tab and see the balance of all SKUs across all locations at once.
There is also an inventory valuation aspect of this based on the purchase price of units that enter the system. The user can define the date ranges to use in order to come up with the weighted average cost per unit and that will apply to all ending balances.
This is built in Google Sheets so you need a free Gmail account to access the link. Once you do, just hit File and Make a Copy for your own editable version of the template.
To enter 'starting balances' simply make a database row entry for each SKU and at each location that is an 'add' entry for the amount of inventory currently existing therein.
If you wanted to use this in Excel, it would be possible, but you would need Office 365, be apart of Office Insiders (free) and understand how to adjust the filter functions in Excel. Those functions have a few small differences in how you write out the formulas in Excel, but it is possible to filter on multiple criteria.
