How to Track Project Profitability in Consulting

The most dangerous number in consulting is a gross profit percentage that looks healthy but isn't verified.

I've watched delivery teams celebrate engagements that "felt" profitable—the client was happy, scope was delivered, the timeline held—only to discover weeks later that the project lost money. By then, the engagement is over. The write-off is locked in. The lessons are academic.

Most project managers know their revenue. They can tell you the contract value, the billing milestones, the invoiced amounts. But ask about actual cost—the real hours spent multiplied by the actual rates of everyone who touched the project—and you get approximations. Guesses. Numbers pulled from memory rather than systems.

This isn't a skills problem. It's a visibility problem. Calculating actual cost from hours times rates across multiple people, multiple billing codes, and multiple rate changes over the life of an engagement is genuinely painful without the right infrastructure. So people approximate. And approximations lead to surprises.

This guide covers how to track project profitability properly—the formulas, the common mistakes, and how to move from manual calculations to real-time visibility.

The Profitability Formula for Professional Services

Let's start with precision. Generic profitability definitions don't help in professional services because our cost structure is different.

The Core Formula

Gross Profit (GP) = Revenue - Cost

Gross Profit Percentage (GP%) = (Revenue - Cost) / Revenue × 100

What "Revenue" Means

In professional services, revenue comes from one of two sources:

Time & Materials (T&M): Revenue = Hours billed × Billing rate per hour

Fixed Price / Milestone: Revenue = Agreed deliverable price (regardless of hours spent)

For T&M engagements, revenue and cost are both driven by hours, but at different rates. For fixed-price work, revenue is fixed and cost varies with actual effort. The profitability math is the same, but the risk profile is different.

What "Cost" Means

This is where consulting profitability tracking gets specific. Your cost is not "expenses" in the general accounting sense. It's the loaded cost of the people who delivered the work.

Cost = SUM(Hours × Billing Rate) for each person on the project

Each person has a cost rate—the fully loaded expense of one hour of their time. This includes:

• Prorated salary (annual salary ÷ available hours per year)
• Benefits (typically 25-35% of salary)
• Overhead allocation (facilities, tools, management)

If a senior consultant earns $150,000/year with 35% benefits loading and you assume 1,880 available hours, their cost rate is approximately $108/hour.

A Concrete Example

Let's make this real with actual numbers.

The Engagement:

• 4-month strategy project
• 5 people on the team
• T&M billing at blended rate of $195/hour
• Contract ceiling: $450,000

The Team:

Planned Profitability:

• Gross Profit: $340,800 - $141,600 = $199,200
• GP%: 58.4%

This is a healthy margin. The question is: will reality match the plan?

Where Most Firms Get the Numbers Wrong

I've reviewed project financials from dozens of consulting firms. The same mistakes appear repeatedly.

Using a Single Blended Rate

"Our average cost is $75/hour" is not the same as calculating actual cost per person. If your senior consultant works 200 hours instead of the planned 100, and your analyst works 50 instead of 200, your actual cost changed—even if total hours stayed the same.

A $95/hour resource working 200 hours costs $19,000. A $50/hour resource working 50 hours costs $2,500.

Blending hides this variance. You don't see that you burned expensive hours on work that was supposed to be junior-level.

Not Updating Rates When They Change

A consultant gets promoted in March. Their rate goes from $70 to $85/hour. If you're using a spreadsheet with their rate in one cell, every cost calculation—including January and February—now uses the new rate. Your historical costs just increased retroactively.

This isn't a minor error. On a 12-month engagement with a 5-person team where 2 people get mid-year raises, the cost distortion can be $15,000-30,000.

Mixing Forecast and Actuals on Different Axes

Comparing "forecasted revenue" to "actual cost" is meaningless because they're on different axes. Forecasted revenue assumes planned hours at planned rates. Actual cost reflects real hours at real rates.

You need to compare like to like:

• Forecasted GP% = Forecasted Revenue - Forecasted Cost
• Actual GP% = Actual Revenue - Actual Cost

The variance between forecast and actual tells you what changed and why.

Forgetting Cost Components

Your senior consultants bill $250/hour and show up in every calculation. But what about the QA lead who spent 20 hours reviewing deliverables? The project manager who spent 40 hours on client coordination? The architect who joined three status calls?

If it's not in the tracker, it's not in the cost. And understating cost overstates profitability.

Forecasted vs. Actual Profitability

This distinction is critical, and most tracking systems don't make it clearly.

Forecasted GP%

Forecasted gross profit is based on your plan:

• Planned hours per person per month
• Billing rates in effect (or expected)
• Planned revenue based on milestones or expected T&M billing

This number tells you where you're heading if everything goes according to plan. It's your target margin.

Actual GP%

Actual gross profit is based on what happened:

• Submitted/approved hours from timesheets
• Billing rates that were active when those hours were worked
• Revenue actually invoiced or earned

This number tells you where you are. It's your real margin.

Why You Need Both

Forecast alone is wishful thinking. Plans change. Hours run over. Scope creeps. A forecast without actuals is hope masquerading as data.

Actuals alone is reactive. By the time you see actual GP% drop from 50% to 35%, the damage is done. You needed to see the variance emerging, not the result after the fact.

