Blog · Finance metrics and formulas · Finance and FP&A teams
Use this cost to serve analysis template to organize cost pools, customer activity and contribution in a workbook. Copy the layouts, fill them from your exports and prove that allocated costs match the ledger.
A cost to serve analysis template connects the cost of service activities to the customers that use them, then shows what remains of gross profit. Use separate tables for cost pools, customer drivers and the resulting contribution. The layouts below are copyable workbook structures; populate them with your own period's exports.
This page supplies the reusable layout. For the detailed calculation method, including allocation choices, use how to calculate cost to serve per customer in Excel.
Put the reporting period, currency, customer identifier and allocation policy at the top of a control sheet. Also name the person who approves the pool mapping and the person who owns the activity export. A reusable template needs those decisions written down so next month's workbook means the same thing.
For this example, a US distributor measures one month in USD. Product cost stays in cost of goods sold. Order administration, outbound delivery and customer support go into separate service pools. Corporate overhead is excluded, so the final figure is contribution after the specified service costs, not net profit.
The approach follows activity-based allocation: identify pools, calculate rates and apply them to activity consumed. OpenStax describes those stages, including their use for services. The cost-to-serve glossary explains the boundary between product cost and the cost of serving an account.
Create a sheet named Pools. Row one contains these headers, with the example records starting in row two. Keep the source ledger account and export reference alongside the calculation columns in the working file.
| A: Pool | B: Monthly cost (USD) | C: Driver quantity | D: Driver unit | E: Rate (USD per driver) |
|---|---|---|---|---|
| Order administration | 6,000 | 300 | Orders | 20.00 |
| Outbound delivery | 9,000 | 150 | Deliveries | 60.00 |
| Customer support | 12,000 | 240 | Support hours | 50.00 |
| Total cost | 27,000 |
In E2, enter the following and fill down through E4:
=IF(C2>0,B2/C2,NA())
Do not sum column C: orders, deliveries and hours are different units. A zero denominator should stop the allocation for review, rather than quietly produce a zero rate. Decide whether it means missing activity, a genuinely inactive pool or unused capacity that should stay separate.
The rates are $6,000 / 300 = $20.00 per order, $9,000 / 150 = $60.00 per delivery and $12,000 / 240 = $50.00 per support hour. Keep full precision in the workbook; the displayed rates here are exact to cents.
Create Customers with one row per customer for the same month. Use a stable account ID in the actual workbook, rather than matching on a customer name that someone can change.
| A: Customer | B: Revenue (USD) | C: Product COGS (USD) | D: Orders | E: Deliveries | F: Support hours |
|---|---|---|---|---|---|
| Alder | 60,000 | 36,000 | 120 | 50 | 60 |
| Birch | 40,000 | 24,000 | 100 | 60 | 100 |
| Cedar | 20,000 | 12,000 | 80 | 40 | 80 |
| Total | 120,000 | 72,000 | 300 | 150 | 240 |
Take revenue after credits and discounts. Take product COGS from the same sales population. Count orders once per order ID, deliveries once per shipment ID, and support hours from approved time records. An invoice with several product lines must not create several orders.
Preserve a transaction-detail sheet for each export. If support detail has customer IDs in column A and hours in column B, summarize it against the customer ID in A2 with:
=SUMIFS(Support!$B$2:$B$1000,Support!$A$2:$A$1000,$A2)
Microsoft documents SUMIFS as adding values that meet specified criteria. Expand the ranges to cover the complete export and check for unmatched customer IDs. Covirage is not affiliated with Microsoft.
Add columns G through J to Customers. G is gross profit, H is allocated service cost, I is contribution after service cost, and J is that contribution divided by revenue.
Enter these formulas in G2, H2, I2 and J2, respectively:
=B2-C2
=D2*Pools!$E$2+E2*Pools!$E$3+F2*Pools!$E$4
=G2-H2
=IF(B2<>0,I2/B2,NA())
Fill down through row four and format J as a percentage. The filled template returns:
| Customer | Gross profit (USD) | Service cost calculation (USD) | Service cost (USD) | Contribution (USD) | Contribution margin |
|---|---|---|---|---|---|
| Alder | 24,000 | 120 × 20 + 50 × 60 + 60 × 50 | 8,400 | 15,600 | 26.0% |
| Birch | 16,000 | 100 × 20 + 60 × 60 + 100 × 50 | 10,600 | 5,400 | 13.5% |
| Cedar | 8,000 | 80 × 20 + 40 × 60 + 80 × 50 | 8,000 | 0 | 0.0% |
| Total | 48,000 | 27,000 | 21,000 | 17.5% |
The total margin is 21,000 / 120,000 = 17.5%, not the simple average of customer percentages. Read the result alongside margin by account, which supplies the broader customer profitability context.
Each activity total must equal its matching denominator in Pools. Each pool must then allocate fully: 300 × $20 = $6,000, 150 × $60 = $9,000 and 240 × $50 = $12,000. Allocated service cost is therefore $27,000, exactly the pool total.
Add a final difference cell:
=SUM(Customers!H2:H4)-SUM(Pools!B2:B4)
It must return zero. Also prove the contribution bridge: $120,000 revenue minus $72,000 product COGS minus $27,000 service cost equals $21,000. Reconcile each input total to its source export before approving the month. Once the workbook is stable, a cost to serve analysis dashboard can organize the recurring review.
Bring your completed cost-pool and customer-activity workbook to Covirage. Covirage maps the columns, its tools compute and check the allocations and contribution, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, which takes about a minute, and bring a sample of your own data.
See customer profitability for the business questions this analysis supports.
Include the reporting period, cost-pool definitions, source references, driver units and customer identifiers. Keep customer revenue and product costs separate from service activities. Add reconciliation cells so a reviewer can see whether all costs and activity records were included exactly once.
No. Allocate costs only within the scope you define and with a defensible driver. Keep corporate costs or unused capacity visible separately when they lack a suitable customer driver. State those exclusions so the resulting contribution is not mistaken for company net profit.
Revenue is convenient but may not explain the work customers cause. An account with small, frequent orders can consume more service resources than a larger account. Prefer measured orders, deliveries, support hours or another activity that reasonably explains each cost pool.