Search our Help.

BI KPIs with tooltips and calculations

Follow

Dashboard KPI

Tooltip

Implemented Formula 

A report in Unleashed to use for verifying values.

Inventory

     

Stock on Hand value

Stock quantities multiplied by the average landed purchased price per product.

Current Value = sum of (QuantityOnHand * averagelandedpriceaftertransaction) for all products in the dim_stocktransactions table where

  • QuantityOnHand = sum of current quantity per product across all warehouses
  • averagelandedpriceaftertransaction is from the most recent transaction per product.

Stock On Hand Enquiry: https://au.unleashedsoftware.com/v2/Enquiry/StockOnHandEnquiry

or

Stock Movement Enquiry.

Allocated Stock value

Value of stock allocated to Open sales orders, Assemblies or stock Transfers.

Current Value = sum of (AllocatedQuantity * latest averagelandedprice) for all products in the dim_stocktransactions  table where

  • AllocatedQuantity = sum of quantity in dim_warehouseproductallocations per product across all warehouses
  • averagelandedpriceaftertransaction is from the most recent transaction per product.

Product Allocations:

https://au.unleashedsoftware.com/v2/Enquiry/ProductAllocations

Available Stock

Value of stock available for sale (stock not linked to a sales order, assembly or transfer).

Current Value = sum of (AvailableQuantity * averagelandedpriceaftertransaction) for all products in the dim_stocktransactions  table where

  • AvailableQuantity = QuantityOnHand - AllocatedQuantity per product across all warehouses
  • averagelandedpriceaftertransaction is from the most recenttransaction per product

Could use Stock on Hand Enquiry and filter out allocated stock so that You only have available stock: https://au.unleashedsoftware.com/v2/Enquiry/StockOnHandEnquiry

Open purchase order cost

Cost of Open purchase orders (stock on order but not yet received).

Value = sum of bclandedcost in dim_purchaseorderlines table where

  • OrderStatus = 'Placed' in dim_purchaseorders

NOTE: Because this is a calculated value, using the status that will change over time, we are unable to calculate values for the past. Values will only be calculated and display from the date BI data was extracted.

View Purchases:

https://au.unleashedsoftware.com/v2/PurchaseOrder/List#status=Placed

Average Days to Sell

The average number of days required to sell all current stock on hand.

Value = days_in_period * average_value_of_stock / cost_of_goods_sold where

  • Days_in_period = 365 if the value of stock was > 0 one year ago. If the date of the earliest stock valuation is less than one year ago, days_in_period = days from the earliest date to the current date, with a minimum of 30 days
  • average_value_of_stock = (opening value of stock at start of period + current closing value of stock) / 2
  • cost_of_goods_sold = sum of abs(transvalue) for transactiontype = 'SalesInvoice'  - sum of transvalue for transactiontype = 'Credit' in dim_stocktransactions table for the period defined above

NOTE: This calculation is slightly different from the one used to display average days to sell on the classic dashboard.

Not available

Annual stock turns

 

How many times the Inventory is sold or used.

Value = cost_of_goods_sold / average_value_of_stock where

  • cost_of_goods_sold = sum of abs(transvalue) for transactiontype = 'SalesInvoice'  - sum of transvalue for transactiontype = 'Credit' in dim_stocktransactions table for the period
  • The period is the last 365 days if the value of stock was > 0 one year ago. If the date of the earliest stock valuation is less than one year ago, the period is from the earliest date to the current date, with a minimum of 30 days
  • average_value_of_stock = (opening value of stock at start of period + current closing value of stock) / 2

NOTE: This calculation is slightly different from the one used to display stock turn on the classic dashboard.

Not available

Cost of goods sold

Cost of goods sold calculation: Cost of inventory at the start of the period plus purchases minus cost of inventory at the end of the period.

Value = sum of abs(transvalue) for transactiontype = 'SalesInvoice'  - sum of transvalue for transactiontype = 'Credit' in dim_stocktransactions  table where

  • transactiontype in ('SalesInvoice', 'Credit') and
  • entrydate >= utc_datetime - '12 months' and
  • entrydate <  utc_datetime

Stock Movement Enquiry:

https://au.unleashedsoftware.com/v2/Enquiry/StockMovementEnquiry

