Skip to main content

Frac CFO

By Jonas Tyrone Lobaton, MSc Quantitative Finance, FMVA®
Founder, Frac CFO · Updated October 10, 2026

Client profitability analysis identifies which customers contribute to your business after accounting for revenue, genuinely traceable costs, service effort and defensible shared-cost allocations. An Excel or Google Sheets dashboard can show the result without replacing your accounting system.


Quick guide:

  • Separate contribution, fully allocated profit and avoidable/incremental profit: each supports a different decision.
  • Never charge direct labor, support hours, write-offs or shared costs twice. Convert non-billable hours to a cost only if they are not already in another category.
  • For TDABC, divide resource-pool cost by practical capacity and keep unused capacity separate from customer charges.
  • Reconcile client totals to the P&L before ranking accounts or proposing changes.
  • A client with negative allocated profit may still make a positive contribution; test retention and repricing decisions using avoidable costs.

Frac CFO
Know Which Clients Actually Make You Money
Frac CFO helps businesses use existing spreadsheets for customized financial modeling, forecasting, optimization, and dashboards that improve financial visibility.

Table of Contents

Client profitability analysis: correct formulas and a worked Excel example

Client profitability analysis links revenue to the costs of serving each customer. The key is to distinguish direct contribution, fully allocated client profit, and the incremental profit relevant to a pricing or retention decision. They answer different questions.

  • Net client revenue = invoiced sales less credited invoices, refunds, and revenue write-offs. If these reductions are already reflected in revenue, do not subtract them a second time.
  • Contribution before shared overhead = net revenue − attributable variable/direct costs − client-specific support labor costs not included elsewhere.
  • Fully allocated client profit = contribution before shared overhead − reasonably assigned shared overhead. Label the result as allocated: changing the allocation driver can change a client’s rank without changing total company profit.
  • Incremental/avoidable profit for retaining or dropping a client = expected revenue kept or lost less only the costs that truly change because of the decision. Shared fixed overhead usually does not disappear when one customer leaves.

No double counting: Non-billable hours are a quantity, not another expense. Convert them to cost with a documented hourly resource rate. If labor is already charged to direct costs or overhead, do not add it again. Apply the same rule to bad debts, discounts, and write-offs already netted against reported revenue.

Worked example: three clients, with Excel formulas

Below is a fictional monthly example in USD. “Other direct costs” exclude client support labor, and “Allocated overhead” excludes the support-labor resource pool.

Client-level profitability ($, illustrative)
Item Client A Client B Client C Total
Net revenue $12,000 $8,000 $6,000 $26,000
Other direct costs $5,000 $4,000 $3,000 $12,000
Client support labor $1,000 $600 $2,200 $3,800
Allocated shared overhead $1,500 $1,000 $1,200 $3,700
Fully allocated client profit $4,500 $2,400 ($400) $6,500
Allocated margin 37.5% 30.0% −6.7% 25.0%

In a sheet with revenue in column B, other direct costs in C, client support labor in D, and shared overhead in E, the formula for allocated client profit (row 2) is =B2-SUM(C2:E2). For its profit margin, use =IFERROR(F2/B2,0) in G2 and format it as a percentage. The sums give $4,500, $2,400 and −$400 respectively.

Interpretation: Client C has a $400 loss after allocation. That alone is not a reason to terminate the account: identify avoidable costs, spare capacity, expected upsell, switching costs, and the impact on remaining overhead before making that decision.

Reconcile unused labor capacity: Suppose the support resource group costs $5,000 per month and has 125 practical working hours. Its time-driven rate is =$5000/125=$40 per hour. Clients A, B and C consume 25, 15 and 55 hours, giving $1,000, $600 and $2,200 charged above. Total client-assigned labor = $3,800; the remaining $1,200 unused-capacity cost stays visible separately. If all other costs are captured, company profit after unused capacity is $6,500 − $1,200 = $5,300. Do not allocate that $1,200 to customers again.

