Value columns in a count table: cost, currency and exchange rate

Suppose a pallet of thermos cups is bought from a Chinese supplier in US dollars, sold in so'm from a market unit and valued in the finance report in so'm at some exchange rate. When the count finds 14 pieces missing, what are they worth? The answer depends on which cost, which currency and which rate, and a table with a single value column cannot say.

Why one value column is not enough

A column called "Amount" can hold dollars in one row and so'm in the next. Nobody can tell which rate sits behind it, or whether it shows cost or selling price. A total of such a column is a number without a meaning. Gap values are used for decisions, such as what to write off and which SKU to investigate first, so they need a clear basis.

The columns to set up

A workable layout has these columns, in this order:

1. Unit cost in the original currency.
2. Cost currency as a short code in the next column (USD, CNY or UZS).
3. Exchange rate to the reporting currency, with its date.
4. Unit cost in the reporting currency, by formula: cost multiplied by rate.
5. Book quantity, counted quantity and gap quantity.
6. Gap value at cost, by formula: gap quantity multiplied by unit cost in the reporting currency.
7. Optional: selling price in so'm and the gap value at selling price, to show the sales that are at risk.

Put the exchange rates on a separate small sheet, one row per currency and date, and let every formula point to it. When a rate is updated, every row follows. A rate typed over a formula in one row is the quickest way to get a wrong total.

Also check for rows where the cost is empty or zero. Such a row produces a gap value of zero, which looks harmless but hides exactly the items you cannot value, often new products or samples.

Decide which cost and which rate, and write it down

There are two common views, and they should not share a column:

• Book value: the cost as your books hold it, in the accounting currency, often fixed at the time of receipt. This is the basis on which an adjustment is posted.
• Current value: the original-currency cost at today's rate, which shows what it would cost to buy the goods again.

Put the basis and the date into the column header, for example "Gap value at book cost" or "Gap value at the rate of 14 October". The owner's report can show both views side by side.

An example with assumed numbers

The unit cost of thermos B-0412 is USD 3.20. Assume for illustration that the book rate is 12,000 so'm per dollar. This is not an actual rate, only a round number. The unit cost is then 3.20 × 12,000 = 38,400 so'm, and the gap of 14 pieces is worth 14 × 38,400 = 537,600 so'm.

Now suppose the rate used in a second report is 12,600. The unit cost becomes 40,320 so'm and the gap is worth 564,480 so'm, which is 26,880 more on a single SKU. Over hundreds of SKUs and larger quantities the difference adds up, and it is the reason two reports on the same count sometimes show different totals.

Typical mistakes

• Adding dollars and so'm in one column.
• Numbers stored as text, which a pivot table ignores.
• Rounding the unit cost to whole so'm before multiplying by large quantities. Round only the final result.
• Forgetting the third currency. If a supplier invoices in yuan, add CNY as a cost currency and a rate to match.
• Not recording the date of the rate. Two months later nobody knows which one was used.

Keep cost and selling price apart

A gap at cost tells you what the books lose. A gap at selling price tells you what the sales could have been. Both are useful, but they answer different questions, so give them separate columns with clear names, and let finance decide which one appears in the formal adjustment. For most counts, cost is the right basis for the books and selling price is only an extra for the owner.

InventoryHub's gap table includes values, based on the cost data from your books, and can be filtered or turned into a pivot table by SKU, warehouse and category.