Stock adjustments value

Total value of all stock adjustment transactions over the latest 365 days.

Value = sum of transvalue in dim_stocktransactions  table where

  • transactiontype = 'StockAdjustment' and
  • entrydate >= utc_datetime - '12 months' and
  • entrydate <  utc_datetime and status = 'Completed'

Reports - Inventory: Transaction Enquiry:

https://au.unleashedsoftware.com/v2/Enquiry/TransactionEnquiry

Stock adjustment count

Total count of stock adjustment transactions over the latest 365 days.

Value = count(distinct(transactionid)) in dim_stocktransactions  table where

  • transactiontype = 'StockAdjustment' and
  • entrydate >= utc_datetime - '12 months' and
  • entrydate <  utc_datetime and
  • status = 'Completed'

Reports - Inventory: Transaction Enquiry:

https://au.unleashedsoftware.com/v2/Enquiry/TransactionEnquiry

Back orders value

Backorders are a status of Sales Orders which are linked to purchase orders where products were out of stock at the time of the sale. Timeline chart will show summary per day linked to creation date of the SO.

Value = sum of bclandedcost in dim_purchaseorderlines table where

  • OrderStatus = 'Backordered' in dim_salesorders

Sales report - Backorder Enquiry: https://au.unleashedsoftware.com/v2/BackorderEnquiry

Sales

 

 

 

Sales Revenue

Sales Revenue from completed invoices, including charges minus credits.

Value = sum of bclinetotal in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)
  • bclinetotal = round(InvoiceQuantity * bcunitprice, 2)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Note: This Invoice Enquiry report however excludes order charges which BI includes.

Gross Profit

Gross Profit from completed sales invoices = Sales Revenue minus Cost of Goods Sold.

Value = sum of bclinetotal - sum of bclinecost in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)
  • bclinetotal = round(InvoiceQuantity * bcunitprice, 2)
  • bclinecost = (InvoiceQuantity - ShipmentQuantity) * latestaveragecost

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Add the hidden Profit field to the results. 

Note: This Invoice Enquiry report however excludes order charges which BI includes.

Gross Profit %

Gross Profit % from completed sales invoices = Gross Profit expressed as a % of Sales Revenue.

Value = 100 * sum of gross profit / sum of abs(bclinetotal) in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Note: This Invoice Enquiry report however excludes order charges which BI includes.

Sales Orders

Count of sales orders from completed invoices.

Value = count of distinct ordernumber in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype = 'Invoice'

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Use the item count displayed at the bottom center of the results page.

Average Revenue per Order

Average Sales Revenue per sales order from completed invoices, including charges minus credits.

Value = sum of bclinetotal / order count in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Export to Excel where you can calculate the average value per SO

 

Units Sold

The number of units sold/invoiced minus units credited and returned for the period selected. 

Value = sum of quantity in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype in ('Invoice', ‘Credit’) and if ‘Credit’ the products must be returned to stock
Not available

Customers 

 

 

 

Total Customers

Count of all current customers at the end of the period selected (excluding obsolete customers).

Value = count of IDs in dim_customers table where

  • customer is not obsolete

View Customers

https://au.unleashedsoftware.com/v2/Customer/List#ObsoleteStatus=No

New Customers

Count of new customers created in the period selected.

Value = count of IDs in dim_customers table where

  • customer is not obsolete
  • createdon >= period UTC start date

N/A

Active Customers

Count of customers placing sales orders in the period selected.

Value = count of distinct customerid in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype = 'Invoice'

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Group by Customer and count them for the period.

Average Orders per Customer

Average Order count per active Customer from completed sales invoices.

Value = order count / active customer count in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Export to Excel and calculate average orders per Customer.

Average Revenue per Customer

Average Revenue per active Customer from completed sales invoices.

Value = sum of bclinetotal / active customer count in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Export to Excel and calculate average Revenue per Customer.

Average Gross Profit per Customer

Average Gross Profit per active Customer from completed sales invoices.

