Case study · Profit engineering
Every payout, reconciled down to the SKU.
Marketplaces tell you what you sold. The payout tells you what you actually kept. I built a profit engine that reads the settlement files from every sales channel, maps every fee, penalty and chargeback to a single chart of accounts, and gives the business a monthly P&L they can trust for every account, product family and SKU.
Key decisions and results
Decisions
- Each transaction type × amount type × description is a mapping row the finance team maintains, and unmapped lines appear with their dollar value.
- Account-level costs are split across SKUs by each SKU’s share of that month’s revenue, so product rows add up to the account P&L.
- Daily profit uses saved actuals for settled months and a trailing three-month average for months that haven’t settled, and the report says which.
Results
- Five reports on one set of numbers: product rows add up to the account total, and the account total matches the payout.
- New fee types show up on the mapping screen instead of dropping out of the numbers.
What the engine produces for every SKU, every month
Illustrative · per $100 of sales · not real figuresThe problem
Every channel, its own accounting dialect
The business sells through several brand accounts on major online marketplaces, as a wholesale vendor to a large retailer, with platform-run fulfilment, and as a drop-ship supplier. Each channel reports money in its own format, on its own schedule, with its own identifiers. Some real costs never show up in any marketplace file at all.
-
Marketplace seller accounts
Settlement reports · listing IDs, fulfilment IDs, SKUs · multi-currencyHundreds of combinations of transaction type, amount type and description. Storage fees are billed a month late, and some lines only give a listing ID or a fulfilment-center ID instead of the seller’s SKU.
-
Wholesale vendor program
Remittances · invoices · contra-COGS / co-opDeductions arrive as short, cryptic invoice codes. Shortage claims, price protection and co-op agreement penalties all come through as payment adjustments.
-
Marketplace with platform fulfilment
Reconciliation reports · item IDsCommissions, fulfilment, storage, disposal, loans and reserves are all listed together in one payout, and each needs a different place in the P&L.
-
Drop-ship supplier program
Invoice & payment files · ±3-month lagA drop-ship payment can land months after the order. Deductions apply to the whole account, not to any one product.
Off-platform costs complete the picture: product cost and returned-goods cost from the order-management system, and bills, checks and credit-card charges from the accounting system. Credit-card charges include ad spend, which appears as one lump sum rather than per product. The old answer to “is this product profitable?” was a spreadsheet rebuilt by hand every month.
The approach
One pipeline, one chart of accounts
Every channel runs through the same six stages, so one marketplace’s fulfilment fee and another’s end up on the same P&L line and can be compared directly.
Ingest
Scheduled jobs pick up downloaded settlement, invoice and remittance files, parse them and save them to MySQL.
Map
Each raw transaction signature is mapped to a named P&L line. Unmapped lines are flagged along with their dollar value.
Resolve
Marketplace listing and fulfilment IDs are converted back to seller SKUs, and SKUs are grouped into parent product families.
Enrich
Adds product cost, returned product cost, shipping, ad spend, delayed storage fees and warehouse handling.
Allocate
Costs that belong to the whole account are split across SKUs by their share of that month’s revenue.
Report
Monthly P&L by account, parent or child SKU, filterable by manager, with Excel export.
Under the hood
Under the hood
A mapping layer the finance team maintains, not the developers
For marketplace seller accounts, each combination of transaction type × amount type × description is saved as a mapping row. A mapping screen shows every combination that is not yet mapped, together with its total dollar value, so a new fee type the marketplace introduces is flagged before it can distort a month’s numbers.
The wholesale vendor program had no clean signature to map on. There, I converted the finance team’s spreadsheet rules into an ordered classifier. It reads invoice-number suffixes and prefixes, descriptions, co-op funding type and linked invoice counts.
| Remittance signal | Becomes |
|---|---|
| description starts SHORTAGE CLAIM or PRICE CLAIM | Accrued expense |
| audit or price-protection invoice codes | Platform penalty |
| straight-payment co-op + linked contra-COGS invoices | Co-op agreement penalty |
| early-payment discount lines | Interest expense |
| return and receivable adjustments | Retailer reserve |
| drop-ship lines or invoices with linked POs | Receivable, excluded |
Costs that belong to the whole account are split fairly, and nothing goes missing
Accounting-system bills, checks, non-advertising card spend and drop-ship program deductions are all charged to the account, not to a product. The engine gathers them into an account-level bucket, then splits each month’s total across SKUs by their share of that month’s revenue. Because the split uses revenue shares, the product rows always add up exactly to the account P&L.
Ad spend works the same way with a different weighting. The card-billed ad total is the true figure, and each SKU’s share of campaign spend decides how much of it that SKU carries. Payroll accounts are excluded, and commission bills go to their own line.
Illustrative: $21,460 of account-level overhead (5.4% of $397,400 monthly revenue) split by each product family’s share of that revenue (bar). The split always adds back to $21,460.
Daily profit using settled costs, even before the month has settled
Managers need profit every day, but settlements only finalise monthly. The newest report combines the two. Once a month is reconciled, its per-SKU expenses are saved. Days in settled months use those actual figures, spread evenly across the month. Days in months that have not settled yet use an estimate: the average of the latest three settled months.
The estimate uses one shared three-month window and always divides by three, even for SKUs that were missing some of those months. As a result, the per-SKU estimates always add up to the account-wide average. The report states its basis in plain words, for example “actual Jan, estimate from Nov–Jan”, and warns when a completed month has not been saved yet.
- Sepnot available · $0
- Octnot available · $0
- Novactual
- Decactual
- Janactual
- Febestimate · avg Nov–Jan
- Marestimate · month in progress
Illustrative timeline. Grey: before settlement data exists, so these costs are counted as $0 and the report says so. Green: saved actuals. Amber: trailing three-month estimate.
What shipped
Five reports, one set of numbers
-
Marketplace Settlement Profit
Marketplace seller · all brand accountsAccount and product P&L built from settlement reports. Converts foreign currency to USD at the rate for each posting date, and books storage fees in the month they apply to rather than the month they are billed.
- ~20 P&L lines, each as % of revenue
- Parent or child SKU grouping
- Monthly & range-total Excel
-
Vendor Settlement Profit
Wholesale vendor + direct fulfilmentSplits revenue, product cost, shipping, co-op fees and promotions between wholesale and drop-ship. Remittance deductions are sorted by the rules-based classifier.
- Penalty & shortage-claim visibility
- Invoice mapping review screen
-
Fulfilment Marketplace Profit
Marketplace + platform fulfilmentReconciliation-report P&L that sets platform loans and reserves apart from real costs, with product-level results by marketplace item.
- Mapping screen hides unmapped zero-value lines
- Per-item manager ownership
-
Drop-ship Settlement Profit
Drop-ship supplier programMatches invoices across a ±3-month window to account for payment lag, and splits account-wide deductions across products by revenue.
- Invoice filtering by cost line
- Product & account views
-
Daily Parent Profit
Marketplace sellerDaily operating profit by product family, including settled monthly expenses, with a comparison header showing change against an earlier period.
- Actual vs. estimate, labelled
- Cached, auto-refreshed
- Growth vs. comparison range
Outcome
From “what did we sell?” to “what did we keep?”
One version of profit
Finance, leadership and product managers read the same figures. Product rows add up to the account total, and the account total matches the payout.
Ownership at the SKU
Each product family has a named manager. Non-admin users see only their own products, so every manager is accountable for the profit on their catalogue.
New fees get noticed
A fee type the system has not seen before appears on the mapping screen with its dollar value, instead of silently dropping out of the numbers.
Two bottom lines are always shown side by side. Net income covers everything the platform and the business paid. Net income including in-house expenses adds the cost of the business’s own warehouse handling, charged per unit sold. The gap between them shows the cost of fulfilling orders in-house.
Built with
Spring Boot service, plain JDBC and scheduled jobs, deployed on the company’s existing internal platform.
- Java 17
- Spring Boot 2.7
- Spring JDBC
- MySQL
- Thymeleaf
- Apache POI (Excel)
- OpenCSV
- @Scheduled ingestion
- Order-management system integration
- Accounting system exports