Tariff Pass-Through and Sourcing Decision Excel Model – Excel XLSX

BENEFITS OF THIS DOWNLOADABLE EXCEL DOCUMENT

1. Compare absorb, full pass-through, profit-maximising partial pass-through and re-sourcing against the same pre-tariff baseline.
2. Use your own price, volume and elasticity to calculate a transparent partial pass-through without Solver, iteration or circular references.
3. Compare 36-month differential NPV and payback with the organisation's switching threshold.

PROCUREMENT STRATEGY EXCEL DESCRIPTION

Tariff Pass-Through and Sourcing Decision Excel Model is an Excel template (XLSX) with a supplemental Zip archive file document available for immediate download upon purchase.

# Tariff Pass-Through Decision Model: Choose Where to Absorb, Reprice or Re-source

A new tariff does not create one pricing answer. Some products can absorb the duty, some should reprice, and others justify a supplier or origin change. This package compares those choices at product and portfolio level, values each over 36 months and makes the assumption behind every recommendation visible, so management can defend a response instead of debating a blended average.

## Three Decisions This Package Helps You Make

1. Decide which strategy fits each product. Compare absorb, full pass-through, profit-maximising partial pass-through and re-sourcing against the same pre-tariff baseline.
2. Decide how much price to move. Use your own price, volume and elasticity to calculate a transparent partial pass-through without Solver, iteration or circular references.
3. Decide whether a sourcing change creates enough value. Compare 36-month differential NPV and payback with the organisation's switching threshold.

## What's Included

•  `TPT_Tariff_PassThrough_Decision_Model_Commercial_Edition_v1` – the 14-sheet decision model for product-level strategy, portfolio economics and the decision memo.
•  `TPX_SKU_Supplier_Cost_Intake_Commercial_Edition_v1` – the 13-sheet companion for tariff headings, origins, rates, supplier qualification, Incoterms, logistics weights and data completeness.
•  `TPT_User_Manual.pdf` – a 67-page illustrated user manual with the model logic, workflow and worked example.

Both Excel workbooks open on a complete 30-product demo. You can review the recommendations and trace their assumptions before replacing the inputs. The package is published by ExpertPro Decision Tools.

## Capabilities Tied to Decisions

| Capability | Business outcome | Decision supported |
|—-|—-|—-|
| Capture headings, origins, effective rates, source dates and confirmation status | Makes tariff-data quality visible before management relies on it | Is the input record complete enough to decide? |
| Compare four strategies at product level | Avoids imposing one portfolio-wide response on products with different economics | Which products should absorb, reprice or move origin? |
| Calculate partial pass-through from price, volume and elasticity in closed form | Keeps the pricing logic visible and reviewable | What price move maximises modelled profit? |
| Value strategies as 36-month differential NPV against the pre-tariff baseline | Places pricing and sourcing alternatives on one economic basis | Which response creates the most value? |
| Produce a decision memo with the recommendation, assumption and break point | Shows where judgement could change the answer | What can management approve, challenge or monitor? |

## How the Model Works

Inputs → Analysis → Controls → Outputs → Decision

1. Inputs: Enter product economics, six-digit tariff heading, origin, duty rate, supplier information, switching costs, timing and elasticity assumptions.
2. Analysis: Calculate the duty impact, optimal partial price response, re-sourcing economics and 36-month differential NPV for four strategies.
3. Controls: Test completeness and rate confirmation, then reconcile the intake and decision workbooks through 62 named verifications and one master status light.
4. Outputs: Review product recommendations, portfolio pass-through, gross-margin effect, payback, NPV and the decision memo.
5. Decision: Select the product-level response, confirm weak assumptions and assign pricing or sourcing actions.

## Worked Demo Evidence

The included case models a flow-equipment manufacturer with 30 products, six origins and USD 49.5 million of sales.