Value = (sum of bclinetotal - sum of bclinecost) / active customer count in fact_salesinvoice table where

  • invoicestatus = ‘Completed’ and
  • invoicedate >= period start date and
  • invoicedate < period end datetime (to the current minute for any-to-date periods) and
  • reporttype in ('Invoice', ‘Credit’, ‘CreditCharge’, ‘OrderCharge’)

For the period use the Sales Invoice Enquiry report: https://au.unleashedsoftware.com/v2/Enquiry/InvoiceEnquiry

Transaction date should be Invoice Date and tick Include Credits on and status = Completed

Add hidden Profit column to the output.

Export to Excel and calculate average Gross Profit per Customer.

Purchases 

     
Created PO value

Total value (including costs) and count of Purchase Orders created for the period selected.

Value = sum of bclandedcost

Count = count of distinct ordernumber

Average = Value/Count (BI Vision Only)

Excluding purchase orders with status = "Deleted"

Using the Purchase Enquiry report https://au.unleashedsoftware.com/v2/Enquiry/PurchaseEnquiry, select your period, Transaction date = Order Date and Order Status = All to extract all purchased on order dates.

Open PO value

Total value (including costs) and count of all Purchase Orders with Open status since BI was last refreshed.

Value = sum of bclandedcost

Count = count of distinct ordernumber

Average = Value / Count (BI Vision Only)

in fact_purchaseorder table where

  • orderstatus IN (‘Costed’, ‘Parked’, ‘Placed’, ‘Receipted’, ‘Unapproved’)

Using View Purchased https://au.unleashedsoftware.com/v2/PurchaseOrder/List?orderStatus=Open with statue = Open to extract all open purchase orders.

Completed PO value

Total value (including costs) and count of all Purchase Order transactions with a Completed status for the period selected.

Value = sum of bclandedcost

Count = count of distinct ordernumber

Average = Value / Count (BI Vision Only)

in fact_purchaseorder table where

  • orderstatus = ‘Complete’ and
  • completeddate >= period start date (UTC) and
  • completeddate < period end datetime (UTC) (to the current minute for any-to-date periods)

Using the Purchase Enquiry report https://au.unleashedsoftware.com/v2/Enquiry/PurchaseEnquiry, select your period, Transaction date = Receipt Date and Order Status = Complete to extract all purchased on Receipted dates. (Note: This report will be updated in the future to allow you to select a completed date in the future.) 

Order cost

The value of additional supplier costing for all completed Purchase Orders. This cost is calculated as a percentage of all completed orders for the selected period.

Costs = sum of bccosttotal

Ratio = 100 * sum of bccosttotal / sum of bcsubtotal

Average = Costs / count of distinct ordernumber (BI Vision Only)

in fact_purchaseorder table where

  • orderstatus = ‘Complete’ and
  • completeddate >= period start date (UTC) and
  • completeddate < period end datetime (UTC) (to the current minute for any-to-date periods)

No report available - Could use View Purchases and add hidden column Costing Sub Total field to the view. 
https://au.unleashedsoftware.com/v2/PurchaseOrder/List#status=Complete

Average delivery duration

For the period selected, this is the average time between when a Purchase Order was placed and when it was receipted. We also calculate it against the required date to determine the average days late.

Value = sum of date_part('day', coalesce(dpo.receiveddate, dpo.completeddate) - dpo.orderdate) / count of distinct ordernumber

Avg Days late = sum(CASE WHEN purchaseorderlinenumber > 1 THEN 0 WHEN requireddate IS NULL THEN 0 ELSE date_part('day', receiveddate - requireddate) END) / count(DISTINCT CASE WHEN fpo.requireddate IS NULL THEN NULL ELSE fpo.ordernumber END)

in fact_purchaseorder table where

  • orderstatus IN (‘Complete’, ‘Receipted’) and
  • receiveddate >= period start date (UTC) and
  • receiveddate < period end datetime (UTC) (to the current minute for any-to-date periods)

No report available

Supplier return value

For the period selected, this is the value and count of stock returned to suppliers due to defects or other related reasons.

Value = sum of (dsrl.returnquantity * dsrl.returnprice) / dsr.exchangerate in dim_supplierreturnlines (dsrl)

Count = count of distinct supplierreturnnumber

Average = Value / Count (BI Vision Only)

