Inventory and Sales Manager (Free Excel Template) for Small Business

Posted on
Inventory & Sales Manager Excel Template - Summary Metrics

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.


For Retail businesses, a new version is available Retail inventory Tracker (Free)

For Manufacturing businesses, Manufacturing Inventory Tracker Excel Template (Free)

For Rental businesses, Rental Inventory Tracker Excel Template (Free)


Inventory Spreadsheet - Summary Metrics
Inventory Spreadsheet – Summary Metrics


FREE DOWNLOAD


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

 

VIDEO DEMO

Click here to watch the video on YouTube.


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 Sheet - Enter Order Details
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.

Sales Report with Filters/Slicers
Sales Report with Filters/Slicers

 

Amount and Cumulative Amount by Month

Inventory and Sales Manager - Report - Amount and Cumulative Amount
Inventory and Sales Manager – Report – Amount and Cumulative Amount

 

Quantity and Cumulative Quantity by Month

Inventory and Sales Manager - Report - Quantity and Cumulative Quantity
Inventory and Sales Manager – Report – Quantity and Cumulative Quantity

 

Amount distributed across Product Categories by Month

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

 

Quantity distributed across Product Categories by Month

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

 

Amount Distributed across Partners

Inventory and Sales Manager - Report - Amount by Partner
Inventory and Sales Manager – Report – Amount by Partner

 

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 with others. If you have any feedback, please share it in the comments below.

 

RECOMMENDED RETAIL INVENTORY TEMPLATES

Retail Business Manager - Excel Template
Retail Business Manager – Excel Template

 

 

534 thoughts on “Inventory and Sales Manager (Free Excel Template) for Small Business

  1. Hi, I am really grateful to have found your designed template. It could ease our recently started online e-commerce.

    But, I have got a problem when trying to enter then product name. Pop up message shows: The value doesn’t match the data validation restrictions defined for this cell. Not sure how should I amend it.

    Regards,
    Christine

    1. Hi, I’ve figured it out, should have watched the video first. But still, thanks 🙂

      1. Thanks for using our template.

        Best wishes

  2. Hi there,

    For some reason the summary table is not recording my inventory when I change the dates to 2019. Any suggestions why?

    Kind regards
    Nikita

    1. Thanks for using our template.

      Please send your file along with the list of issues to contact@indzara.com

      Best wishes

  3. hi!
    do you have a template that have a report that sort by customers? for example i want to see all the products that customer A bought, that also contains the date and the items he bought, and the total of it.

    Something like that. Actually I need an office supplies monitoring but the supplies that the employee gets will deduct by their salary. That’s why I need a report that sort by customer. I hope you can help me. Thank you in advance for the response.

    1. Thanks for your message.

      The current template can be sorted and you can get a snapshot of the customer and their purchases. However, we are not accepting any customized projects now.

      Best wishes

Leave a Reply

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