Medical Practice KPIs: A Monthly Dashboard Built With AI

Track six KPIs monthly: no-show rate, days in accounts receivable, net collection rate, first-pass denial rate, patient wait time and visits per provider per day. Export aggregate totals from your practice management system into a sheet, let AI write the formulas and a short commentary, and compare each number with your own previous twelve months.

A dashboard is only useful if it changes what somebody does on Monday. Most practice KPI pages list twenty metrics and stop; the six here each link to an action. A rising no-show rate means changing reminders, a rising days-in-AR figure means working aged claims, and a long wait means looking at the schedule template. AI does two jobs well: it builds the sheet quickly and writes the commentary. It does not decide which number to worry about, and it should never see a patient record.

Follow me on Instagram@sagnikteaches

The six numbers, defined so they can be compared month to month

Definitions cause more argument than the numbers. Write each one down once, in the sheet, and keep it unchanged. If you change a definition, restart the trend line and say so.

Connect on LinkedInSagnik Bhattacharya

Two choices deserve a line of explanation in the sheet. For no-shows, decide whether a late cancellation counts. Many practices count only patients who did not arrive and did not cancel in advance, and track "cancelled under 24 hours" as a separate column so the two causes stay separate. For net collection rate, note that payments in a month include money for earlier visits, so a single month can look odd; many managers watch a three-month rolling figure alongside it.

Subscribe on YouTube@sagnikteaches

A worked month: a five-provider practice, March

All numbers are illustrative. A five-provider family practice exports its March totals and last month's from the practice management system, without any patient-level data. The inputs:

• Appointments still booked on the day: 1,420 in March, 1,310 in February. No-shows: 128 and 131.
• Accounts receivable at month end: $342,000 in March, $355,000 in February. Charges in the last 90 days: $612,000 and $588,000.
• March charges $206,000, contractual adjustments $41,200, payments received $158,300.
• Claims submitted 312, denied on first pass 34.
• Check-in to roomed: median 14 minutes, 90th percentile 31 minutes.
• Completed visits 1,290 across 105 provider clinic days.

The formulas give these results. No-show rate: 128 / 1,420 = 9.0 percent, against 10.0 percent in February (131 / 1,310). Days in AR: daily charges are $612,000 / 90 = $6,800, so $342,000 / $6,800 = 50.3 days, against $355,000 / ($588,000 / 90 = $6,533) = 54.3. Net collection rate: $158,300 / ($206,000 - $41,200 = $164,800) = 96.1 percent. First-pass denials: 34 / 312 = 10.9 percent. Visits per provider per day: 1,290 / 105 = 12.3.

The story is mostly good, which is itself a useful finding: AR fell by four days and no-shows by one point. The question for the month is the 10.9 percent denial rate and the 90th-percentile wait of 31 minutes. Two numbers that need a closer look is about right for a monthly meeting. If everything is red every month, nobody reads the dashboard.

Exporting the data without touching patient records

Almost every practice management system can export a report of appointments by status, AR by age bucket, charges and payments by month, and claim status counts. Choose reports that show counts and totals. Sort the export so any patient identifier is not in the file: if a report insists on patient names, use a summary variant or delete those columns in the spreadsheet before the file goes anywhere near an AI tool. The safest pattern is to build the totals yourself in the sheet, then paste only the totals into the prompt.

A small routine keeps this honest. Keep one tab called "Raw exports" inside the practice's own storage. A second tab called "Monthly inputs" has twelve rows per KPI input and is the only thing you ever paste elsewhere. Patient-level files never leave their system. If you want a fuller checklist, the tutorial on patient data and AI goes through it.

Building the sheet with a prompt instead of by hand

You do not need to be a spreadsheet expert. Describe your input tab and ask the AI for the formulas. A prompt that works well on a business-plan account:

