Inventory Management in Google Sheets

 This is a Google Sheets inventory management and tracking spreadsheet. I have never done one specifically in Google Sheets and it turns out there are some really nice functions in this software that make it effective at creating inventory tracking tools for any small / medium business.

$45.00 USD

After purchase, the template will be immediately accessible. This sheet can also be found in the accounting and inventory spreadsheet bundles.


inventory tracking google sheets

You will be required to have a free Gmail account in order to use this. The download contains access to the sheet and once you open it, just go to File > and Make a Copy for your own editable template. There are also instructions on the file.

Inventory forecasting and management can be hard and I've seen that with many businesses requesting all sorts of management tools for inventory over the years. This spreadsheet is a tracker tool that lets you enter inventory transaction data (entering and leaving) in order to see the current balance and value of inventory across multiple SKUs and multiple locations.

Technically there is no limit to the number of SKUs and Locations that can be tracked with this Google Sheet template. By default, the location count is capped at 26, but you can expand it by simply adding more columns and dragging the formulas over. No advanced skills are needed beyond that.

Features:

  • Track count of inventory and value at each location by SKU (totals also calculated)
  • Track when inventory SKUs start to approach defined safety stock levels
  • Track inventory expirations if needed

Anyone can easily use this, and here are some steps:

  1. Hit File > Make a Copy for your own editable version
  2. Clear out the default test data in blue text by hitting 'Edit' > 'Delete' > 'Values' of selected cells.
  3. Enter data in all the 'data entry' labeled columns which will be in blue text
  4. Now, you can manage inventory. The rest of the sheet is automated.

Tabs:

  • Lists - define unique list of SKUs / Locations / Safety Stock
  • Database - where all data entry happens (there are 8 extra fields to append other data points and that is useful for tracking expiry if you need to)
  • Inventory per location - Shows total units entering / leaving each location and the current inventory balance per location as well as the inventory value per location. SKU selector is at the top and data updates as different SKUs are selected
  • Inventory view all - Shows the total units at each location by SKU as well as the total inventory value per location and total inventory value per SKU as well as aggregates.
  • Expirations - Shows all batches (rows) of purchases if the expiry is going to happen within 'x' days. There are formulas that calculate days until expiry automatically.
  • Low Stock - Shows any SKU that is below safety stock levels (across all locations)
  • Total Sales - This is a unique bucket and on the 'database' tab you can define a third type of transaction with is 'sold' and that is to track all inventory that has been sold over time by SKU and by location.
Note, the way inventory valuation is done is based on the user defining what dates to consider and the formula will figure out the weighted average cost per unit in that timeframe and that applies to all existing inventory balances.

To enter starting inventory balances, you can simply do a single 'add' entry for each SKU and each location based on the current units that exist. This means you would go through and make a row that says 'location x has 5 units of SKU A' and 'location y has 10 units of SKU A' and so on for each combination of SKUs and Locations.