INDZARA

Retail Inventory Tracker 2023 – Free Google Sheet Template

As an owner of a retail business, it is important to know the availability of inventory to manage orders. This Inventory Google Sheet Template is developed to help people in buying products from suppliers and selling them to their customers in a retail business scenario.

This Inventory Google Sheets Template can be effectively used to determine which products to reorder, who are your best suppliers and customers.

This template is designed for Google sheets, but if you are looking for an Excel template, please visit Retail Inventory Tracker 

Benefits of Google Sheets inventory tracker

Order Management

Inventory Management

Finance

Data Management

Reporting

Free Google Sheets

How to Use Retail Inventory Tracker Google Sheets Template

Steps to use this template

  1. Make a Copy and save this template in your Google Drive.
  2. Configure the input entries.
  3. The reports are populated automatically.

Step 1: Make a Copy of the sheet.

Click on the link above and then make a copy of the sheet as shown in the screenshot below.

You can save the copy in your own google drive for your use. Once copied, it can also be shared with your colleagues to collaborate as needed. 

Step 2: Configure the input entries.

Initial Setup

These Initial Setup steps are to be done first as a one-time activity.

This Google Sheet Template is one of the simplest templates to get started. Following are the inputs needed for this template to populate the reports automatically.

Enter Business Information

In the Settings sheet, Enter your business information such as address, email and phone number.

Enter business information such as addresses and phone number

Enter Product Categories

If you are selling several products in your business, it is recommended that you categorize your products. This helps a lot in managing them and understanding their sales performance.

Enter Product Categories in your retail business

Enter Products

In the Products sheet, let’s enter each of our products in a separate row.

Please start entering from row 4

Enter Products with Name, Description Starting inventory and Re-order point

Let’s see each of the fields in the Products table. 

There are a few more columns of product information we can input.

Enter Unit of measurement, Product category, Tax information for each product

The other columns in this sheet are all calculated columns.

The columns that have Green colored labels are all calculated columns. Please do not edit the formulas in them. 

Enter Product Prices

In the Prices sheet, we will be entering Purchase and Sales prices.  This information will be used to auto-populate prices in our orders. This will save a lot of time in data entry of orders.

Enter product purchase and sales prices in Prices sheet

Purchase Price is the price we pay our suppliers to purchase products. Sales Price is the price we sell the products to our customers at.

To begin with, let’s assume we start using this template from Nov 1, 2016 to enter orders.

We enter each product in this Prices table and enter Nov 1, 2016 as the Effective Date. The Purchase and Sales prices we enter will be the prices effective as of Nov 1, 2016.

What if price changes?

The template is designed to accommodate price changes for products. You may have an increase in prices of certain products over time. Not a problem.

If price changed for a product from Jan 1, 2017, we will just add a new row, enter the Product ID, Effective date (as 01-Jan-2017) and the new Purchase and Sales prices. Please note that we have to add new rows whenever prices change, and not to replace the older data.

We have to enter both purchase and sales price in each row, even if only one of them changes.

Enter list of Partners 

In the Partners sheet, we store the list of our partners. Partners include Suppliers and Customers.

Enter customer and supplier information in Partners sheet

If a partner is both a customer and a supplier (it is possible in some scenarios), enter the partner only once.

This sheet now serves as a nice organized set of data about your partners.

We have completed the initial set up now. It’s time to enter our first order.

Creating Orders

Before we enter our order, let’s learn about the types of orders. You can create 3 types of orders in this template.

3 types of orders Purchase, Sale and Adjust
  1. PURCHASE: When we purchase products from our suppliers, we enter a PURCHASE order. This order will add the purchased items to inventory on the Expected Date.
  2. SALE: When we sell products to our customers, we enter a SALE order. This order will subtract sold items from the inventory on the Expected date.
  3. ADJUST: We can create an ADJUST order and enter negative quantity values to reduce inventory or positive values to increase inventory as needed. This can be used to adjust our inventory numbers to ensure that the numbers match the inventory on hand. For example, we may lose products due to damage or expiry or other reasons. We would want to adjust our inventory accordingly and that’s where we can use ADJUST order type.

Creating a Purchase Order

Orders are entered in this template in 2 stages – 1) Order Header  and 2) Order Details. Let’s use an example. The products here are shirts for boys and girls. They are available in different colors.

In the Order Headers sheet, we enter the following information.

Entering a purchase order in Order Headers sheet

The Order Number should be unique. In other words, each order should be entered in one and only one row. The field should not be blank.

We can enter any method of numbering orders. The template does not limit that and does not create any pre-defined order numbers. Here, we have entered ‘P1’ as order number, to reflect that it is the first purchase order we are entering. 

Order Date and Expected Date

Each order will have 2 dates. Order Date and Expected Date. Order Date is the date when the order is placed. Expected Date is the date when the inventory is impacted.

For example, if you place a purchase order on Nov 5th. The supplier says the products will reach your inventory on Nov 25th. Here, Nov 5th is Order Date and Expected Date is Nov 25th. If there is a delay later and the supplier says it will only reach on Nov 27th, then we have to update the Expected Date of our order to Nov 27th.

Order Type is ‘Purchase’ and we have chosen our supplier in the Partner Name field.

There are additional information we can enter in the Order Header.

Additional information on Purchase Order – Order Header

Now, we enter the items on the order in the Order Details sheet.

Entering order line items in Order Details sheet – Product, Quantity and any discount

It is very simple. Enter Order Number, Product ID, Quantity and any Unit Discount.

