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
Good Day,
I am using your inventory-and-sales-manager excel program. However after entering a number of Purchases and Sales entries the “CURRENT INVENTORY LEVEL” still shows 0 for PRODUCTS AVAILABLE and 0 for QUANTITY. Can you please assist.
Have you refreshed the data as explained in the blog post above? DATA RIBBON –> Refresh All
Thanks,
yes I have…
My date format was wrong… I was using 14/06/2015 instead of 14-Jun-15
Ah… Good catch. Thanks for the update. I am glad that you found that now. Best wishes.
Ty Sir, do i enter only negative quantity or negative rate and negative amount also – one more question
can i delete some data from 2nd worksheet some times or its only
one way entry process
thanking you with best best wishes
Please enter only quantity as negative. Unit price should be positive. Amount is automatically calculated.
You can remove data from orders_and_inventory sheet as you need.
Thank you,
Hi Sir,
Its a great template. I want to ask u one question,how I adjust
cancelled order before shipment or after shipment in this
template.
with best wishes ,
Thank you. For example, if a sale order has been cancelled/returned, please enter a new sale order and enter negative quantity. That would update the inventory correctly. If it’s a purchase order that has been cancelled/returned, please enter a new purchase order and enter negative quantity.
Please let me know if there are other questions. Thank you.
This is fab, just what I’ve been looking for to use for a social enterprise I’ve started.
But I use openoffice calc, which although it is opening your .xlsx file I am getting errors
Would it be possible to have a version in an earlier version of Excel, or would some functions not work then?
Thanks for the feedback. I have two versions above. One that works only in Excel 2010 and 2013. The other version works in Excel 2007 and Excel 2011 for Mac. Have you tried that one? I don’t have access to Excel 2003 and hence cannot test it.
Best wishes,
Thanks you IND ZARA and best wishes. Pls I’m interested in having a trading/Sales,(Computer shop in Abu dhabi-UAE) profit and loss template and still have an inbuilt inventory manager like this. I want to be able to keep an eye on my stock inventory as sales is done daily. Daily, I want to have my closing stock balances for each product.
can u help?
please send me.
Shafeeqhe.
email ID: hebashafiq@yahoo.com
Please review https://indzara.com/product/retail-inventory-and-sales-manager-excel-template/. I think it has the features that you are referring to. Please let me know if there are questions.
Dear Indzara,
Simply put, you are wonderful. Thanks for your generosity! Pls I’m interested in having a trading/Sales, profit and loss template and still have an inbuilt inventory manager like this. I want to be able to keep an eye on my stock inventory as sales is done daily. Daily, I want to have my closing stock balances for each product.
can u help? I’ll appreciate it mightily.
Thanks again for the inventory manager.
Thank you for the kind words.
Profit/Loss, current Inventory for all products in a report and invoicing are some of the features available in the premium version of the template. https://indzara.com/product/retail-inventory-and-sales-manager-excel-template/
Please review and let me know if there are any questions. Best wishes.
Hello sir !!!t
Before the question I want to say that I just started to use your program and I think is a great job. Thank you !!!
Is it possible to introduse about 3.000 plus items into the program ?
Thank you again !!!
jay alvarez
Thank you. I am glad that it has been useful. I will try to increase the product limit in the next version. I am sorry that I cannot get to it immediately.
Best wishes,
Hi Ind,
Is there a way for me to see the profits I earned from my sales every month? Considering that the purchase unit price of the product is not fixed.
You would have to calculate the difference between sales and purchase amounts every month. It would require writing new formulas.
It is available in the premium version https://indzara.com/product/retail-inventory-and-sales-manager-excel-template/
You can see the screenshots and the video on the product page.
Please let me know if there are questions. Thanks.
Hi all! this is a great find! I was looking everywhere for this kind of spreadsheet and this is the best. very useful and easy to use. I’ve been using this for a year now.
Thanks for your feedback. I am glad that it has been useful. Best wishes.
Hello ind zara,
We think your system is really great and it helped us allot to enhance our business.
The overview of our stock is much better than before.
But I have one question:
Is it possible to make a print button that prints the whole list (so not only the part you can see) that is in your scroll down menu called: ” PRODUCTS WHERE CURRENT INVENTORY <= RE-ORDER POINT"
So we can print out what we need to buy soon.
I must confess im very new at Excel so i don't have a clue what to do, i have tried some things and searched the internet but i still have clue.
Your pre made work convinced me to use it.
I hope you can help me.
And if not, than im still very grateful
Greetings Jeroen Maasdam
Thanks. I am glad that it is useful. You can unhide (right click on a sheet name and choose unhide and select the ‘Help’ sheet) a sheet named Help which has the list of all products.
In the premium version of the template, I have provided a separate report where you can choose only products that need to be ordered and printed. indzara.com/product/retail-inventory-and-sales-manager-excel-template/
Please let me know if there are any questions. Best wishes.
Hello Ind,
Thank you for your quick reply.
That i now now how to get a overview of all the products is also helping allot.
I now still have to use the small drop down list to see all the product we need to reorder but i found a way to make that a bit easier to, by just adjusting the number of skips he makes when scrolling.
I’m also thinking about getting the premium version, I have to check this with my college.
Thank you again for your help and great product.
Greetings Jeroen Maasdam
You are welcome. Thanks for your feedback. Please leave a comment if you have any further questions. Best wishes.