Inventory Management Template

There are many off the shelf Inventory Management software programs, however they often don’t provide the flexibility that a business owner or operations manager need. In this blog post we have created an easy to use, copiable, inventory management template that allows you to aggregate sales from many sources, is ready to go after a short configuration, and all built on Google Sheets for easy and convenient customization.

Case study

We have an active wear client, Authen, in Rio de Janeiro that was having difficulty monitoring inventory from pre-sales. Their logistics partner provided inventory management services for products after they arrived at the wharehouse, however this brand pre-sells a majority of their stock so they needed a way to communicate inventory levels daily and automatically to their sales reps so that they wouldn’t sell out-of-stock items. Prior to creating their inventory management system tied together by Import Sheet they kept all purchase orders in separate, offline, Excel spreadsheets, and had an analyst manually tally sales at the end of the day. According to CEO, Christopher Spikes, “we lost a lot of time focusing on the management of this and less time on value add things like scheduling and making sales calls.  We are now much more efficient and profitable because of our purchase order and inventory tracking system”.

Overview of the template

This template includes two Google Sheets, one of which is tracks the current inventory and the other of which is used for purchase orders. The purchase order sheet can be replicated multiple times if necessary (e.g., one sheet per sales rep, per client, or per region). We’ll configure the Inventory sheet to import purchase orders daily and decrement the number of items in stock. The updated stock will then be imported to the purchase order sheets identifying which items have been sold out.

Before we start

As a best practice input fields are white and all other fields are colored. The sheets are configured to accept 200 unique products, 20 orders per Sales Order sheet and 10 Sales Order sheets per Master Inventory sheet. These limits are easily increased with a few simple changes the default formulas. Follow the steps below to configure your Inventory Management:

Step 1) Install Import Sheet and copy the template

If you haven’t done so yet, install the Import Sheet add-on by clicking this link: Import Sheet Add-on for Google Sheets. Next click on the icon below to access the two sheets needed for this template, Inventory Master, and Sales Orders. Open the sheets and make a copy of them by clicking ‘File > Make a copy…’

Inventory Management Template: Install Import Sheet and Copy from the Template

Inventory Management Template: Install Import Sheet and Copy from the Template

Step 2) Fill in your initial inventory

Open the Inventory Master sheet and fill out the tab ‘Initial Inventory’ with all of your products including their unique ID, names, descriptions, and quantities

Step 3) Configure the Sales Orders sheet

Configure Sales Orders to import the current inventory from Inventory Master

  1. Click “Add-ons -> Import Sheet -> Start”, this will open the Import Sheet. Click on the + in the lower right corner to create an Import connection.
  2. In the next screen, select the Connect as type of connection
  3. Find and select your copy of the Inventory Master spreadsheet. In section “Select source sheet” select the sheet ‘Current Inventory’. In section “Automatic Update” click on “edit” and change the first field to “Daily”, the second to “1AM to 2AM” and click “OK“. And in section “Connection Name” click on “edit” and type “Current Inventory” (it must be exactly this name). Take a look at the image below for an example of how your options should be configured and then click ‘CONNECT’:

 


Inventory Management Template: Example of Import Sheet configuration

After importing the inventory you should now input product pricing on the ‘Master Order Sheet’ sheet (column E). If you already have sales then you can fill in the columns F and beyond with the quantities from your orders. Feel free to change the row headers for your sales orders (e.g., ‘ORDER QUANTITY 1’ could be changed to the name of the client). This will auto populate the tab ‘Order Summary’ which we will import to the ‘Inventory Master’ sheet in the next step.

Step 4) Connect Inventory Master to Sales Orders

We will now configure the connection between ‘Inventory Master’ and ‘Sales Orders’ so that orders are automatically decremented from the inventory.

  1. Open the ‘Inventory Master’ sheet and click “Add-ons -> Import Sheet -> Start”. Click on the + in the lower right corner to create an Import connection.
  2. In the next screen, select the Connect as type of connection
  3. Find and select your copy of the ‘Sales Orders’ spreadsheet. In section ‘Select source sheet” select the sheet ‘Order Summary. In section “Automatic Update” click on “edit” and change the first field to “Daily”, the second to “Midnight to 1AM” and click “OK“. And in section “Connection Name” click on “edit” and type “Order 1”  (you can use another name here if you desire) then click ‘CONNECT’.
  4. On the sheet ‘Total Orders’ in cell E1 replace ‘Insert Name’ with the name of the sheet you just imported (e.g., ‘Order 1’). Repeat the steps above for any other Order sheets you have now or in the future

Congrats!

You’ve now set up your Inventory Management Template which will take in new orders daily, decrement current inventory, and send the updated inventory back to the Order sheets so that your inventory and purchase orders are always synchronized. If you require more frequent updates feel free to swap daily for hourly updates when configuring your Imports. Also, the system is yours now, so feel free to customize as you see fit to meet the needs of your business.

Comments are closed.