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, Thank you for making this template and making it available on the internet! I think I know the answer to this question, but I will ask anyway – Is there any way this spreadsheet can run on a MacBook using Office/Excel 2008?
Hoping,
Corinna
Hello Corinna, Unfortunately, I don’t know for sure. I don’t have a Mac. I have received numerous requests for templates compatible with Mac. I plan to address that soon. If you have a Mac, please let me know or send me screenshots of how the template works. Thank you.
For me, I can’t even save and use the template on Excel for Mac. Any suggestions?
The template uses slicers which, I believe, are not compatible with Mac versions of Excel. I am sorry that this template is not useful to you as it is now.
did you used any visual basic codes here?just asking..
All the templates until now do not use any visual basic. They are built using formulas and conditional formatting. Thanks.
Dear sir ,
Hi!,
is there any other source to enter our good or items,and their respective details will be upload list in one time from csv or any other resource..so we dont want to enter each and every item one by one.. and in our item have too much variance in colour’s and size’s ..there is any solutions for that..thing’s..
Thanks
The template needs just the name, description, category and re-order point. If your source data can be edited to display in that order, you can quickly copy and paste that in the Products table in the template. It can be up to 2000 products. If you want to record the colours and sizes, you can do that separately in a different worksheet. They don’t impact the template’s calculations, as long as the product names you enter are unique in the Products table. Hope it helps.
dear sir
excellent excel sheet , is there a way to see items sold in a day , there total amt and there sum
for example i need to see total value of items sold on 14th october, how do i see it
Sir, I didn’t include that in the scope of the template. However, there is a not-so-friendly way to see that information. If you use the Orders_and_Inventory sheet and choose the filter on Order Date (Row 13), you can narrow down to only items on a specific date. The sum would show up in Excel’s status bar.
Doesn’t work on office for mac 2011 🙁
Unfortunately, I don’t have a Mac now to test.
Do any of the sheets work? Is it just the ‘Report’ worksheet?
Thanks,
i tested it on my Mac, the report sheet doesnt work.
Hi, I used it with Office for Mac 2011. It works fine except for the Report sheet.
Kristle, Thanks for the feedback.
ind zara can u help fix the report page on Mac version 2011 pls…. It really help me alot on my business but i wanna use it everywhere on my mac book air too…
I plan to get a Mac in August and start building templates in Mac. I don’t know exactly when this template will be completed. I am sorry that I don’t have specific dates yet. But I will consider this template as one of the first templates.
Thanks you! Waiting for the mac version out 🙂
A file that is compatible with Excel 2011 for Mac is available now along with the file for Windows. The Mac version does not have the Analysis sheet (because it needs slicers) and charts are not present in the Analysis_Details sheet (because it needs PivotCharts). All other features have been retained.
Thanks for your patience and support.
Please send me an ammended file to accommodate 1600 products thanks a million- jewellapaz@gmail.com
I have updated this post with a new version of the template which can handle up to 2000 products.
Fantastic template, but is there anyway i can amend it to keep track of more than 100 products?
Thanks!
Thank you. I have updated this post with a new version of the template which can handle up to 2000 products. I hope this helps.
Best on the web!!! Thank You for Your effort!!!
I am glad you like it. Thank you.
That’s really good stuff. My buddies at work will definitely be awestruck ! Thanks for sharing.
TO:
indzara
DEAR SIR.
WITH WARM WISHES, I RAGHAVENDRA FROM HUBLI, KARNATAKA REALLY APPRECIATE FOR THE MAKING OF Inventory and Sales Manager (Excel Template)
I KINDLY REQUEST YOU PLEASE SEND US FULL FREE VERSION OF ACCOUNTING WITH INVENTORY EXCEL TEMPLATE .I AM BASICALLY BUSINESSMAN PRESENT AM USING BUSY WIN ACCOUNTING WITH INVENTORY SOFTWARE.
AM REALLY SHOCKED WHEN I SEEN YOUR EXCEL ACCOUNTING DEMO. SO I WANT TO START IN FULL PLEDGE WITH YOUR EXCEL FORMAT. KINDLY SEND IT FULL FUNCTIONED EXCEL WORK SHEET.
AN EARLY RESPONSE TO THIS IS HIGHLY APPRECIABLE. MY EMAIL ID IS raghukon@yahoo.com
REGARDS
RAGHAVENDRA
7795542361
Hi,
Great excel template, thanks for taking the time!
I am trying to make a sheet of my own that has a function like your :
PRODUCTS WHERE CURRENT INVENTORY <= RE-ORDER POINT
and am having a very hard time replicating whats going on here, my table works close to the same where there is a product category, quantity category and a re-order point. Could you please break-it-down and explain what is going on in that table?
I see that most of the formulas work off of the "product" line of code : =ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX(Tbl_Current_Inventory,SMALL(IF(INDEX(Tbl_Current_Inventory,,5)<=0,ROW(INDEX(Tbl_Current_Inventory,,5))-ROW(Help!$C$4),""),$L$5-ROW($H$4)),1),"")), 1, 1)
but I am having a hard time understanding it. Particularily the " <=0,ROW(INDEX(Tbl_Current_Inventory,,5))-ROW(Help!$C$4),""),$L$5-ROW($H$4)),1),"")), 1, 1) " portion, as well as the exact values being used in the tbl_current_inventory
thanks!
Thanks for the compliments. The formulas are array formulas. Have you entered them as array formulas? Thanks.
I have, my question I guess is why are you using the “<=0", the $C$4 and the $L$5, I am having a hard time understanding where their relevance comes into the equation.
<=0 is to only pick up products that have inventory less than re-order point. I see D3 (instead of C4) in my file - this is needed as the ROW function only returns the true row number of the sheet but not the row number of the product 'table'. So, we subtract the row number of the header of the table. The header is in row 3. Hence I use D3. L5 is where we store the result of the scroll bar. As we use the scroll bar (up or down), L5 changes and we need this to change which product is listed. Hope this helps. Best wishes.
I noticed that my “products where current inventory <=reorder point", current inventory column is frozen
Please email the file to indzara@gmail.com so that I can see what the issue is. Thank you.
Hi indzara, remarkable template. I was wondering if it would be plosives to include actual delivery or revived date in order to measure on time delivery performance. Also is there anyway historical data can be entered?
Thank you very much.
You can add a column to store the delivery date and then calculate how often the delivery date is the same as Expected date.
You can enter historical data just like new data.
Best wishes.
You gotta pay for that shit cuz!!