Inventory Reports
Overview
An Inventory Report is a document or record that provides a detailed overview of the items an organisation currently holds in stock. It typically includes information such as the quantity, condition, location and value of each item.
Inventory reports help you track inventory levels, identify discrepancies and plan restocks or manage resources efficiently.
Settings
User Settings
Note
The defaults are the values for new databases and new users. Your database may have different values.
| Setting | Description | Default |
|---|---|---|
| Materials & Inventory > Inventory - Access Allowed |
| None |
| Point of Sale > Default: Inventory Location | Dropdown list with all locations to select a default location for the specific user. | None |
| Purchase Orders > Default: Inventory Location | Dropdown list with all locations to select a default location for the specific user. | None |
| Sales Orders > Default: Inventory Location | Dropdown list with all locations to select a default location for the specific user. | None |
| Work Orders > Default: Inventory Location (Material) | Dropdown list with all locations to select a default location for the specific user. | None |
| Work Orders > Default: Inventory Location (Output) | Dropdown list with all locations to select a default location for the specific user. | None |
General Settings
| Setting | Description | Default |
|---|---|---|
| Customer Credit Notes > Inventory: Auto Receive Inventory when Customer Credit Note is changed to a Complete Status | Yes or No. | No |
| Materials & Inventory > Enable automatic costing on Inventory Items |
| Weighted Average Price |
Buttons Explained
| Button | Description |
|---|---|
| Reset | Resets the selected data filters. |
| View | Generates a report based on the filters selected. |
| Output | Available upon request. |
| Actions |
|
Filters Explained
Info
Filters differ between views and include one or more of these filters.
| Filter | Description |
|---|---|
| Set | A dropdown list with various Date options. |
| ← → | Adjusts the date fields to the previous or next dates based on the selected date setting. |
| Date | Lets you enter dates in custom Date fields (only available with certain date settings). |
| Customer | A dropdown list of all customers to select All or a specific Customer. |
| Category | A dropdown list of all categories to select All or a specific Category. |
| Location | A dropdown list of all locations to select All or a specific Location. |
| Hide blanks | Checkbox. If checked hides inventory items with no quantity in stock. |
| Active | Checkbox. If checked only shows inventory items that are active. Available in the Default, Price Update, Stock Check and Replenishment views (not in the Detailed Register). |
| Exceptions |
|
Report Views
Fields Explained: Default View Date Selected
Info
The Default View provides a snapshot of items in stock. If a Date is selected, additional columns are added showing the stock movements.
Top Row
- Field Chooser (*): Lets you add or remove any of the heading columns.
Detail Section
- Field Chooser (*): Lets you add or remove any of the columns.
| Field | Description |
|---|---|
| ID | The system-generated number allocated to the inventory item. Each location has its own number. |
| Customer | The name of the customer who was selected when setting up the material. |
| Category | The inventory item's category. |
| Subcategory | The inventory item's subcategory. |
| Description | The inventory item's description. |
| Code | The inventory item's code. |
| Location | The inventory item's location. |
| Unit | A description of the inventory item's unit. |
| Min Qty | The Minimum Quantity to hold in stock. |
| Reorder Qty | The Quantity to Order. |
| Bin No | The bin number of where the inventory item is kept. |
Opening Balance
| Field | Description |
|---|---|
| Qty | The Inventory Quantity on hand at the start date. |
| Unit Price | The Unit Price of the inventory items at the start date. |
| Value | The value of the inventory items at the start date (Qty x Unit Price). |
Movement
| Field | Description |
|---|---|
| Received | The Inventory Items Received between the Start and End Dates. |
| Issued | The Inventory Items Issued between the Start and End Dates. |
| Adjusted | The Quantity of inventory items adjusted between the Start and End Dates. |
| Total | The Total Quantity of inventory items moved between the Start and End Dates. |
| Value | The Value of Inventory Movements between the Start and End Dates. |
Closing Balance
| Field | Description |
|---|---|
| Qty | The quantity of inventory on hand at the closing date. |
| Unit Price | The unit price of the inventory on hand at the closing date. |
| Value | The value of the inventory on hand at the closing date. |
| Value Issued | The value of inventory issued during the start and end dates. |
| Value Adjusted | The value of inventory adjustments during the start and end dates. |
| Unit Price% | The unit price percentage change from the opening to closing unit price values. |
| Value Received | The value of inventory received during the start and end dates. Calculated as the Received Qty multiplied by the Closing Unit Price. |
Menu (...)
| Field | Description |
|---|---|
| Open linked Material Item | Opens the selected material's setup page. |
| View Material Summary | Opens a report with the material's details and transactions. |
| View Detailed Inventory Register | Opens a report showing all the material's inventory transactions. |
Fields Explained: Default View No Date
| Field | Description |
|---|---|
| ID | The system-generated number allocated to the inventory item. Each location has its own number. |
| Customer | The name of the customer who was selected when setting up the material. |
| Category | The inventory item's category. |
| Subcategory | The inventory item's subcategory. |
| Description | The inventory item's description. |
| Code | The inventory item's code. |
| Location | The inventory item's location. |
| In Stock | The quantity of the inventory item in stock. |
| Reserved | The quantity of the inventory item reserved. |
| On Order | The quantity of the inventory item ordered. |
| Available | The quantity of the inventory item that is available. |
| Unit | A description of the inventory item's unit. |
| Min Qty | The minimum quantity to keep on hand. |
| Reorder Qty | The quantity to order. |
| Bin No | The bin number that the inventory item is stored in. |
| PO No | The linked Purchase Order number. |
| Menu |
|
Fields Explained: Price Update
Info
The Price Update View provides a snapshot of pricing information and includes actions for you to adjust the prices.
Fields marked in green (✎) can be edited.
| Field | Description |
|---|---|
| ID | The system-generated number allocated to the inventory item. Each location has its own number. |
| Description | The inventory item's description. |
| Code | The inventory item's code. |
| Category | The inventory item's category. |
| Customer | The name of the customer who was selected when setting up the material. |
| Location | The inventory item's location. |
| Unit | A description of the inventory item's unit. |
| Min Qty | The minimum quantity to keep on hand. |
| Reorder Qty | The quantity to reorder. |
| In Stock | The quantity of the inventory item that is in stock. |
| Last PO Date | The date used on the last Purchase Order for that inventory item. |
| Last PO Price | The price used on the last Purchase Order for that inventory item. |
| Cost Price | The current Cost Price of the inventory item. |
| Sell Price | The current Sell Price of the inventory item. |
| Var (SP - CP) | The price difference between the Sell and Cost Prices. |
| Sup Price | The Supplier's Price for the inventory item. |
| Est Cost | The Cost Price used on Estimates. |
| Stock Price | The current calculated or manually entered inventory price (based on the selected setting). |
| Menu |
|
Fields Explained: Stock Check
Info
The Stock Check View provides a snapshot of inventory-item quantities. This report is especially helpful after a Stock Take because it highlights discrepancies between physical stock counts and stock counts in the system (generated from transactions).
Heading
- Field Chooser (*): Lets you add or remove columns from the Heading Section.
Detail
| Field | Description |
|---|---|
| Field Chooser (*) | Lets you add or remove columns from the Details Section. |
| ID | The system-generated number allocated to the stock-check transaction. |
| StockID | The system-generated number allocated to the stock item. Each location has its own number. |
| Description | The inventory item's description. |
| Code | The inventory item's code. |
| Customer | The name of the customer who was selected when setting up the material. |
| Category | The inventory item's category. |
| Location | The inventory item's location. |
| Unit | A description of the inventory item's unit. |
| Min Qty | The minimum Inventory quantity to keep on hand. |
| Reorder Qty | The inventory quantity to reorder. |
Stock Check
| Field | Description |
|---|---|
| Date | The date of the Stock Check transaction. |
| Unit Price | The inventory item's unit price. |
| Est Qty | The expected quantity from the application (calculated from transactions that involve inventory items). |
| Act Qty | The counted quantity. |
| Var Qty | The variance: Act Qty - Est Qty. |
| Gain/Loss | The value of the inventory that was gained or lost (Var Qty x Unit Price). |
| Status | The status of the Stock Check (Pending or Complete). Pending Stock Checks are not counted in the quantity in stock. |
Menu (...)
- Open linked Material Item: Opens the selected material's setup page.
- View Material Summary: Opens a report with the material's details and transactions.
Fields Explained: Transactions
Info
The Transactions View lists all the transactions that affected inventory quantities. This report can be used to determine the source of inventory changes.
Transactions that originate from Stock Checks are highlighted in yellow.
| Field | Description |
|---|---|
| Field Chooser (*) | Lets you add or remove columns. |
| ID | The system-generated number allocated to the transaction. |
| Status | The Status of the inventory transaction. |
| Trans | A code indicating the type of transaction. |
| No. | The system-generated materials movement number. |
| Date | The date of the transaction. |
| User | The user who created the transaction. User = SYS for system-created transactions. |
| Company | The name of the Customer or Supplier that was part of the transaction. |
| Reference | The reference field from the source transaction. |
| Customer | The name of the customer who was selected when setting up the material. |
| Category | The inventory item's category. |
| Subcategory | The inventory item's subcategory. |
| ItemID | The source document's number. |
| Description | The inventory item's description. |
| Code | The inventory item's code. |
| Resolve | The resolve (action to take) option selected for the material or transaction (e.g. Receive, Issue, Order, Reserve). |
| Location | The inventory item's location. |
| Unit | A description of the inventory item's unit. |
| Qty | The quantity of inventory applicable to the transaction. |
| Unit Price | The unit price of the inventory in the transaction. |
| Total | The total value of the transaction (Quantity x Unit Price). |
| Qty Stock | The quantity of inventory moved (negative numbers = issue, positive numbers = receive). |
| Total Stock | The total value of the transaction (negative numbers = earnings, positive numbers = cost). |
| Menu (...) |
|
Fields Explained: Replenishment
Info
The Replenishment View provides a snapshot of upcoming inventory changes and offers a quick way to order stock to meet the minimum stock holding requirements.
It uses the following material settings:
The following rules determine if an item is on the Replenishment Report:
- The material is needed (that is, there is an active sales or Work Order).
- The material must be ordered from either a Work Order or a Purchase Order.
- The quantity in stock is less than the minimum quantity.
| Field | Description |
|---|---|
| Lead Days | The number of days from placing an order to receiving the goods. |
| Min Qty | The minimum quantity to keep in stock. Normally calculated as the quantity sold during a period equal to the lead days plus a small buffer. |
| Reorder Qty | The quantity to order when stock is replenished. |
| Field | Description |
|---|---|
| Field Chooser (*) | Lets you add or remove columns. |
| ID | The system-generated number allocated to the transaction. |
| Category | The inventory item's category. |
| Description | The inventory item's description. |
| Code | The inventory item's code. |
| Unit | A description of the inventory item's unit. |
| Location | The inventory item's location. |
| Customer | The name of the customer who was selected when setting up the material. |
| Subcategory | The inventory item's subcategory. |
| Min Qty | The Minimum Inventory Quantity to keep on hand from the item's setup. |
| Re-Order | The inventory quantity to order from the item's setup. |
| In Stock | The inventory quantity currently in stock. |
| On Order | The inventory quantity ordered from an Active Work Order or Purchase Order (Resolve = Order). |
| Reserved | The inventory quantity reserved from an Active Work Order or Sales Order (Resolve = Reserve). |
| Available | The inventory quantity available which equals the quantity in stock less the quantity reserved. |
| Description | The inventory item's description. |
| Resolve | The resolve (action) taken in the transaction. |
| Min Qty | The minimum Inventory Quantity that should be on hand. |
| Unit | A description of the inventory item's unit. |
| Currency | The transaction's currency. |
| Price | The Unit Price used in the transaction. |
| Last Order | The last order number linked to the inventory item (e.g. PO 2002). |
| Menu (...) |
|
Fields Explained: Detailed Register
Info
The Detailed Register provides a detailed list of all Stock Movements for a specific item at a specific Location.
If you select a date, an Opening Balance appears, unlike with No Date (all transactions).
The Detailed Register is filtered by date, Material and Location only. It has no Active filter.
| Field | Description |
|---|---|
| Field Chooser (*) | Lets you add or remove columns. |
| ID | The system-generated number allocated to the inventory register. |
| Trans Type | A code indicating the transaction type. |
| Trans | The system-generated transaction number. |
| Date | The date of the transaction. |
| Description | The description of the transaction. |
| User | The user who created the transaction. User = System for system-generated transactions. |
| Location | The inventory item's location. |
| Unit Price | The inventory item's unit price. |
| Unit Price (Trans) | The inventory item's price used in the transaction. |
| Unit | The description of the inventory item's unit. |
| Unit (Trans) | The description of the inventory item's unit used in the transaction. |
| Qty | The quantity of the inventory item. |
| Qty (Trans) | The quantity of the inventory item in the transaction. |
| Receive | The quantity of Inventory Received. |
| Issue | The quantity of Inventory Issued. |
| Balance | The quantity of inventory still on hand at the end of the transaction. |
| Menu |
|
Fields Explained: Inventory - Ageing
Info
The Inventory Ageing View shows how long inventory is kept in stock. It pinpoints inventory that is just sitting on the shelves.
| Field | Description |
|---|---|
| Field Chooser (*) | Lets you add or remove columns. |
| Stock ID | The system-generated number allocated to inventory register. |
| Status | The inventory item's status. |
| Category | The inventory item's category. |
| Subcategory | The inventory item's subcategory. |
| Customer | The name of the customer who was selected when setting up the material. |
| Rep | The rep allocated to the material's customer. |
| Description | The inventory item's description. |
| Code | The inventory item's code. |
| Location | The inventory item's location. |
| Ageing columns | Columns that show how old the inventory items are. Columns are available for:
|
| Menu (...) |
|
How Ageing Is Calculated
- Each receipt (Materials Movement - Receive) is placed in an ageing column by the month it was received:
- 30 days: the current month.
- 60 days, 90 days, 120 days: one, two and three months back.
- 180 days: four and five months back.
- 1 year: from twelve months back up to the 180 days column.
- 2 years: the year before that.
- 2+ years: older receipts.
- A Stock Check that finds more stock than expected counts as a receipt on the Stock Check date. A Stock Check that finds less counts as an issue.
- Issues are taken from the oldest columns first (first in, first out), so the columns show the age of the stock still on hand.
- The values are the quantities multiplied by the item's current unit price.
Fields Explained: Batch Tracking
| Field | Description |
|---|---|
| Field Chooser (*) | Lets you add or remove columns. |
| MatUnitID | The system-generated number allocated to the material. |
| Status | The Work Order's status. |
| WO No. | The Work Order number in which the inventory item was used. |
| Description | The inventory item's description. |
| Reference | The inventory item's reference. |
| Code | The inventory item's code. |
| Subcategory | The inventory item's subcategory. |
| Customer | The customer that was / will be invoiced (Customer linked to material). |
| MMR No/s | The Material Movements (Receive) number/s. Refers to the materials received to produce the batch. |
| MMI No/s | The Material Movements (Issue) number. Refers to finished goods issued from the batch. |
| Expires | The batch's expiry date. |
| MMR Date | The Material Movements (Receive) Date. |
| Ageing (d) | Displays the interval from current date to the first Material Movements (Receive) Date. |
| Received | The quantity of the inventory item that was received. |
| Issued | The quantity of the inventory item that was issued. |
| Total Qty | The total quantity of inventory moved. |
| Unit Cost | The unit cost of the inventory moved. |
| Total Cost | The total cost of the inventory moved. |
| Unit Sell | The unit selling price of the inventory moved. |
| Total Sell | The total selling price of the inventory moved. |