2023 Inventory and Sales Manager – Free Google Sheet Template
If you are struggling in managing your product inventory and sales this Google Sheet is suited for you. For everyone who runs a small business of buying products from suppliers and selling them to customers this sheet will be very helpful. This template is suitable for both retail and wholesale business.
What more? The template will assist in knowing the inventory levels of each product and understanding which products to re-order.
This template is designed for Google sheets, but if you are looking for an Excel template, please visit the Inventory and Sales Manager.
Features of the Inventory and Sales Manager Google Sheets Template
- 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
How to Use the Inventory and Sales Manager in Google Sheets Template
Overview of steps
- Make a copy of the sheet
- Input the Product and Order details
- View details & Report
Step 1: Make a copy of the sheet
Click on the link above to access the Google Sheet. Make a copy of the Sheet as shown in the screenshot below. Save this template on your Google Drive.
Note: Once you open the saved template, you have to just configure your inputs to customize the calendar according to your needs. The reports will be auto populated.
Step 2 : Input the Product and Order details
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 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.
Step 3 : View details & Report
View information about overall inventory availability
- 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.
- 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 the Report worksheet to understand the purchase/sales trends and also to identify the top performing products and most valuable suppliers/customers.
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
Amount Distributed across Partners
Product Ranking based on Sales Amount or Quantity
Thanks a lot for this valuable template.
Could you please tell me a way to incorporate returns (to supplier) as well?
You are welcome and thank you for showing interest in our template.
You will have to enter the sale order with negative quantity and negative amount for order return.
Also following is the link to the new version of Retail Inventory tracker template:
Awesome. Thanks a lot for the response.
How do you keep track of the shipping fees that your suppliers charge you, and the ones you charge your customers?
Thank you for showing interest in our template.
You can add shipping as a product and add it in each order to track the same.
Thanks for sharing this template.
I’ve created a separate copy, and placed the products with categories. Once I started orders_and_inventory, formulas are not working to calculate amount, invertory details etc.
Further, I was using your Excel template and copy pasting the same entries, but still formulas are not working.
Please help me in this regards,
Thank you for using our template.
I am unable to replicate the highlighted issue. Hence requesting to share access to your sheet to firstname.lastname@example.org to check further.
Hi I bought your inventory & sales system plus invoice sys.
How could I combine these two systems ?
Thank you for purchasing the templates.
You can use the VLOOKUP or INDEX function to take the invoice details and payment details from the order headers tab in Retail Business Manager pro to Invoice Manager template. Note (Best Practice): On using the VLOOKUP or INDEX referring to two different sheet, you will have to move/share the template together as compressed .zip or .rar to ensure the formula works after extracting the template in the new location.
Also in excel or google sheets I can not find a report to give me all available products quantities. I can only use the choose product field
There is a hidden sheet (which you can unhide) to see the complete list of products’ quantities.
Also, if in your business, the price of product is same for all customers, then I would recommend https://indzara.com/2017/02/free-retail-inventory-management-template/ This template will auto-populate price on orders, and also allows you to see the current inventory of all products.
Please let us know if any questions.
I have been using the excel version of inventory and sales manager. I downloaded the google sheets version copied all data over. Problem is I use the sort on product category often. In google sheets when I add a sort to that column it will not work any ideas?
Thanks for using our tempaltes.
We will look into the Google sheets question and get back to you.