Retail tariff impact analysis: landed cost and margin

Retail tariff impact analysis calculates how supplied landed-cost assumptions change SKU gross profit and when that change reaches the sales period. Separate existing stock from new purchases before applying any increase. A replacement-cost view can help pricing decisions, but it answers a different timing question from the cost of units expected to sell this period.

Start with approved assumptions and finished goods

Use a purchase export, opening inventory summary and sales plan for the same finished-goods SKUs. Bring quantities, existing unit carrying cost, supplier cost, inbound freight and other included acquisition charges, planned net selling price, and the units expected to sell from each inventory layer.

Have finance confirm the costing convention and the trade owner supply the scenario's incremental duty amount or percentage and value base. Every percentage below is a hypothetical business planning input. None represents a current legal rate or a classification conclusion.

The IRS inventory guidance in Publication 538 describes purchased merchandise cost using invoice price after appropriate discounts plus transportation and other acquisition charges. Use that as background for defining included costs, while following the company's applicable accounting policy. Keep the boundary consistent with cost of goods sold.

This review covers goods a retailer buys for resale. Component usage and production effects belong in manufacturing tariff impact analysis.

Build the landed-cost scenario

An illustrative US homewares retailer plans to sell lamps and kettles. Its base purchase costs below exclude the hypothetical incremental duty. Existing carrying costs equal supplier cost plus inbound charges in this simplified example; no other cost changes are assumed.

Put the following columns in A through G. The incremental assumption applies only to supplier cost in column D, because that is the value base supplied for this exercise.

Existing unit cost in H2, new scenario unit cost in I2 and total units in J2 are:

=D2+E2
=H2+D2*F2
=B2+C2

Fill down through row 3. Lamp cost rises from $24 to $26 for new purchases: 20 + 4 + 20 x 10%. Kettle cost rises from $35 to $41: 30 + 5 + 30 x 20%. Applying the percentage to freight as well would change the assumed value base, so it would be a different scenario.

Calculate the period margin from inventory layers

Revenue in K2 uses total units at net selling price. Cost of goods sold in L2 combines existing and new inventory costs. Gross profit in M2 and gross margin in N2 follow:

=J2*G2
=B2*H2+C2*I2
=K2-L2
=IF(K2=0,"",M2/K2)

The example assumes all the listed units sell during the planning period, with no returns, additional markdowns or ending stock from these quantities. Display margins to two decimals and keep full precision in the workbook.

For example, lamp COGS is 80 x 24 + 120 x 26 = $5,040. The total margin is 7,060 / 20,000 = 35.30%. Use =SUM(M2:M3)/SUM(K2:K3) for the total, rather than averaging SKU percentages. The gross profit margin method explains that denominator in full.

Reconcile the cost increase

With the same sales quantities and no incremental duty, base COGS is 200 x 24 + 200 x 35 = $11,800. Base gross profit is therefore 20,000 - 11,800 = $8,200. The scenario reduces gross profit by $1,140, from $8,200 to $7,060.

Prove the change from the new-purchase units:

The Excel check is =SUMPRODUCT(C2:C3,I2:I3-H2:H3). Microsoft's SUMPRODUCT documentation describes summing products of corresponding ranges. This check must equal scenario COGS minus base COGS when price and volume stay fixed. Covirage is not affiliated with Microsoft.

Separate timing from the pricing decision

Change one input: suppose all planned sales use newly purchased stock instead of the stated existing layers. Quantities and selling prices stay fixed. Cost becomes 200 x 26 + 200 x 41 = $13,400, leaving $6,600 gross profit and a 33.00% margin.

The $460 difference from the first scenario equals the existing-stock benefit: 80 x 2 + 50 x 6. That shows why a current-period margin can remain stronger than the replacement-cost margin without any change in price.

The commercial team can assess price, promotion and purchase timing from these two views. It still needs demand and inventory information before changing orders. A cost scenario assumes the listed units sell; it cannot establish customer acceptance of a new price.

Where it goes wrong

• Increasing old stock automatically. Keep carrying cost and replacement cost in separate views.
• Adding duty twice. Confirm whether the supplier's quoted landed cost already contains the assumed charge.
• Changing the value base. State whether the supplied assumption applies to supplier cost or another approved amount.
• Mixing margin with markup. Margin divides gross profit by revenue; markup versus margin uses a different denominator.
• Ignoring receipt timing. Units arriving after the planned sale cannot fund that inventory layer. Reconcile units available, sold and remaining before trusting the result.

Run the scenario on your retail files in Covirage

Bring your SKU purchase export, inventory summary, selling prices and business-approved assumptions to Covirage. Covirage maps the columns, its tools compute and check the cost and margin comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.

See cost variance analysis for the supporting workflow.