MENU
      Passthrough Cost Global List template
      • 07 Mar 2023
      • 3 Minutes to read
      • Dark

      Passthrough Cost Global List template

      • Dark

      Article summary

      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

      Screen Shot 2022-10-21 at 1.54.43 PM.png

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

      ColumnTypeDefinition
      Adjustment TypecalculatedSelect 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 {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
      Plain text
      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

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

      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
      Plain text

      Maintain Margin Price

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

      New Price

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

      Price Change

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

      New Margin

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

      Current Margin

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

      Margin Difference

      CASE
          WHEN {New Margin} IS NULL
          or {New Margin} = 0 THEN 0
          ELSE ({New Margin} - {Current Margin})
      END
      Plain text

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

      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.