What data to pull and how to allocate costs pragmatically

Start with exports you already have. No ERP migration is necessary for a first controlled model, but client IDs and cost classifications must be consistent across sources.

  • Invoices, credits and payments: Client ID, service or product, period, invoice amount, credits, discounts and write-offs. Reconcile net recognized revenue to the general ledger rather than treating cash receipts as revenue.
  • Direct costs: Materials, subcontractors, delivery and other traceable costs, including attributable payroll only when it is not already included in a separate labor pool.
  • Time logs: Client, staff/resource group, activity, hours and whether the work was billed. Both billable and non-billable client-service activity consume capacity.
  • Payroll and capacity: Loaded cost by resource group, hours available, and practical capacity for the period. Distinguish customer-used time from unused available time.
  • Shared overhead: Define each pool, exclude already-direct-charged costs, and use a plausible driver (transactions, tickets, hours or storage volume) only where the relationship makes operational sense.

Controls before ranking: (1) reconcile client revenues and credit notes to finance totals; (2) reconcile direct expenses to the chart of accounts; (3) match tracked hours and capacity to resource costs; (4) display unmatched clients, missing cost rates and duplicate transactions; (5) report the unallocated/unused balance rather than silently forcing the whole ledger into customer costs.

For a small service business, Excel Tables and SUMIFS often suffice. Use a client ID mapping table (and XLOOKUP when needed) instead of joining by display names, which change.

Choosing a costing method: direct contribution, ABC or TDABC

Choose the least complex model that still captures meaningful differences in customer service requirements. None of these methods makes estimates perfectly precise.

  • Direct/contribution margin: Subtract truly traceable costs to reveal what the account contributes before shared overhead. Use it as the starting point for account decisions, not as a full-company profit statement.
  • Activity-based costing (ABC): Use separately defined pools and measurable drivers such as orders processed, tickets resolved or picking activities. Exclude costs already directly traced to the client.
  • Time-driven ABC (TDABC): Rate = total cost of supplying a resource group ÷ practical capacity. Multiply that rate by the time needed for each client activity, using reasonable time estimates or time logs. Track unused capacity cost outside customer allocations.

Practical vs theoretical capacity: Practical capacity allows for normal breaks, training, meetings and similar unavailable time. It is not simply total contractual hours. As an example, a $5,000 monthly support pool / 125 practical hours yields $40 per hour; 95 client-used hours cost $3,800 and leave $1,200 of unused-resource cost visible separately. If some non-billable work serves clients, charge its actual time once through the same system.

Document drivers and cost-pool exclusions. Re-test client rankings when time estimates, activity mix or allocation rules change. For the underlying framework, see Kaplan and Anderson on time-driven ABC.

Excel workflow: from raw exports to ranked clients

  1. Build a Client Map. Create one stable ClientID and assign any alternate billing names, branches and projects to the right account.
  2. Import net revenue. Load invoices plus credits, refunds and write-offs using consistent period rules. Aggregate with a PivotTable or SUMIFS; reconcile totals against the ledger.
  3. Import direct expense records. Assign traceable costs to client IDs and report unmapped lines as an exception list. Do not mark a missing code as zero expense.
  4. Create the labor cost-rate table. Define cost pools and practical capacity, then compute an hourly rate (=PoolCost/PracticalHours). Sum client usage with SUMIFS(TimeLog[Hours],TimeLog[ClientID],[@ClientID]). Labor cost = hours × the rate, provided those costs are not already included elsewhere.
  5. Allocate genuinely shared overhead. For each defensible pool, assign cost based on consumption of the chosen driver. Keep unassigned resources and non-client costs visible.
  6. Compute three views. Contribution, fully allocated client profit, and decision-specific avoidable/incremental profit. Rank by the metric suited to the decision, and flag clients whose ordering changes under different allocations.
  7. Check the bridge to the P&L. Sum allocated client profit and subtract separately disclosed unassigned/unused resource cost and any costs excluded from client-level analysis. Investigate residuals instead of forcing every client subtotal to equal company profit.

