Case Study 28-2: Maya Analyzes Her Consulting Revenue

Background

Maya Reyes is in year three of her independent consulting practice. By any surface measure, things are going well. She has 11 active clients. Revenue is up. She turned down two projects last quarter because she did not have capacity. Her calendar is consistently full.

But in February of 2024, Maya sits down to do her annual financial review and realizes she has never actually analyzed her own business the way she analyzes her clients' businesses. She tracks invoices and cash flow in a spreadsheet — she knows whether checks are coming in — but she has never asked deeper questions:

• Which clients are actually generating most of her revenue?
• Which types of projects are most profitable on an hourly basis?
• Is her business growing in a healthy way, or just busy?
• If she wants to raise her rates in 2024, which projects can support it?

Maya opens up maya_projects.csv and maya_invoices.xlsx and starts her own analysis.

The Data

maya_projects.csv contains one row per project:

project_id,client_name,project_type,start_date,end_date,billing_type,
contracted_value,hours_estimated,hours_worked,invoiced_amount,paid_amount
PRJ-001,Hartwell Retail,Strategic Advisory,2023-01-15,2023-03-30,Fixed Fee,
  18000,80,72,18000,18000
PRJ-002,Nguyen & Associates,Process Improvement,2023-02-01,2023-04-15,Hourly,
  12250,70,70,12250,12250
PRJ-003,Hartwell Retail,Market Analysis,2023-03-20,2023-05-10,Fixed Fee,
  9500,50,61,9500,9500

Note that hours_worked may differ from hours_estimated. For fixed-fee projects, the effective hourly rate depends on actual hours worked, not estimated.

The Analysis

Step 1: Load and Calculate Effective Rates

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as mticker

# Load and prep
projects = pd.read_csv("maya_projects.csv", parse_dates=["start_date", "end_date"])
projects["effective_hourly_rate"] = (
    projects["invoiced_amount"] / projects["hours_worked"]
).round(2)
projects["hours_overrun"] = projects["hours_worked"] - projects["hours_estimated"]
projects["overrun_pct"] = (
    projects["hours_overrun"] / projects["hours_estimated"] * 100
).round(1)

# Revenue by year
projects["year"] = projects["start_date"].dt.year
annual_revenue = projects.groupby("year")["invoiced_amount"].sum()
print("Annual revenue:")
print(annual_revenue)

First finding: Maya's revenue grew from $312,000 in Year 1 to $398,500 in Year 2 to $447,250 in Year 3 (2023). That is a 28% two-year growth rate. Solid.

But the growth rate is decelerating: 27.7% in Year 1-to-2, only 12.2% in Year 2-to-3. That is worth understanding.

Step 2: Which Clients Drive Revenue?

from sales_analysis import pareto_analysis, revenue_by_dimension

# Revenue by client — Maya's version of the Pareto analysis
client_revenue = (
    projects.groupby("client_name")
    .agg(
        total_revenue=("invoiced_amount", "sum"),
        total_projects=("project_id", "count"),
        total_hours=("hours_worked", "sum"),
        avg_project_value=("invoiced_amount", "mean"),
    )
    .reset_index()
    .sort_values("total_revenue", ascending=False)
)
client_revenue["pct_of_total"] = (
    client_revenue["total_revenue"] / client_revenue["total_revenue"].sum() * 100
).round(1)
client_revenue["cumulative_pct"] = client_revenue["pct_of_total"].cumsum().round(1)
client_revenue["effective_hourly_rate"] = (
    client_revenue["total_revenue"] / client_revenue["total_hours"]
).round(2)

print(client_revenue.to_string(index=False))

Maya has 11 clients. The results are striking:

Maya's top 5 clients account for 70.4% of her revenue. The 80% threshold is at client 6. With 11 clients, this is a more diffuse Pareto than Acme — but her top 2 clients alone represent 36.2% of her revenue, which is meaningful concentration for a solo practitioner.

Finding 1: If Maya loses Hartwell Retail, she loses roughly $90,000 in annualized revenue. That is not diversified enough.

Step 3: Which Project Types Are Most Profitable?

