Formula

(Total Referrals − No Access) ÷ Total Referrals

Partner Discount rows are excluded entirely from both numerator and denominator before any calculation. No Access = the rep could not get into the property — these are subtracted from the booked count but kept in the denominator.

Example

WPB has 490 referrals in Jan. 36 are Partner Discount (excluded). Of remaining 454, 62 are No Access.
Booked = 454 − 62 = 392 · Rate = 392 ÷ 454 = 86.3%

Goal

Set per branch per month in the Goals sheet. Aggregate default: 86%. Green = at/above goal. Red = below goal.

Source

Booking Rate Data tab · Date field: Referral Date · Partner Discount rows excluded · Key columns: No Access (0/1)

Cancel Rate Formula

GC Cancelled (cancel tabs) ÷ GC Booked (booking sheet) × 100

GC Cancelled = rows where GC Cancelled = 1 in the branch cancel tab. GC Booked = rows where GC = 1 in the Booking Rate sheet (Partner Discount rows excluded).

Example

WPB Jan 2025: 153 GC jobs booked. 33 rows have GC Cancelled = 1 in the cancel tab.
Rate = 33 ÷ 153 = 21.6%

Goal

Set per branch per month in the Goals sheet. Green = at or below goal. Red = above goal.

Source

GC Cancelled count: WPB / ORL / HOU / TPA cancel tabs (GC Cancelled column) · GC Booked: Booking Rate Data tab (GC column)

Avg GP% Formula

Sum of GP% values ÷ Number of qualifying jobs

Qualifying = closed, non-cancelled, GP% between −200% and 110% (outlier filter)

Missed Revenue Formula

(Actual GP$ × Goal% ÷ Actual%) − Actual GP$

Only shows when actual GP% is below goal. Answers: "how much more GP$ would we have earned at goal rate?"

Example

HOU closes 20 jobs. Actual GP% = 46%. Actual GP$ = $180,000. Goal = 52%.
Projected at goal: $180,000 × (52 ÷ 46) = $203,478
Missed Revenue = $203,478 − $180,000 = $23,478

Charts

Job Volume %: % closed vs % cancelled per month · Cancelled vs Landed: raw counts over time · GP% Chart: job count bars + actual GP% line (green=at/above goal, red=below) + goal line (gray dashed) · Missed revenue shown below each GP% chart

Source

GC Jobs Report tab · Date field: Date Closed · GP% goal: 52% (from Goals sheet)

Formula

Actual GP$ × (Goal% ÷ Actual%) − Actual GP$

This scales your actual GP$ up proportionally to what it would have been at the goal rate. It assumes the same number of jobs and same contract amounts — only the margin changes.

Example

HOU closes 20 jobs. Actual GP% = 46%. Actual GP$ = $180,000. Goal = 52%.
Projected at goal: $180,000 × (52 ÷ 46) = $203,478
Missed Revenue = $203,478 − $180,000 = $23,478

When It Shows

Only displayed when actual GP% is below the goal. If at or above goal, the branch shows ✓. Shown in the KPI card, GP% charts, and branch summary table.

Limitation

This is a straight ratio projection — it does not account for the possibility that higher margins might mean fewer jobs won, or that contract amounts themselves could change.

Completion Time Formula

Avg Days = Sum of Completion Days ÷ Number of Jobs

Days = time from field inspection to completed report. Goal is ≤ 2 days.

Example

TPA completes 3 inspections in March: 1 day, 2 days, 4 days.
Avg = (1 + 2 + 4) ÷ 3 = 2.3 days → shown in red (above 2-day goal)

Date Filter

Filtered by the global date range. Month column uses full text format (e.g. "January 2026") — the dashboard parses this automatically.

Source

TJS Metrics Tracker tab · Col A: Month (e.g. "January 2026") · Col B: Location · Col C: Count · Col D: Completion Days

📋 Booking Rate Data

Pulled weekly from the Inside Sales HEX Dashboard. Data is added to the Booking Rate Data tab in the spreadsheet.

❌ Cancellation Data

Pulled weekly from the HEX Cancellation Dashboard for each branch (WPB, ORL, HOU, TPA) and added to the corresponding cancel tab.

📊 Volume & GP% Data

Pulled weekly from the HEX GC Jobs Report Dashboard and added to the GC Jobs Report tab in the spreadsheet.

🔍 TJS Metrics Data

Manually inputted each month from the TJS Metrics Tracker 2026 ↗ into the TJS Metrics Tracker tab.

🎯 Goals

Updated annually in the Goals sheet. Changes here automatically update all goal lines and conditional formatting across the dashboard.

Raw Data Spreadsheet