I have a Google Sheet tab "Inputs" with one row per month and these columns:
A Month, B Booked on the day, C No-shows, D AR at month end, E Charges last 90 days,
F Charges this month, G Contractual adjustments, H Payments received,
I Claims submitted, J Claims denied first pass, K Median wait minutes,
L Completed visits, M Provider clinic days.
Create a tab "Dashboard" with one row per month and formulas for: no-show rate,
days in AR, net collection rate, first-pass denial rate, visits per provider per day.
Show each formula as text so I can check it. Add conditional formatting that turns a cell amber if it is more than 10 percent
worse than the 12-month average and red if more than 20 percent worse. Higher is worse for
no-show rate and denial rate and days in AR; lower is worse for net collection rate and visits.
Handle divide-by-zero by returning blank.

The output normally includes the right formulas and one subtle error. In an illustrative run, the model computed days in AR using charges for the month divided by 30, rather than the 90-day average, which swings with seasonality. A second slip to look for is direction: a model told "red if worse" often colours a rising net collection rate red, because it treated every increase as bad. Check each formula against the table above. The "show each formula as text" instruction is what makes this practical, because you can compare formulas in under five minutes. For more spreadsheet prompting patterns see Google Sheets AI prompts.

Reading the numbers: thresholds that trigger action

A KPI without a trigger is decoration. Agree thresholds once, from your own history, and write them next to the metric. A method that works without outside benchmarks is to take your own twelve-month average and set "amber" at ten percent worse and "red" at twenty percent worse. For the example practice, assume its twelve-month average no-show rate is 9.5 percent and days in AR is 52. March sits inside both. If April brought no-shows to 11.5 percent, that is more than 20 percent worse than the average, so the sheet turns red and the action is to review reminders.

Write what happens on red. For no-shows: check the reminder log for a gap, look at which appointment types and which weekday are worst, and test a confirmation message. For days in AR: pull the 61 to 90 and 90+ buckets, and start the aged-claims routine from prioritising aged claims with AI. For denials: list the top five denial reasons and trace each to a front-desk or coding step. These are decisions the manager makes; AI helps with the analysis.

The commentary prompt, with sample output and fixes

Numbers do not persuade partners; a paragraph does. Ask AI for four short lines using only the table you give it.

Write a monthly KPI commentary for the partners of a five-provider medical practice.
Use ONLY the table below. Do not add causes you cannot see in the numbers.
Format: one sentence on what improved, one on what worsened, one on what to check next month,
and one sentence of questions for the partners. Under 120 words. British English.

[paste the dashboard row for this month and last month, plus the 12-month averages]

A realistic first output reads: "March was a steadier month. No-shows fell from 10.0 to 9.0 percent and days in AR dropped from 54.3 to 50.3, both better than the twelve-month average. First-pass denials were 10.9 percent and waits reached 31 minutes at the 90th percentile; the denial rate is the item to investigate. We will review the top denial reasons next month. Do the partners want to change Tuesday's schedule template?" What you would fix: any invented cause (a common one is "following the new reminder system", when you never changed reminders), and any claim of improvement that is not in the table. Delete anything the numbers do not show.

Five practices, five different dashboards

• A solo practitioner. Three KPIs are plenty: no-show rate, days in AR and visits per day. The sheet is one tab and the review is ten minutes.
• A walk-in clinic. Wait time moves to the top and appointments drop out. The sibling tutorial, an urgent care KPI dashboard, covers visits per hour and arrival patterns.
• A specialist practice with procedures. Add time to next available new-patient appointment and referral conversion, because access is its constraint.
• A practice with an outsourced biller. Days in AR and denial rate belong in the billing contract's reporting schedule; the dashboard becomes a check on their report, not a replacement.
• A multi-site group. The same sheet with a site column, and a pivot that shows the worst site per KPI. Resist averaging across sites, because one bad site hides in the mean.

A mistake that fooled one team

An illustrative practice reported a "nine percent no-show rate" for a year, and then a partner noticed that waiting-list slots filled at short notice were being counted as booked appointments, which lowered the percentage. The definition had drifted when the front desk changed how they recorded same-day fills. The fix was a definition line in the sheet and a rule that any change to how appointments are recorded is logged with a date. When a KPI moves suddenly, check the definition and the data entry before you change the clinic.

