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)
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
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
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
- 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
- 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
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.
The report sheet has slicers (filters) at the top.
Amount and Cumulative Amount by Month
Quantity and Cumulative Quantity by Month
Amount distributed across Product Categories by Month
Quantity distributed across Product Categories by Month
Product Ranking based on Sales Amount or Quantity
If you find the template useful, please share it with others. If you have any feedback, please share it in the comments below.
RELATED FREE TEMPLATES
Manufacturing Inventory Tracker Excel Template (Free)
Rental Inventory Tracker Excel Template (Free)
RECOMMENDED PREMIUM RETAIL INVENTORY TEMPLATES
-
Product on sale
Retail Business Manager – Excel TemplateOriginal price was: $50.$40Current price is: $40. -
Retail Business Manager (Pro) – Excel Template (Multiple Locations)$50
-
Product on saleRetail Business Manager – Google Sheet TemplateOriginal price was: $50.$40Current price is: $40.
578 Comments
I want to buy it but facing problem,,,,as i have debit cards not CREDIT CARDS,,,and its not accepting
Thanks for your interest. The system currently accepts credit and debit cards that are one of the following: Visa, MasterCard, American Express, JCB, Discover, and Diners Club. PayPal account is also accepted. Please confirm if the card you are trying is one of the above. Thanks.
When i add new sale/purchase and refresh data the report looses the order year and month info… the year dissapear and the month changes to specific dates instead of months… any idea why?
now i have to copy/paste all my info into new blank template every time i need to refresh report
Thanks. This can happen if the data is refreshed (Refresh All in DATA ribbon) when the table is empty. From the blank template, please enter your data with valid dates and only then refresh. This will let the slicers gather year/month info. If the table is empty and refreshed, then Excel looses that year/month formats. If this procedure is followed, then you do not have to copy/paste every time. Please let me know if there are questions. Thanks.
The report page isn’t showing the slicers. I am working in Excel 2007. Is there any way to get them for this version?
Sorry, Excel 2007 does not support slicers. Thanks.
Hi Sir,
This excel sheet is very useful. However I have a query. I have 10-15 odd products for each product description. This makes entering product name repetitive. Is there a way to first select correct product description and then select the product from the shortened list.
eg:
Product Name Product Description
Product A Type 1
Product B Type 1
Product C Type 1
Product D Type 1
Product E Type 2
Product F Type 2
Product Z Type 2
Could i first enter product description and then select the correct product from the different product names of the same description
I hope I am clear. If you could help me out with this, it will be great.
Thanks,
Piyush
Thank you. I understand your question and agree with the need. But that would require some additional formulas to be written or a macro. I am sorry I am currently tied up with other projects and am unable to get to this now.
Please visit https://trumpexcel.com/2013/07/dependent-drop-down-list-in-excel/ for Sumit’s tutorial on how to build dependent drop down lists.
Hope this helps.
Best wishes.
Extraordinary, excellent, superb, greatest & best free template for inventory. Thank you very very much for sharing this. I hope your paid version successfully sold.
I have 2 questions.
In Orders_and_Inventory tab, the formula for PENDING SALES QUANTITY is =IFERROR(SUMIFS(Tbl_Orders[Quantity],Tbl_Orders[Expected Date],”>”&TODAY(),Tbl_Orders[Product Name],F4,Tbl_Orders[Order Type],”SALE”),””)
In Help tab, the formula for Pending Purchase is =IF(A4=””,””,SUMIFS(Tbl_Orders[[#Data],[Quantity]],Tbl_Orders[[#Data],[Product Name]],A4,Tbl_Orders[[#Data],[Order Type]],”Purchase”,Tbl_Orders[[#Data],[Expected Date]],”>”&TODAY()))
Why you use different formats?
Will it be incorrect if we use =IFERROR(SUMIFS(Tbl_Orders[Quantity],Tbl_Orders[Expected Date],”>”&TODAY(),Tbl_Orders[Product Name],F4,Tbl_Orders[Order Type],”PURCHASE”),””) for Pending Purchase or
=IF(A4=””,””,SUMIFS(Tbl_Orders[[#Data],[Quantity]],Tbl_Orders[[#Data],[Product Name]],A4,Tbl_Orders[[#Data],[Order Type]],”Sale”,Tbl_Orders[[#Data],[Expected Date]],”>”&TODAY())) for PENDING SALES QUANTITY?
Other question is for “Re-order – Curr Inv” column in Help sheet, why the formula is Curr Inv – Re-order?
Also please provide alternative formulas for “Amount” and “Quantity” column that not depend on pivot data (pivot_products) a.k.a directly refer to Orders_and_Inventory only.
For Amount: Is it OK -> =SUMIFS(Tbl_Orders[Amount],Tbl_Orders[Order Type],”Purchase”,Tbl_Orders[Product Name],A4)
For Quantity: is it OK -> =SUMIFS(Tbl_Orders[Quantity],Tbl_Orders[Order Type],”Purchase”,Tbl_Orders[Product Name],A4)
Thank you very much.
Thanks for the compliments.
1. Both formats of formulas are acceptable. I think previous versions of Excel used one format and the newer the other. But both work.
2. Help sheet – Label should be Curr Inv – Reorder. Formulas are correct. We look for negative value to identify products to re-order. If (Curr Inv – ReOrder) is negative, then product should be re-ordered.
3. I use the pivot table approach so that the Amount, Quantity and Rank can update as the user uses the filters in the Report sheet. If you do not need that functionality, you can definitely create new columns and write formulas as you had indicated.
Best wishes,
Extraordinary, excellent, superb, greatest & best free template for inventory. Thank you very very much for sharing this. I hope your paid version successfully sold.
I have 2 questions.
In Orders_and_Inventory tab, the formula for PENDING SALES QUANTITY is =IFERROR(SUMIFS(Tbl_Orders[Quantity],Tbl_Orders[Expected Date],”>”&TODAY(),Tbl_Orders[Product Name],F4,Tbl_Orders[Order Type],”SALE”),””)
In Help tab, the formula for Pending Purchase is =IF(A4=””,””,SUMIFS(Tbl_Orders[[#Data],[Quantity]],Tbl_Orders[[#Data],[Product Name]],A4,Tbl_Orders[[#Data],[Order Type]],”Purchase”,Tbl_Orders[[#Data],[Expected Date]],”>”&TODAY()))
Why you use different formats?
Will it be incorrect if we use =IFERROR(SUMIFS(Tbl_Orders[Quantity],Tbl_Orders[Expected Date],”>”&TODAY(),Tbl_Orders[Product Name],F4,Tbl_Orders[Order Type],”PURCHASE”),””) for Pending Purchase or
=IF(A4=””,””,SUMIFS(Tbl_Orders[[#Data],[Quantity]],Tbl_Orders[[#Data],[Product Name]],A4,Tbl_Orders[[#Data],[Order Type]],”Sale”,Tbl_Orders[[#Data],[Expected Date]],”>”&TODAY())) for PENDING SALES QUANTITY?
Other question is for “Re-order – Curr Inv” column in Help sheet, why the formula is Curr Inv – Re-order?
Thank you very much.
please I need your assistant, I have a motor spare parts business, that contain 2000 diferent items but don’t know how to organize it.
You can enter up to 2000 products in this template. Each product is entered separately in the Products sheet. You can also categorize similar products in the Category column. Hope this helps. Thanks.
I would like to add more columns because there is a bit more data that I need to track. I have inserted the columns where I need them but I can’t figure out how to change the formatting without messing everything up. Example I need a column for Services as well so I inserted a column after Product Name but the new column won’t allow me to enter text, it is just showing me the drop down list of product names. I feel like I am missing something very simple. Can you help?
When you inserted a column, the drop down is taking the data validation list from the column to the left. You can select the new column cells and then from DATA ribbon, choose Data Validation. Then clear the data validation list. This will clear out the drop down values and allow any entry. Hope this helps. Best wishes.
Thank you so much for your response back on this issue. I got it all fixed!
You are welcome. I am glad to hear that everything is working. Best wishes.
Dear sir, I’m working on warehouse, not sales
On “Order Type” can I edit or add Received, Issued, to drop down manual?
Sir, ‘Order Type’ drop down values are linked with formulas used to calculate inventory. So, if the ‘Order Type’ values are changed, the formulas have to be modified accordingly. Otherwise, the formulas will break. Thanks.
Hi,
When I click into a formula “PRODUCTS WHERE CURRENT INVENTORY <= RE-ORDER POINT" to look at the formula then when I press enter I loose all the data. Why is this. I try to fix the problem but it does not work.
Thanks
Darrin
Thanks. Instead of entering ENTER, please enter CTRL+SHIFT+ENTER. The formulas are ‘array formulas’. For more details, there are good video tutorials from ExcelIsFun such as https://www.youtube.com/watch?v=vqfikSMx7mE Thank you. Best wishes.