How to Create a Client Profitability Report—with a Worked Example

A client profitability report brings each client's revenue, delivery costs and actual working hours into one view. It helps freelancers, consultants and small service businesses see which relationships generate a worthwhile return and which need a closer review.

To create one, start with consistent project records, calculate the same measures for every completed project, then group the underlying amounts by client. This guide includes a worked report and spreadsheet formulas you can adapt in Excel or Google Sheets.

What should a client profitability report include?

A useful report answers three questions: what did the client generate, what did serving them require, and what should you review next? A client revenue report answers only the first.

• Reporting basis: period, currency and whether the report covers completed projects or another clearly defined basis.
• Revenue: the fee after relevant discounts or credits, using a consistent basis.
• Direct costs: costs attributable to the work, such as project-specific contractors or materials.
• Actual hours: delivery, meetings, revisions and client-specific administration.
• Internal labor cost: an explicit allowance for the working time used.
• Client profit and margin: the amount remaining after the costs included in the report, and that amount as a percentage of revenue.
• Effective hourly rate: revenue less direct costs, divided by actual hours.
• Review notes: missing records, unusual costs, scope changes and the next action.

For the broader customer-analysis principle, BDC's customer profitability guide explains why discounts and the cost of serving a customer belong in the comparison. The simplified service-business example below uses its own illustrative figures.

1. Choose a consistent reporting basis

For a first report, use completed projects from one review period. Keep active work in a separate view so that a fee recorded early does not hide hours still required to finish delivery. If you report on monthly work for a long-running engagement instead, match the revenue and costs to that same work period and explain the basis.

Do not switch between invoices issued, cash collected and completed project revenue within the same comparison. They answer different questions. Track unpaid invoices separately: a positive project result does not establish that payment has arrived.

Use one currency, or convert figures consistently and document the conversion basis. Where sales tax or VAT is collected on behalf of an authority, avoid treating it as service revenue. Use a consistent cost basis for recoverable versus nonrecoverable taxes, appropriate to your business records.

2. Build one reliable project record

Begin with one row per project. Use a consistent client name so the same customer does not appear under several spellings. Record a project identifier, completion date, revenue, direct costs, estimated hours, actual hours and internal hourly cost.

Include time spent answering project messages, holding meetings and completing revisions. If some hours are estimates, label them. A precise-looking margin based on incomplete time records deserves a data-quality flag.

Decide which costs belong where. If a contractor invoice already includes that contractor's labor, do not subtract the same labor again through an hourly allowance. Keep shared business overhead separate unless you have chosen and documented an allocation method.

3. Calculate client profit, margin and effective hourly rate

For the following management report, use these definitions:

• Internal labor allowance = actual hours × internal hourly cost.
• Client profit after included costs = revenue − direct costs − internal labor allowance.
• Client margin = client profit ÷ revenue × 100.
• Effective hourly rate = (revenue − direct costs) ÷ actual hours.

The internal hourly cost values the time used; it is not your selling rate. For an owner, it may be a planning allowance rather than a wage actually paid. State what it includes and apply it consistently.

In this example, general overhead, financing costs and taxes are not allocated. The result is therefore a management measure after the stated project costs, not final business net profit or take-home pay.

Client profitability report example

Suppose three clients have completed work in the reporting period. All amounts are illustrative US dollars, and the internal labor allowance is $30 per hour for each client.

• North Studio: $3,000 revenue, $300 direct costs and 60 actual hours.
• Harbor Consulting: $2,400 revenue, $200 direct costs and 32 actual hours.
• Maple Services: $1,800 revenue, $150 direct costs and 45 actual hours.

Work through North Studio

The labor allowance is 60 × $30 = $1,800. Subtract $300 direct costs and $1,800 labor from $3,000 revenue: $900 client profit. Divide $900 by $3,000 to get a 30% margin.

The effective hourly rate is ($3,000 − $300) ÷ 60 = $45 per hour, before the internal labor allowance and unallocated overhead. Harbor Consulting's equivalent rate is $68.75; Maple Services' is approximately $36.67.

Read the result without jumping to a decision

