Knowing the real profit on every sale, down to the last penny of fees

The client

A UK online retailer selling across a dozen Amazon marketplaces, its own web shop, eBay and TikTok Shop.

The challenge

Every channel has its own costs: commission, fulfilment fees, storage, postage, packaging, payment charges, promotions, VAT and refunds. The sale price says little about whether an item made money, and the real figures arrive late, in different formats, from a dozen different places. The business needed profit worked out as soon as an order arrived, then corrected automatically as the actual numbers came in, with every change traceable.

What we built

• A profit calculation for every item on every order, across nearly 30 kinds of income and cost
• Automatic replacement of each estimate with the actual figure as it arrives, keeping the earlier figure for audit
• Import and matching of marketplace settlements, courier invoices and payment-provider fees
• A reconciliation report that checks, order by order and fee by fee, what Amazon paid against what the system expected
• Profit reports by item, channel, supplier, brand, category and buyer, with Excel export
• A "Monday morning" dashboard with 17 views, including performance against budget
• Stock valuation that keeps every batch of an item at the same average cost
• A predicted profit at today's price on every listing, used by the pricing tools

Where the figures come from

When an order arrives, every line is costed straight away from rate cards the business maintains: the marketplace's commission, the postage on the cheapest suitable courier service, packaging chosen by the box-selection tool, payment fees, VAT, and the average cost of the stock actually on the shelf.

Then the real figures replace the estimates, one by one:

• Payment fees, from PayPal, Adyen (which also handles Klarna and Clearpay) and Braintree, collected automatically through the day and shared across the items on each order
• Postage, from courier invoices: finance uploads each courier's invoice spreadsheet, and the system recognises the format and matches every parcel to its order by order number and tracking number
• Amazon's settlements, downloaded overnight. Each one lists what Amazon actually paid and charged for every order: the sale, commission, fulfilment fees, promotions, refunds, chargebacks, claims, advertising and Amazon's own shipping labels
• Marketplace finance data, such as TikTok Shop's per-order fees and the discounts it funds itself
• Stock cost, from purchase orders and deliveries, at the average value of the stock held
• Returns and refunds, from each returns case: the refund, return postage, the value of the item coming back, and any money recovered from a courier, supplier or marketplace

Every change explained

Each figure records where it came from and what it replaced, so finance can see exactly why an order's profit changed and how far the estimates were out. Foreign currency is converted at the European Central Bank rate for each settlement, and staff can override a figure by hand, which is recorded too.

Checking the money

A reconciliation report compares Amazon's settlements with the system's figures for every order and every type of fee. It sorts the differences into "matches", "not settled yet" and "needs a look", and lists any money that doesn't belong to an order. From 2018 to 2025 the system also matched each Amazon settlement against the entries in the retailer's accounts in Xero, trying combinations of entries until they added up to the settlement total.

Technical details

Each profit line has a type, source, effective date and amounts in order currency and GBP. A newer figure links the one it replaces through a superseded-by pointer, so the full estimate-to-actual history is kept across around 29 profit types and a dozen sources.

Amazon V2 settlement reports are fetched overnight from SP-API, stored raw, then parsed separately into settlement lines. A configurable line-type table maps each Amazon transaction type to a profit type, including VAT-netting of commission after Amazon's August 2024 change. Courier invoices are parsed from password-protected Excel and matched set-based in SQL. PayPal, Adyen and Braintree feed a general reconciliation ledger.

The reconciliation report is a two-pass, server-side aggregation: order-anchored by marketplace reference, then settlement-anchored for orphans and non-order transactions. Reporting reads a precomputed profit cube, and the dashboard uses a nightly fact table plus a live query for today. Xero invoices, bank transactions, journals and payments were downloaded from 2017 to 2025.

• C#
• .NET
• ASP.NET MVC
• SQL Server
• Entity Framework
• Amazon SP-API