Inventory management template in Google Sheets

inventory-management-template-google-sheets

Written by Laura Tennyson

Sep 9, 2020

Inventory management involves monitoring stock levels and organizing the supply and storage of materials, merchandise, products, or goods. An inventory is essentially a list of all the items that you store until they are resold. At the heart of this process, you need some kind of system for tracking each item that enters or leaves your company. This is where an inventory management template can help.

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, 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 prevents supply chain disruption. The results of that are happy customers and a healthy bank balance.

Today there’s a huge range of inventory management software on the market. Large companies tend to use specialist software or an ERP, but for SMEs, this is a costly (and unnecessary) investment. Inventory software can be 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.

inventory-management-template-dashboard

Why choose Google Sheets for inventory management?

  • Flexible: Google Sheets enables you to create a tailor-made inventory control system that you can change and adapt yourself, without any specialist software.
  • Accessible: As it’s cloud-based, Google Sheets can be accessed by any member of the team at any time, as long as they have an internet connection.
  • Compatible with everything: All other software packages integrate with Google Sheets so you can easily merge data from multiple departments or processes, such as sales, order, or e-commerce software.
  • Easy to use: There’s no need to train your team as they will already be familiar with spreadsheets. Maintenance is straightforward and there’s no need for technical or IT support.

About the template

Sheetgo’s spreadsheet-based workflow template is a pre-built system for automated inventory management that can be used by companies handling any kind of product, from kitchen ingredients to clothing.

The template gives managers a reliable way to track inventory and stock levels while providing warehouse or office staff with a simple method to log which items have entered or left the company.

inventory-management-template-current-inventory-list

What’s a spreadsheet-based workflow?

A regular spreadsheet template is a single file containing pre-built tables, charts, layouts, and formulas. In contrast, a Sheetgo workflow template is an interconnected system. It consists of several Google Sheets that exchange data between them via Sheetgo connections. This allows staff to share data with colleagues and other departments — without working in the same spreadsheet.

A spreadsheet-based workflow template gives you:

  • Organization and data privacy: Each file in the template carries out one task. This keeps data flows secure and segregated. Individual team members or departments enter data into their own spreadsheet and that information flows directly into the manager’s inventory dashboard.
  • Scalability: Google Sheets has a powerful collaboration function but when several people are working in the same file it can become large and heavy to process. Keeping data in separate, but connected, spreadsheets ensures that the system operates smoothly. The template provides a great starting point for a more advanced system. If you want to add more data inputs to the template in the future, simply connect more files to the workflow.
  • Integration: A spreadsheet-based workflow allows you to import data from other software, as well as other teams and departments. In a few clicks, you can connect your finance workflow to your inventory workflow to generate reports for management.

What you get with this inventory management template:

When you install the Sheetgo inventory management template, three separate Google Sheets files are saved to your Google Drive:

  1. A check-in spreadsheet where the individual or team responsible records which items enter the company. This data might include new deliveries or returned items, for example.
  2. A check-out spreadsheet where the individual or team responsible registers items leaving the inventory. This might include items sold, materials used in manufacturing, or products sent to other departments.
  3. An inventory management master sheet for the manager. This file contains automated dashboards with an overview of current stock levels. Data flows into this file from the check-in and check-out spreadsheets via Sheetgo connections.

Working alone or looking for a basic system?

Is someone else in your team responsible for inventory management? Share this article with your manager so they can set up the workflow.

If you manage your inventory alone and you don’t collaborate with colleagues, try our basic inventory template to check items in and out, in one single file.

How to get started

Click on the blue button above to install the inventory management template.

Inventory management

Google Sheets-based workflow template to check items in and out and monitor stock levels.

  1. Login to Sheetgo with your Google, Microsoft, or Dropbox account.
  2. Click Install template.
  3. Wait a few moments while Sheetgo creates copies of the files and connects them.

The inventory management master sheet opens inside Sheetgo.

To view or open the Check-in or Check-out spreadsheets, click on the Workflow icon in the sidebar on the right-hand side of the screen. Double click on either file.

Take a look at each of the files to familiarize yourself with the format of the template. You will see that each file contains sample data, which you or your colleagues can delete or overwrite.

inventory-management-template-open-workflow-bar

How to use the inventory workflow template

Step 1: Share the input spreadsheets

Share the Check-in and Check-out spreadsheets with the colleagues responsible for entering and managing that data.

For check-in, this might be staff working in your depot, warehouse, or office.

For check-out, this could be sales staff, administrators, or your production team if you work in manufacturing or catering.

If you open either of the spreadsheets inside Sheetgo, click Open in new. Give edit access to the correct colleague(s) using the green Share button.

inventory-management-template-open-input-sheet-in-new-tab

Step 2: Enter your inventory data

Now it’s time to start filling out the connected spreadsheets with your company’s inventory data. As you can see, the template contains sample data to show you how the system works.

You or your colleagues can simply overwrite this data and start logging your stock in the check-in and check-out spreadsheets.

inventory-management-template-replace-sample-data-check-in-sheet

Step 3: Update the workflow

This template contains pre-built Sheetgo connections. These connections pull data from the Check-in and Check-out spreadsheets directly into the central Inventory Management spreadsheet.

When you update (or “run”) the workflow, the data in the master sheet is refreshed with the latest information from the input spreadsheets.

To do this manually, open Sheetgo, select the workflow, and click Run on the floating menu bar.

Check the Inventory management master sheet. You will see that the dashboards now contain the latest data from the input sheets.

inventory-management-template-update-run-workflow

Step 4: Automate the workflow

Once your inventory workflow is up and running and your colleagues are entering data into their spreadsheets on a regular basis, you can automate the workflow. Automation ensures you’ve always got the latest inventory data in your dashboards, without having to open a spreadsheet or open Sheetgo.

Click Automate on the floating menu bar and select how often you want the data to be updated: from once an hour to once a month.

inventory-management-template-schedule-automatic-updates

Optional: Expand your inventory management system

As your business grows, you can expand the inventory system by adding more files and Sheetgo connections.

You can also link to other workflows such as sales or finance, to create an expansive data sharing system across the entire company.

To add more connections

    1. Open the Inventory Management workflow in Sheetgo.
    2. Click Connect on the floating menu bar.

Find tutorials and guidance in the Sheetgo support center.

If you’re looking for a more complex inventory management system in Google Sheets or you’d like us to build a custom system for you, book a call with our automation experts.

Automate your check-in

Interested in automating more of your procurement and inventory processes? Try linking the template to a barcode scanning app or using Google Forms to automatically update your check-in spreadsheet. Check out this post on how to use Google Forms and spreadsheets for your inventory.

Looking for a basic inventory template?

If you work alone with a small inventory list, try our basic inventory template. It doesn’t have any Sheetgo connections, but it provides a simple way to check items in and out in one single file. Get a copy here.

You may also like…

Share This