FIFO Inventory Tracker – Free Retail Inventory Management Template

Are you managing a small business with retail inventories? Furthermore, does your inventories have expiration dates and you find it difficult to track them? If your business gets inventories from suppliers and sells them at a profit to customers, tracking your inventory and managing sales is an integral part to be profitable.

Introducing our FIFO (First-In-First-Out) Inventory Tracker, a single tool for all your inventory management woes! This template helps you track expiry dates of your products along with some amazing features like Order Management, Finance, Data Management, Reporting and more.

This Excel template is designed for products that have expirations, if you are looking for a template that just manages purchases from suppliers and sells to customers, please visit Retail Inventory Tracker in Excel.

Why do we need an FIFO Inventory and Sales Management tool?

The following information are key to understanding your small business better and make informed decisions:

  1. Check your current inventory for each product. Fulfilling orders on time is crucial for maintaining business credibility.
  2. Identify low inventory products compared to the Re-order point. Use this data to make informed purchase orders.
  3. Take into account the expired products and track available inventories for products and orders based on purchase orders (using First-In_First_Out_Princliple).
  4. Monitor product sales. Invest in profitable products and avoid those that underperform.
  5. Track your business’s profit/loss. Adjust your business strategy if you’re not turning a profit.
  6. Identify top customers and suppliers. Cultivate strong relationships with high-revenue suppliers and provide special services to top customers for potential sales growth.
FIFO Inventory Tracker – Current Status Report for Inventories

In order to get to this information easily and quickly, we need some kind of software.  There are several sophisticated and expensive cloud based software available to manage inventory and sales for retail businesses.

For small and medium size businesses, especially when we are starting up, it is important that any software we choose is easy to use, customize and not expensive. This is why I am excited to present a free Excel template as a solution.

If you buy raw materials from suppliers and assemble/produce finished products to sell, please check our best selling Manufacturing – Inventory and Sales Manager Excel Template

Features of Retail Inventory Tracker Excel Template

Order Management

Inventory Management


Data Management


How to Use the FIFO Inventory Tracker

Before we get started, I highly recommend reading these articles if you are new to Excel templates or Excel Tables.

  1. Important tips about using Excel Templates from
    1. Do not edit calculated cells (ones with formulas).
    2. Input data is always visible and can be edited easily.
    3. Backup by saving copies of this file regularly.
  2. Introduction to Excel Tables (How to use Excel tables to enter data)

To make it easy for you to identify which fields are input fields, calculated and custom fields, I have followed the following color scheme in column headings (or labels).

Requirements: This template is compatible with Microsoft 365 subscriptions only.

Overview of steps

  1. Initial Setup
    1. Enter Business Information in Settings sheet
    2. Enter Product Categories in Settings sheet
    3. Enter list of Products in Products sheet
    4. Enter current Prices of products in Prices sheet
    5. Enter list of customers and suppliers in Partners sheet
  2. Creating Orders
    1. Enter list of Orders in Order Headers sheet.
    2. Enter each order’s details (line items) in Order Details sheet.
  3. Viewing business report
    1. View summary of business performance in Report sheet
    2. View the Inventory details of each batch in the Inventory sheet

Detailed Step by Step instructions (with screenshots)

Initial Setup

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

Step 1: Enter Business Information

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

Enter business information such as addresses and phone number

Step 2: 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

Step 3: Enter Products

It’s time to enter our 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 with other relevant product details

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

For adding starting inventory for each product, the same should be done as a purchase order which is explained in the later sections of this article.

The other columns in this sheet are all calculated columns. We will discuss more about this later in this article.

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

Step 4: Enter Product Prices

In 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 Jan 1, 2023 to enter orders.

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

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, 2024, we will just add a new row, enter the Product ID, Effective date (as 01-Jan-2024) 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 there is change in only one of them.

Step 5: 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.

How to enter initial inventories?

Initial or starting inventories should be added as purchase orders and follow the below steps for the same:

1. Create a Partner(used as a dummy entry) in the Partners sheet called “Starting Inventory” and no other details to that row item.

Enter Partner Details

2. In Order_Header, enter the order number (again, a dummy) as “ST INV” and enter the order date and expected date, include the Order type as “PURCHASE” only.

Enter a dummy Order number

3. Now, in the Order_Details, enter all the starting inventories against the ST INV order number as shown:

Enter all product details in the Order_Details

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 2 types of orders in this template.

  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.

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. This 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 Aug 5th. The supplier says the products will reach your inventory on Aug 15th. Here, Aug 5th is Order Date and Expected Date is Aug 15th. If there is a delay later and the supplier says it will only reach on Aug 21st, then we have to update the Expected Date of our order to Aug 21st.

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:

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

Entering order line items in Order Details sheet – Product, Quantity, any discount and Expiry Date (if any)

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 Blue color and 10 units in Red color and so on. There is a discount of $5 (any currency you use) for a few of these items.

The template will calculate amounts, tax and other details for each line item as shown below:

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

Unit Price is automatically pulled over from the Prices sheet. Price chosen will be the one that was effective as of the Order Date of 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 Aug 13th and products were given to customer on the same day (Aug 13th).

In the Order Details sheet, we add 5 rows of the sale product details.

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 Aug 13th (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 or nearing expiry), 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 Aug 15th, we see some faulty products in them and wish to return to supplier.

The same can be captured as below:

Purchase Return Order – Handling Supplier returns in retail

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

Similarly for a Sale order return, make a new entry and in the Order Details sheet, ensure to include an expiry date for the same.


This sheet, which gets automatically populated gives you a table of all the products (based on Orders) and their inventory details and expired quantity.

All you need to do is to refresh the Excel Sheet to get this populated, instantly.

Inventory Table with order based inventory details

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 taking into consideration any expired inventories.

The above presents the overall summary of all products together. This is also available for each product in the product sheet.

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

Use this retail inventory template to handle all your inventory management requirements. If more details are required please visit customer support for this retail inventory management excel template.

Recommended Templates

For more features like

Exit mobile version