Here, we have entered a purchase order to purchase 15 units of Boys Shirt in Red color and 10 units of Girls Shirt in Red color. There is a discount of $2 (any currency you use) for each of the 10 Girls shirts and no discounts for the Boys shirts.

The template will calculate amounts for each line item.

Calculations of Amount and tax for each line item in the order

Creating a Sales Order

Entering a sales order is very similar to the purchase order, except that our Order Type is ‘Sale’ now.

Entering a Sale Order in Order Header sheet

As shown in the image above, to add an order, we just add our entry in a new row in Order Headers sheet.

We enter S1 as Order Number. This sale order was placed on Nov 26th and products were given to customers on the same day (Nov 26th).

 In the Order Details sheet, we add 2 rows as we are selling two products (MSBL and MSGR).  10 units of MSBL and 5 units of MSGR.

Entering Sale Order line items with Product and Quantity in Order Details sheet

This order will now automatically reduce inventory for each of the products, effective as of Nov 26th (Expected date).

Handling Supplier Return

If we have a situation where we want to return products back to our supplier due to some reason (example: defective products), we can do so easily.

We will enter a new Purchase order.

Tip: For easier identification of return orders, you can enter order number differently. For example, use a prefix of PR for purchase return orders.

In our example, after receiving the products on Nov 25th, we notice that there are 5 defective BRD units. We want to return them.

So, on the next day (Nov 26th), we send the products back to the supplier.

Purchase Return Order – Handling Supplier returns in retail

In the Order Details sheet, we will enter the information on returning product and quantity.

Purchase Return Order – Handling Supplier returns in retail

Since we are returning 5 units of BMGR, I have entered -5 as Quantity. Entering a negative value is important. That ensures that our inventory is reduced by 5 units for this product.

Handling Customer Return

Similar to the Supplier Return, we can also handle customer returns. If customer decided to return products to us, we can enter that information in the template. We use a Sale order for that purpose.

Customer Return Order – Handling products returned by customer

In this example, SR1 is the sale return order that is placed on Nov 30th.

Customer Return Order – Entering quantity of products returned by customer

4 units of BMGR are returned by the customer.  We enter -4 as Quantity. This will be used by the template to add 4 units to GRD inventory, effective as of Nov 30th.

Creating an ADJUST order

On some occasions, we may find that a product is either expired or damaged locally at the warehouse. We cannot return it to the supplier, and we cannot sell that to customer too. We need to make sure that our current inventory calculations reflect the true available inventory to sell. This is where we can use the order type ‘Adjust’.

In the Order Headers sheet, we first create a new Adjust order.

Entering an Adjust order to handle scenarios by product expiry or damage

For example, one BMGR shirt was damaged in the warehouse and we notice it on Dec 1st. So, we enter it as shown below.

Adjust Order – Entering quantity and discount

We enter -1 as Quantity. This will reduce the inventory by 1.

If we want to increase inventory levels without entering a purchase order, we can use an ADJUST order where we enter positive values as Quantity.

 We enter 35 as Unit Discount (as that is the sales price of the product). This is to zero out the impact on cost. If we are not incurring any additional cost by disposing the shirt, then this method is recommended.

If we incur any additional cost, then we enter the appropriate Unit Discount so that the total Amount after Tax reflects the disposal cost.

Step 3: Automated reports

Business Performance Reporting

The template has extensive automated and interactive reporting in the Report sheet.

Current Status (Inventory level and Inventory value)

Current Status of Inventory – Retail Business

The following metrics are displayed to reflect the status as of today.

 The above presents the overall summary of all products together. We would also want to see this information individually for each product. To do that, we go to the Products sheet.

Inventory levels of each product in Products table

Now, the rest of the Report sheet presents an interactive way of accessing business performance metrics.

We can customize the date range for the report by choosing any start and end dates.

Enter Start and End Date for Report

We leave the Refresh as ON. If you enter a lot of order data over time and if you notice the file is getting slower, you can turn this OFF. It will stop the report from refreshing constantly and that will improve performance.

For the date range we entered, we can see the summary metrics.

Summary Business Performance Metrics for Retail Business

We can view these metrics by month, for 12 months at a time.

Monthly Metrics on Sales and Purchases – Sales Amount, Cost of Goods Sold and Profit

We can choose one of the metrics to display data on a chart showing trends over 12 months.

Choose metric to display on chart
Monthly Metrics on Sales and Purchases – Trend Chart

Top 10 and Bottom 10 Products

One of the important pieces of understanding business performance is knowing which products are selling the most and which ones are not. We have 3 ways of measuring sales – Quantity, Amount and Margin. This allows us to understand the true impact of the products to the business.

Choose one of the 3 Sales metrics (Sales Quantity, Sales Amount and Margin)

We will see top 10 and bottom 10 Product Categories by the selected Sales metric.

Top 10 Product Categories by Sales Metric
Bottom 10 Product Categories by Sales Metric

Similarly, the top 10 and bottom 10 Products by sales metric.

Top 10 Products by Sales Metric
Bottom 10 Products by Sales Metric

If we want to look for details of a specific product, we can choose the product ID from the drop down.

Choose Product to view Performance metrics
Product Performance Summary metrics
View Inventory level for selected product
Sales Quantity – Monthly Trend for chosen product

Partner Performance

Another important aspect is to understand best partners (customers and suppliers).

Top 10 Customers by Sales
Top 10 Suppliers by Purchase Amounts

We can then see the details of one specific partner at a time.

Choose Partner to view Performance metrics
Partner Performance Summary metrics
Exit mobile version