Landed cost import template in Excel
Landed cost import template in Excel
The supplier invoice is the easy part of importing. What sits between that invoice and the number your stock system should hold is a whole layer of cost that most small importers never work out: international freight, cargo insurance, the tariff paid at clearance, and the agent, warehouse and inland transport charges that come with moving goods through a port. Leave that layer out and every margin in the business is a guess that happens to look healthy until the month is closed.
This landed cost import template in Excel collects those charges in one sheet and allocates them across the goods in proportion to what each item is worth. What comes out is a unit cost that includes everything it took to place that unit on the shelf, not just the price printed on the overseas invoice. That figure is what you price from, what you compare suppliers against, and what tells you whether a reference is still worth importing at all.
The workbook ships with three sample products and every number already solved, so you can follow the mechanics before overwriting anything with your own data. There are no macros, no external connections and nothing to install: the arithmetic lives in visible formulas on the sheet, which means anyone can trace where a figure came from and which cell feeds which result.
What landed cost is and what this sheet solves
Landed cost is the full amount paid to get one unit ready for sale. It starts with the value of the goods at origin and adds the freight that carried them, the insurance that covered the journey, the tariff settled when they were cleared, and the agency, storage and inland freight that followed. The supplier invoice covers the first of those items only, which is why a product bought at what looks like a good price can end up costing considerably more than that invoice suggests.
The sheet makes one structural decision and holds to it all the way down: each charge is spread by value, never by unit count. An item that accounts for half the value of the shipment carries half the freight, even if it fills only a few cartons; a large cheap carton carries very little, even when it takes up half the container. That rule kills the usual mistake of loading the inexpensive product with charges it never generated, and it keeps the unit cost defensible if anyone ever audits the margin.
The allocation matters because every later decision rests on it. With a complete unit cost you can set a price that actually covers the work of bringing the goods in, spot references that sell in volume but contribute almost nothing, and compare two shipments of the same product bought months apart. It also gives you something concrete to discuss with a supplier whose origin price fell while freight rose enough to leave the landed figure unchanged.
How the sheet is put together
| Block | What it holds |
|---|---|
| Header | Company, shipment reference, total freight, total insurance, tariff rate and other charges. |
| Product table | Three rows with item, quantity, unit value at origin, total value, share of the shipment and the allocation of each charge through to unit cost. |
| Totals row | Sum of quantities, of value at origin, of each charge and of the full landed cost. |
| Instructions | A separate sheet with the order of entry and the usage warnings. |
The header holds what applies to the shipment as a whole; the table pushes those totals down to each product. That split is deliberate. If a revised freight invoice arrives next week, you change one number at the top and every row recalculates on its own, without editing products one at a time.
Read the sheet in that order, top to bottom, because it mirrors the way a shipment actually unfolds. The cost of the journey is known first, the quantities and values come next, and the unit cost that goes into inventory appears last.
What each column means
| Column | What it represents |
|---|---|
| Item | Name of the product travelling in the shipment. |
| Quantity | Units received that are being costed. |
| Unit value | Price per unit at origin, in the currency of the invoice. |
| Total value | Quantity multiplied by unit value. |
| Share | The portion of total shipment value that this product represents. |
| Freight | The part of total freight assigned to it through that share. |
| Insurance | The part of total insurance assigned on the same basis. |
| Tariff | Amount produced by applying the tariff rate to the product value. |
| Other | Clearance, agency, storage and inland transport charges. |
| Total cost | Value at origin plus every allocated charge. |
| Unit cost | Total cost divided by quantity; the figure that goes into inventory. |
The eleven columns read as a chain: share drives the allocation, the allocation builds total cost, and total cost divides by quantity. When a row shows an odd unit cost, the fault is nearly always in the quantity or in a share that does not add up, not in the formula at the end of the row.
The share column deserves the closest look, and for good reason: if the three rows do not add up to total value at origin, the allocation is skewed and every unit cost carries the same error. That is why the totals row is worth as much as the table it closes.
The example already loaded in the workbook
| Product | Quantity | Value at origin | Share | Total cost | Unit cost |
|---|---|---|---|---|---|
| Ream of paper | 100 | 2,000,000 | 50 percent | 2,550,000 | 25,500 |
| Toner cartridge | 50 | 1,500,000 | 37.5 percent | 1,912,500 | 38,250 |
| Cardboard box | 200 | 500,000 | 12.5 percent | 637,500 | 3,187.50 |
| Total | 350 | 4,000,000 | 100 percent | 5,100,000 | Weighted cost |
Against value at origin of 4,000,000 the sheet charges 600,000 of freight, 40,000 of insurance, 400,000 of tariff and 60,000 of other costs. With the tariff rate set at 10 percent, the full landed cost reaches 5,100,000, so the gap against the supplier invoice is 1,100,000, or more than a quarter of the value of the goods.
The effect is easiest to see product by product. The ream was bought at 20,000 a unit and lands at 25,500, an increase of 5,500 per unit; the toner settles at 38,250 and the box at 3,187.50. None of those three numbers appears on the overseas invoice, yet they are the ones that decide whether the shipment makes money.
The tariff rate is an editable cell rather than a constant buried in a formula. If the rate for a shipment changes, you type the new figure in the header and the tariff and unit cost recalculate straight away under the same value-based allocation.
What to do with the unit cost
The unit cost this sheet produces is the one that should go into inventory, not the invoice price. If the system carries the ream at 20,000 and prices are set from that figure, every unit sold gives back part of a freight bill that was never recovered. The shortfall builds quietly until it surfaces as a gap at closing.
With the right figure, three conversations change. Sales, because price stops being calculated on an incomplete base. Purchasing, because the supplier quoting the lowest origin price may not be the cheapest landed if its packaging or routing makes the journey dearer. And the business itself, because a high-volume, low-value item can contribute less than a low-volume item that costs more per unit.
How to use it, step by step
- Enter the company name and a reference that identifies the shipment in the header, such as the supplier invoice number.
- Record freight, insurance and other charges exactly as they appear on the carrier and customs agent invoices.
- Type the tariff rate into its cell; it is a figure from the operation, and the workbook imposes none of its own.
- Load the products with item, quantity and unit value at origin, using the quantity actually received.
- Check that the share column adds up to total value at origin before looking at any cost figure.
- Read the unit cost on each row and compare it with the purchase price to see how much the import added.
- Keep one copy per shipment and post the unit cost to inventory on the day the goods arrive, not weeks later.
Common mistakes when costing an import
- Leaving freight out of the cost because it arrives on a different invoice and a different date, and treating it as a monthly expense instead.
- Spreading charges by unit count rather than by value, which punishes the cheap item and hides what the expensive one really costs.
- Applying the tariff only to some products in the shipment when the allocation has to cover every row.
- Forgetting insurance and agency fees because they look small next to the value of the goods.
- Typing a unit cost over the formula and breaking the calculation chain without noticing.
- Costing the quantity ordered instead of the quantity received, when fewer units arrive than were invoiced.
- Updating the cost in the system months later, after part of the stock has already been sold at a cost that never existed.
Which program opens it
The file runs in Excel, LibreOffice Calc and Google Sheets with no conversion step. Excel and LibreOffice keep every formula and the formatting you see in the screenshot; if you upload it to Google Sheets, check the number formats for thousands and decimal separators, because the cloud version reinterprets them according to the regional settings of the account.
No macros and no special permissions are involved, so it opens on an ordinary office machine without IT support. On a shared drive, keep the master copy read-only for the rest of the team and save each shipment as its own file.
The screenshot shows the sheet with the example filled in: the header with the totals for the journey, the three product rows with their share of the shipment, and the totals row where the landed cost of 5,100,000 is spread down to the unit costs. It is the same view you get when the file opens, with nothing to switch on.
If a shipment carries more than three products, insert rows inside the table and drag the formulas from the row above; the allocation keeps working as long as the share is calculated against the overall total. For a shipment with dozens of items, group them into families first and add up at the end.
The template is published by Kardex Tauro alongside other control formats that follow the same logic: a header with the period data, a table of movements and a closing block of totals you can check. Download it, replace the sample with your own shipment, and compare what you thought each unit cost with what it actually cost.
