Free authorization tracker spreadsheet for ABA agencies

Why agencies track this in both directions

An authorization grants a fixed number of 15-minute units of a specific CPT code over a date range. Delivering past the balance is unbillable labor you have already paid for, plus recoupment exposure if it slips through. Delivering well under the approved hours reads as low utilization, and payers cut hours at the next authorization. A tracker that only warns about running out solves half the problem.

The columns, and the ones that calculate themselves

Download the authorization tracker (CSV)

One row per authorized code per client. You fill in the first nine columns; the rest are spreadsheet formulas that work out the burn-down when the file is opened in Excel or Google Sheets. Delete the example row, then copy the formulas down as you add clients.

• You enter: client, payer, authorization number, CPT code, authorization start and end dates, units authorized, units delivered, and units reserved (on the schedule but not yet delivered)
• Units remaining: authorized minus delivered minus reserved
• Burn %: delivered plus reserved, as a share of what was authorized
• Weeks left in the authorization, counted from today
• Weeks of cover left: remaining units divided by your average weekly delivery
• Flag: OK, then WATCH from 80% burn, then CRITICAL from 95%

Reading weeks of cover

Burn % tells you how much is gone. Weeks of cover tells you whether the remainder will last, and it is the number that matters for re-authorization.

Compare it with how long your payer takes to turn a re-authorization around. When weeks of cover drops below that turnaround, the packet is already late, however healthy the burn percentage looks. When weeks of cover is far above the weeks left in the period, the opposite problem is building: the client is receiving well under the authorized hours, which is the pattern that invites cuts at renewal.

Where every spreadsheet eventually fails

A spreadsheet is reconciled after the fact, usually weekly, by whoever owns it. The scheduling decision that overruns an authorization happens days earlier, at the moment a make-up session gets booked. By the time the spreadsheet catches it, the session was delivered.

MegAligna runs the same burn-down live from the schedule and warns at the booking slot, before the session is committed. This spreadsheet is the manual equivalent, and it works: it just depends on someone reconciling it before the schedule moves.

ABA practice management software runs the same burn-down against sessions as they are documented, and ABA billing covers what happens when an authorization runs out mid-period.