The variance between forecast and actual is the actionable insight. If your forecast said 55% GP and your current actual is 48%, you have a 7-point gap to investigate. Why? More hours than planned? Lower billing realization? Different mix of resources?

Variance analysis while the project is still running lets you course-correct. Variance analysis after the project ends lets you write better proposals next time.

How to Track This in a Spreadsheet (and Why It Breaks)

Let's walk through the typical Excel-based approach, because understanding its limitations explains why purpose-built tools matter.

The Typical Setup

Tab 1: Rate Card

• List of team members
• Billing rate per person
• Cost rate per person

Tab 2: Hours

• Rows for each person
• Columns for each week or month
• Hours entered manually or pasted from timesheets

Tab 3: Cost Calculation

• Formula: =Hours × VLOOKUP(Person, RateCard, CostRateColumn)
• Summarized by month and totaled for the project

Tab 4: Revenue

• Milestones with amounts and dates, or
• Billed hours × billing rate calculation

Tab 5: Profitability Summary

• Total Revenue - Total Cost = GP
• GP / Revenue = GP%

Where It Breaks

Rate changes aren't versioned. When you update someone's rate in the Rate Card tab, every VLOOKUP that references it changes—including historical months. January's cost now uses April's rate.

New team members break formulas. Someone joins the project. You add a row. But the SUMIFS on the summary tab referenced rows 5-15, and now there's a row 16. The new person's hours aren't in the total.

Multiple billing codes become chaos. One project, three SOWs. Now you need to split hours by SOW, track revenue by SOW, and calculate profitability by SOW and rolled up. Your simple tracker becomes a 12-tab monster with cross-tab references that nobody fully understands.

No real-time visibility. Timesheets come in Friday. Someone consolidates them Monday. The PM reviews Tuesday. You're always looking at last week's numbers. If a problem is emerging, you see it days after you could have acted.

The Specific Formula Problems

Here's a real issue I've debugged multiple times:

=SUMIFS(Hours, NameRange, "Sarah Chen", ProjectRange, "Alpha")
× VLOOKUP("Sarah Chen", RateCard, 3, FALSE)

This looks right. But if Sarah's name is spelled "Sarah Chen" in Hours and "S. Chen" in the Rate Card, the VLOOKUP returns #N/A. If someone later changes the Rate Card, the historical calculation changes. If the Hours range doesn't include newly added rows, they're excluded.

Every junction is a potential failure point.

How to Track This in MyProjectBudget

I built MyProjectBudget specifically because I kept running into these spreadsheet failures. Here's how it handles the problems:

Rates with Effective Dates

Every billing rate has a start date. When you change someone's rate, you're not overwriting the old rate—you're adding a new rate with a new effective date. Historical costs automatically use the rate that was active at the time.

If Sarah's rate was $70/hour through March 31 and $85/hour starting April 1, her March hours calculate at $70 and her April hours calculate at $85. No manual adjustment. No formula errors.

The Forecast Grid

Enter hours by person by project by month in one unified view. Costs calculate automatically based on rates. You see every team member, every project, every month in one place. Adding a new person or project extends the grid—no formulas to copy.

Real-Time Profitability Dashboard

As timesheets are submitted and approved, the dashboard updates. GP% per project refreshes in real time. You don't wait for a Monday consolidation ritual—you see the numbers as they happen.

Baseline Comparison

Save your original forecast as a baseline. As actuals come in and forecasts get refined, compare current projections to the original baseline. The variance tells you what changed from the plan you sold.

The Metrics Your Leadership Actually Wants to See

When executives review project financials, they don't want to see raw hours and rates. They want synthesized insights. Here's what matters:

GP% by Project by Month (Trend)

Is this project getting more or less profitable over time? A project that started at 55% GP and is now at 42% GP has a problem—even if 42% is still technically profitable. The trend matters as much as the absolute number.

Forecast vs. Actual Cost Variance

Are we spending more than planned? If we budgeted $200K in cost and we've incurred $185K with the project 80% complete, we might be okay. If we've incurred $185K with the project 60% complete, we're heading for an overrun.

Revenue to Be Invoiced

How much have we delivered but not yet billed? This matters for cash flow and for understanding true earned revenue. If you've done $100K of work but only invoiced $60K, you have $40K of unbilled revenue—or $40K of work you may never collect if the relationship sours.

Hours Burned vs. Hours Remaining

Are we going to run out of budget? If the SOW has 2,000 hours and we've burned 1,600 with 50% of scope remaining, the math doesn't work. Either scope gets cut, or we're eating hours.

Utilization by Person Across Projects

Is anyone over-allocated? If your senior consultant is assigned 180 hours across three projects but there are only 160 working hours in the month, something has to give.

Tracking project profitability isn't complicated in principle. It's multiplication and subtraction. But doing it consistently, accurately, and in real time—with proper rate versioning, multi-project support, and baseline comparison—requires infrastructure that spreadsheets can't provide.

The firms that maintain healthy margins aren't using secret formulas. They have visibility. They see profitability while there's still time to act. They don't discover problems at the project retrospective.

Ready to replace your spreadsheet? MyProjectBudget gives you forecasts, actuals, and profitability in real time—across every project and billing code. Free to start, no credit card required.

Try MyProjectBudget Free