type_analysis = (
    projects.groupby("project_type")
    .agg(
        total_revenue=("invoiced_amount", "sum"),
        total_hours=("hours_worked", "sum"),
        project_count=("project_id", "count"),
        avg_effective_rate=("effective_hourly_rate", "mean"),
        avg_overrun_pct=("overrun_pct", "mean"),
    )
    .reset_index()
    .sort_values("avg_effective_rate", ascending=False)
)

type_analysis["avg_effective_rate"] = type_analysis["avg_effective_rate"].round(2)
type_analysis["avg_overrun_pct"] = type_analysis["avg_overrun_pct"].round(1)

print(type_analysis.to_string(index=False))

Maya's standard rate is $175/hr. Strategic Advisory is beating that rate by 23% because Maya estimates conservatively (slightly above hours needed) and finishes efficiently. Implementation Support is running 24.3% over budget, dragging her effective rate down to $141/hr — $34 below her standard.

Finding 2: Maya is most profitable on Strategic Advisory work (which she also finds most interesting). She is least profitable on Implementation Support, where scope creep consistently erodes her hourly rate.

# Visualize the effective rate comparison
fig, ax = plt.subplots(figsize=(10, 5))

type_analysis_sorted = type_analysis.sort_values("avg_effective_rate")
colors = [
    "#27AE60" if r >= 175 else "#E74C3C"
    for r in type_analysis_sorted["avg_effective_rate"]
]
bars = ax.barh(
    type_analysis_sorted["project_type"],
    type_analysis_sorted["avg_effective_rate"],
    color=colors,
)

ax.axvline(175, color="#2C3E50", linestyle="--", linewidth=2, label="Standard rate ($175/hr)")
ax.set_title("Effective Hourly Rate by Project Type\nMaya Reyes Consulting — 2023",
             fontweight="bold")
ax.set_xlabel("Effective Hourly Rate ($)")

for bar, val in zip(bars, type_analysis_sorted["avg_effective_rate"]):
    ax.text(val + 1, bar.get_y() + bar.get_height() / 2,
            f"${val:.0f}", va="center", fontsize=9)

ax.legend()
plt.tight_layout()
plt.savefig("maya_project_rates.png", dpi=150)
plt.show()

Step 4: Revenue Growth and Trend

# Monthly revenue trend
projects["year_month"] = projects["start_date"].dt.to_period("M")

monthly = (
    projects.groupby("year_month")["invoiced_amount"]
    .sum()
    .reset_index()
    .sort_values("year_month")
)
monthly["mom_pct"] = (monthly["invoiced_amount"].pct_change() * 100).round(1)

# Calculate trailing 3-month average
monthly["trailing_3mo"] = monthly["invoiced_amount"].rolling(3).mean()

fig, ax = plt.subplots(figsize=(13, 5))
months = [str(p) for p in monthly["year_month"]]
x = range(len(months))

ax.bar(x, monthly["invoiced_amount"], color="#4472C4", alpha=0.7, label="Monthly Revenue")
ax.plot(x, monthly["trailing_3mo"], color="#FF6B35", linewidth=2.5,
        marker="o", markersize=5, label="3-Month Rolling Average")
ax.set_xticks(list(x))
ax.set_xticklabels(months, rotation=45, ha="right", fontsize=8)
ax.set_title("Maya Reyes Consulting — Monthly Revenue 2023", fontweight="bold")
ax.set_ylabel("Revenue ($)")
ax.yaxis.set_major_formatter(mticker.FuncFormatter(lambda v, _: f"${v:,.0f}"))
ax.legend()
plt.tight_layout()
plt.savefig("maya_monthly_revenue.png", dpi=150)
plt.show()

The rolling average reveals the underlying trend more clearly than the choppy monthly bars. Maya's revenue is genuinely growing — the rolling average slopes upward through 2023 — but the pace of growth slowed in H2. Several months in Q3 showed revenue below the rolling average.

Step 5: Are Fixed Fee or Hourly Projects More Profitable?

