The Inventory Analysis Pivot View allows you to build custom, drill-down reports using product, vendor, and warehouse data. For example, choose to measure Inventory Turns, then break the turns for each Product Rank, Warehouse, Product Line, and Product. Save the layout to run the report repeatedly, or export the data to send to your warehouse supervisor.
Note: All calculations, including the Inventory Value are based on your Sales Order GP% Cost Column, set under the Pricing/Costing tab in your Company Master. This includes Inventory Value, Cost of Sales and Profits. Quantity fields refer to the default Purchasing Unit of Measure for the product. The values displayed may differ from those on your Inventory Value report for this reason.
When you create a new pivot report (or modify one using the Inventory Analysis Dataset, the following data is available .
- Go to Analytics > Pivot Views > Pivot Table Editor
. - Click New, and then from the Dataset list, choose Inventory Analysis.
Measures
| Data field | Description |
|---|---|
| 12 Month Average Inventory Value | The average inventory dollar value for the product over the past 12 months |
| 12 Month Cost of Sales | The average Cost of Sales for the product over the past 12 months |
| 12 Month Demand | The total Demand for the product over the past 12 months. Demand is the sum of your customer orders, not the actual sales; it includes unfilled orders and canceled backorder quantities |
| 12 Month Hits | The total number of hits for the product over the past 12 months. |
| 12 Month Profits | The total profit dollars for the past 12 months |
| 12 Month Sales | The total sales dollars for the past 12 months |
| 12 Month Units | The total units sold of the product in the past 12 months |
| Available | The current available quantity of the product |
| Committed | The current committed quantity of the product |
| GMROI | The Gross Margin Return on Investment for the product. This is an inventory profitability evaluation ratio that analyzes a firm's ability to turn inventory into cash above the cost of the inventory. It is calculated by dividing the gross margin by the average inventory cost. This field may display blank if the product does not have at least 12 months of history |
| Inventory Value | The current value of the inventory on hand. This calculation is based on the Sales Order GP% Cost Column cost for the product and the current on hand quantity |
| Months Since Last Sale | The number of months that have passed since the last time that the product was invoiced on a Sales Order |
| On Hand | The current on hand quantity for the product |
| On PO | The current quantity of the product on open Purchase Orders |
| On Transfer | The current quantity of the product on open Stock Transfers |
| Turns | The average number of times that you purchase the product over a 12 month period. This field will display a blank if the product does not have at least 12 months of purchasing history. |
Inventory
| Data field | Description |
|---|---|
| Product Code | The Product Number. |
| Warehouse Code | The reference number of the warehouse. |
Product
| Data field | Description | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Product Code | The Product Number | ||||||||||||||
| Product Code + Description | The reference code for the Product plus the Product description | ||||||||||||||
| Product Line Code | The reference code for the Product Line assigned to the product on the invoice | ||||||||||||||
| Price Group Code | The reference code for the Price Group assigned to the product on the invoice. | ||||||||||||||
| Buy Line Code | The reference code for the Buy Line assigned to the product on the invoice. | ||||||||||||||
| Vendor Code | The reference number for the primary vendor assigned to the product on the invoice | ||||||||||||||
| Web Category Code | The reference code for the Web Category assigned to the product on the invoice | ||||||||||||||
|
C1-C7 Cost L1-L4 Price |
The cost and list prices for the product. For details, see Learn about cost and list prices. | ||||||||||||||
| Product Status |
The reference code for the status of the product on the invoice.
|
||||||||||||||
| Product | The product description. | ||||||||||||||
| Overall Rank |
The overall rank of the product.
|
Warehouse
| Data field | Description | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| Warehouse Code | The reference number of the warehouse on the invoice. | ||||||||||
| Warehouse Code + Description | The reference code for the Warehouse plus the Warehouse description | ||||||||||
| Warehouse | The name of the warehouse on the invoice | ||||||||||
| Warehouse Type |
The type of warehouse, using the codes below.
|
Buy Line
| Data field | Description |
|---|---|
| Buy Line Code | The reference code of the Buy Line of the product on the invoice. |
| Buy Line Code + Description | The reference code of the Buy Line plus the Buy Line description. |
| Buy Line | The name of the Buy Line of the product on the invoice. |
Price Group| Data field | Description |
|---|---|
| Price Group Code | The reference code of the Price Group assigned to the product on the invoice. |
| Price Group Code + Description | The reference code of the Price Group plus the Price Group description. |
| Price Group | The name of the Price Group assigned to the product on the invoice. |
Product Line| Data field | Description |
|---|---|
| Product Line Code | The reference code of the Product Line assigned to the product on the invoice. |
| Product Line Code + Description | The reference code of the Product Line plus the Product Line description. |
| Product Line | The name of the Product Line assigned to the product on the invoice. |
| Major Group Code | The reference code for the Major Group assigned to the product on the invoice. |
| Product Line Buyers | The system initials of the buyers assigned to the Product Line that is assigned to the product on the invoice. |
Vendor| Data field | Description |
|---|---|
| Vendor Code | The Vendor Number |
| Vendor Code + Description | The reference code for the Vendor plus the Vendor description. |
| Vendor | The Vendor Name |
| City | The city of the vendor's main address |
| State | The state of the vendor's main address |
| Postal Code | The Zip Code of the vendor's main address |
| Vendor Category Code | The reference code of the Vendor Category assigned to the vendor |
| Vendor Buyers | The system initials of the buyers assigned to the vendor that is assigned to the product on the invoice. |
| Contact | The primary contact for the primary vendor assigned to the product. |
| The primary email address for the primary vendor assigned to the product. | |
| Fax | The primary fax number for the primary vendor assigned to the product. |
| Phone | The primary phone number for the primary vendor assigned to the product. |
| Inventory Turns | The twelve month cost of the product divided by the twelve month average inventory value. |
| Lead Time (Days) | The lead time in days for the product or vendor. |
Vendor Category| Data field | Description |
|---|---|
| Vendor Category Code | The reference code of the Vendor Category assigned to the vendor |
| Vendor Category Code + Description | The reference code for the Vendor Category plus the Vendor Category description. |
| Vendor Category | The name of the Vendor Category assigned to the vendor. |
Web Category| Data field | Description |
|---|---|
| Web Category Code | The reference code for the Web Category that the product is assigned to |
| Web Category Description | The description of the Web Category that the product is assigned to |
| Web Category Parent Code | The reference code for the highest level Web Category that the product is assigned to |