| Demo result | Evidence shown in the model |
|—-|—-:|
| Annual duty impact | USD 1,963,358 |
| Duty impact as a share of sales | 4.0% |
| 36-month value versus absorbing everywhere | USD 2,493,956 |
| Portfolio pass-through | 78.5% |
| Gross margin after recommended actions | 41.0% vs 38.7% under full absorption |
| Pre-tariff gross margin | 42.7% |
| Longest recommended sourcing-switch payback | 22.4 months vs 24-month ceiling |

The model recommends eight products absorb, 19 reprice – 14 in full and five in part – and three move origin. The recommended portfolio recovers 58% of the annual duty impact in gross profit. The companion shows 27 of 30 product records complete, 95.5% of 66 rate rows confirmed against the stated schedule and the oldest rate checked 76 days earlier. These figures demonstrate the method; they are not a promise of your result.

## Built for Trust

•  Built to the FAST modelling standard 02c, with a controlled function set and visible calculation logic.
•  364 named ranges, seven charts and 62 named verification checks across the two workbooks.
•  Four checks compare rates used in the decision model with rates supplied by the intake, row by row.
•  No macros, volatile functions, external links or hidden sheets.
•  The partial pass-through calculation uses no Solver, iteration or circular reference.
•  A frozen run date supports repeatable analysis.
•  Inputs and benchmark bands carry sources, status and dates where applicable.
•  Data remains in your local Excel files.
•  Designed for Microsoft Excel 2019 and Microsoft 365 desktop.

## Who It Is For

The package is designed for CFOs, finance directors, pricing leaders, procurement and sourcing teams, supply-chain leaders and commercial finance professionals managing tariff-driven cost pressure.

Typical uses include tariff-response planning, SKU-level repricing, origin-switch screening, supplier-change business cases, margin-defence reviews and board or investment-committee decision support.

## Honest Limitations

•  The companion is not a duty-rate database and does not look up or update rates automatically.
•  It does not classify goods; the user or customs adviser owns the tariff heading and origin determination.
•  It is not customs-compliance software and does not file declarations or provide legal or tax advice.
•  It does not model quotas, antidumping proceedings, trade-remedy litigation or retaliation.
•  Elasticity is an input. The model makes that judgement visible but does not estimate demand behaviour from transaction data.
•  It does not predict competitor responses.
•  Recommendations depend on the data and assumptions supplied and do not guarantee margin or cash outcomes.

## Frequently Asked Questions

### Does the package look up tariff rates for me?

No. You enter the applicable rates. The companion records the source, date checked and confirmation status so reviewers can see how current and reliable each input is.

### Does it choose one strategy for the entire portfolio?

No. It compares four strategies for each product, then aggregates the selected recommendations into a portfolio view.

### How is partial pass-through calculated?

The model uses a closed-form relationship based on your price, volume and elasticity inputs. The algebra is visible in the workbook and does not require Solver or iteration.

### Can it evaluate a supplier or origin change?

Yes. It compares switching costs, timing, recurring economics, payback and 36-month differential NPV. The user remains responsible for confirming feasibility, qualification and tariff treatment.

### Does my data leave Excel?

No. There are no macros, external links or system connections; the workbooks operate locally on the data you enter.

### Is this customs, legal or tax advice?

No. It is a decision-support model. Classification, origin, rate interpretation, filing and compliance remain with the responsible professionals.

### Which version of Excel is required?

The workbooks are designed for Microsoft Excel 2019 and Microsoft 365 desktop. Other spreadsheet applications are not part of the supported configuration.

## Turn a Tariff Cost into Product-Level Decisions

Open the worked demo, inspect why each product receives a different recommendation, then replace the inputs with your own rates, economics and constraints. Choose the Tariff Pass-Through Decision Model when pricing and sourcing teams need one reviewable basis for action.

Source: Best Practices in Procurement Strategy, Tariff Management Excel: Tariff Pass-Through and Sourcing Decision Excel Model Excel (XLSX) Spreadsheet, ExpertPro Consulting