billing_analysis = (
    projects.groupby("billing_type")
    .agg(
        total_revenue=("invoiced_amount", "sum"),
        project_count=("project_id", "count"),
        avg_effective_rate=("effective_hourly_rate", "mean"),
        avg_overrun_pct=("overrun_pct", "mean"),
    )
    .reset_index()
)
billing_analysis["avg_effective_rate"] = billing_analysis["avg_effective_rate"].round(2)
billing_analysis["avg_overrun_pct"] = billing_analysis["avg_overrun_pct"].round(1)
print(billing_analysis.to_string(index=False))

Fixed-fee projects outperform hourly billing by $23/hr on average — but only because Maya has gotten better at scoping them. Her early fixed-fee projects (Year 1) often ran over; her Year 3 fixed-fee projects are consistently on or under budget.

Finding 3: Maya should shift more of her work to fixed-fee arrangements for project types where she has strong scoping confidence (Strategic Advisory, Market Analysis). She should keep Implementation Support as hourly or add a scope-change clause.

Maya's Action Plan

After the analysis, Maya identifies four changes for 2024:

1. Raise rates on Strategic Advisory work. She is achieving $215/hr effective on these projects at $175/hr standard — she has pricing power she is not using. In 2024, she will propose $195/hr for new Strategic Advisory engagements.

2. Revise Implementation Support contracts. Every implementation project runs over scope. She will add a time-and-materials escalation clause: initial estimate at fixed fee, with scope changes billed at $175/hr. Alternatively, she will increase the implementation fixed fee by 25% to account for the average 24.3% overrun.

3. Reduce concentration in top 2 clients. At 36.2% of revenue from two clients, Maya is exposed. She will intentionally pursue 2–3 new advisory clients in 2024 to diversify her base below 30%.

4. Standardize on better scope documentation. The overrun analysis reveals that the problem is not client behavior — it is Maya's initial scoping. She will start using a structured scope document template before every engagement.

# Summary print statement for Maya's planning meeting
print("=" * 55)
print("  MAYA REYES CONSULTING — 2023 REVENUE ANALYSIS")
print("=" * 55)
print(f"  Total 2023 Revenue:        ${projects['invoiced_amount'].sum():>12,.2f}")
print(f"  Total Projects Completed:  {len(projects):>12,}")
print(f"  Total Hours Billed:        {projects['hours_worked'].sum():>12,.0f}")
print(f"  Blended Effective Rate:    ${projects['invoiced_amount'].sum() / projects['hours_worked'].sum():>11.2f}/hr")
print(f"  Standard Rate:             ${'175.00':>14}/hr")
print(f"  Rate Premium:              {(projects['invoiced_amount'].sum() / projects['hours_worked'].sum() - 175) / 175 * 100:>10.1f}%")
print()
print("  Revenue Concentration:")
print(f"    Top 1 client:  {client_revenue.iloc[0]['pct_of_total']:.1f}% of revenue")
print(f"    Top 2 clients: {client_revenue.iloc[:2]['pct_of_total'].sum():.1f}% of revenue")
print(f"    Top 5 clients: {client_revenue.iloc[:5]['pct_of_total'].sum():.1f}% of revenue")
print()
print("  Best project type (by effective rate): Strategic Advisory @ $215/hr")
print("  Worst project type:  Implementation Support @ $141/hr")
print("=" * 55)

What This Case Study Illustrates

Sales analytics scales down beautifully. Every technique in this chapter works for an 11-client consulting practice just as well as for a 155-customer distributor. The tools are not size-dependent.

The effective hourly rate concept is powerful for any service business. Whether you are a consultant, an agency, a law firm, or a freelancer, the gap between your stated rate and your actual effective rate is critical information. Most service providers track the former and ignore the latter.

Knowing your profitable segments lets you optimize for them. Maya did not need to take on more clients or work more hours to grow revenue. She needed to shift her mix toward higher-effective-rate project types and price her lower-effective-rate work appropriately. The data made that visible.

Concentration risk is a solo practitioner's existential issue. For a freelancer or small firm, losing a top client can eliminate 20–30% of revenue overnight. Maya identifying this proactively — and making client diversification a 2024 goal — is the kind of strategic decision that data analysis enables before the crisis happens.