2024 Inventory and Sales Manager Excel Template

This Inventory and Sales Manager Excel template is suited for managing inventory and sales if you are running a small business of buying products from suppliers and selling to customers. (Retail/Wholesale).

This retail inventory excel template will assist in knowing the inventory levels of each product and understanding which products to re-order. Also, you can quickly view the purchases/sales patterns over time and the best-performing products.

This Excel template is designed for Microsoft Excel, but if you are looking for a Google Sheet template, please visit Inventory and Sales Manager in Google Sheets.


A new version is available with additional features such as auto-price population. The new template has automatic price population on order line items which this one doesn’t. There are some users who do not want to auto-populate the prices as they want flexibility to change prices for different customers. For those, the template on this page would be more useful. Hence, we have retained both templates on our site. 

Retail inventory Tracker (Free)
Inventory Spreadsheet - Summary Metrics

FEATURES

  • Enter and manage up to 2000 different Products
  • Set custom re-order points for each product
  • Simple and Easy data entry
  • Know current inventory levels of each product
  • Identify the products to be re-ordered
  • Know if the sale orders can be fulfilled
  • Easily understand the sales and purchase patterns (monthly and cumulative)
  • Quickly see your top customers and suppliers
  • Identify your best performing products
  • Know how the different product categories contribute to sales
  • Easily retrieve and view your order details

Free Excel inventory template with formulas

This template is developed using only formulas and does not have any macros or code. Formulas are used to calculate inventory and sales. You can view the formulas in the sheet and can edit them if needed. We recommend not editing any formulas unless you are very sure about the changes and the impact on functionality. 


DOWNLOADS

REQUIREMENTS

Windows and Excel 2010 (or above version)

Mac and Excel 2011 (or above version)

VIDEO DEMO



HOW TO USE THE TEMPLATE

Enter Products

Enter list of products and re-order points in the Products worksheet

Excel Inventory Management - Products Table
Excel Inventory Management – Products Table

Product Category: This allows you to categorize products. If you have numerous products, categorizing similar products together can help in understanding product performance.

Re-order Point: Amount that you set for each product, where when the current inventory level hits that amount, you will place a new purchase order to replenish inventory. (For more, read Re-Order Point Article in Wikipedia)

Enter Orders

Enter the line items for all the orders (both purchase and sale) in the Orders_and_Inventory worksheet

Inventory Template – Enter Order Details

If you have any existing inventory when you start using the template, enter them first. You can then continue to enter your new orders (purchase and sales) as they happen. The template will then give you accurate count of your inventory.

  • Order Number: This Order number is not used in the template to calculate anything. This has been provided for you to track your orders easily. You can filter the Orders table by choosing specific order number to see all the items in that order. If your systems generate any order numbers, you can enter them here. If you don’t have any such systems, you can create your own. The only recommendation is that you should have a unique order number for each order.
  • Order Type: There are two types of Orders: Purchase and Sale. When you place an order to acquire products from suppliers, it is called a Purchase order. When your customer places an order to buy products from you, it is called a Sale.
  • Order Date: For Purchase orders, this is the date when the order is placed by you to your supplier. For Sale orders, this is the date when the order is placed by your customer to you.
  • Expected Date: For Purchase orders, this is the date when the inventory becomes available for you to sell. For Sale orders, this is the date when the inventory will leave you to the customer.
  • Partner: For Purchase orders, your supplier is the Partner. For Sale orders your customer is the Partner.
  • Quantity: Number of units of products. The unit can be any numeric value. Even if your unit is not whole numbers, you can still use the Quantity field.
  • Unit Price: In Purchase orders, this is the cost of buying one unit of the product. In Sale orders, this is the revenue from selling one unit of the product.
  • Amount (Calculated field): (Unit Price X Quantity) = represents the amount of money. In Purchase orders this would be money leaving you and in Sale orders, this would be money that customers pay you.
  • Inventory Availability (Calculated field): This is the quantity (number of items) of the product available in inventory as of the Expected Date.

View information about overall inventory availability

Inventory Spreadsheet Excel Template - Summary Metrics

Inventory Spreadsheet Excel Template – Summary Metrics

  • Current Inventory of a product = (Total Purchases of Product – Total Sales of Product) as of today
  • Products Available: Number of Products where the current inventory level is greater than 0.
  • Quantity: Total Number of items of all Products currently available
  • Products to Re-order: Number of Products where the current inventory is less than or equal to the re-order point
  • Order Items that cannot be fulfilled (Current): Among the orders where the fulfillment date is less than or equal to today, number of line items in orders where the available inventory is less than the Sale quantity
  • Order Items that cannot be fulfilled (Future): Among the orders where the fulfillment date is in the future, number of line items in orders where the available inventory is less than the Sale quantity

View details of one specific product

Choose a product from the drop down and see details of that specific product.

Choose Product to view current inventory

Choose Product to view current inventory

  • Pending Purchase Quantity: Quantity in the Purchase Orders that are expected to be available in the future
  • Pending Sale Quantity: Quantity in the Sale Orders that are expected to be delivered in the future

View products to re-order

List of Products to order
List of Products to order

If there are line items that cannot be fulfilled or if there are products to re-order, take actions appropriately

View Report

View the Report worksheet to understand the purchase/sales trends and also to identify the top performing products and most valuable suppliers/customers.

Since there are pivot tables and charts, please refresh the data by pressing Ctrl+Alt+F5 or going to DATA ribbon and selecting Refresh All. This updates the charts with your new transactions.

Excel Spreadsheet - Data Refresh
Excel Spreadsheet – Data Refresh

The report sheet has slicers (filters) at the top.

