How to Track HVAC Maintenance Agreements in a Spreadsheet

# How to Track HVAC Maintenance Agreements in a Spreadsheet

A spreadsheet is a perfectly reasonable place to track maintenance agreements, and for a contractor with thirty of them it is arguably the right answer.

What matters is the shape. A single flat list of agreements cannot answer the two questions that decide whether the book stays healthy — which visits are outstanding and what expires next — and that is why most contractors' agreement spreadsheets quietly stop being used.

The shape: four tabs

PLANS        one row per tier you sell
   ↓
AGREEMENTS   one row per agreement; links to a plan
   ↓
VISITS       one row per included visit; links to an agreement
   ↓
EQUIPMENT    one row per covered system; links to an agreement

The key decision is one row per visit, not a "visits completed" counter on the agreement row.

A counter tells you 1 of 2. A visits tab tells you which one is outstanding, when it was due, how overdue it is, and who was supposed to do it. That is the difference between knowing you have a problem and being able to fix it.

Tab 1 — Plans

Two formulas make this tab earn its place:

cost   = (visits × labour_hours × labour_rate)
       + (visits × parts_per_visit)
       + (visits × travel_per_visit)
       + admin_per_year
       + (expected_repair_revenue × repair_discount_pct / 100)

margin = (price − cost) / price

Most contractors have never seen that second number for their own plans. Why it matters →

Tab 2 — Agreements

Tab 3 — Visits

The tab that does the work.

Tab 4 — Equipment

The formulas that make it useful

Four, and they are the whole point of doing it this way.

Days until expiry — on the agreements tab:

=DAYS(end, TODAY())

Conditional-format anything under 90 in amber and under 30 in red. That single rule is most of a renewal process.

Visits outstanding — on the agreements tab:

=COUNTIFS(Visits!agreement_id, agreement_id, Visits!completed, "")

Overdue visits — on the visits tab:

=IF(AND(completed="", due<TODAY()), "OVERDUE", "")

Filter that column and you have the list that matters most.

Renewal rate:

=COUNTIF(renewal_status,"renewed") /
 COUNTIFS(renewal_status,"<>not-contacted")

Count renewed, declined and lapsed in the denominator. Excluding the ones you never reached produces a flattering number rather than a useful one.

A weekly rhythm

Monday, ten minutes:

1. Sort the visits tab by the overdue flag. Book them.
2. Sort agreements by days-until-expiry. Anything under 90 gets an owner.
3. Check nothing under 30 days still has a visit outstanding — deliver it before asking them to renew.

That is the entire process. It is not sophisticated and it works.

Where it stops working

Honestly, and these are the signals rather than a sales pitch.

Two people need it at once. The moment someone works from a copy, you have two versions of the truth and neither is trustworthy.

Somebody sorted one column without the others. This silently destroys the file and is the single most common way a contractor's spreadsheet dies. If it has happened once, it will happen again.

Technicians cannot reach it. The visit findings are the most valuable data you collect and they are collected in a driveway. If that means typing them up at 6pm from memory, it will not happen properly.

The customer report is a separate job. A spreadsheet cannot produce the document the customer receives, so it gets written by hand or not at all — and a visit with no report is, from the customer's side, the same as no visit.

Renewal reminders are still manual. The spreadsheet can show you the list. Somebody still has to act on it every single week, forever.

It is past a hundred-odd agreements. Not a hard limit, but roughly where the weekly ten minutes becomes an hour that nobody does.

The honest comparison →

Start here regardless

Even if you are about to buy software, build this first.

You will be importing into whatever you buy, and the structure you decide now is the structure you live with. Four tabs with IDs linking them import cleanly. One flat list with "2 visits, 1 done" in a notes column does not.

See the same four objects as working software → Plans, agreements, visits and covered equipment — with the overdue list and the renewal cadence already surfaced.

Related

• HVAC Maintenance Agreement Software →
• Software vs Spreadsheet →
• How Much Should an Agreement Cost? →

Want your existing agreement spreadsheet migrated without losing the history? Talk to BDEVY →

hvac maintenance software hvac maintenance agreement software hvac software for small business