Plasma KPI Dashboards

1

All

All

Source: Bain provided Plasma_KPI data source has all precomputed fields. Also month empty indicates it is for that complete respective year.

DataSize: Around 100,000 records with different KPI data

Source: Plasma_Output_Transactions : Data is distributed by Type of deliverable, Year, Quarter, Month, Selling Region (origin and destination).

DataSize: Around 1000 rows

Plasma_KPI → For reading data from plasma side.

Plasma_Output_Transactions → For consumer data corresponding to equivalent Plasma KPI and waterfall.

Plasma_Output_Quotes → For consumer quote related data corresponding to quotes.

Plasma_Price_Waterfall_KPI → For KPI data corresponding to the waterfall.

For all percentage calculation for Quartile charts and Price Increase Realization Waterfall chart the percentage value is capped between 0 and 100%. Note that Price Increase Realization Waterfall difference value can continue to be less than zero.

2

Executive Summary

Filters

Industry, Industry Vertical Sector and Level 3, Date

Data will be fetched from customer computed summary i.e. Plasma_Output_Transactions or Plasma_Output_Quotes matching the date and the value for the corresponding graph will be supplied.

Final Input implemented:

Business Unit: For customers who has data distributed across various business unit this will be dynamically appear. For all other customers who has single unit this field will not be visible in input filters. It picks up unique values from Plasma_Output_Quotes and Plasma_Output_Transactions. This field has no corresponding field to select values from Plasma_KPI i.e. Bain data, so it will not effect information from Bain source.

Industry

Region Selling To

Waterfall chart details:

Whether to show or hide zero price points. Region Selling To is populated based on selected Industry.

3

Executive Summary/ Insights

Percent of accounts with negative margin, calculated with revenue

Plasma_KPI: PERCENT_ACCOUNTS_NEGATIVE_MARGIN_REVENUE

Plasma_Output_Transactions: SUM (Revenue Account Negative Margin) / SUM (Total Revenue)

If the output value is negative then it will be shown as zero value.

4

Executive Summary/ Insights

Percent of transactions with negative margin, calculated with revenue

Plasma_KPI: PERCENT_TRANSACTIONS_NEGATIVE_MARGIN_REVENUE

Plasma_Output_Transactions: SUM (Revenue Transaction Negative Margin) / SUM (Total Revenue)

If the output value is negative then it will be shown as zero value.

5

Executive Summary/Manage

TTM per unit realized price percent increase

Plasma_KPI: TTM_PER_UNIT_REALIZED_PRICE_INCREASE

Plasma_Output_Transactions = SUM(Annual_Percent_Change_Per_Unit_Realized_Price) / Count(UniqueID)

Count(UnitqueID) → Is for computation of simple average.

Note: It was discussed with Bain and the behavior change is approved by latest requirement.

6

Executive Summary/ Realize

Percent of deals outside guidelines, calculated with number of deals

Plasma_KPI: PERCENT_OUTSIDE_GUIDELINES_NUMBER

Plasma_Output_Quotes: SUM(Number_Outside_Guidance) / SUM(Total_Number_Quotes)

7

Executive Summary/ Realize

Deal velocity in days (average time taken to approve a quote)

Plasma_KPI: APPROVAL_PROCESS_DEAL_VELOCITY_SIMPLE_AVG

Plasma_Output_Quotes:

In plasma output quotes the data is available in hours whereas data from Bain is in days.
So we need to convert the value found by doing the below into days i.e. divide the result of below formula by 24 as 1 day contains 24 hours.

SUM(Simple_Avg_Approval_Process_Deal_Velocity)/Count(UnitqueID)

Count(UnitqueID) → Is for computation of simple average.

8

Executive Summary/ Realize

Average number of steps in approval process (simple average)

Plasma_KPI: AVERAGE_NUMBER_APPROVAL_PROCESS_STEPS_SIMPLE_AVG

Plasma_Output_Quotes:

SUM(Weighted_Avg_Number_Approval_Process_Steps)/ Count(UnitqueID)

Count(UnitqueID) → Is for computation of simple average.

9

Executive Summary/ Insights

Comparison Waterfall

Data is picked from “Plasma_KPI” datamart. All rows with KPI_NAME = ‘PRICE_WATERFALL’ are used.

The KPI_DISPLAY_NAME currently used are

