Margin Optimization Model
Models gross and net margins at the agency, department, and client level. Allocates overhead costs and identifies specific levers (pricing, staffing mix, overhead reduction) to improve margins toward target thresholds.
Agency - Margin Optimization Model.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Gross margins look healthy but net margins are thin after overhead
- No structured way to model the margin impact of pricing or staffing changes
- Overhead costs growing faster than revenue without clear attribution
- Difficulty setting margin targets by department or service line
- Lack of scenario modeling for cost reduction initiatives
Who This Is For
- Agency CFOs building profitability improvement plans
- Managing directors setting departmental margin targets
- Operations directors evaluating cost reduction initiatives
- Agency owners preparing for valuation or acquisition
Inputs
- $Revenue by department or service line
- $Direct labor cost by department
- $Direct non-labor costs
- $Total overhead costs
- %Target net margin
Outputs
- Gross margin by department
- Net margin after overhead allocation
- Margin gap to target
- Overhead cost per revenue dollar
- Break-even revenue at current cost structure
- Scenario outcomes for pricing and cost changes
How Calculations Work
Gross margin is computed per department as revenue minus direct costs. Overhead is allocated proportionally (by revenue or headcount). Net margin is gross margin minus allocated overhead. The model then calculates how much pricing increase or cost reduction is needed to reach the target margin. Scenario toggles let you model specific changes (e.g., 5% rate increase, one fewer hire, 10% overhead cut).
Example Use Case
Scenario: An agency has $200,000/month revenue, $110,000 direct costs, and $60,000 overhead. Target net margin is 25%.
Result: Current gross margin: 45%. Current net margin: 15%. Margin gap: 10 points. To reach 25%, the agency needs either a 12% rate increase, $20,000 in cost reductions, or a combination of both.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
What is the difference between gross and net margin?
Gross margin is revenue minus direct costs. Net margin further subtracts overhead (rent, admin salaries, software, insurance). Net margin is the true profitability metric.
How should overhead be allocated?
Revenue-proportional is the simplest. Headcount-proportional is fairer when departments have very different team sizes. Choose based on what drives overhead in your agency.
What is a healthy net margin for an agency?
15-25% is typical for well-run agencies. Below 10% signals structural cost problems. Above 30% may indicate underinvestment in growth.
Can I model the impact of losing a client?
Yes. Remove that client's revenue and direct costs from the INPUT. Overhead stays fixed, showing the margin impact of the loss.
How does staffing mix affect margins?
Replacing senior staff with junior staff lowers cost but may reduce bill rates. The model lets you change cost and bill rates to see the net margin effect.
Download Margin Optimization Model
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.