Passthrough Cost Global List template

Objective

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

Example flow

Edit data

1. Open the Passthrough Cost Global 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 Adjusted Cost column.)

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

CASE 
    WHEN {Adjustment Type} = 'Amount' THEN {Cost} + COALESCE({Adjustment}, 0) 
    WHEN {Adjustment Type} = 'Percentage' THEN {Cost} * (1 + COALESCE({Adjustment}, 0)) 
    WHEN {Adjustment Type} = 'Override' THEN COALESCE({Adjustment}, {Cost}) 
    ELSE {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

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

Cost Adjusted List

CASE 
    WHEN {Cost Passthrough Strategy} = 'Maintain Margin' THEN {Maintain Margin Price} 
    WHEN {Cost Passthrough Strategy} = 'Passthrough None' THEN {List Price} 
    WHEN {Cost Passthrough Strategy} = 'Passthrough 50%' THEN {List Price} + ({Cost Difference} *.5) 
    WHEN {Cost Passthrough Strategy} = 'Passthrough 100%' THEN {List Price} + {Cost Difference} 
    ELSE {List Price}  
END

Maintain Margin Price

{Adjusted Cost} / (1 - {Current Margin}) * 100 / 100.00</code></pre>

New Price

COALESCE ({Price Override}, {Cost Adjusted Price})

Price Change

CASE 
    WHEN COALESCE ({List Price}, 0) = 0 THEN NULL 
    ELSE ({Cost Adjusted Price} - {List Price}) / {List Price} 
END

New Margin

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

Current Margin

({List Price} - {Cost}) / {List Price}

Margin Difference

CASE 
    WHEN {New Margin} IS NULL 
    or {New Margin} = 0 THEN 0 
    ELSE ({New Margin} - {Current Margin}) 
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 New 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:

   • Global and Country Lists Prices base workbook
   • Country Prices base workbook
   • Product Costs waterfall element workbook