A small business owner was tracking jobs and revenue in a spreadsheet but had no visibility into individual job profits. They had all the right pieces of information, but it needed to be combined to show the full picture — both at the grand scheme and the individual job level. Without insight into job-level information, there was no reliable basis for pricing decisions.
I started with the existing information — invoices, notes, and expenses recorded across a few sources. That information was consolidated into a single structured spreadsheet with consistent formatting and a clear logic that could be maintained by the owner going forward. Together, we worked on reviewing the information and confirming accuracy.
"After sorting through documentation and discussion with the owner, the following table was created."
| Job # | Service Type | Est. Materials | Act. Materials | Mat. Variance | Est. Hrs | Act. Hrs | Hr. Variance | Rate/hr | Est. Labor | Act. Labor | Labor Variance | Est. Total | Act. Total | Invoice | Gross Margin | Anticipated Margin | Actual Margin |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| J-001 | Panel Upgrade | $1,850 | $2,140 | $290 | 8.0 | 12.5 | 4.5 | $75 | $600 | $938 | $338 | $2,450 | $3,078 | $3,200 | $123 | 23.4% | 3.8% |
| J-002 | Service Call | $120 | $95 | ($25) | 2.0 | 1.8 | -0.2 | $75 | $150 | $135 | ($15) | $270 | $230 | $620 | $390 | 56.5% | 62.9% |
| J-003 | EV Charger Install | $480 | $510 | $30 | 4.0 | 4.5 | 0.5 | $75 | $300 | $338 | $38 | $780 | $848 | $1,050 | $203 | 25.7% | 19.3% |
| J-004 | Outlet Addition | $210 | $195 | ($15) | 3.0 | 3.2 | 0.2 | $75 | $225 | $240 | $15 | $435 | $435 | $750 | $315 | 42.0% | 42.0% |
| J-005 | Panel Upgrade | $1,900 | $2,380 | $480 | 8.0 | 13.0 | 5.0 | $75 | $600 | $975 | $375 | $2,500 | $3,355 | $3,200 | ($155) | 21.9% | -4.8% |
| J-006 | Lighting Retrofit | $380 | $410 | $30 | 5.0 | 5.5 | 0.5 | $75 | $375 | $413 | $38 | $755 | $823 | $1,400 | $578 | 46.1% | 41.3% |
| J-007 | Service Call | $85 | $70 | ($15) | 1.5 | 1.2 | -0.3 | $75 | $113 | $90 | ($23) | $198 | $160 | $480 | $320 | 58.9% | 66.7% |
| J-008 | Kitchen Rough-In | $920 | $870 | ($50) | 10.0 | 11.5 | 1.5 | $75 | $750 | $863 | $113 | $1,670 | $1,733 | $2,800 | $1,068 | 40.4% | 38.1% |
| J-009 | Panel Upgrade | $1,800 | $2,290 | $490 | 8.0 | 11.5 | 3.5 | $75 | $600 | $863 | $263 | $2,400 | $3,153 | $3,200 | $48 | 25.0% | 1.5% |
| J-010 | EV Charger Install | $490 | $525 | $35 | 4.0 | 4.0 | 0.0 | $75 | $300 | $300 | — | $790 | $825 | $1,050 | $225 | 24.8% | 21.4% |
| J-011 | Lighting Retrofit | $290 | $340 | $50 | 4.0 | 5.0 | 1.0 | $75 | $300 | $375 | $75 | $590 | $715 | $1,100 | $385 | 46.4% | 35.0% |
| J-012 | Outlet Addition | $180 | $165 | ($15) | 2.5 | 2.5 | 0.0 | $75 | $188 | $188 | — | $368 | $353 | $595 | $243 | 38.2% | 40.8% |
| J-013 | Service Call | $95 | $80 | ($15) | 2.0 | 1.5 | -0.5 | $75 | $150 | $113 | ($38) | $245 | $193 | $520 | $328 | 52.9% | 63.1% |
| J-014 | Panel Upgrade | $1,950 | $2,510 | $560 | 8.0 | 14.0 | 6.0 | $75 | $600 | $1,050 | $450 | $2,550 | $3,560 | $3,200 | ($360) | 20.3% | -11.3% |
| J-015 | Kitchen Rough-In | $880 | $900 | $20 | 9.0 | 10.0 | 1.0 | $75 | $675 | $750 | $75 | $1,555 | $1,650 | $2,650 | $1,000 | 41.3% | 37.7% |
| J-016 | Lighting Retrofit | $420 | $395 | ($25) | 5.0 | 4.5 | -0.5 | $75 | $375 | $338 | ($38) | $795 | $733 | $1,400 | $668 | 43.2% | 47.7% |
| J-017 | EV Charger Install | $510 | $490 | ($20) | 4.0 | 4.5 | 0.5 | $75 | $300 | $338 | $38 | $810 | $828 | $1,050 | $223 | 22.9% | 21.2% |
| J-018 | Panel Upgrade | $1,800 | $2,180 | $380 | 8.0 | 11.0 | 3.0 | $75 | $600 | $825 | $225 | $2,400 | $3,005 | $3,200 | $195 | 25.0% | 6.1% |
| J-019 | Service Call | $110 | $90 | ($20) | 2.0 | 1.5 | -0.5 | $75 | $150 | $113 | ($38) | $260 | $203 | $550 | $348 | 52.7% | 63.2% |
| J-020 | Outlet Addition | $240 | $225 | ($15) | 3.0 | 3.0 | 0.0 | $75 | $225 | $225 | — | $465 | $450 | $750 | $300 | 38.0% | 40.0% |
| TOTALS | $2,150 | 25.2 | $1,890 | $32,765 | $6,440 |
"With reliable data organized, we could now take a deeper look into which service types had a healthy margin and which needed reevaluation."
| Service Type | # Jobs | Avg Est. Total | Avg Act. Total | Avg Cost Overrun | Avg Invoice | Avg Gross Margin | Avg Margin % | Notes | Anticipated Margin |
|---|---|---|---|---|---|---|---|---|---|
| ⚠ EV Charger Install | 3 | $793 | $833 | ($40) | $1,050 | $217 | 20.6% | Labor and materials are both about 5% over estimates. Target margin could be increased. | 24.4% |
| Kitchen Rough-In | 2 | $1,613 | $1,691 | ($79) | $2,725 | $1,034 | 37.9% | — | 40.8% |
| Lighting Retrofit | 3 | $713 | $757 | ($43) | $1,300 | $543 | 41.8% | — | 45.2% |
| Outlet Addition | 3 | $423 | $413 | $10 | $698 | $286 | 40.9% | — | 39.4% |
| ⚠ Panel Upgrade | 5 | $2,460 | $3,230 | ($770) | $3,200 | ($30) | -0.9% | Labor and material consistently over. Labor is on average over 50% and materials are on average over 23% more than estimated. Target margin could be increased. | 23.1% |
| Service Call | 4 | $243 | $196 | $47 | $543 | $346 | 63.8% | — | 55.2% |
This analysis produced a clear picture of where the business was and wasn't making money by service type. Certain services had margins that were negative despite a healthy anticipated margin, while others were performing above expectations.
With this kind of visibility, the owner now had a basis for two decisions that previously had to be made on instinct — which service type to prioritize, and where pricing needs to be adjusted. These decisions directly affect whether growth translates into profitability.
After this initial discovery, we worked together toward building a system to maintain accurate records and preserve this visibility. Working together, we put a process in place for tracking the time and materials used on each job.