CHEM06: Improve profitability due to better cost recovery via cost-to-serve analytics KPIs

Pricing Analyst/Manager (primary) / Product or Finance Manager (secondary)

Business Objective:

Chemicals companies closely track their customer cost-to-serve (CTS).  The core elements of this are freight, warehouse, packaging, and services.  If a customer requires value-added services (e.g. special packaging) they need to pay for this extra cost (also called cost recovery).  These costs change over time, but they are not always actively managed.  Pricing teams will look to decrease margin compression by actively seeking out underperformance in cost-to-serve elements across their portfolio.

·       Availability of cost-to-serve data at a customer/transaction level

·       Visibility of cost data (e.g. data table, comparative waterfall, or heatmap)

·       Visibility at a ship-to level (e.g. freight cost understanding requires ship-to detail)

·       Timing of receipt of cost data so it is accurate and allocated correctly

·       Ability to move to action immediately once issue identified

Dashboard

o   Data table with customer level ship-to CTS details (outliers / trending)

o   Time series at individual customer ship to and product category level (trending)

o   Comparative waterfall (showing average to single customer)

o   Heat map (showing CTS variation by product group, region, market…)

o   Pie chart – negative margin impact of each CTS category

·       Reduce margin compression over time with out-of-date CTS adjustments/costs

·       Increase margin by identifying underperformance / action on high CTS customers

·       Establish norms for cost recovery for future price setting processes

