Automated Productivity Dashboard

The Situation

A primary care physician was about to lead a 3-month scheduling experiment. Her clinic was testing a different approach than the rest of the health system: rather than shortening appointment times to hit productivity targets, they’d add two “double-booked” slots per session to absorb same-day demand.

The goal: prove that their approach kept provider productivity above 75% — the organization’s benchmark — without burning out staff or degrading care.

The problem: she needed to track and share results weekly with 16 providers, and had no easy way to do it. The data came from a central office report, dropped into a new Excel tab every week. Pulling numbers by hand, building charts, and updating comparisons would have taken hours — and she’s a doctor, not a data analyst. She wasn’t particularly comfortable in Excel.

What was built

A self-updating Excel dashboard that requires no technical skill to maintain.

Every week, the physician pastes the new report data into a designated area on one tab. The dashboard updates itself automatically — no formulas to write, no charts to rebuild, no numbers to look up.

• Provider productivity chart — all 16 providers, color-coded green (above 75%) or red (below). Colors update automatically as data comes in. A reference line marks the 75% goal.
• Last-week performance table — per-provider breakdown: productivity rate, slots available, patients seen, gap vs. the 75% goal, April–June baseline, and a running cumulative trial gap.
• Clinic snapshot — four summary cards: baseline productivity, baseline vs. goal, trial productivity (populates as July–September data comes in), and trial vs. baseline comparison.
• Before vs. after table — side-by-side view of the pre-trial (April–June) and trial (July–September) periods: productivity rates, total patients seen, variance from the 75% goal.
• Data entry guardrails — color-coded paste zone, a dropdown limited to Pre-Trial or Trial, number-only validation on patient count columns, and step-by-step instructions built into the file.

What the user does each week:

1. Open the file
2. Paste 16–17 rows of provider data into the blue zone
3. Set the date and pick “Trial” from the dropdown
4. Press Ctrl+Alt+F9

That’s it. Everything else is automatic.

How It Was Done

The dashboard was built using a Python script that read the existing weekly report format, extracted and normalized 22 weeks of provider data, and generated the fully-formatted Excel file — formulas, charts, colors, validation rules, and all.

The end product is a plain Excel file. No macros, no plugins, no Python required to use it. The script only runs once (or when the structure needs to change); the file lives on its own after that.

Total active build time: approximately 3 hours.

The Outcome

The physician now has a professional, shareable dashboard ready for the July trial launch. Week over week, she pastes in data and the dashboard tells the story — which providers are above the goal, how the clinic is trending overall, and whether the double-booking approach is working compared to the pre-trial baseline.

What would have been a recurring manual task every week is now a two-minute paste operation.

What Made this project work

Your content goes here. Edit or remove this text inline or in the module Content settings. You can also style every aspect of this content in the module Design settings and even apply custom CSS to this text in the module Advanced settings.

• The source data was consistent week to week — same format, same report.
• Requirements were clear: specific providers, specific metrics, specific audience.
• The end user needed simplicity above all else — the technical complexity was hidden in the build, not exposed to her.