Portfolio / Case study 02

CostingInventoryE-commerce

COGS and inventory valuation model

A weighted-average inventory and COGS model that gave the business product margins based on real landed costs.

ClientVankea
IndustryE-commerce, campervan furniture
My roleAccountant
ToolsExcel, Shopify data, Zoho Books
COGS and inventory valuation model for Vankea

Project walkthrough

See how the work was put together

COGS and inventory valuation model for Vankea, project walkthrough
COGS and inventory valuation modelClick to play
finzain · project log

$ start project "COGS and inventory valuation model"

✓ Brief agreed with Vankea

✓ Collected purchase, freight and duty data by shipment

✓ Built a weighted-average costing model at SKU level

✓ Reconciled stock movements against sales records

→ Review with the client…

● Delivered and handed over

The project

Problem, approach and result

01

The challenge

Product margins were estimated from supplier prices alone. Freight, duties and batch-to-batch price changes were not reaching the cost of goods sold.

02

What I did

  • Collected purchase, freight and duty data by shipment
  • Built a weighted-average costing model at SKU level
  • Reconciled stock movements against sales records
03

The result

Margins by product were based on landed cost, which made pricing and reorder decisions far easier to defend.

How the costing model works

The model uses weighted-average costing at SKU level. For every shipment I collected the purchase price, the freight and the duties, and added them together to get a landed cost per unit. When new stock arrives at a different price, the model blends it with the stock already on the shelf in proportion to quantity, so the average cost moves smoothly instead of jumping with each batch.

Each sale is then costed at the current average, which means the cost of goods sold follows what the business really paid to get the product into the warehouse. I reconciled the stock movements against the sales records so that any difference between what the books said and what was physically sold could be traced back to a shipment or an entry.

Why supplier price alone gives a misleading margin

A product that looks profitable on the supplier invoice can look very different once the freight and duties for that shipment are added. For an e-commerce business that imports its materials, those extra costs change from batch to batch, so estimating margin from the supplier price alone slowly drifts away from reality. The model tracks the items that usually get missed:

  • Freight and shipping charged per shipment
  • Import duties and handling fees
  • Price changes between supplier batches
  • Stock that is on hand versus stock already sold

What it changes for pricing and reordering

With margins based on landed cost, the conversation about pricing becomes much easier to defend. You can see which products carry the business and which ones only look good on paper, and you can decide what to reorder with real numbers in front of you.

If you import stock or buy through several suppliers, a good first step is simply recording freight and duty against each shipment rather than as a monthly lump. It is a small habit that makes every later report more reliable.

Gallery and links

Screens from the project

Figures are anonymised. Click an image to enlarge it.

COGS and inventory valuation model main screen
Main view
COGS and inventory valuation model detail screen
Detail
COGS and inventory valuation model report screen
Report

Let's connect

Want something like this for your business?

Book a free 30-minute call on Google Meet, or reach me directly.