'Global List Price',
'Foreign Exchange Adjustments',
'Local Regional Adjustments',
'Local List Price',
'Volume Discounts',
'Sales Channel Discounts',
'Seasonal Discounts',
'Customer Negotiated Discounts',
'Free Giveaways',
'Competitive Discounts',
'Instant Rebates',
'End Customer Discounts On Invoice',
'Customer Pick Up Allowance',
'Other On Invoice Promos',
'Customer Charge Freight',
'Customer Charge Customization',
'Customer Charge Installation',
'Invoice Price',
'Stocking Allowance',
'Early Payment Discounts',
'Quick Cash Payment Discounts',
'Rebates',
'Marketing Market Development Promos',
'Rewards Customer Loyalty Program',
'Volume Bonus',
'Slotting Allowance',
'Trade Spend',
'End Customer Discounts Off Invoice',
'Other Off Invoice Discounts',
'Net Price',
'Non-Standard Orders Customization Costs',
'Consignment Cost',
'Shrinkage Returns Corrections',
'Freight',
'Rush Orders',
'Free Expedited Shipping',
'Inventory Maintenance',
'Receivables Carrying Late Payment Costs',
'Installation Costs',
'Support and Service Costs',
'Performance Guarantees Basic Terms Warranty',
'Other Transaction Costs',
'Realized Price',
'Raw Materials',
'Production',
'Labor',
'Other COGs',
'Total Margin'

Mean value is used in the chart.

Following columns from “Plasma_Output_Transactions” datamart are used for build the chart.

'Sum_Global_List_Price',
GROUP : 'Local_Adjustments',
'Sum_Foreign_Exchange_Adjustments',
'Sum_Local_Regional_Adjustments',
'Sum_Local_List_Price',
GROUP : 'On-Invoice_Discounts',
'Sum_Volume_Discounts',
'Sum_Sales_Channel_Discounts',
'Sum_Seasonal_Discounts',
'Sum_Customer_Negotiated_Discounts',
'Sum_Free_Giveaways',
'Sum_Competitive_Discounts',
'Sum_Instant_Rebates',
'Sum_End_Customer_Discounts_On_Invoice',
'Sum_Customer_Pick_Up_Allowance',
'Sum_Other_On_Invoice_Promos',
GROUP : 'Up_Charges',
'Sum_Customer_Charge_Freight',
'Sum_Customer_Charge_Customization',
'Sum_Customer_Charge_Installation',
'Sum_Invoice_Price',
GROUP : 'Off-Invoice_Discounts',
'Sum_Stocking_Allowance',
'Sum_Early_Payment_Discounts',
'Sum_Quick_Cash_Payment_Discounts',
'Sum_Rebates',
'Sum_Marketing_Market_Development_Promos',
'Sum_Rewards_Customer_Loyalty_Program',
'Sum_Volume_Bonus',
'Sum_Slotting_Allowance',
'Sum_Trade_Spend',
'Sum_End_Customer_Discounts_Off_Invoice',
'Sum_Other_Off_Invoice_Discounts',
'Sum_Net_Price',
GROUP : 'Transaction_Costs',
'Sum_Nonstandard_Orders_Customization_Costs',
'Sum_Consignment_Cost',
'Sum_Shrinkage_Returns_Corrections',
'Sum_Freight',
'Sum_Rush_Orders',
'Sum_Free_Expedited_Shipping',
'Sum_Inventory_Maintenance',
'Sum_Receivables_Carrying_Late_Payment_Costs',
'Sum_Installation_Costs',
'Sum_Support_and_Service_Costs',
'Sum_Performance_Guarantees_Basic_Terms_Warranty',
'Sum_Other_Transaction_Costs',
'Sum_Realized_Price',
GROUP : 'Cost_of_Goods_Sold',
'Sum_Raw_Materials',
'Sum_Production',
'Sum_Labor',
'Sum_Other_COGs',
'Total_Margin'

Plasma_KPI Data : Data is picked from “Plasma_KPI” datamart. All rows with KPI_NAME = ‘PRICE_WATERFALL’ are used.

KPI_Display_NAME =

Global List Price
Foreign Exchange Adjustments
Local Regional Adjustments
Local List Price
Volume Discounts
Sales Channel Discounts
Seasonal Discounts
Customer Negotiated Discounts
Free Giveaways
Competitive Discounts
Instant Rebates
End Customer Discounts On Invoice
Customer Pick Up Allowance
Other On Invoice Promos
Customer Charge Freight
Customer Charge Customization
Customer Charge Installation
Invoice Price
Stocking Allowance
Early Payment Discounts
Quick Cash Payment Discounts
Rebates
Marketing Market Development Promos
Rewards Customer Loyalty Program
Volume Bonus