What a no-show costs, in a quick sum

A percentage is easy to ignore; a sum is not. Take the example practice: 128 no-shows in March. If the average completed visit brings in about $123 of collected revenue ($158,300 collected divided by 1,290 completed visits, from the same export), the lost revenue is about $15,700 for the month, before counting provider time that could have gone to someone on the waiting list. Cut the rate from 9.0 to 7.0 percent and you recover roughly 28 visits, or about $3,400 a month. Treat that as an upper bound, because not every empty slot can be refilled. The sum belongs in the commentary because it turns a percentage into a decision about whether reminders, deposits or overbooking are worth the effort.

The sheet can hold this as one extra line: no-shows multiplied by average collected revenue per visit. Label it "indicative" in the header so nobody quotes it as a loss figure in a meeting.

Aged AR in buckets, not just one average

Days in AR is an average, and averages hide the tail. Add a small table of AR by age bucket (0 to 30, 31 to 60, 61 to 90, 91 to 120 and over 120 days) with the share of the total in each. Say the example practice has $342,000 in AR: $176,000 in 0 to 30 days (51.5 percent), $84,000 in 31 to 60, $41,000 in 61 to 90, $24,000 in 91 to 120 and $17,000 beyond. The figure to watch is the share over 90 days, here $41,000 or 12.0 percent. Days in AR can fall while the old bucket grows, if new claims are paid fast and old ones are ignored. When the over-90 share rises two months in a row, the action is the aged-claims routine rather than anything about new claims.

Measuring patient wait without a new system

Wait time is the KPI practices most often skip because nobody records it. You can start with two timestamps that most systems already hold: check-in time and the time the patient is roomed or the visit starts. Export the two columns for a sample week with no names, calculate the difference for each visit, and take the median and the 90th percentile. In the sheet, =MEDIAN(range) and =PERCENTILE(range, 0.9) do it. Use the median to describe a typical visit and the 90th percentile to describe the bad days, because the average is dragged around by one very long wait. If your system does not record roomed time, ask the nurses to tap a button or write a time on the schedule for two weeks. A fortnight of rough data beats a year of guessing, and the result usually points to two or three weekday and time-slot combinations that cause most of the waiting.

Charts that people read

A dashboard tab with six small line charts, one per KPI, over the last twelve months, is easier to read than one crowded chart. Ask the AI for chart instructions for your tool, and keep to three rules. Put the oldest month on the left. Draw the twelve-month average as a faint horizontal line. Use one colour for the line and reserve red for a point outside your threshold. Skip pie charts and three-dimensional effects; they make a trend harder to read. Add the number for the latest month as a label above each chart, because partners will often read the number and glance at the shape.

Sorting denial reasons into groups you can act on

A single denial rate does not say what to fix. Export the denial reason counts without patient detail, and ask the AI to group them into front-end causes (eligibility, missing authorisation, wrong demographics), coding and documentation causes, and payer-side causes. Illustratively, 34 first-pass denials in March might sort as 14 eligibility or registration errors, 9 missing or invalid authorisations, 6 coding queries and 5 payer-side errors. The front-end groups are the front desk's to fix, and 23 of 34 is two-thirds of the problem. Check the model's grouping against the reason codes yourself, since it will sometimes put a duplicate-claim denial in the wrong place.

When to add a seventh KPI, and when to drop one

Six numbers will serve most practices for a year. Add a seventh only when a decision is stuck for want of it. A practice that keeps arguing about whether to hire another medical assistant might add visits per support-staff hour; a practice debating a new location might add new-patient volume and the source of referrals. Set a test for every metric on the sheet: in the last three months, did anyone change anything because of this number? If the answer is no, hide it for a quarter and see whether anyone asks for it back. A short dashboard that gets read each month is worth more than a complete one that gets opened twice a year. Review the list every January and write down why each metric stays.

Which AI account to use

