Passthrough Cost Region 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 region price list.

Example flow

Edit data

1. Open the Passthrough Cost Region 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 Price}, 0) = 0 THEN NULL
    ELSE 1 - ({NewPrice} / {Current Country Price})
END

Cost Adjusted List (List Price Review worksheet)

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

New Price (List Price Review worksheet)

COALESCE({PriceOverride}, {CostAdjustedList})

Price Change (List Price Review worksheet)

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

Has Price Change (List Price Review worksheet)

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

New Margin (List Price Review worksheet)

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

Current Margin (List Price Review worksheet)

({Current Region Price} - {Current Cost}) / {Current Region 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}) / {CurrentCost}
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 to the base and master data workbooks.

2. From the Actions menu, select Publish.

   The selected records are synchronized with the Region Prices base workbook.