Slotting Allowance
Trade Spend
End Customer Discounts Off Invoice
Other Off Invoice Discounts
Net Price
Nonstandard Orders Customization Costs
Consignment Cost
Shrinkage Returns Corrections
Freight
Rush Orders
Free Expedited Shipping
Inventory Maintenance
Receivables Carrying Late Payment Costs
Installation Costs
Support and Service Costs
Performance Guarantees Basic Terms Warranty
Other Transaction Costs
Realized Price
Raw Materials
Production
Labor
Other COGs
Total Margin

Company Data : Columns from “Plasma_Output_Transactions” datamart is used.

Following chart modes are supported

1. percentage : Value of Sum_Global_List_Price is considered as the percent basis. Price points grouped (highlighted in red) and drill down option available.

2. percentage_detail : Value of Sum_Global_List_Price is considered as the percent basis. All price points and adjustments are displayed and calculated

Data from Bain is already computed considering ‘Global List Price’ as the start price point.

10

Executive Summary

Percentage of Product with negative margin displayed per month

1. The values are directly fetched from Plasma_KPI data source i.e. for matching criteria where KPI name = PERCENT_PRODUCTS_NEGATIVE_MARGIN_NUMBER for matching Industry, Industry Vertical Sector, level 3, Selling from and Selling To for Month and Year for last 12 months. If month is not selected then it will be for Jan-Dec for that month

The percent of products with negative margin (a) SUM (Number Product Negative Margin) / SUM (Total Product This Month) OR

(b) SUM (Number Product Negative Margin) / (SUM(Number Product Negative Margin + Number Product Positive Margin)) OR

(c) SUM (Revenue Product Negative Margin) / SUM (Total Revenue)

If month and year is selected then it will show the value starting from that month and year, for last 12 months. If user does not select month but year would be always selected so it will always show data for last 12 months i.e. from Jan-Dec provided its not for current year. For current year it will fetch data for the last 12 months starting from current month. Also if user has selected selling From and Selling To region these will be part of filter.

15. Confirm the formula among (a), (b) or (c)

SC: (a)

This chart has not been included yet.
Here is the Jira ticket in the backlog :
https://pricefx.atlassian.net/browse/PFPLASMA-95

Plasma_KPI:PERCENT_PRODUCTS_NEGATIVE_MARGIN_REVENUE for trailing twelve months

Plasma_Output_Transactions : SUM(Number_Product_Negative_Margin)/
SUM(Total_Products_This_Month) for trailing twelve Months

Feedback from Billy: <Convert this to QuartileDashboard with drilldown for month for same KPI>

11

Insights

Total Revenue

Plasma_KPI: Sales.

Plasma_Output_Transactions:

SUM(Total_Revenue)

Note that both Bain and Customer data are converted to Million with normal rounding to 1 decimal digit after million conversion.

12

Insights

Percentage of customers to reach revenue deciles

Plasma_KPI: PERCENT_CUSTOMERS_10_REVENUE
PERCENT_CUSTOMERS_20_REVENUE
PERCENT_CUSTOMERS_30_REVENUE
PERCENT_CUSTOMERS_40_REVENUE
PERCENT_CUSTOMERS_50_REVENUE
PERCENT_CUSTOMERS_60_REVENUE
PERCENT_CUSTOMERS_70_REVENUE
PERCENT_CUSTOMERS_80_REVENUE
PERCENT_CUSTOMERS_90_REVENUE
PERCENT_CUSTOMERS_100_REVENUE

Reading the median value for each of the KPIs.

Plasma_Output_Transactions:

SUM(Number_Customers_10_Revenue_Decile)/ SUM(Total_Accounts_This_Month_Region)

SUM(Number_Customers_20_Revenue_Decile)/ SUM(Total_Accounts_This_Month_Region)

SUM(Number_Customers_30_Revenue_Decile)/ SUM(Total_Accounts_This_Month_Region)

SUM(Number_Customers_40_Revenue_Decile)/ SUM(Total_Accounts_This_Month_Region)

SUM(Number_Customers_50_Revenue_Decile)/ SUM(Total_Accounts_This_Month_Region)

SUM(Number_Customers_60_Revenue_Decile)/ SUM(Total_Accounts_This_Month_Region)

SUM(Number_Customers_70_Revenue_Decile)/ SUM(Total_Accounts_This_Month_Region)

SUM(Number_Customers_80_Revenue_Decile)/ SUM(Total_Accounts_This_Month_Region)

SUM(Number_Customers_90_Revenue_Decile)/SUM(Total_Accounts_This_Month_Region)

SUM(Total_Accounts_This_Month_Region)/SUM(Total_Accounts_This_Month_Region)

13

Insights

Percentage of Products to reach revenue decile

Plasma_KPI:

PERCENT_PRODUCTS_10_REVENUE
PERCENT_PRODUCTS_20_REVENUE
PERCENT_PRODUCTS_30_REVENUE
PERCENT_PRODUCTS_40_REVENUE
PERCENT_PRODUCTS_50_REVENUE
PERCENT_PRODUCTS_60_REVENUE
PERCENT_PRODUCTS_70_REVENUE
PERCENT_PRODUCTS_80_REVENUE
PERCENT_PRODUCTS_90_REVENUE
PERCENT_PRODUCTS_100_REVENUE

Reading the median value for each of the KPIs.

Plasma_Output_Transactions:

SUM(Number_Products_10_Revenue_Decile)/SUM(Total_Products_This_Month_Region)
SUM(Number_Products_20_Revenue_Decile)/SUM(Total_Products_This_Month_Region)
SUM(Number_Products_30_Revenue_Decile)/SUM(Total_Products_This_Month_Region)
SUM(Number_Products_40_Revenue_Decile)/SUM(Total_Products_This_Month_Region)
SUM(Number_Products_50_Revenue_Decile)/SUM(Total_Products_This_Month_Region)
SUM(Number_Products_60_Revenue_Decile)/SUM(Total_Products_This_Month_Region)
SUM(Number_Products_70_Revenue_Decile)/SUM(Total_Products_This_Month_Region)
SUM(Number_Products_80_Revenue_Decile)/SUM(Total_Products_This_Month_Region)
SUM(Number_Products_90_Revenue_Decile)/SUM(Total_Products_This_Month_Region)
SUM(Total_Products_This_Month_Region)/SUM(Total_Products_This_Month_Region)

14

Insights

Company Waterfall

No Bain data is used for this chart

Following columns from “Plasma_Output_Transactions” datamarts are used for build the chart.

'Sum_Global_List_Price',
GROUP : 'Local_Adjustments',
'Sum_Foreign_Exchange_Adjustments',
'Sum_Local_Regional_Adjustments',
'Sum_Local_List_Price',
GROUP : 'On-Invoice_Discounts',
'Sum_Volume_Discounts',
'Sum_Sales_Channel_Discounts',
'Sum_Seasonal_Discounts',
'Sum_Customer_Negotiated_Discounts',
'Sum_Free_Giveaways',
'Sum_Competitive_Discounts',
'Sum_Instant_Rebates',
'Sum_End_Customer_Discounts_On_Invoice',
'Sum_Customer_Pick_Up_Allowance',
'Sum_Other_On_Invoice_Promos',
GROUP : 'Up_Charges',
'Sum_Customer_Charge_Freight',
'Sum_Customer_Charge_Customization',
'Sum_Customer_Charge_Installation',
'Sum_Invoice_Price',
GROUP : 'Off-Invoice_Discounts',
'Sum_Stocking_Allowance',
'Sum_Early_Payment_Discounts',
'Sum_Quick_Cash_Payment_Discounts',
'Sum_Rebates',
'Sum_Marketing_Market_Development_Promos',
'Sum_Rewards_Customer_Loyalty_Program',
'Sum_Volume_Bonus',
'Sum_Slotting_Allowance',
'Sum_Trade_Spend',
'Sum_End_Customer_Discounts_Off_Invoice',
'Sum_Other_Off_Invoice_Discounts',
'Sum_Net_Price',
GROUP : 'Transaction_Costs',
'Sum_Nonstandard_Orders_Customization_Costs',
'Sum_Consignment_Cost',
'Sum_Shrinkage_Returns_Corrections',
'Sum_Freight',
'Sum_Rush_Orders',
'Sum_Free_Expedited_Shipping',
'Sum_Inventory_Maintenance',
'Sum_Receivables_Carrying_Late_Payment_Costs',
'Sum_Installation_Costs',
'Sum_Support_and_Service_Costs',
'Sum_Performance_Guarantees_Basic_Terms_Warranty',
'Sum_Other_Transaction_Costs',
'Sum_Realized_Price',
GROUP : 'Cost_of_Goods_Sold',
'Sum_Raw_Materials',
'Sum_Production',
'Sum_Labor',
'Sum_Other_COGs',
'Total_Margin'

Note that there is no KPI associated in this chart as its done purely on customer data. Following chart modes are supported

1. absolute : Absolute values are displayed. Pricepoints grouped (highlighed in red) and drilldown option available.

2. percentage : Value of Sum_Global_List_Price is considered as the percent basis. Pricepoints grouped (highlighed in red) and drilldown option available.

3. detail : Absolute values are displayed in the waterfall with all pricepoints and adjustments displayed.

4. percentage_detail : Value of Sum_Global_List_Price is considered as the percent basis. All pricepoints and adjustments are displayed.

