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
Hello! This template is so helpful. However, is there a report that can be made to see the complete list of inventory in stock. I see from previous comments that you mentioned about a hidden “help” sheet. How can I find this?
Also, I noticed that the product drop down list in the “Order and Inventory” sheet will come up as how it is entered in the “Products” sheet. I mean that if it’s not arranged alphabetically (or in order), it’s will not come up alphabetically. This may lead some people to believe that some product are not available if it is not where it should be. If the “Product” list is arranged/sorted A to Z, will it affect anything already entered in the “Order and Inventory” sheet?
Looking forward to your answer! And thank you advance!
To unhide worksheets, please right click on any of the visible worksheets. You will see a menu appear where you can choose ‘unhide’. Then you will see a list of hidden worksheets. You can choose ‘help’ to unhide the ‘help’ worksheet.
You are correct. The product list in the drop down menu appear in the same order as the products in the ‘Products’ sheet. You can sort the ‘Products’ table (it’s an Excel TABLE). Select any product name in that TABLE. Enter Ctrl+A. This would select the TABLE. Right Click and choose Sort. This will sort the product names and will not adversely impact the template.
Best wishes,
First off, thank you for building such a fantastic management tool! It looks to be the perfect solution for managing my nonprofit’s merchandise as we grow our sales! I spent considerable time today imputing data, eager to mess with the reports tab, only to find that my version of excel is not compatable! (excel 2007 on Windows Vista). Where there should be buttons and drop downs, there are boxes with the following message: “This shape represents a slicer. Slicers are supported in Excel 2010 or later. If the shape was modified in an earlier version of Excel, or if the workbook was saved in Excel 2003 or earlier, the slicer cannot be used.”
Is there any way of obtaining these tools with my version of excel or do you have a template that functions with older versions of excel for PC? Thank you again for this fantastic tool!
Best,
Michael
Additionally, is it possible to add a column of product description to the right of product name on the help page? My product names are somewhat cryptic because they include style, gender, color, and size. I think this would be nice for buyers to decipher the product names when they are putting together sales orders. Thanks again!
Michael
Thank you very much, Michael. I am glad the template is useful.
I don’t have a version that works with Excel 2007 now. I plan to build one in the future. I will post it once I have it.
Michael, It is easy to add a column of product description in the help page. If you would like to add the product description now, please e-mail at indzara at gmail. When I get time, I can work on it. It may take a few days. I will do my best.
Thanks you for this spreadsheet and really it is an excellent workbook have multiple use,
But could you please help me to get the list of stocks available now in a different sheet (like de same format what we give in reorder) ? or if i update the stocks in different sheet is there any option available to add those along with the purchased qty already updated in orders-inventory sheet?
Expecting your kind reply…
Thanks!
Shinoze Mohammed
Hello Shinoze,
Thank you. There is a hidden sheet named ‘help’ which has the list of all products with the current inventory levels. You can use that to view or print. However, that sheet has formulas embedded and one should be careful not to edit them unintentionally.
Thanks for your kind reply…
You are welcome.
Thank you for all your help with the spread sheet. I have now got all areas working. Just one more question,
Can you print the list of items to be re-ordered or see the complete list without scrolling down.
Many thanks
Janis
janis777@me.com
Thanks, Janis. I’ve e-mailed my response to you.
For other readers to know, this template currently does not provide a printable list of all products to be re-ordered. I plan to add the functionality to the next version.
Thank you very much for sharing such a great work
i was trying to make a template for my small T-Shirt business
i print t-shirts and have 3 different point of sale
if you can help it will be greatly appreciated
i need template witch can help me with my inventory and what more to print
for example i have one lizard design to print in sizes
i print one design on sizes 3 months to XXL size in different colors
and i need to see weekly sale of design and sizes with color sold in three different places and what more to print and and chart base information in weekly, monthly and yearly
i shall be really thank-full to you
Thanks. I believe you can use this template to manage inventory and sales of t-shirts. Each different version of a T-shirt will be a separate product. Please let me know if this doesn’t help.
Thank you, I have replied and sent you the screen shots via my other email account as it is difficult to send screen shots via my mobile.
I hope you have received them ok
hi, I have discovered that I only have 4 categories showing up whereas I have at least 20.. this seems to be why I do not have the correct total for inventory, can you please advise, thank you
email
janis777@me.com
I have responded to your e-mail. Please send me screenshots and I will look into it.
HI, excellent spread sheet. I have entered all product names, description and category, however on the worksheet orders and inventory, the total products have stopped adding up from line 38 onwards.?? have you any suggestions as to what may be causing this please ……
email, janis777@me.com
I need to create a report with the full inventory of items and their quantities in stock.
Is this possible from this template?
Do you mean a list of all products and the current stock quantities? There is a hidden sheet named ‘help’ in the document. That lists all the products and the current inventory levels. Please note that the sheet has formulas.
Thank you so much that is awesome…Great spreadsheet
I am glad you like the template.
HI. I also need the full inventory report. how can i find the hidden sheet help
To unhide worksheets, please right click on any of the visible worksheets (worksheet name). You will see a menu appear where you can choose ‘unhide’. Then you will see a list of hidden worksheets. You can choose ‘help’ to unhide the ‘help’ worksheet.
That’s excellent template,
I understood that you made the template for purchase/sales but I realize that I can use that just to follow where we transferred what we purchased and helps a lot to keep minimum stock in warehouse…
Is it possible to have one changed version from you?
Instead “Sale” – “Transfer to”…Reports are nice and that’s just “make up” to produce report for our own purposes.
If possible, please make one “transfer’ version and
Many thanks in advance,
Zoran
Thanks, Zoran.
Can you please explain with an example? I am not sure I am following your requirement. What does ‘Transfer to’ mean? What business model is it?