Passthrough Cost Country List template

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

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

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

6. 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

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

8. 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