Inventory management template in Google Sheets

by Oct 7, 2019

What is inventory management?

Inventory management involves tracking stock levels and organizing the supply and storage of materials and goods. At the heart of this process, there should be a system for monitoring every item that enters or leaves 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 to 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 which 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 an ERP system, 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 more features than you would ever need in a small business or organization.

Instead, you can 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 this ready-to-use workflow template you can:

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

All from a spreadsheet.

What do I need?

We’ve created the template for you in Google Sheets, with all the formulas and Sheetgo connections already in place to generate your dashboard.

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.

Here’s how to get started:

How to use the template

The inventory template is a workflow containing 3 connected Google Sheets files. When you install the template, it will save to your Google Drive automatically. Check out the first tab in the master spreadsheet for full instructions.

Share the workflow with all team members involved in inventory control. If you have separate departments or colleagues monitoring goods received (checked-in items) and goods sold (checked-out items) you can share each spreadsheet with the correct team. Only the manager, or the person responsible for ordering, usually needs to see the inventory management master sheet. This provides an overview of items in and out.

Follow the steps below to use the Sheetgo inventory management workflow template:

Step 1: Fill out the check-in spreadsheet

Enter the items you currently have in stock in the “Check-in” 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: Fill out the check-out spreadsheet

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: Run the workflow

Run the workflow to update all the connections and see your inventory management system working! You can automate the workflow by scheduling automatic updates hourly, daily, weekly, or monthly.

Take a look at the “Current inventory list” tab in your Inventory master sheet — here you can see the current status of every single item in your inventory.

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

An automated inventory management system

Your Sheetgo inventory management workflow template is now up and running. You can

  • 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, giving you up-to-date information on stock levels in the inventory dashboard.

Automate your check-in 

Interested in automating more of your procurement, ordering and inventory processes? Try using Google Forms to automatically update your check-in spreadsheet. Check out our post on how to use Google Forms and spreadsheets for your inventory.

Share This