Passthrough Cost Country List template
  • 20 Apr 2023
  • 3 Minutes to read
  • Dark
    Light

Passthrough Cost Country List template

  • Dark
    Light

Article summary

Objective

Use the recommendations in this template to analyze the impact of supplier cost changes and make necessary price updates, including a full or partial "cost passthrough" to the country price list.

Example flow

country list cost passthrough workflow.png

Edit data

  1. Open the Passthrough Cost Country List scenario workbook.
    When you created the scenario, this workbook was loaded with data from base and decision support workbooks.

  2. Open the New Product Costs worksheet.

  3. In the New Cost column, update product costs. Update values manually or upsert data from a CSV file.

  4. Update the Proposed Start Date column. (If this column isn't visible, select > to expand the Publish Info column group.)

  5. Specify or select values for Adjustment Type and Adjustment as shown in the following table. (If these columns aren't visible, select > to expand the Adjustments column group.)

ColumnTypeDefinition
Adjustment TypeinputSelect Amount, Override, or Percentage.
AdjustmentinputEnter a value based on the Adjustment Type selection. For example, if you select an adjustment type of Percentage, then enter a percentage value such as 5%.

The value of the Adjusted Cost column is calculated as follows:

CASE
    WHEN {AdjustmentType} = 'Amount' THEN {New Cost} + COALESCE({Adjustment}, 0)
    WHEN {AdjustmentType} = 'Percentage' THEN {New Cost} * (1 + COALESCE({Adjustment}, 0))
    WHEN {AdjustmentType} = 'Override' THEN COALESCE({Adjustment}, {New Cost})
    ELSE {New Cost}
END
  1. In the Cost Passthrough Strategy column, select how the new product cost is applied to the global list price.

The following information shows how other columns, column groups, and KPI cards in this worksheet are calculated. Use this information for additional decision support.

Cost Difference (List Price Review worksheet)

CASE
    WHEN {Current Cost} = 0 THEN 0
    ELSE ((New Cost} - {Current Cost}))
END

New Discount (List Price Review worksheet)

CASE 
    WHEN COALESCE({Current Country List}, 0) = 0 THEN NULL 
    ELSE 1 - ({New Price} / {Current Country List}) 
END

Cost Adjusted List (List Price Review worksheet)

CASE 
    WHEN {CostPassthroughStrategy} = 'Maintain Margin' THEN {MaintainMarginPrice} 
    WHEN {CostPassthroughStrategy} = 'Passthrough None' THEN {Current Country Price} 
    WHEN {CostPassthroughStrategy} = 'Passthrough 50%' THEN {Current Country Price} + ({CostDifference} *.5) 
    WHEN {CostPassthroughStrategy} = 'Passthrough 100%' THEN {Current Country Price} + {CostDifference} 
    ELSE {Current Country Price} 
END

Cost Adjusted List (List Price Review worksheet)

CASE
    WHEN {CostPassthroughStrategy} = 'Maintain Margin' THEN {MaintainMarginPrice}
    WHEN {CostPassthroughStrategy} = 'Passthrough None' THEN {Current Country Price} 
    WHEN {CostPassthroughStrategy} = 'Passthrough 50%' THEN {Current Country Price} + ({CostDifference} *.5) 
    WHEN {CostPassthroughStrategy} = 'Passthrough 100%' THEN {Current Country Price} + {CostDifference} 
    ELSE {Current Country Price} 
END

New Price (List Price Review worksheet)

COALESCE({PriceOverride}, {CostAdjustedList})

Price Change (List Price Review worksheet)

CASE 
    WHEN COALESCE({Current Country Price}, 0) = 0 THEN NULL 
    ELSE ({CostAdjustedList} -{Current Country Price}) / {Current Country Price}
END

Has Price Change (List Price Review worksheet)

CASE 
    WHEN {Current Country Price} = {CostAdjustedList} THEN 0 
    ELSE 1
END

New Margin (List Price Review worksheet)

CASE
    WHEN COALESCE({Current Country Price}, 0) = 0 THEN 0
    ELSE ({NewPrice} - {New Cost}) / {NewPrice}
END

Current Margin (List Price Review worksheet)

({Currenty Country Price} - {Current Cost}) / {Current Country Price}

Margin Difference (List Price Review worksheet)

CASE
    WHEN {NewMargin} IS NULL or {NewMargin} = 0 THEN 0
    ELSE ({NewMargin} - {CurrentMargin})
END

Adjusted Cost Change (New Product Costs worksheet)

CASE
    WHEN COALESCE({Current Cost}, 0) = 0 THEN 0
    ELSE ({AdjustedCost} - {Current Cost}) / {Current Cost}
END

Unadjusted Cost Change (New Product Costs worksheet)

CASE
    WHEN COALESCE({Current Cost}, 0) = 0 THEN 0
    ELSE ({New Cost} - {Current Cost}) / {Current Cost}
END

Has Cost Change (New Product Costs worksheet)

CASE
    WHEN {Current Cost} IS NULL THEN 0
    WHEN {AdjustedCost} IS NULL THEN 0
    WHEN {AdjustedCost} = {Current Cost} THEN 0
    ELSE 1
END

  1. Open the List Price Review worksheet to review the value of Cost Adjusted List. This shows how the strategy you chose applies to the global list prices.
Notes
  • By default, the List Price Review worksheet is filtered to show only records with cost changes.
  • Calculated columns, column groups, and KPI cards in this worksheet provide additional decision support.
  1. Review the resulting global price in the New Price column of the List Price Review worksheet. If desired, override the value:
    1. Enter a value in the Price Override column.
    2. Update the Proposed Start Date column. (If this column isn't visible, select > to expand the Publish Info column group.)

Publish data

  1. In the New Product Costs and List Price Review worksheets, select the Publish checkbox for records that represent updated and final data. These records will be published back to the base and master data workbooks.

  2. From the Actions menu, select Publish.

    The selected records are synchronized with:

    • Country Prices base workbook
    • Product Costs waterfall element workbook

Was this article helpful?

Changing your password will log you out immediately. Use the new password to log back in.
First name must have atleast 2 characters. Numbers and special characters are not allowed.
Last name must have atleast 1 characters. Numbers and special characters are not allowed.
Enter a valid email
Enter a valid password
Your profile has been successfully updated.