15

Insights/ Manage

Number of products TTM

Plasma_KPI:

NUMBER_PRODUCTS_TTM

Plasma_Output_Transactions :

For latest data display, from last completed month take TTM using formula. SUM(Total_Products_Available_Annual)/ COUNT(UniqueId) for the latest month. e.g. if latest month for which some data is available for Plasma_Output_Transactions is Feb-2021 then this picks value of that month for calculation.

In drilldown use the same formula per respective month.

16

Insights

Number of accounts TTM

Plasma_KPI:

NUMBER_ACCOUNTS_TTM

Plasma_Output_Transactions :

For latest data display, from last completed month take TTM using formula. SUM(Total_Accounts_Available_Annual)/ COUNT(UniqueId) for the latest month. e.g. if latest month for which some data is available for Plasma_Output_Transactions is Feb-2021 then this picks value of that month for calculation.

In drilldown use the same formula per respective month.

17

Insights

Number of sales people TTM

Plasma_KPI:

NUMBER_SALES_PEOPLE_HC_TTM

Plasma_Output_Transactions :

For latest data display, from last completed month take TTM using formula. SUM(Total_Sales_HC_Available_Annual)/ COUNT(UniqueId) for the latest month. e.g. if latest month for which some data is available for Plasma_Output_Transactions is Feb-2021 then this picks value of that month for calculation.

In drilldown use the same formula per respective month.

18

Manage

Price Increase Realization Waterfall

Plasma_KPI:

KPI Name:TTM_PER_UNIT_LOCAL_LIST_PRICE_INCREASE

• For gap between List Price and Invoice Price use following label : On-Invoice Discounts

KPI Name:TTM_PER_UNIT_INVOICE_PRICE_INCREASE

• For gap between Invoice Price and Net Price use following label : Off-Invoice Discounts

KPI Name:TTM_PER_UNIT_NET_PRICE_INCREASE

• For gap between Net Price and Realized Price use following label : Transaction costs

KPI Name:TTM_PER_UNIT_REALIZED_PRICE_INCREASE

Plasma_Output_Transaction:

• Annual_Size_Change_Per_Unit_List_Price
  For gap between List Price and Invoice Price use following label : On-Invoice Discounts

• Annual_Size_Change_Per_Unit_Invoice_Price
  For gap between Invoice Price and Net Price use following label : Off-Invoice Discounts

• Annual_Size_Change_Per_Unit_Net_Price
  For gap between Net Price and Realized Price use following label : Transaction costs

• Annual_Size_Change_Per_Unit_Realized_Price

An additional behavior is that if TTM_Size_Change_Per_Unit_List_Price value is less than 0.001 then the company data is not displayed instead only values from Bain reference would be displayed.

19

Manage

TTM Number of List Price Changes per product

Plasma_KPI:: TTM_NUMBER_LIST_PRICE_CHANGES_PER_PRODUCT

Plasma_Output_Transactions:

For latest data display, from last completed month take TTM using formula. SUM(Number_Local_List_Price_Change) of lastest twelve months / Total_Products_Annual of latest month

The drilldown is disabled for this chart.

20

Manage​

TTM Number of List Price Increases per product

Plasma_KPI: TTM_NUMBER_LIST_PRICE_UPLIFTS_PER_PRODUCT

Plasma_Output_Transactions:

For latest data display, from last completed month take TTM using formula.

SUM(Number_Local_List_Price_Uplift) of lastest twelve months / Total_Products_Annual of latest month

The drilldown is disabled for this chart.

21

Realize

Number of deals

Quartile Chart, for latest data display, from last completed month take TTM using formula.

In drilldown use the same formula per respective month.

Plasma_KPI: NUMBER_DEALS

Plasma_Output_Quotes: For TTM, SUM(Total_Number_Quotes)/Count(UniqueID) more like simple average.

22

Realize

Average deal size

Plasma_KPI: AVERAGE_DEAL_SIZE

Plasma_Output_Quotes: For TTM SUM(Sum_Revenue_Approved) / SUM(Number_Approved)

23

Realize

Deal velocity for all deals (simple average)

Plasma_KPI: OVERALL_DEAL_VELOCITY_SIMPLE_AVG

Plasma_Output_Quotes: For TTM SUM(Simple_Avg_Overall_Deal_Velocity) / Count(UnitqueID). Convert into days the value by dividing it by 24.

24

Realize

Percent of deals needing approval, calculated with number of deals

Plasma_KPI: PERCENT_NEEDING_APPROVAL_NUMBER

Plasma_Output_Quotes: SUM(Number_Need_Approval)/ SUM(Total_Number_Quotes)