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
Great work! However i was wondering the incorporation of bar coding. How feasible would that be?
Thank you.
Do you mean the data in the template should update based on scanning a bar code?
Hi, very great and impressive template indeed, could you please make the template on excel 2007? thus the report tab could be worked soon 🙂
thanks.
Thank you.
I will be getting a copy of Excel 2007 this week. I will do my best to update my templates soon.
many thanks..
Have you updated the file already?
I am sorry I haven’t. I have updated the All-Purpose Calendar Maker template for Excel 2007. I have another couple of projects I am working on, before I can update Inventory and Sales Manager. To be realistic, It will be a few weeks. Thank you.
Woaaw,
Could you please send me the updated template on safraz@rocketmail.com
Thanks in Advance
You are welcome. The version posted here is the latest version. If there are further improvements, I will be posting here. Thanks for using the template.
Thanks you so much for this template.
Kindly send me the updated version to azl192@gmail.com
Thanks in advance!!!
You are welcome. The version posted here is the latest version. If there are further improvements, I will be posting here. Thanks for using the template.
Thanks looks like a great template. Is there a way to see profits for an item for a given period of time by using the most recent purchase price or an average or the last 2-3 purchaseand sales for that item or items.
Sorry this contact form is being very buggy. I want to clarify, what I am looking for is a report in which I can type in a date range and it will take all of the sales in that range and subtract the costs of the items sold taking the cost from the most recent purchase order of that item or an average from the most recent 2 or 3 purchase orders.
Thank you.
What you are looking for, is not readily available. However, the source data is all there in the template and calculations need to be set up to create the profits as you point out. It is feasible.
Can you aid me in making a simpler chart: I want to know the dollar amount of all inventory at a certain date, how can this be done.
The current inventory value (dollars) is now available in the Retail Business Manager excel template. https://indzara.com/product/retail-business-manager-excel-template/ Best wishes.
Are you looking for the dollar amount of inventory of each product or all the products together? To calculate the dollar amount, If you would like to use the price of last sale/purchase, it can be done. The inventory data is in the Orders table in the Orders_and_Inventory sheet in the Inventory Availability column. We just need to multiply that with the Unit Price column to calculate the amount. We can write a formula to do this and the formula can accommodate any date you enter. If this meets your needs, please e-mail me. I can send you a version with the changes. Thanks.
I find your template very helpful but I am also intrested in this “dollar” version!
Thanks
LInnea
linnea.sales@gmail.com
The inventory value (dollars) is now available in the Retail Business Manager excel template. https://indzara.com/product/retail-business-manager-excel-template/ Best wishes.
I request you from now on please make sheets in google spreadsheet
It has same functions and that would work all platforms including Mac iOS android windows and everywhere else even without haveing excel or any special software
Thanks
You are awesome
Keep it up
Hi, this is an incredible tool! Thank you for posting this!
Where do I enter pre-existing inventory?
Is this under the “CHOOSE PRODUCT” header or the “PRODUCTS WHERE CURRENT INVENTORY <= RE-ORDER POINT” section?
Thank you!
I am glad you find it useful.
Before you begin entering your new purchase and sale orders, enter the pre-existing inventory numbers for each product separately in each row. You would enter this in the Orders_and_Inventory sheet under the ‘Enter your order details’ message. The date you enter for the pre-existing inventory should be the earliest date. Your new orders should come after that.
Hope that clarifies.
Yes I have tried different apps also bought one app (documents)
I am sorry that it didn’t work. I recognize the need to develop solutions for different platforms. However, it will take some time for me to build them.
Thanks for the feedback.
thanks for sharing such a good work
and here i need help i am unable to import this worksheet to google spreadsheet to use it on my ipad
or is there any other way to edit it opn ipad, i can view but not edit
Thanks for your kind words. I don’t expect the template to work on Google Spreadsheets, due to incompatibility of features. I have not tried using the template on iPad. Have you tried any of the apps available to view and edit Office documents on iPad?
I’m having a bit of trouble on the Inventory Available Column…Is there anyway to make the formula calculate by sequence of rows instead of by product only? Example: If I currently have 50 of “Product A” in stock and I sell 49 of “Product A” it shows I have 1 left. Then the same day I sell another 5 of “Product A”. It now shows that I do not have enough to fill BOTH orders – but I actually had enough product to fill the first order, just not the 2nd. Is there a way for it to calculate by sequence instead of just all orders ever entered for that date or dates going forward?
Also – I am able to view inventory by selecting the product at the top of the document, but is there a way to view all current inventory at once on one page?
Never mind on the page listing ALL inventory, i found it 🙂
My first question though is basically asking – can the formula in the “Inventory Available” column calculate from the last orders “Inventory Available” total for that product instead of total inventory?, that way it wont change the previous Inventory Available amounts for previous orders.
It should be feasible. However, the way I have it set up now, it calculates the total availability of a product as of the expected date. Thanks for the comment. I will try to include this in the next version.