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
Upstanding, superb just what I’ve been struggling to create.
You guys did an excellent work. Am I authorized to modify this worksheet to fit to my actual needs? Do you allow me?
Here is what I intend to do with the worksheet:
— Translate it to full Portuguese Language (My Country official language);
— Try to add Invoice ability (I appreciate some help to accomplish this);
— Use it in my workplace (add company’s logo and details, etc.)
Please contact thru my email because I need help.
Sincerely yours, Bilay
Thank you. Yes, you can modify the worksheet to suit your needs.
There is also the premium version that has the invoicing feature in-built. https://indzara.blogspot.com/2014/05/retail-inventory-and-sales-manager.html
Best wishes,
i came across to this blogsite and i find it interesting ..anyway, im patrick an inventory keeper and i need templates hope this will help me..
if you have updated version kindly sent it to my mail
thanks
patrick, inventory man…from philippines
Thank you.
The template in this page is a free version that you can download. Premium version is available here: https://indzara.blogspot.com/2014/05/retail-inventory-and-sales-manager.html
These two are the templates I currently have. Hope this helps.
thank you for this helpful template.
I would like to ask about how to make the Best Selling Items of the month in report page?
thank you very much.
You are welcome.
I believe the Product Ranking table (screenshot in the post above) in the Report sheet provides this information. Please let me know if that doesn’t address your question.
Thanks.
Hi, thanks for the reply.
I found that the Product Ranking table in my file is ranking for the top purchase items (top in stock amount). I would like to know bout best selling items and the graph for the ‘amount by partner’ I found that it cant show the quantity for the partner, it only show amount. ( can it possible to show the top customers list? ) thank you
Sorry about the delay in response.
1) Product Ranking Table is dynamic. If you choose Order Type as ‘Sale’ in the slicer at the top of the Report page, then the product ranking table will provide both amount and quantity of sales. You can choose top 10 or bottom 10 by Amount or Quantity.
2) Amount by Partner chart provides only amount. If you unhide the sheet named ‘pivot_partner’ you can see the pivot table. You can change Sum of Amount to Sum of Quantity in the VALUES section of Pivot table. That would update the chart to show quantity instead of amount.
To see the top 10 customers you can sort in the pivot table by quantity. This would require knowing pivot tables. Please let me know if this doesn’t help.
Thank you.
This is an amazing template, thank you very much for sharing it.
I have noticed that in the Report page, and in the Order year and Order Month filter, the is are 2 categories <10/1/2012 and >10/2/2012, that are not in the order and Inventory page, and that messes a bit this filters…. Is there anything I can do?
Thank you very much.
You are welcome.
Please email me the file at indzara at gmail. I can take a look at it. Thanks.
I’m really impressed.. your Inventory and sales management is really interesting.
My suggestion is that instead of making conditional formatting where Sale quantity is higher than Inventory available.. in this case, the inventory current will not match with the quantity where -5 or -9 are not calculated in stock management, because we can’t sale a product which we don’t have enough in the inventory.
So my suggestion is to make a data validation to restrict the sale of each product that is higher than what is available. I already did this to the one i downloaded and it works well. contact me if u need it
Thanks for the idea. Best wishes,
Hi, sir
My name is Aijaz From Hyderabad, I got a job as a Store keeper. my company is into dairy products. I need your advice which template is use full
Hello Aijaz,
For managing inventory and tracking sales, you can use the template in this page. For creating invoices, you can try the invoice builder. Hope this helps.
Thank you for this template! I do have one question, under order type how can i add additional types of order types such as “donation” or “gifts”? I apologize of this is a “beginner’s question” but I am not that proficient with Excel. Also, I was worried that if I played around with the Excel template too much it would mess up all the existing calculations.
You are welcome.
Order Type is integral to the calculations in the template. It determines whether we subtract inventory or add inventory. Simply adding new order types will not lead to correct calculations.
My suggestion is to put price as 0 if it’s a donation or a gift, but still use Sale or Purchase (depending on whether you are donating or receiving donation) as order type so that the inventory calculates correctly.
Hope that helps. Thanks.
Could you please update me on the status of this template with Mac compatibility and barcode scanner integration?
I have a Mac computer now and will begin testing this template in Mac before November.
I don’t have any plans to do barcode scanner integration for now. Sorry.
Thanks for following up. Best wishes,
The Mac version for Retail Inventory and Sales Manager is being tested. It will not have the Analysis sheet and the charts in the Analysis Details sheet. All the other features mentioned here https://indzara.blogspot.com/2014/05/retail-inventory-and-sales-manager.html will be retained.
Please e-mail at indzara at gmail for more details.
Thank you so much for this template, its really helpful 🙂
You are welcome. I am glad that it is useful.
This comment has been removed by a blog administrator.