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
=INDEX(Help!$L$4:$O$2003,ROW(J56)-ROW($J$56)+$M$56,1) How can use it for the product ranking if Change The Column Header name Ex: Product name as Product Code Then formula don`t work accurate. Pls help me how use it.
I am sorry I am not following your question correctly.
This formula pulls the data from the range L4 to O2003 in hidden ‘Help’ Sheet. The number 1 in the end of formula represents the column it is pulling the data from. 1 is first column in the range (L4 to O2003) and that is column L. This column has the product ranking data. If we enter 2 in place of 1, it will pull the product name data.
Hope this helps.
=INDEX(Help!$L$4:$O$2003,ROW(J56)-ROW($J$56)+$M$56,1) How can use it for the product ranking if Change The Culomn Header name Ex: Product name as Product Code
I am sorry I am not following your question correctly.
This formula pulls the data from the range L4 to O2003 in hidden ‘Help’ Sheet. The number 1 in the end of formula represents the column it is pulling the data from. 1 is first column in the range (L4 to O2003) and that is column L. This column has the product ranking data. If we enter 2 in place of 1, it will pull the product name data.
Hope this helps.
This is a great tool for managing my business!
Is there a way to transfer a bulk order to smaller units. for example I purchase a carton as my bulk but can make 4 smaller product out of it?
Thanks in advance for your help!
Thank you. There is no conversion logic built-in. We have to convert the carton to number of units and then enter in units. We could set up some additional columns to enable this calculation, but that is not currently built in the template. Thanks. Best wishes.
i have four file in this Different Storage Location (Approx 21 Storage location) , Item Code ( 4000 +) , Description are Common in every file,
Need one Desk Sheet Data & Distribution condition ( Issue or Not ) ,analyze Different Storage location wise
1. Pending PO Qty from All Branches
2. Defective Return to HO ( SAP transaction movement 261-262-455= Balance Qty to be Return
3. All Branches Minimum Stock
4. All Branches Online Stock
Pop logo on sale if there is discrepancy by conditional formatting
1. Pending Qty should not be Greater Then Minimum Stock
2. Defective return Qty should not be less ( Return Pending )
3. Branch Stock – Minimum Stock = Balance Qty if is less should be highlihgt
Thanks. I take projects for a fee. Please email detailed requirements with sample data to indzara@gmail.com and I will get back to you with an estimate for development. The more details you provide, it will be better for me to evaluate and understand your needs. Thanks.
hello sir,
in Excel, Pending purchase quantity is not working. can you help me.
Thanks in advance.
Please email the file to indzara at gmail and specify exactly what is not working. I will look into it and respond. Thanks.
This is a great tool! how can I run an available inventory report for Inventory purposes? thanks
Thank you. There is a hidden sheet named ‘help’ in the document. That lists all the products and the current inventory levels. Please note that the sheet has formulas and do not edit them.
The Retail Business Manager template shows the current inventory for each product in the Products sheet itself. https://indzara.com/product/retail-business-manager-excel-template/
Please let me know if there are any questions. Best wishes.
If I wish to track which item of that serial number is to sell to which customer.
Is it possible?
E.g., I bought 3 psu from supplier A
I sell 2 psu to customer A n keep 1 in office store
I want to select the 2 psu and know which 2 are sold n can keep track of warranty.
To track individual items, you can try entering each item in a separate order line. You can then add an additional column to track that item’s warranty date or supplier. Hope this helps. Best wishes.
I GIVE AWAY FREE SHIRTS SOMETIMES. IF I NEED TO MINUS ONE OUT OF STOCK WHAT DO YOU SUGGEST I DO. CAUSE I CANT JUST PUT IN THE NEW NUMBER BECAUSE IT MESSES UP THE FORMULA.
To do free products or to account for lost/damaged products, I would suggest creating a sales order of -ve quantity. This should reduce the inventory. Please let me know if this does not work. Thanks. Best wishes.
How do you suggest putting in supplies needed for the business. Or do you recommend using something else for that? Also I have an item that uses several different products to create one product. What do you suggest doing for that?
I am not completely sure about your question. Please excuse me if I have misunderstood.
To track the usage of supplies that are not part of the product we sell, then you can use a second copy of the template to manage supplies.
If a product is manufactured from raw materials, then please try the Manufacturing inventory tracker. https://indzara.com/2016/08/free-manufacturing-inventory-tracker/ Please note that this does not support the scenario of complex products where raw materials are used to create a subproduct (which we directly sell to customers) and we use subproducts to create other products. This multi-level BOM is not supported in any of the templates published on the site so far.
If I have missed your question, please let me know. 🙂
TO:
indzara
DEAR SIR.
WITH WARM WISHES, I am Mohamed Haneefa FROM Kerala, I REALLY APPRECIATED FOR MAKING OF Inventory and Sales Manager (Excel Template)
I KINDLY REQUEST YOU PLEASE SEND US FULL FREE VERSION OF INVENTORY CONTROLLING or STOCK IN DAYS WITH EXCEL TEMPLATE .AM USING INVENTORY SOFTWARE WHICH IS NOT SUPPORTED THE SAME.
AM REALLY SHOCKED WHEN I SEEN YOUR EXCEL ACCOUNTING DEMO. SO I WANT TO START IN FULL PLEDGE WITH YOUR EXCEL FORMAT. So, I KINDLY REQUESTING TO YOU SEND IT FULL FUNCTIONED EXCEL WORKSHEET.
An early response to this is highly appreciatable. As shown my mail ID.
haneefampm@gmail.com
Warm Regards,
Mohamed Haneefa
9037186185
Thanks for your kind words.
Please see all small business templates at https://indzara.com/small-business-excel-templates/ All the templates are posted online for download. Please let me know if there are any questions. Best wishes. Thanks.