What does total cost of ownership include in this template?

Piece price, freight, brokerage and handling, packaging, duty and extra tariffs (landed cost), plus inventory carrying on the pipeline and safety stock, the cost of defects that reach you, supplier management and an expediting allowance, and the one-time transition cost spread over your horizon. In the example the current source has a 6.10 piece price but a TCO of about 10.18 per unit, 67% above the price.

How is inventory carrying cost calculated?

Landed cost times the weeks of stock you own because of this source (transit plus safety stock) divided by 52, times your carrying rate. For a landed cost of 8.71, six weeks in transit, four weeks of safety stock and a 22% carrying rate, that is 8.71 x 10 / 52 x 22% = 0.37 per unit. A source two weeks away carries much less.

What is the break-even extra tariff?

The extra tariff on your current source at which another option's TCO equals the current source's. It allows for the tariff also raising the value of the stock you carry. In the example the Mexico option breaks even at about 1.3% extra tariff on the current source, so at the example's 25% it wins clearly, while the domestic supplier needs about 16.8%.

How is payback worked out?

One-time transition cost divided by the yearly saving against your current source before that cost is spread, times 12 for months. In the example, moving to Mexico costs 85,000 once and saves about 77,000 a year before the transition cost, a payback of about 13 months. An option that does not save shows No payback.

Where do the duty and tariff rates come from?

From your broker or the importing country's customs tariff for the part's HS code and origin. The rates in the example are illustrations, not current tariffs. Choose on Setup whether duty is charged on FOB value (piece price, as in the United States) or CIF value (price plus freight, as in the European Union, the United Kingdom and India).

How does this relate to the Sourcing Total Cost of Ownership calculator?

The calculator works out the TCO adder over piece price as volume times a hidden cost per unit times a burden factor, plus annual supplier management cost. This workbook itemises the hidden costs instead of scaling them, which equals the calculator with the burden factor at 100%. For the example's current source the adder is about 4.08 per unit over the 6.10 price.

Planning and Supply Chain

Reshoring Total Cost of Ownership Excel Template

Compare up to four sources for a part, offshore, nearshore, your own plant or a domestic supplier, on total cost of ownership per unit and per year, with payback of the move and the tariff at which each option beats your current source.

Overview

This reshoring total cost of ownership workbook is for sourcing managers, buyers and operations and finance leaders who need to know whether a cheaper offshore price is really cheaper. It ships with a worked example for a fictional lighting manufacturer comparing four sources for a die-cast aluminium housing: the current supplier in China, a supplier in Mexico, its own plant and a domestic supplier. All prices, rates and costs are invented, and the duty and tariff rates are illustrations rather than current tariffs.

Each option is costed per unit with the same lines. Landed cost adds freight, brokerage and handling, packaging, duty and any extra tariff to the piece price. Inventory carrying cost charges your carrying rate on the stock in transit and in safety stock, valued at landed cost. Quality cost turns the defect rate into money with your cost per defective unit. Supplier management, an expediting and disruption allowance and the one-time cost of moving are spread over the annual volume and your horizon. The sum is the total cost of ownership; with the burden factor at 100%, its adder over piece price matches the Sourcing Total Cost of Ownership calculator on this site.

In practice, put today's source in option 1 and the alternatives beside it. The stacked chart shows where each option's cost comes from, the payback tells finance how long the move takes to pay, and the break-even tariff answers the question a tariff announcement raises: how high would the extra tariff on the current source have to go before another option wins. The tariff chart shows the same thing as lines crossing.

What this template includes

Setup with your currency label, inventory carrying rate, the horizon one-time costs are spread over, and the duty base (FOB or CIF value)
TCO Model with the part, annual volume and the cost of a defective unit, and an options grid for up to four sources, option 1 being your current source
Per option inputs: piece price, freight, brokerage and handling, packaging, duty and extra tariff rates, transit and safety-stock weeks, defects in PPM, annual supplier management, an expediting and disruption allowance, and the one-time transition cost
Per option results: customs value, duty, extra tariff, landed cost, inventory carrying cost, quality cost, management and disruption, spread transition cost, TCO per unit and per year, and TCO above piece price
Comparison with the current source: annual difference, percentage difference, payback months of the transition cost, the break-even extra tariff and a rank
Dashboard KPI tiles: lowest-TCO option, current source TCO, yearly saving and payback
Two native charts: what each option's TCO is made of (stacked by cost element) and TCO per unit as the extra tariff on the current source changes from 0% to 60%
Method and Verify sheets: every formula in words, and a self-check against an independent reference, with agreement to the Sourcing Total Cost of Ownership calculator
A CSV with live formulas of the options grid for any spreadsheet app; the dashboard, charts, dropdowns and self-check are in the .xlsx

Suggested use case

Use it to build the business case for reshoring or nearshoring a part, to test whether a lower offshore price survives freight, tariffs, inventory and quality, to see how far a tariff would have to move before switching pays, and to show finance the payback of the transition.

How to use it

On Setup, type your currency label, your inventory carrying rate, the number of years to spread one-time costs over, and whether duty is charged on FOB or CIF value.
On TCO Model, enter the part, its annual volume and what a defective unit that reaches you costs.
Name your current source as option 1 and up to three alternatives, and fill in each one's price, logistics, duty and tariff rates, lead times, defect rate, annual costs and one-time transition cost.
Read TCO per unit, the annual difference from your current source, payback and the break-even tariff for each option.
Open the Dashboard to see what each option's TCO is made of and how a tariff change moves the answer.

Frequently Asked Questions

What does total cost of ownership include in this template?: Piece price, freight, brokerage and handling, packaging, duty and extra tariffs (landed cost), plus inventory carrying on the pipeline and safety stock, the cost of defects that reach you, supplier management and an expediting allowance, and the one-time transition cost spread over your horizon. In the example the current source has a 6.10 piece price but a TCO of about 10.18 per unit, 67% above the price.
How is inventory carrying cost calculated?: Landed cost times the weeks of stock you own because of this source (transit plus safety stock) divided by 52, times your carrying rate. For a landed cost of 8.71, six weeks in transit, four weeks of safety stock and a 22% carrying rate, that is 8.71 x 10 / 52 x 22% = 0.37 per unit. A source two weeks away carries much less.
What is the break-even extra tariff?: The extra tariff on your current source at which another option's TCO equals the current source's. It allows for the tariff also raising the value of the stock you carry. In the example the Mexico option breaks even at about 1.3% extra tariff on the current source, so at the example's 25% it wins clearly, while the domestic supplier needs about 16.8%.
How is payback worked out?: One-time transition cost divided by the yearly saving against your current source before that cost is spread, times 12 for months. In the example, moving to Mexico costs 85,000 once and saves about 77,000 a year before the transition cost, a payback of about 13 months. An option that does not save shows No payback.
Where do the duty and tariff rates come from?: From your broker or the importing country's customs tariff for the part's HS code and origin. The rates in the example are illustrations, not current tariffs. Choose on Setup whether duty is charged on FOB value (piece price, as in the United States) or CIF value (price plus freight, as in the European Union, the United Kingdom and India).
How does this relate to the Sourcing Total Cost of Ownership calculator?: The calculator works out the TCO adder over piece price as volume times a hidden cost per unit times a burden factor, plus annual supplier management cost. This workbook itemises the hidden costs instead of scaling them, which equals the calculator with the burden factor at 100%. For the example's current source the adder is about 4.08 per unit over the 6.10 price.