For the worked example, a simple single-row Excel formula is =B2-SUM(C2:E2), where revenue and the mutually exclusive cost categories occupy B:E. The support-labor rate and source time logs should be maintained in supporting sheets even if the final profitability dashboard fits on one screen.

Scenario test: A 10% price increase does not guarantee 10% more profit. Adjust volumes, customer churn risk, variable delivery costs, price elasticity and support demand before proposing repricing. Run best/base/worst cases instead of projecting a fixed markup into perpetuity.

Turn the results into action: reprice, redesign, retain or exit

A negative fully allocated margin is an investigation trigger, not an automatic instruction to fire the customer. A client may still cover substantial avoidable costs and help absorb shared fixed resources.

  • Reprice: Model whether higher fees offset service intensity after plausible changes in order volume, churn and delivery effort.
  • Redesign service: Introduce change-order charges, clearer support allowances, paid rush work, minimum fees or scheduled service windows when commercially appropriate.
  • Retain and invest: Protect high-contribution clients with strategically important growth potential, even if a disputed allocation method makes their accounting margin appear weak.
  • Exit selectively: Consider ending an account only when expected incremental benefit of retaining it is negative after considering costs that really disappear, capacity redeployment, contract terms and risks.

Example: Client C above reports −$400 allocated profit, but earns $800 before shared overhead. If the $1,200 overhead assignment remains after losing the client, deleting Client C from the roster does not automatically improve company profit by $400. Also verify which direct costs and labor hours would truly be saved or redeployed. That is why retention decisions need a separate incremental scenario.

Client profitability dashboard: useful KPIs and the whale curve

Keep the output decision-focused: who contributes cash to operations, what customer service actually consumes, and where profit would change if pricing or scope changes.

  • Ranked client contribution: Revenue less attributable variable and client-service costs, before allocations that won’t disappear if the client leaves.
  • Fully allocated profit and margin: Useful for revealing cost-to-serve patterns and benchmarking, provided the allocation method is documented.
  • Share of unprofitable clients: Count of clients with a negative selected metric divided by clients analyzed. Specify which profit definition is used.
  • Top service drivers: Support hours, tickets, rush shipments, special handling and other disproportionate demands.
  • Unused capacity and reconciliation: Resource pool cost, costs assigned to clients, unused capacity, unmapped records and the residual bridge back to the company P&L.

How to build the client profitability whale curve

Sort clients by fully allocated client profit from highest to lowest. Plot cumulative client profit on the Y-axis and clients (or cumulative client percentage) on the X-axis. Do not confuse this with plotting cumulative profit against cumulative revenue.

In the three-client example, the curve rises to $4,500 after Client A, reaches $6,900 after Client B, then declines to $6,500 after Client C. The downward segment signals an allocated loss for C; the company still needs an incremental analysis before deciding to exit. When total profit is zero or negative, showing the Y-axis in dollars avoids misleading percentage-of-total calculations.

Three-client whale curve · cumulative allocated profit ($)

$7k$0$4,500$6,900$6,500Client AClient BClient C

The simple example above is an explanatory graphic, not a screenshot of the Excel file. For a real workbook, chart the cumulative-profit column after sorting by descending client profitability. Refresh rankings monthly and review major costing assumptions and pricing scenarios quarterly or when activity changes materially.

Spreadsheet-first client profitability analysis at Frac CFO

Start with Excel or Google Sheets when you can reliably export invoices, expenses and time data, and when someone owns the monthly reconciliation. The analysis can live on a one-page dashboard with supporting source-data and calculation tabs; a literal single-tab file is usually less auditable.

A tailored engagement can include a cleaned ClientID map, clearly defined cost pools, a customer P&L with separate contribution and allocated-profit views, a reconciliation to company profit, a whale curve, and repricing scenarios. We scope the work around the number of clients, time periods, cost drivers and input systems before quoting.