North Studio generates the most revenue, but Harbor Consulting contributes the largest profit amount in this report. Maple Services has the lowest margin under the stated assumptions. That is a reason to inspect its records, scope and delivery process—not automatic proof that the relationship should end.

These percentages are example results, not recommended targets or industry benchmarks. Set review thresholds around your own costs, capacity and business requirements.

4. Set up the report in Excel or Google Sheets

For a simple manual worksheet, use these columns in row 1:

A: Client; B: Revenue; C: Direct costs; D: Actual hours; E: Internal hourly cost; F: Labor allowance; G: Client profit; H: Margin; I: Effective hourly rate.

With the first record in row 2:

• F2: =D2*E2
• G2: =B2-C2-F2
• H2: =IF(B2>0,G2/B2,"") — format the cell as a percentage.
• I2: =IF(D2>0,(B2-C2)/D2,"") — format as currency per hour.

These formulas assume the input cells contain valid figures. Validate inputs before relying on the output. A blank actual-hours field is missing data, not proof that delivery took zero hours. Zero revenue makes percentage margin undefined; zero hours makes an hourly rate undefined. Flag those records for review rather than replacing every error with zero. Some spreadsheet locales use semicolons instead of commas in formulas.

If a client has several completed projects, group them with a PivotTable or a consistent summary method. Sum revenue, direct costs, labor allowances and hours first. Then calculate the client's margin and hourly rate from those totals.

5. Calculate totals from amounts, not averages of percentages

For the example report, total revenue is $7,200, direct costs are $650 and the labor allowance is 137 × $30 = $4,110. Total client profit is $7,200 − $650 − $4,110 = $2,440.

The combined margin is $2,440 ÷ $7,200 = 33.89%. A simple average of the three client margins is approximately 32.78%, which is not the combined margin because the revenue amounts differ.

Likewise, the combined effective hourly rate is ($7,200 − $650) ÷ 137 = $47.81. Do not average individual client hourly rates when the hours differ.

6. Add a decision and an owner to each review

A profit by client report is most useful when it leads to a specific investigation or change:

• Actual hours above estimates: inspect revisions, meetings and the original estimate.
• High direct costs: check contractor costs and what the quote covers.
• Repeated extras: review how changes are approved and billed.
• Controlled scope but weak return: review the next price or service package.
• Missing time or cost data: fix the record before treating the ranking as reliable.

Record the next action, responsible person and review date. For example: “Review Maple's revision rounds before the next proposal; clarify the included allowance and quote additions separately.” Follow existing agreements when discussing any future changes with the client.

The guide to checking whether a client is actually profitable explains how to interpret the measures. For delivery changes, use the process for handling scope creep.

Use a repeatable client reporting workflow

You can start with the manual columns above. If you prefer an existing workbook, the Dovea Client Profitability Tracker provides Excel and Google Sheets templates with project records, completed-work profitability, client-level analysis and estimated-versus-actual hours. Active work is kept separate from completed results.

The workbook is an optional planning tool. It does not replace reliable records, professional judgment or accounting advice. The column layout and formulas in this guide are a standalone example, not instructions to overwrite its built-in formulas.

Explore the Client Profitability Tracker

Unsure which business habit to review first? Try the free Profit Leak Finder for a 15-question self-check and a seven-day plan.

Frequently asked questions

What is the difference between a client revenue report and a profitability report?

A revenue report totals what each client generates. A profitability report also includes the costs and working time required to serve them. A high-revenue client can have a weaker margin than a smaller client.

Should I include unfinished projects?

Keep unfinished projects visible separately. Use completed work for final project comparisons, or use a clearly defined period-based method that matches revenue and costs. Avoid mixing incomplete costs with the full expected project fee.

How often should I review the report?

Choose a routine that fits your delivery cycle. A monthly review can work for frequent projects; longer engagements may need separate work-in-progress checks and a completed-project review. Update the report when relevant time and cost records are available.

Does client profit equal money available to withdraw?

No. The result depends on which costs you included, and payment may not have arrived. Check outstanding invoices, business overhead, cash commitments and reserves separately before deciding what cash is available.