·       Cost recovery (total, by CTS category, by product/customer/region category

·       Cost recovery = CTS adjustments – CTS costs

Cost recovery (freight, packaging, services) by product category and region

• Ability to get a dashboard where up-to-date predefined cost-to-serve metrics are collected

• Ability to switch level of granularity of the reporting using filters

o    The dashboard has the following filter options:

Ability to select a time period

Ability to filter on some Customer attributes, including Ship-To

Ability to filter on some Product attributes

Ability to select a currency for the values displayed in the portlets

Ability to select an aggregation metric that will show Margin Impact and Recovery Data (in the table portlet) breakdowns by the selected attribute

o   The dashboard has 4 portlets

• The portlets are showing the following metrics:

Margin Impact: Occurring recovery completeness and recovery opportunities expressed in margin absolute value

Recovery Heat Map: Cost recovery expressed in percentage (by Country v/s Product Line)

Recovery Trend: Recovery evolution trends over time (month, with potential to change this period type) per cost-to-serve category

• Recovery Data Table: Recovery status overview per cost-to-serve category with Total Cost, Total Charge (to customer) and Recovery % for each cost-to-serve metric. A “health indicator” will also show any recovery % metric that falls into certain % thresholds in three traffic light (green, yellow, red) tiers.

Ability to download portlets in various formats (PNG, PDF and more)

Non-functional requirements

• Automatically updated metrics in the dashboard

These are not included in the baseline estimate. This use case has the dashboards and reports as described in the functional requirements section. You can check them out, here.

• Total Charge (per Cost-to-serve category): Is considered as “charge” the part of the cost-to-serve value that has been invoiced (charged) to the customer.

• Total Cost (per Cost-to-serve category): Is considered as “cost” the part of the cost-to-serve value that is supported by the company.

• Cost recovery percentage: (Sum Total Charge / Sum Total Cost) * 100%

• Margin impact = Total Charge – Total cost. If delta >0, the margin impact is positive, else it is a margin lost. (See additional details in the user story)

During scope validation process we are ensuring that the project deliverables are completed according to the agreed scope and quality standard by asking the following questions:

As a Pricing Manager/ Analyst I want to access transactional and historical data so I can use it throughout the application.

User Story Name - Costs data

I want to: Have access to Costs Data

so I can: Use them throughout the application and especially for my cost-to-serve analysis dashboard

Acceptance Criteria:

• Costs data are available in the transactions data set 

Already covered by CHEM00, a prerequisite for all CHEM Use cases, and thus for CHEM06.

User Story Name - Customers Data (and Ship-to data)

I want to: Have access to Customers Data (and Ship-to data)

so I can: Use them throughout the application and especially for my cost-to-serve analysis dashboard

Acceptance Criteria: Customers data and Ship-to are available in the transactions data set

Already covered by CHEM00, a prerequisite for all CHEM Use cases, and thus for CHEM06.

User Story Name - Historical Transactions data

I want to: Have access to Historical Transactions data

so I can: Use them throughout the application and especially for my cost-to-serve analysis dashboard

Acceptance Criteria:

• Transactions Data must be enriched with Ship-To details and cost-to-serve elements details (costs and charges)

• This baseline functionality / Use case covers up to 5 cost-to-serve elements.

Already covered by CHEM00, a prerequisite for all CHEM Use cases, and thus for CHEM06.

As a Sales Rep/Pricing Manager I want to have a dashboard available that I can use to monitor the cost recovery per cost-to-serve element.

User Story Name - Dashboard filter

I want to: Filter the scope of my cost-to-serve analysis

so I can: Identified outline opportunities

Acceptance Criteria:

Fields to filter on are:

• From & To Date

• Account Group: Ship-To

• Industry

• Continents

• Countries

• Regions

• States

• Customer Segments

• Customer Names

• Product Lines

• Product Groups

• Target Currency

• View Recovery By

User Story Name - Dashboard Generic filter

I want to: Use a generic filter

so I can: Create an advanced filter with a complex query to filter the scope of my cost-to-serve analysis

Acceptance Criteria: Generic filter option is enabled in the dashboard

User Story Name - Margin Impact Pie chart

I want to: See what the actual negative margin impact of each cost-to-serve category is

so I can: Plan corrective actions accordingly

Acceptance Criteria: Displayed metrics are the following:

• Total Recovery amount per dimension.

• Total Recovery amount is equal to the delta between the Costs and the Charges. If Charges - Costs >0, margin impact is positive, else it is a margin lost.

o    Dimension to be selected in the “View Recovery By” option in the dashboard filter menu.

o    Value currency corresponds to the Target Currency selected in the dashboard filter menu.

Each sector of the pie chart shows the total margin impact by the selected dimension. (I.e., If the selected dimension is “Country” then the pie chart shows the total margin impact by each country.)

Each sector is clickable

When clicked, the user can access the margin impact breakdown per cost-to-serve category for the dimension clicked. In our example by Country, we will get the breakdown for a particular country.

Recovery Amount:

Is taken from the value of Total Recovey Delta:

Total Recovery Delta is calculated with the formula:

SUM (Charge Field) - SUM (Cost Field)

Recovery Breakup with Margin:

It is calculated with the formula:

If the value for Total Recovery Delta is greater than or equal to zero:

grossMargin – totalRecoveryDelta

If the value for Total Recovery Delta is less than zero:

grossMargin + totalRecoveryDelta

where:

Gross Margin is taken from Transactions data with the formula:

SUM (Gross Margin)

Total Recovery Delta is calculated with the formula:

SUM (Charge Field) - SUM (Cost Field)

User Story Name - Recovery heatmap

I want to: See the cost-to-serve recovery percentage variations per Country v/s product line

so I can: Identifiy outline opportunities

Acceptance Criteria: Displayed metrics are the following:

• cost-to-serve recovery percentage variations per Country v/s product line

Recovery percentages are depicted by color

User Story Name - Recovery Trend Time series

I want to: See the cost-to-serve recovery percentage variations per cost-to-serve category

so I can: Identify outline opportunities

Acceptance Criteria: Displayed metrics are the following:

• cost-to-serve recovery percentage trend per cost-to-serve category

• Total Charge per cost-to-serve category

• Total Cost per cost-to-serve category

All cost-to-serve category are clickable to be analysed independently

User Story Name - Recovery Data table

I want to: See the cost-to-serve recovery

so I can: Identified outline opportunities at the lowest level of detail

Acceptance Criteria: Displayed metrics are the following:

• cost-to-serve recovery percentage trend per cost-to-serve category

• Total Charge per cost-to-serve category

• Total Cost per cost-to-serve category

Metrics are displayed at any level of granularity available in the filter option “View Recovery By”

The technical design for this use case incorporates one or more elements depending on the nature of the customer’s data:

• The core data, consisting of a main underlying Datamart

• Data enrichment logic, most likely composed of dataload logics

• The dashboard logic itself, which is composed of a few functional elements

o   Dashboard filters, which can potentially vary, but the core filter fields must exist in the source datamart

o   The 4 core charts in the dashboard 

The dashboard should be based off a single Datamart that contains sufficient data to cover all the core requirements. It is worth noting that cost-to-serve data should be broken down to the transaction level in order to complete most of this use case design, but in particular it is necessary for the 4^th portal – at the breakdown level. This means that the cost to serve data must be available at a per transaction basis and allocated out to each transaction data row, so that when the dashboard filters are applied, or data is aggregated, it can be split appropriately amongst transactions to provide an accurate view. Any cost to serve element will need to account explicitly for both the charges (revenue from customers) and cost (cost to serve) in order for the value to be shown in the dashboard. 

The need for data enrichments depends heavily on how the customer’s data is provided and set up. A core aspect of the solution for this use case is the level of granularity of the data. In order for data to be properly filterable to the use case, the cost to serve data elements must breakdown at the transaction line-item level. If the native data does not have that set up, data enrichments will be required.

As an example, cost to serve elements might be tracked in a separate system (possibly not even ERP) and might be aggregated as an amount at the customer level based on a time period (like a month). This would mean that the total cost-to-serve for that customer / month would need to be allocated to each transaction line item appropriately so that data can be filtered and reflect accurate numbers. The specificity of how these elements are allocated to each transaction line item, though, can impact how accurate the metrics will be. To further the example

The ”base case” for this use case assumes no data enrichment is necessary.

Standard charts vs. high charts – this design focuses on the use of HighChart charts. Though those rely on the configuration to provide the data, this use case still assumes the use of all data being consolidated in a single Datamart.

It’s generally good practice to include a Constants element at the top of the logic to allow commonly used variables to be easily edited. Constants classes are commonly used in OO programming to allow common changes – like table names or field names – to be altered easily without having to make the change in many places. Library methods can be created in this element or a separate “Library” element as well.

Any number of filter inputs can be added, as well as an aggregation selector (select one of the filter fields) that can be used on the Margin Impact and Recovery Data Table. The fields used in the filters and for the aggregation must exist in the source Datamart, but otherwise there are no serious concerns.

Pulling in the cost recovery data basically means grabbing the transactions that meet the filter criteria. Creating the recovery metrics requires that all the charge data (revenue) and all the cost data (cost to serve) for each cost to serve element and then dividing the charge amount by the cost amount to reach the recovery percentage. Stoplight thresholds for coloration of the cells in the Recovery HeatMap and the coloration of the Status elements can be based on hard-coded or parameterized (in a Parameter table) for ease of adjustment.

Four kinds of charts are created in this dashboard:

1. Margin Impact – a pie chart that shows the pie as the total recovery amount and divides the pie by that total amount using the selected aggregation dimension.

2. Recovery HeatMap – a heat map chart that shows the total recovery percentage based on two dimensions – business unit and regionality in Pricefx’s demo example, but whatever metrics are considered by the customer to be the most useful are fine choices. The cell color can be driven by whatever thresholds for the recovery percent the customer prefers.

3. Recovery Trend – this bar/line chart shows bars and lines that depict the costs and charges for each cost to serve element as stacked bars as well as the trend lines representing recovery percentage from period to period. The recovery % calculation is the same as elsewhere in the dashboard. The time period to be shown in the chart can be customized, but performance will degrade the more history is required. This should be made clear when making the decision on the orientation here.

4. Recovery Data Table – the data retrieved here is similar to the data in the heat map – focusing on the raw data and recovery percentage for each aggregation from the selected dimension (same as Margin Impact). Each cost to serve element is broken out in a Result Matrix with a field for the associated total charge amount, the associated total cost amount, the total recovery percent, and a traffic light status field. There are also a total charge, cost and recovery percent fields that represent the total amount associated with all cost to serve elements. The traffic light threshold can use the same targets as are used in the Heat Map, or they can be different.