Frac CFO’s Profitability and Pricing Analysis is an existing segment-focused offer with tier limits. A full customer-level ABC or TDABC implementation may exceed that standard scope and should be quoted as a custom project rather than implied to be included in the base package.

When client profitability analysis pays off fastest

The best indicators are uneven support demands, complex delivery costs, concentrated revenue, repeated discount requests, or accounts whose profitability cannot be explained from gross margin. There is no evidence-based rule that it works only for 30–100 customers. A ten-client consulting practice or a retailer with hundreds of commercial accounts can both benefit if costs and decisions justify the effort.

Likewise, implementation time depends on data readiness. A clean ledger and timesheet export may support a quick prototype; reconciling billing entities, multi-service engagements, poorly coded costs, non-billable time and multiple systems may require significantly more work. Price the project after reviewing those inputs, not by assuming every model takes just a few hours.

Start with a sample period. Validate three or four representative customers, check the P&L bridge and unused capacity, then scale to the remaining accounts. This reduces the risk of automating an incorrect allocation rule.

Get a client profitability model scoped to your business

Find out which clients actually make you money — and why. Frac CFO can build an Excel or Google Sheets model showing net revenue, direct costs, cost-to-serve, attributable contribution, allocated profit, unused capacity and potential pricing actions.

Because detailed client-level ABC/TDABC may go beyond the standard profitability product’s client/segment and scenario limits, request a custom scope based on your number of customers, data systems and cost drivers.

Request a custom client profitability model

For a smaller product, service or segment analysis, see the separately scoped Profitability and Pricing Analysis offer.

Client profitability analysis FAQ

What is client profitability analysis?

It is a client-level view of net revenue, traceable cost, customer-service effort and, when useful, defensibly allocated shared overhead. Keep contribution and fully allocated profit separate so decision-makers can see whether a loss is driven by actual client costs or allocation policy.

How do I calculate customer profitability in Excel?

For an allocated client-profit row, use =B2-SUM(C2:E2) with net revenue in B and nonoverlapping cost categories in C:E. Margin is =IFERROR(F2/B2,0). Reconcile those client totals to the company P&L, accounting for unused capacity and unassigned costs separately.

What is the difference between contribution and allocated profit?

Contribution excludes shared overhead that may not change with account volume. Fully allocated profit also assigns a portion of common costs. Contribution is often more relevant for a short-run retention decision; allocated profit highlights how intensely an account uses resources and whether pricing needs review.

How should I allocate shared overhead between clients?

Use traceable usage drivers when possible—hours, orders, tickets or transactions—and document each cost pool. Test whether changing the driver changes rankings. Never allocate the same payroll or expense item both directly and through overhead.

How do you calculate the cost of non-billable work?

Record the client-related hours and multiply by a documented cost per usable resource hour. For TDABC, calculate that rate from the resource-group cost divided by practical capacity. Count the time once, whether or not it appears on an invoice.

Should I stop serving a client with a negative allocated margin?

Not on that metric alone. Calculate avoidable and incremental profit, including whether labor capacity can be redeployed and which fixed costs will remain. Test repricing, service changes and expected future value before exiting.

How often should I refresh the analysis?

Monthly summaries are useful where billing and resource use change materially. Review allocation rates when the cost structure or practical capacity changes, and conduct deeper pricing reviews quarterly or at renewal. A slower cycle may be enough for stable contracts.

About the author

Jonas Tyrone Lobaton is the founder of Frac CFO. He holds a Master’s degree in Quantitative Finance and the FMVA® certification, with work spanning financial modeling, forecasting, optimization, dashboards, business finance, and spreadsheet automation.

Sources and further reading

The figures and formulas in the worked example are illustrative, not real client data. Allocation methods should be validated against each business’s accounting records.

7 Responses

Leave a Reply