in dim_supplierreturns (dsr) table where

  • status = ‘Completed’ and
  • returndate >= period start date and
  • returndate < period end datetime (to the current minute for any-to-date periods)

View Supplier Returns on Status = Completed
https://au.unleashedsoftware.com/v2/SupplierReturn/List#status=Completed

Production

     

Total Assembly Cost

The total cost of all components used plus additional supplier costs during assembly for the period selected.

Value = sum(coalesce(unitprice,0) * (coalesce(quantity,0) + coalesce(wastagequantity,0)) + coalesce(suppliercost,0))

from fact_assembly table where

  • assemblytype  = 'Assembly' and
  • assemblystatus = ‘Completed’ and
  • assemblylevel IN ('Component','SupplierCost')
  • assemblydate >= period start date and
  • assemblydate < period end datetime (to the current minute for any-to-date periods)

Production Enquiry report, Value of Production Type = “Assembly In”: https://au.unleashedsoftware.com/v2/Production/ProductionEnquiry

Completed Assemblies

The total number of assemblies completed for the period selected.

Count = count of distinct assemblyid

from fact_assembly table where

  • assemblytype  = 'Assembly' and
  • assemblystatus = ‘Completed’ and
  • assemblylevel = 'Assembly'
  • assemblydate >= period start date and
  • assemblydate < period end datetime (to the current minute for any-to-date periods)

Production Enquiry report, number of Production Type = “Assembly In”: https://au.unleashedsoftware.com/v2/Production/ProductionEnquiry

Avg. Cost per Assembly

The Total cost of all completed assemblies divided by the number of assemblies completed during the period selected.

Avg. Cost = Total Assembly Cost / count of distinct assemblyid

from fact_assembly table with conditions above.

Manual calculation using above 2 results

On-time Assembly

The average days between the date Assemble By and the Assembly Date indicating if assemblies were on average Early, On-time, or Late.

Value = sum(extract(day from (assemblydate - assembleby)))::numeric / count of distinct assemblyid

from fact_assembly table with conditions above.

Not available - manual calculation.

Wastage as % of Assemblies

Cost of wastage as a % of the total cost of assemblies completed in the period selected.

Value = 100 * Cost of Wastage / Total Assembly Cost

from fact_assembly table with conditions above.

Not available - manual calculation.

Units Produced

The total number of units produced for the period selected.

Value = sum(coalesce(quantity,0)) 

from fact_assembly table with conditions above.

Production Enquiry report, Value of Production Type = “Assembly In” Qty column: https://au.unleashedsoftware.com/v2/Production/ProductionEnquiry

Avg. Cost per Unit

The total assembly cost divided by the number of units produced.

Value = Total Assembly Cost / Units produced

from fact_assembly table with conditions above.

Not available - manual calculation.

Cost of Wastage

Total wastage captured when completing assemblies for the period selected.

Value = sum(coalesce(unitprice,0) * coalesce(wastagequantity,0))

from fact_assembly table where

  • assemblytype  = 'Assembly' and
  • assemblystatus = ‘Completed’ and
  • assemblylevel = 'Component'
  • assemblydate >= period start date and
  • assemblydate < period end datetime (to the current minute for any-to-date periods)

Not available - manual calculation.

Additional Supplier Costs

Sum of any additional supplier costs added to assemblies completed for the period selected.

Value = sum(coalesce(suppliercost,0))

from fact_assembly table where

  • assemblytype  = 'Assembly' and
  • assemblystatus = 'Completed' and
  • assemblylevel = 'SupplierCost'
  • assemblydate >= period start date and
  • assemblydate < period end datetime (to the current minute for any-to-date periods)
Not available - manual calculation.

Total Net Assembly Cost

The total cost of Completed Assemblies minus Additional Supplier Costs for the period selected.

Value = sum(coalesce(unitprice,0) * (coalesce(quantity,0) + coalesce(wastagequantity,0)))

from fact_assembly table where

  • assemblytype  = 'Assembly' and
  • assemblystatus = ‘Completed’ and
  • assemblylevel = 'Component'
  • assemblydate >= period start date and
  • assemblydate < period end datetime (to the current minute for any-to-date periods)

Not available - manual calculation.


 

Was this article helpful?
0 out of 0 found this helpful