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 Indzara. Wonderful file here. One question, IN the Reports sheet, the years 2012 & 2013 are there in the year filter. How do i extend this to 2016 or 2017? How to add more years
Thank you. The filters in the report sheet should automatically update based on the data entered in the Orders & Inventory sheet. Please refresh the data by pressing Refresh All in the DATA Ribbon.
If this does not solve the problem, please email me the file at indzara @ gmail
Thanks & Best wishes.
Hello,
I have been reviewing your Inventory spread sheets and they very impressive. Unfortunately I have not found one to suit my needs based on my unique & quickly changing situation. I would like your best recommendation to track my inventory based on the following needs & dynamics:
*I need to track 2 types of inventory (Primary use / Contingency use) rented to my 8 clients.
*The inventory expires every 6 months but can be refurbished if possible if not it is destroyed.
*Each client is on a different location and move biweekly to a new location, unpredictable schedule.
*A “new package” of various products is preset at their new location before their movement.
*Their old “Package” is recovered when they move, expired products are removed from the “Old Package”, The remaining inventory is dismantled & reassembled into a new package for the next client move.
*All my clients use the same inventory which has many types but is limited to Two sizes (2inch or 3inch).
*My clients are capable of destroying their entire package 700+ pieces at any given time by mistake as they operate 24 hours a day / 7 days a week.
*Each piece has a cost to: Buy new or Refurbish.
*I must also track my destroyed inventory by type & cost.
Any help in this matter would be greatly appreciated, you can contact me by email as I eagerly await your response.
Thanks for your feedback.
I am very sorry for the delayed response.
Your requirements seem unique and complex. I don’t have such a template.
It would require some significant development in order to automate calculations. I take customization projects for a fee. This would definitely take a significant time and thus cost will be high. If you are interested, please email indzara@gmail.com.
Thanks & Best wishes.
Sir, I also noticed you used a mm/dd/yyyy date format. Can it be changed to dd/mm/yyyy format?
If so,how can I effect it without messing with this very-useful template.Many cheers!!
You can click on those date cells and then change the format in the number format menu (or press Ctrl+1). The way the dates appear do not impact the calculations. However, it is important that they are entered as dates and not text. Sometimes Excel will treat certain formats as text and not as dates. We should watch out for that. Best wishes.
Many thanks for your response sir. I then suppose that if I wish it to show in the reports,I have to adjust the pivot tables?
You are welcome. First it needs to be added to the Orders-and-Inventory sheet. Then it needs to be added to the pivot tables. Finally it is brought to charts/tables by formulas. Best wishes.
Sir, I thank you immensely for your work. Please what would be the effect of adding an “Expiry Date” column to the Products table? Would it mess with the logic of the template?
Many thanks
You are very welcome. Just adding new columns will not affect the template adversely. Thanks & Best wishes.
how make invoice ?
To create invoices, please see https://indzara.com/2016/07/free-excel-invoice-template-sales/
To manage inventory and invoices together for retail businesses, please see https://indzara.com/product/retail-business-manager-excel-template/
Thanks & Best wishes.
Previous Question: How can change the column name of Orders_and_Inventory Sheet. Example: Product Name as a product code.
Answer :Product Name field is used in a pivot table that feeds the Report sheet. So, changing the Product name would then require updating the dependent pivot tables. Pivot tables are in hidden sheets.
Question : How can update the Pivot tables.
When field name is changed in the Products table, then the Pivot table will lose that field. Then, we go in the pivot table and add that new Product Code where Product name was previously there.
Then, we need to go to the dependent formulas (that formulas that have GETPIVOTDATA function). Those formulas would have broken due to the pivot table break. Now, we update that GetPivotData to point to the product code reference.
A suggestion: If this is about just appearance, you can type in a label ‘Product Code’ above the ‘Product Name’ in the Products table. You can change the Product Name text font color such that it does not appear visible. This approach is to avoid all the formula changes.
Best wishes.
Hi,
I’ve found this spreadsheet incredibly helpful!
Just a quick question. In order type I need to add the option “Sample Sale” (free of charge sale).
Would you be able to explain how I could do this? I was searching for the name ranges to edit it that way but I seem to be having a bit of difficulty doing so.
Adding a value to the drop down would require editing the data validation setting. Please add the new value to the list.
If new Order Type needs special handling in inventory calculations, that would require adjusting many formulas in many places.
Thanks. Best wishes.
How can change the column name of Orders_and_Inventory Sheet. Example: Product Name as a product code.
Product Name field is used in a pivot table that feeds the Report sheet. So, changing the Product name would then require updating the dependent pivot tables. Pivot tables are in hidden sheets. Thanks & Best wishes.
How can update it
Hi, Indzara
This is a good spreadsheet ever.
I am working with your spreadsheet but i want to filter the product with category.
how can i do that?
Thank you.
In the Report sheet, you can filter by clicking on the Product Category slicer.
In the Orders and Inventory sheet, you can use table header filters to filter. Category is in column J.
Best wishes.