For aggregated numbers the privacy risk is low, but a business plan with training off by default is still the right habit. ChatGPT Business and Claude Team are each $25 per user per month billed monthly or $20 annually, with a two-seat minimum; Microsoft 365 Copilot Business is $21 per user per month on annual billing; and Gemini in Sheets comes with Google Workspace Business Standard ($14 per user per month, annual) and above, not with Business Starter (list prices checked in November 2026, so confirm on the vendor pages). The Excel =COPILOT() worksheet function stopped working on 14 September 2026, and old workbooks show #NAME? in those cells when they recalculate, so if you use Excel, ask the Copilot side pane or a chat assistant for ordinary formulas instead. If the tools are new to you, the tutorial on cleaning messy data is useful before you start exporting.

Making the monthly routine small enough to survive

1. On the second working day, run the five exports and paste totals into the "Monthly inputs" tab (twenty minutes).
2. Check the formulas turned the right colours and that no cell shows an error (five minutes).
3. Paste the dashboard row into the commentary prompt, read the output, and delete anything the numbers do not show (ten minutes).
4. Send the one-page result to the partners with a single question for the meeting.
5. Write one action next to each red item, with an owner and a date.
6. Next month, begin by asking whether last month's action happened. This is the part that makes the dashboard worth building.

If you want to go further and automate the pull from your system, automating monthly management reports covers the options. If you would rather map the whole reporting routine with someone, my AI implementation consultation covers that, and the AI guide is the wider starting point.

Practice KPI questions managers ask

Which KPIs should a small medical practice track monthly?

Start with six: no-show rate, days in accounts receivable, net collection rate, first-pass claim denial rate, patient wait time and visits per provider per working day. Together they cover access, cash and flow. Add more only when a number would change a decision you make, and drop any KPI nobody discussed in the last three months.

How do you calculate days in AR?

Divide total accounts receivable at month end by average daily charges. Average daily charges are charges over the last 90 days divided by 90. If AR is $342,000 and 90-day charges are $612,000, daily charges are $6,800 and days in AR is about 50. Use the same window every month so trends compare.

Is it safe to give patient data to AI to build a dashboard?

Do not. Export aggregated counts and totals, such as appointments by week or AR by age bucket, with no names, dates of birth or account numbers. AI only needs the totals to build formulas and write commentary. Keep patient-level reports inside your practice management system.

What benchmarks should I compare my KPIs to?

Compare first to your own last twelve months, which is the benchmark that reflects your payer mix and specialty. Published benchmarks from industry bodies exist, but definitions differ, so check how each is calculated before you compare. A rising trend in your own figures matters more than a gap to someone else's average.

Further reads

• How to Build a KPI Dashboard With AI When You Have No Data Team — General KPI dashboard method when nobody is a data person.
• Law Firm KPI Dashboard: A Monthly Partner Pack Built With AI — A partner-pack layout from another professional practice.
• How to Reduce No-Shows With AI Reminders and Automatic Rebooking — Reminder and rebooking routines that move the no-show rate.
• How Dental Practices Use AI for Recalls and Appointment Reminders — Recall routines that keep the schedule full.
• AI Denial Management for Medical Billing: Group, Appeal, Track — What to do when the denial rate turns red.
• Medical Practice AI Budget: What to Fund First and What It Costs — Which AI spend to fund after the dashboard.
• Medical Billing Client Onboarding: Contracts, Access, AI Reports — A medical billing company's onboarding sequence for a new practice, from contract and BAA to access, enrolment checks and the first report.
• Medical Billing Proposal: Draft Practice Quotes With AI — Draft a medical billing proposal with AI: percentage vs flat-fee pricing, a scope table, an onboarding timeline and a fully worked quote.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Microsoft Support (COPILOT function), Google Docs Editors Help (PERCENTILE), the Google Workspace pricing page (archived copy of 26 October 2026), and the ChatGPT Business, Claude Team and Microsoft 365 Copilot Business pricing pages, checked 4 November 2026. KPI definitions are standard revenue-cycle formulas; all figures in the worked example are illustrative.