Agency

Client Cost Breakdown

Decomposes the total cost of serving each client into direct costs (labor, tools, media spend) and indirect costs (overhead allocation). Produces a per-client margin and ranks clients by profitability to inform account strategy.

Agency - Client Cost Breakdown.xlsx

Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice

Download Free

What This Spreadsheet Solves

Not knowing the true cost to serve each client after overhead
High-revenue accounts masking low or negative margins
Inability to allocate shared costs (office, management) fairly across clients
No data to support pricing adjustments or scope renegotiations
Misjudging which accounts to grow vs. which to sunset

Who This Is For

Agency owners reviewing account profitability
Account directors preparing for client business reviews
Agency CFOs building cost allocation models
Operations managers optimizing team assignments

Inputs

textClient name
$Direct labor cost
$Tools and software allocated
$Media or third-party spend
$Revenue from client
%Overhead allocation percentage

Outputs

Total cost to serve per client
Gross margin per client
Net margin after overhead
Client profitability rank
Cost breakdown pie chart per client

How Calculations Work

Direct costs (labor, tools, media) are summed for each client. Overhead is allocated proportionally based on revenue share or headcount share (configurable). Total cost is subtracted from revenue to derive gross and net margins. Clients are ranked by net margin percentage, and a waterfall chart shows cost composition.

Example Use Case

Scenario: An agency has Client A ($30,000 revenue, $15,000 labor, $2,000 tools, $5,000 media, 12% overhead) and Client B ($18,000 revenue, $8,000 labor, $1,500 tools, $0 media, 12% overhead).

Result: Client A: total cost $25,600, net margin 14.7%. Client B: total cost $11,660, net margin 35.2%. Client B ranks higher despite lower revenue.

What You Get — 5 Sheets

READMEDefinitions of direct vs. indirect costs, overhead allocation methods, and instructions for multi-client data entry.

INPUTClient roster with fields for revenue, labor cost, tool costs, media spend, and overhead allocation rate.

LOGICAggregates direct costs, applies overhead allocation, computes gross and net margins, and generates ranking formulas.

OUTPUTClient profitability ranking table, margin comparison bar chart, and per-client cost breakdown pie charts.

CONFIGOverhead allocation method toggle (revenue-based vs. headcount-based), default overhead rate, and minimum margin threshold.

Technical Details

File Format:.xlsx (Open XML)

Macros:None — pure formulas

Compatibility:Excel 2016+, Google Sheets, LibreOffice

Input Cells:Clearly marked with blue background

Formulas:All outputs are live Excel formulas

Protection:LOGIC sheet formulas protected, INPUT cells editable

Frequently Asked Questions

How should I allocate overhead?

Revenue-based allocation is simpler and works for agencies with similar service delivery. Headcount-based allocation is more accurate when team sizes vary significantly across clients.

What counts as a direct cost?

Any cost directly attributable to a specific client: assigned staff salaries, client-specific tools, media spend, subcontractor fees.

What if a team member works on multiple clients?

Split their cost proportionally based on time tracked per client. Use actual timesheets if available.

How often should I run this analysis?

Monthly for active accounts. Quarterly for a full portfolio review with overhead recalculation.

Can this handle pass-through costs billed to the client?

Yes. Enter pass-through costs in media spend and include the corresponding revenue. They will wash out in margin calculations.

Download Client Cost Breakdown

Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.

Download Free (.xlsx)Browse all 100 templates