Blog · Board and management reporting
Prepare a customer profitability report from ledger and activity exports. Follow the spreadsheet formulas, contribution rankings, cost-driver review, and reconciliation that make the report ready for management.
A customer profitability report ranks customers by the contribution left after direct and agreed service costs, with every total reconciled to the source files. Build the reconciliation first, then add rankings, cost-driver explanations, and actions. A polished account list is not ready for review while revenue or allocated cost remains unexplained.
Write the period, customer population, currency, and cost boundary at the top of the report. Name the ledger close used and the person responsible for approving allocations. The same customer ID must identify an account in every file, including accounts with credits or service activity but no sales.
Keep net revenue, product cost, service cost, and contribution in separate columns. The SEC's guide to financial statements distinguishes net revenue after returns and allowances, costs of sales, and operating expenses. Your management report should preserve those distinctions even when its customer allocations go beyond the financial statement layout.
Use the existing cost-to-serve calculation guide to establish driver rates. This report adds the review structure around that calculation: control totals, rankings, exceptions, and an accountable next step.
For an illustrative US distributor, report July 1 through September 30, 2026, in USD. Retain original transaction IDs so reviewers can find the postings behind each customer total.
| Sheet | Row meaning | Columns |
|---|---|---|
| Ledger | One posting, including negative credits | A posting date; B customer ID; C net revenue; D direct product cost |
| Activity | One dated activity summary | A activity date; B customer ID; C delivery drops; D service hours |
| Report | One customer | A customer ID; B revenue; C product cost; D drops; E service hours |
Do not join raw ledger rows directly to repeated activity rows. Aggregate each file by customer first, or the join can multiply both values. The resulting input summary is:
| Customer | Net revenue (USD) | Product cost (USD) | Drops | Service hours |
|---|---|---|---|---|
| Cedar | 160,000 | 100,000 | 80 | 40 |
| Harbor | 240,000 | 168,000 | 200 | 100 |
| Mesa | 100,000 | 55,000 | 20 | 10 |
| Total | 500,000 | 323,000 | 300 | 150 |
On a Setup sheet, put the start date in B1 and October 1, 2026, in B2, the first excluded date. With the customer ID in Report!A2, revenue in B2 is:
=SUMIFS(Ledger!$C:$C,Ledger!$B:$B,$A2,Ledger!$A:$A,">="&Setup!$B$1,Ledger!$A:$A,"<"&Setup!$B$2)
For product cost, use the same formula with Ledger!$D:$D as the sum range. Drops in D2 use:
=SUMIFS(Activity!$C:$C,Activity!$B:$B,$A2,Activity!$A:$A,">="&Setup!$B$1,Activity!$A:$A,"<"&Setup!$B$2)
For hours, substitute Activity!$D:$D. Fill down through row 4. Microsoft's SUMIFS documentation explains this multiple-condition summation and requires matching range dimensions. These formulas use Excel; Covirage is not affiliated with Microsoft.
Finance supplies these service pools for the same period. Their driver totals agree with the input summary above. On Rates, put pool cost in B, driver units in C, and rate in D, starting at row 2.
| Pool | Cost (USD) | Driver units | Rate (USD per unit) |
|---|---|---|---|
| Delivery | 12,000 | 300 drops | 40 |
| Account service | 9,000 | 150 hours | 60 |
| Total cost | 21,000 |
Rate in Rates!D2 is =B2/C2, filled down. On Report, add F delivery cost, G service cost, H contribution, I contribution percentage, and J contribution rank:
F2 =D2*Rates!$D$2
G2 =E2*Rates!$D$3
H2 =B2-C2-F2-G2
I2 =IF(B2=0,"",H2/B2)
J2 =RANK.EQ(H2,$H$2:$H$4,0)
Enter the expression after each cell label and fill down. Microsoft's RANK.EQ documentation confirms that order zero ranks the largest value first and assigns equal ranks to ties.
| Customer | Delivery cost (USD) | Service cost (USD) | Contribution (USD) | Contribution percentage | Rank |
|---|---|---|---|---|---|
| Cedar | 3,200 | 2,400 | 54,400 | 34.0% | 2 |
| Harbor | 8,000 | 6,000 | 58,000 | 24.2% | 1 |
| Mesa | 800 | 600 | 43,600 | 43.6% | 3 |
| Total | 12,000 | 9,000 | 156,000 | 31.2% |
Percentages are rounded to one decimal; calculations retain full precision. Harbor contributes the most dollars, while Mesa has the highest percentage. Keep both columns visible. The ten-account contribution example shows the arithmetic across a larger account set.
The customer totals must agree with the selected ledger and activity population. Separately compare the allocation to each cost pool. Use =SUM(Report!F2:F4)-Rates!B2 for delivery and the equivalent G-column check for service; both must return zero.
| Reconciliation step | Amount (USD) |
|---|---|
| Net revenue | 500,000 |
| Less product cost | 323,000 |
| Less delivery pool | 12,000 |
| Less service pool | 9,000 |
| Customer contribution | 156,000 |
| Less common overhead retained centrally | 19,000 |
| Operating profit under this report boundary | 137,000 |
The overhead is a supplied ledger input. The bridge proves 500,000-323,000-12,000-9,000-19,000=137,000. Total contribution percentage is =SUM(H2:H4)/SUM(B2:B4), not the average of customer percentages. Use the margin definitions when deciding what to label each subtotal.
Give finance ownership of the bridge and account managers ownership of the explanations. Harbor's larger delivery and service allocations should prompt a review of shipment patterns and contractual support. They do not prove that the spending can be removed.
Add an action owner, proposed change, approval needed, and review date beside each exception. Preserve the original report so the next review can distinguish a changed allocation rule from a changed customer relationship. A broader profitability report can roll these customer totals into the management review without recreating the account-level calculation.
Bring a sample of your own ledger and activity exports, with the customer mapping and agreed allocation rules. Covirage maps the columns, and its tools compute the contribution table and check the reconciliation; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See customer profitability in Covirage for the supporting approach.
Use the same cadence as the decisions it supports. A monthly report can support account reviews after the ledger closes, while a quarterly review can examine allocation assumptions. Keep the period length and cost boundary consistent before comparing reports.
Only when the report clearly labels that allocation and explains its purpose. Show customer contribution before common overhead, then reconcile to operating profit separately. An allocated expense does not automatically disappear if the company stops serving that customer.
Show both, but use contribution dollars for the primary economic ranking and contribution percentage as a companion. Revenue identifies commercial scale. Neither ranking establishes which account deserves investment until you review service commitments, avoidable costs, and the account's circumstances.