Metric Definition Worksheet

Most predictive reporting initiatives fail because firms skip the foundational work of properly defining their metrics. Partners end up looking at dashboards showing "utilization" calculated three different ways across three different departments. Finance flags revenue concerns that operations can't replicate. Everyone wastes time in meetings arguing about whose numbers are right.

This worksheet eliminates that chaos. Use it to document 5-7 core metrics with surgical precision: exact calculation formulas, specific source system fields, numeric thresholds, and named alert recipients. Once completed, this becomes your firm's single source of truth for metric definitions.

For each metric, document all seven components below. Incomplete definitions create reporting failures.

Component 1: Metric Name

Use plain English that any partner can understand without explanation. Avoid internal acronyms.

Good Examples:

• Billable Utilization Rate
• Average Collection Period
• Client Concentration Risk

Bad Examples:

• BUR-FTE (requires explanation)
• KPI-07 (meaningless)
• Productivity Index (too vague)

Component 2: Business Purpose

Write one sentence explaining why this metric matters to firm profitability or risk management. If you can't articulate clear business impact, don't track it.

Example: "Billable Utilization Rate measures what percentage of available staff hours generate revenue. Below 70% indicates overstaffing or insufficient client demand. Above 90% signals burnout risk and capacity constraints for new work."

Component 3: Exact Calculation Formula

Document the precise formula with every variable defined. Include the specific field names from your source systems.

Example:

Billable Utilization Rate = (Billable Hours / Available Hours) × 100

Where:
- Billable Hours = SUM of [Deltek.TimeEntry.BillableHours] 
  WHERE [Deltek.TimeEntry.Status] = "Approved"
  AND [Deltek.TimeEntry.ChargeType] = "Client"
  
- Available Hours = [Workday.Employee.StandardHours] × [WorkdaysInPeriod]
  EXCLUDING [Workday.TimeOff.ApprovedHours]
  EXCLUDING employees with [Workday.Employee.Status] = "Leave" or "Terminated"

Specify rounding rules. State whether you calculate daily, weekly, or monthly, then aggregate up.

Component 4: Source Systems and Fields

List every system and the exact table/field names. This allows your IT team or BI analyst to build the data pipeline without guessing.

Example:

Primary Sources:
- Deltek Vantagepoint: TIMESHEET.BILLABLE_HRS, TIMESHEET.APPROVAL_STATUS
- Workday HCM: WORKER.STANDARD_HOURS, TIME_OFF.APPROVED_HOURS

Reference Data:
- Shared calendar system: BUSINESS_DAYS table for workday calculations
- Employee master: EMPLOYEE.STATUS, EMPLOYEE.HIRE_DATE, EMPLOYEE.TERM_DATE

If you're pulling from spreadsheets or manual entry, document that too. It flags data quality risks.

Component 5: Reporting Frequency and Lag

State how often you calculate this metric and how current the data will be.

Example:

Calculation Frequency: Weekly, every Monday at 6 AM
Data Lag: 2 business days (time entries approved by Friday, calculated Monday)
Historical Retention: Rolling 24 months

Be realistic about lag time. If timesheets aren't approved until the 5th of the month, don't promise real-time utilization data.

Component 6: Threshold Ranges and Triggers

Define numeric ranges that trigger different alert levels. Use three tiers: target (green), warning (yellow), critical (red).

Example:

Billable Utilization Rate Thresholds:

Target Range (Green): 75-85%
- Optimal productivity without burnout risk
- No alerts generated

Warning Range (Yellow): 65-74% or 86-92%
- Alert: Practice Group Leader
- Review within 5 business days

Critical Range (Red): Below 65% or above 92%
- Alert: Practice Group Leader + COO + Managing Partner
- Immediate review required (same business day)
- Below 65%: Indicates overstaffing or pipeline problem
- Above 92%: Burnout risk, quality concerns, capacity constraint

Set thresholds based on your firm's historical performance and strategic targets, not industry averages from consulting reports.

Component 7: Alert Recipients and Actions

Name specific roles (not just "management") and define what action each recipient should take.

Example:

Yellow Alert Recipients:
- Practice Group Leader: Review staff assignments, identify available capacity or pipeline gaps
- Resource Manager: Run 30-day forward capacity analysis

Red Alert Recipients:  
- Practice Group Leader: Same as yellow, plus prepare staffing recommendation
- COO: Review with Managing Partner within 24 hours
- Managing Partner: Approve staffing changes or pipeline development plan

Alert Delivery Method: Email + Slack #operations-alerts channel
Escalation Rule: If no acknowledgment within 4 hours during business days, escalate to Managing Partner

Metric 1: Average Collection Period

