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 are used to track inventory levels, identify discrepancies, and help with planning restocks or managing resources efficiently. 

Navigation: Main Menu > Materials > Inventory

Settings

User Settings

Navigation: Main Menu > Office > My Organisation > Double-click to open a Staff File > Settings (tab)

• Materials & Inventory > Inventory - Access Allowed: Options are None, Read-only, or Full Access.

• Point of Sale (PoS) > Default: Inventory Location: Dropdown list with all locations to select a default location for the specific user.

• Purchase Orders > Default: Inventory Location: Dropdown list with all locations to select a default location for the specific user.

• Sales Orders > Default: Inventory Location: Dropdown list with all locations to select a default location for the specific user.

• Work Orders > Default: Inventory Location (Material): Dropdown list with all locations to select a default location for the specific user.

• Work Orders > Default: Inventory Location (Output): Dropdown list with all locations to select a default location for the specific user.

General Settings

Navigation: Main Menu > Setup > Settings & Default

• Customer Credit Notes > Inventory: Auto Receive Inventory when Customer Credit Note is changed to a Complete status: Options are Yes or No.

• Materials & Inventory > Enable automatic costing on Inventory Items: Options are No, Weighted Average Price, or Last Received Price.

Buttons Explained

• Reset: Resets the selected data filters.

• View: Generates a report based on the filters selected.

• Output: Available upon request.

• Actions: 

  • Price Update View: Options to change the Inventory Prices of the selected records as follows:

• Set Stock Price = Last PO Price

• Set Stock Price = Cost Price

• Set Stock Price = Sell Price

• Set Stock Price = Supplier Price

• Set Stock Price = Est Cost

• • Replenishment View: Creates transactions to replenish inventory:

    • Create Purchase Order

    • Create Work Order

  • Batch Tracking View: Set Work Order Status to Complete.

• Menu:

  • Show Filter Row

  • Shows or hides a row with filter options for each column

  • Available views:

    • Default

    • Price Update

    • Stock Check

    • Transaction

    • Replenishment

    • Detailed Register

    • Inventory Ageing

    • Batch Tracking 

  • Print Grid: Prints the current view to the default printer.

  • Email Excel: Opens the default email template with the current view attached in .xlsx format.

  • Export Grid: Allows users to choose a save location for the current view in .xlsx format.

  • Save Grid

  • Restore Grid

  • Restore Default Grid (All Users)

Filters Explained

Filters differ between views and include one or more of these filters.

• 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: Allows users to type 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.

• Exceptions: 

  • Checkbox. If check shows inventory items with exceptional movements that require attention.

  • This checkbox requires a date to be chosen under Set. 

  • No data will be shown if it is checked and No Date (all records) selected.

• Sort by: A dropdown list with an additional sort field. Options are:

  • Category

  • Location

  • Customer

  • Value

Fields Explained: Default View Date Selected

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 (*): Allows users to add or remove any of the heading columns.

Detail Section

• Field Chooser (*): Allows users to add or remove any of the columns.

• ID: The system-generated number allocated to the inventory item. There is a number for each location.

• 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 that should be held in stock.

• Reorder Qty: The Quantity that should be Ordered.

• Bin No: The bin number of where the inventory item is kept.

Opening Balance

• 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

• 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

• 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 (...)

• • Open linked Material Item: Opens the selected material item's setup page.

  • View Material Summary: Opens a report with the material item's details and transactions.

  • View Detailed Inventory Register: Opens a report showing all the material item's inventory transactions.

Fields Explained: Default View No Date

• ID: The system-generated number allocated to the inventory item. There is a number for each location.

• 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 that should be on hand.

• Reorder Qty: The quantity that should be ordered.

• Bin No: The bin number that the inventory item is stored in.

• PO No: The linked Purchase Order number.

• Menu: 

  • Open Purchase Order: Opens the linked purchase order.

  • Open linked Material Item: Opens the selected material item's setup page.

  • View Material Summary: Opens a report with the material item's details and transactions.

  • View Detailed Inventory Register: Opens a report showing all the material item's inventory transactions.

Fields Explained: Price Update

The Price Update View provides a snapshot of pricing information and includes actions for users to adjust the prices.

• ID: The system-generated number allocated to the inventory item. There is a number for each location.

• 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 that should be on hand.

• Reorder Qty: The quantity that should be reordered.

• 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 typed in inventory price (based on the selected setting).

• Menu: 

  • Open linked Material Item: Opens the selected material item's setup page.

  • View Material Summary: Opens a report with the material item's details and transactions.

Fields Explained: Stock Check

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 (*): Allows users to add or remove columns from the Heading Section.

Detail

• Field Chooser (*): Allows users to 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. There is a number for each location.

• Desc: 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 that should be on hand.

• Reorder Qty: The inventory quantity that should be reordered.

Stock Check 

• Date: The date of the stock check transaction.

• Unit Price: The inventory item's unit price.

• System: The quantity of stock generated by the system (i.e. from transactions that involve inventory items).

• Counted: The quantity of stock that was counted.

• Variance: The difference between the System and Counted Inventory Quantities.

• Gain/Loss: The quantity of inventory that was gained or lost.

Menu (...)

• Open linked Material Item: Opens the selected material item's setup page.

• View Material Summary: Opens a report with the material item's details and transactions.

Fields Explained: Transactions

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 Chooser (*): Allows users to 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 item 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 (...)

  • Open transaction: Opens the inventory transaction.

  • Open linked Material Item: Opens the selected material item's setup page.

  • View Material Summary: Opens a report with the material item's details and transactions.

Fields Explained: Replenishment

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:

- Lead Days: The number of days from placing an order to receiving the goods.

- Min Qty: The minimum quantity that should be 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. 

The following rules determine if an item is on the Replenishment Report:

- The material is needed (i.e. there's 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 Chooser (*): Allows users to 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 that should be 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 (...):

  • Open the Last Order: Opens the last order that used this inventory item.

  • Open linked Material Item: Opens the selected material item's setup page.

  • View Material Summary: Opens a report with the material item's details and transactions.

  • View On Order Transactions: Opens the Material Summary open to the list of transactions with the On Order filter.

  • View Reserved Transactions: Opens the Material Summary open to the list of transactions with the Reserve filter.

Fields Explained: Detailed Register

The Detailed Register provides a detailed list of all Stock Movements for a specific item at a specific Location.

If a date is selected, an Opening Balance is shown unlike with No Date (all transactions).

• Field Chooser (*): Allows users to 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:

  • Open Transaction: Opens the selected transaction.

  • Open linked Material Item: Opens the selected material item's setup page.

  • View Material Summary: Opens a report with the material item's details and transactions.

  • View Stock Check:  Opens the stock check transaction.

Fields Explained: Inventory - Ageing

The Inventory Ageing View shows how long inventory has been kept in stock. It will pinpoint inventory that is just sitting on the shelves.

• Field Chooser (*): Allows users to 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 item'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:

  • 2+ years

  • 2 years

  • 1 year

  • 180 days

  • 120 days

  • 90 days

  • 60 days

  • 30 days

  • Total

• Menu (...)

  • Open linked Material Item: Opens the selected material item's setup page.

  • View Material Summary: Opens a report with the material item's details and transactions.

  • View Detailed Inventory Register: Opens a report showing all the material item's inventory transactions.

Fields Explained: Batch Tracking

• Field Chooser (*): Allows users to add or remove columns.

• MatUnitID: The system-generated number allocated to the material item.

• 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 item).

• 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: 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.