Inventory management template in Google Sheets

by Oct 7, 2019

What is inventory management?

Inventory management means tracking your stock levels in real-time. The process involves monitoring every item that either comes in or goes out of your inventory. An inventory is essentially a list of all the items, goods, merchandise and materials that you store until they are resold.

Good inventory management is a balancing act: you need to avoid shortages and make sure your customers receive their orders on time. On the other hand, sitting on a large quantity of unsold stock can also be detrimental for your business. As well as being bad for cash flow, unsold items risk becoming damaged, spoiled, stolen, going out of date or out of fashion!

Keeping tabs on what items are selling and recognizing when stocks are running low will help you keep your supply chain moving, customers happy and your budget healthy! 

Today there’s a huge range of inventory management software on the market. Large companies usually use specialist software or ERP tools, but for SMEs this is a costly (and unnecessary) investment. Inventory software is often difficult to use and doesn’t always provide the flexibility that a business owner or operations manager needs. It also tends to have way more features than you would ever need in a small business or organization.

Instead, it’s possible to create a reliable, user-friendly inventory management system with the tool you already know and use every single day: spreadsheets.

To make it even easier, we’ve created a free inventory management template for you in Google Sheets.

Why use an inventory management template?

With our ready-to-use template you can:

  • Register all items checked in and out—in one spreadsheet.
  • Monitor your stock levels at-a-glance, in an automated dashboard.
  • View key metrics, such as top-selling items
  • Maintain healthy stock levels

All in one spreadsheet.

What do I need?

We’ve created the template for you in Google Sheets so all you need is a Google Account. It’s completely free.

The template is provided with sample data to show you how the system works. Just overwrite this with your own data and your inventory management system is up-and-running. We’ve already written the formulas to generate the dashboard.

Let’s get started:

How to use the template

The template is a Google Sheets file named Inventory Management Template and it will automatically save to your Google Drive. Check out the first tab in the spreadsheet for full instructions.

Step 1:

Firstly, enter what items you currently have in stock in the “Check-in” items tab. Delete or overwrite the sample data provided. Select the unit of measure (e.g. kilograms, pounds) from the dropdown menu. Whenever you receive deliveries of new stock, update this tab.

Step 2:

When an item is sold, register this information in the “Check-out” tab. You will see that the item name and unit of measure columns are gray. This means they are locked; you don’t need to enter any data manually (the spreadsheet does this automatically, based on the item’s stock keeping unit, or SKU).

Step 3:

Your inventory management system is working!

Check out your “Current inventory list” tab—here you can see up-to-date status of every single item in your inventory.

In the dashboard you can see your live data in graphs and charts.

High stock turnover? Different colleagues managing deliveries and sales?

If your company has a large inventory and/or a high stock turnover, recording everything in one spreadsheet might become unmanageable or messy.

Spreadsheets are a brilliant tool for tracking stock levels but too many “hands” on the same sheet can lead to manual mistakes. It’s fairly easy to accidentally overwrite data entered by a colleague, for example.

If you have separate departments or colleagues monitoring goods received (checked-in items) and goods sold (checked-out items) then it’s a good idea to separate this data. Only the manager, or the person responsible for ordering, needs a full overview of items in and out.

Automate your inventory management system with Sheetgo

If you’re familiar with spreadsheets, you can use Sheetgo to manage your company’s inventory in an automated system. This means you can:

  • Automatically import data from check-in and check-out spreadsheets—directly into your master sheet
  • Save time on communication and eliminate copy-pasting
  • Separate data flows to ensure accuracy and reduce manual errors

And the best part? The data flows automatically into the dashboard.

Interested in automating other parts of your procurement and inventory systems? You can use Sheetgo to create automated spreadsheet-based systems for all of your business processes. Check out our post on how to create a purchase order management system in Google Sheets.

Share This