Business Purpose: Measures average days between invoice date and payment receipt. Above 60 days indicates client payment issues or billing process problems affecting cash flow.

Calculation Formula:

Average Collection Period = (Accounts Receivable / Revenue) × Days in Period

Where:
- Accounts Receivable = SUM of [QuickBooks.Invoice.AmountDue] 
  WHERE [QuickBooks.Invoice.Status] = "Unpaid" or "Partial"
  
- Revenue = SUM of [QuickBooks.Invoice.TotalAmount]
  WHERE [QuickBooks.Invoice.Date] BETWEEN [PeriodStart] AND [PeriodEnd]
  
- Days in Period = 30 (for monthly), 90 (for quarterly)

Source Systems:

• QuickBooks Online: Invoice table (INVOICE.AMOUNT_DUE, INVOICE.STATUS, INVOICE.DATE)
• Client master: CLIENT.PAYMENT_TERMS, CLIENT.CREDIT_HOLD_STATUS

Frequency and Lag:

• Calculation: Monthly, 1st business day of month
• Data Lag: 1 business day (prior month close)
• Historical: Rolling 36 months

Thresholds:

Target (Green): 30-45 days
Warning (Yellow): 46-60 days
  Alert: Billing Manager + CFO
  Action: Review aging report, contact clients over 45 days
  
Critical (Red): Over 60 days
  Alert: Billing Manager + CFO + Managing Partner
  Action: Immediate client contact, payment plan discussion, consider credit hold

Alert Recipients:

• Billing Manager: Run detailed aging report, prepare client contact list
• CFO: Review cash flow impact, approve collection actions
• Managing Partner: Approve credit holds or write-offs over $10K

Metric 2: Client Concentration Risk

Business Purpose: Percentage of total revenue from top 3 clients. Above 40% creates dangerous dependency on few relationships.

Calculation Formula:

Client Concentration Risk = (Top 3 Client Revenue / Total Revenue) × 100

Where:
- Top 3 Client Revenue = SUM of top 3 values in 
  [Deltek.Project.ActualRevenue] grouped by [Deltek.Client.ClientID]
  for rolling 12-month period
  
- Total Revenue = SUM of [Deltek.Project.ActualRevenue] 
  for same rolling 12-month period

Source Systems:

• Deltek Vantagepoint: Project revenue (PROJECT.ACTUAL_REVENUE, PROJECT.CLIENT_ID)
• Client master: CLIENT.NAME, CLIENT.INDUSTRY, CLIENT.RELATIONSHIP_START_DATE

Frequency and Lag:

• Calculation: Quarterly, 5th business day after quarter end
• Data Lag: 5 business days (revenue recognition close)
• Historical: Rolling 5 years

Thresholds:

Target (Green): Below 30%
Warning (Yellow): 30-40%
  Alert: Managing Partner + Business Development Director
  Action: Develop client diversification plan within 30 days
  
Critical (Red): Above 40%
  Alert: Managing Partner + All Partners + Board (if applicable)
  Action: Immediate diversification strategy, monthly monitoring

Alert Recipients:

• Managing Partner: Lead diversification planning
• Business Development Director: Accelerate new client acquisition
• All Partners: Activate networks for new client opportunities

After completing definitions for your 5-7 metrics:

Week 1: Validation

• [ ] Share worksheet with practice group leaders for feedback
• [ ] Verify all source system fields exist and are accessible
• [ ] Confirm alert recipients accept responsibility for actions
• [ ] Test one metric calculation manually to validate formula

Week 2: Technical Setup

• [ ] Provide worksheet to BI analyst or IT team
• [ ] Build data extraction queries for each source system
• [ ] Create staging tables if combining multiple sources
• [ ] Schedule automated calculation jobs

Week 3: Dashboard and Alerts

• [ ] Configure dashboard visualizations (use red/yellow/green color coding)
• [ ] Set up automated alert emails with direct links to detailed reports
• [ ] Create Slack or Teams channel for alert notifications
• [ ] Test alert delivery and escalation rules

Week 4: Launch

• [ ] Run parallel calculations (new system vs. old method) for one month
• [ ] Document any discrepancies and resolve
• [ ] Train alert recipients on expected actions
• [ ] Schedule monthly metric review meeting for first quarter

Ongoing: Refinement

• [ ] Review thresholds quarterly against actual firm performance
• [ ] Adjust calculations if source systems change
• [ ] Add new metrics as strategic priorities evolve
• [ ] Archive metrics that no longer drive decisions

This worksheet becomes a living document. Update it whenever you modify calculations, change thresholds, or add new metrics. Store it in your firm's shared drive where anyone can reference the official definitions.