Prior Authorization Tracking Spreadsheet
Two documents and three sheets that check every booked appointment against the authorization meant to cover it, before the patient arrives.
Free download · No account needed
Upcoming Service Cross-check
412 booked visits, checked against five fields each
Six weeks of a fictional practice, Ashgrove Rehabilitation. Generated from the schedule, not from the register, which is why the last row can appear at all.
| Status of the booked visit | Visits | Share | At risk | Seen by an expiry report |
|---|---|---|---|---|
| Date, units, provider, site and code all match | 318 | 77.2% | — | — |
| Booked after the valid-through date | 41 | 10.0% | $3,444 | Yes |
| Booked past the point the units run out | 29 | 7.0% | $2,436 | No |
| Rendering provider or site does not match | 14 | 3.4% | $1,176 | No |
| No authorization on file at all | 10 | 2.4% | $840 | No |
| At risk in total | 94 | 22.8% | $7,896 | 41 of 94 |
The other direction is worse. Eighty-seven authorizations expire inside this window and only 23 of them have any visit booked after the expiry, so a report sorted by expiry date asks the practice to chase 64 courses that have already finished.
Remaining columns: Days Away, Auth Valid From, Auth Valid Through, Date In Range, Cumulative Units At This Visit, Units Authorised, Failed Field, Request By, Owner Group, Action, Resolved On.
Every prior authorization tracking spreadsheet on page one is the same object: one row per request, a status column, an expiry date, and conditional formatting that turns the row amber thirty days out. It is a register, and a register cannot show you a problem. Every problem in this area is a collision between an authorization and an appointment, and the register holds only one of the two.
This pack takes both. Every booked visit in the forward window is checked against the authorization meant to cover it on five fields at once. Those five are the date against the valid range, the running unit count including everything booked ahead of it, the rendering provider, the site of service, and the code. A visit fails on any one. Working from the schedule rather than the register is what makes the last category visible at all, because a visit with no authorization has no row to appear on.
Ashgrove Rehabilitation ran it across 412 booked visits and found 94 at risk, worth $7,896 in six weeks. Only 41 of those were visits booked past an expiry date, which is all a register-keyed report can see. The other 53 were units running out on authorizations valid until December, providers and sites that did not match, and visits with nothing on file. Meanwhile 87 authorizations expired inside the window and 64 of them had nothing booked after the expiry.
What's in the pack
Upcoming Service Cross-check
One row per booked visit, generated from the schedule, with the field that failed and the group that owns the fix. Date and unit failures go to authorizations, mismatches to scheduling, and missing files to intake, where the referral tracking pack gates them before anything is booked.
Authorization Register
One row per authorization with the five checkable fields plus units booked ahead, which is the column that turns a number from the past into something that can answer a question about next Tuesday.
Request-by dates, computed per payer
The first uncovered visit less the payer's decision timeframe, less the practice's own median from flagged to submitted, less a buffer. Standard Medicare Advantage determinations run to seven calendar days from 1 January 2026, extendable by fourteen.
Lapse Log
Records when each lapse became knowable rather than when it was discovered, and keeps 'nothing available to this practice' on the list of causes. Reading denials back the other way is the denial to authorization feedback loop.
Tracking Procedure
One page: the two inputs, the window, the run cadence, and which of the three owners each failure mode routes to. Short enough that somebody reads it.
Escalation Note
Raised when a booked visit has no realistic route to coverage, so the practice and the patient decide in advance rather than finding out from a remittance. The clinical half is a medical necessity letter.
The approval you already hold
A Medicare Advantage organization that approved a service through prior authorization may not later deny it for lack of medical necessity, absent good cause or fraud. Where a continuation genuinely needs new criteria, the payer policy criteria summary builds that checklist.
Authorization Expiry and Schedule Watch
A weekly re-run that leads with request-by dates already passed, then the units running out on authorizations nothing else is watching.
How to use it
- 1
Open in River, or download it
Open the pack in River and let the agent build the register and run the cross check, or download the blank Word and CSV files instantly and work through them yourself.
- 2
Send both inputs, not one
The active authorizations from the payer portals, and the next six weeks of the schedule. Neither list can show a problem on its own, and the register version of an authorization is often not the payer's.
- 3
Cross every booked visit against five fields
Date in range, units not exhausted counting everything booked ahead, rendering provider, site of service, code. Start from the schedule so a visit with no authorization still appears.
- 4
Work to the request-by date
Not the expiry. Computed per payer from what that payer has actually taken rather than what it publishes, and it usually lands several weeks earlier than anybody expects.
Frequently asked questions
Is this template free?
Yes. Download the whole pack as Word documents and CSV sheets with no credit card. "Edit with AI" is a separate, optional path for practices that want the agent to build the register and run the cross-check from their own exports. Other packs are in the template library.
What format are the downloaded files?
Word documents (.docx) for the tracking procedure and the escalation note, and CSV (.csv) for the register, the cross-check and the lapse log, zipped into one file. They open natively in Word, Pages, Google Docs, Excel, Numbers and Sheets.
Why is an expiry column not enough?
Because it only tests one of five fields. It cannot see units running out on an authorization valid for months, a provider or site mismatch, or a visit with no authorization at all. At Ashgrove that was 53 of 94 at-risk visits, and 64 of the 87 expiring authorizations needed no chasing.
How often does the cross-check need to run?
Weekly on a fixed day, plus on demand whenever a block of appointments is booked or moved. A run against last week's schedule reports last week's problems, so both inputs get refreshed every time rather than one of them.
Does an approval already granted protect the visit?
For Medicare Advantage, an approved item or service cannot later be denied for lack of medical necessity except for good cause or reliable evidence of fraud. Whether your approval covers this date, code, provider and site is the question, which is why all five are fields.
What about a patient who changed plans mid-course?
A Medicare Advantage plan must not disrupt or require reauthorization for an active course of treatment for a minimum ninety-day transition period when someone enrolls after starting it. The course start date is a field on the register so this can be shown rather than argued.
What does 'Edit with AI' actually do?
It creates a free River account, installs this exact pack as a private workspace, and opens it ready to read your exports. Getting the authorization approved in the first place is a different job, handled by the prior authorization pack.
Check the schedule, not the register
Download the blank pack as Word and CSV files, or open this exact pack in River and let the agent cross your next six weeks of appointments against your live authorizations.
Edit with AI