Inventory and Sales Manager – Excel Template – Report Filters/Slicers

Amount and Cumulative Amount by Month

Inventory and Sales Manager – Excel Template – Report – Amount and Cumulative Amount

Quantity and Cumulative Quantity by Month

Inventory and Sales Manager – Excel Template – Report – Quantity and Cumulative Quantity

Amount distributed across Product Categories by Month

Inventory and Sales Manager – Excel Template – Report – Amount by Product Category

Quantity distributed across Product Categories by Month

Inventory and Sales Manager – Excel Template – Report – Quantity by Product Category

Product Ranking based on Sales Amount or Quantity

Inventory Sheet - Excel Template - Report - Product Ranking
Inventory Sheet – Excel Template – Report – Product Ranking

If you find the template useful, please share it with others. If you have any feedback, please share it in the comments below.


Manufacturing Inventory Tracker Excel Template (Free)

Rental Inventory Tracker Excel Template (Free)


578 Comments

  • This is an excellent spreadsheet! I need to modify slightly. I am a novice in Excel and in my business is steel manufacturing. My inventory is purchased in bulk and is represented by inventory tags (ex: tag# F137) when I get an order and I use my inventory to fill an order I need to take the tag# from the inventory. How can I change this template to accommodate that? I can send a sample of the awful spreadsheet I have so far if necessary. I have 10 fields that describe my product(s) that I need to track. I need everything in this spreadsheet but need to add the additional data. Any information/suggestions would be helpful.

    Reply
    • Thank you.
      I am not exactly sure what you are looking for. Please email me the file at indzara at gmail dot com and highlight what specifically you need. I will do my best to provide directions. Thanks & Best wishes.

      Reply
  • sir

    inventory sales and purchase can you provide inventory valuation and supplier payment and customer payment and collection list

    thanking you.
    aravind

    Reply
    • Both (inventory valuation and payment status) are features that are being considered for the next version of Retail Inventory and Sales Manager Excel Template. They are not available yet. I am sorry.

      Thanks & Best Wishes.

      Reply
  • Hello,

    Thank you for this template, it is very cool.
    Is there anyway to do this thing for google spreedsheet?
    I need an online work.

    Thanks a lot.

    Reply
    • You are very welcome. Thanks for feedback. I don’t have one for Google Sheets yet. I will look into it in future. Thanks & Best wishes.

      Reply
  • Hi ,

    Thank you very much for sharing this effective template.

    Just enquiring whether the information on the report can be based on expected date and not order date?

    Many Thanks,
    Chanelle

    Reply
      • You are welcome.
        Please edit formula for (YearMonth) field in the OrderDetails sheet and then rebuild the pivot tables (hidden sheets) which drive the Report sheet.

        Thanks & Best wishes.

        Reply
  • Hi

    I would like to thank you for your great inventory tamplate.

    I would like to know how can we transfer the pending pruchase to the current inventory in the Help sheet.

    Thanks in advance

    Reply
    • You are welcome. Can you please enter the pending purchases as a Purchase order? The current inventory calculations would automatically take that into account.
      Best wishes.

      Reply
  • Hello indzara,

    First of all, great template, thank you! I’m trying to translate it in Dutch, however when I change the “Data Validation” messages it stops checking for duplicate names and just keeps showing: “Check Product Names in Product Table” instead of “Duplicate Product Names”.

    I change the default SUM from:

    =ALS.FOUT(ALS(SOM(1/AANTAL.ALS(Tbl_Product[Product Name];Tbl_Product[Product Name]))<AANTALARG(Tbl_Product[Product Name]);"Duplicate Product Names";"No Errors");"Check Product Names in Product Table")

    To the translated one:

    =ALS.FOUT(ALS(SOM(1/AANTAL.ALS(Tbl_Product[Product Name];Tbl_Product[Product Name]))<AANTALARG(Tbl_Product[Product Name]);"Dubbele productnaam";"Geen fouten");"Controleer de aanwezige producten")

    The only thing I changed is the text, but then it never tell's me about duplicates anymore. Can you please explain me since I can't find out why?

    Thank you and thank you in advance!

    Best regards, Andries

    Reply
    • Thank you.

      It is an array formula. I am assuming you are entering the formula by pressing CTRL+SHIFT+ENTER. Excel will put curly brackets at the beginning and end automatically, as a signal that it is an array formula.

      If the issue still persists, please email me at indzara@gmail.com. Thank you.

      Reply
  • hello sir,

    thank you for your great efforts on this template,
    i’m depending on it for about a month and it’s very reliable template.I’d like to know how can i calculate the profit/loss in this template considering that purchases is all our costs and selling is our revenues. we are startup business and cannot afford buying templates.

    appreciate your greet job

    Reply
  • I am glad to hear that you are able to use the template to manage your inventory.

    If the product is returned and can be re-sold, then please enter a new SALE order for the product with negative quantity. This will put that product back into available inventory.

    If the product is damaged and cannot be used anymore, then please enter a SALE order for the product with positive quantity and enter the price as 0 (since you are not making any more. This would reduce the inventory of the product as expected. Please note that SALE qty will be inflated because of this.

    Good luck with your business.

    Reply
  • Hello Sir,

    I am using this template for managing our inventory from past 3 months its a great template and i am very thankful to you for making our work easy. our inventory are managed well because of your great work. anyway thank you. and i need one small help from your end we are receiving sales return and damage products this are loss to our sale is their any option to enter it in this excel? it would be great if you help me. we are startup business and we are not able to afford for buying software to manage inventory i hope you help us in managing it.

    Thank you for the wonderful console.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *