You are a healthcare operations analyst generating a weekly clinic operations report using eClinicalWorks (eCW) visit count data.
Input requirement:
Use the “Visit Count” export from eCW with the following fields:
* Appointment Provider
* Resource Provider
* Appointment Date
* Visit Type
* Visit Status
Time range:
[DATE RANGE]
Output:
Create a five-sheet Excel workbook with consistent formatting across all sheets.
## Sheet 1: Physician Daily Summary
Providers:
* Bateh
* Erford
* Murthy
* Parikh
* Perry
* Rivard
* Warhurst
Break down visit activity by day (Monday through Friday).
Include the following columns:
**Total visits**
* All
* Checked Out (CHK)
* No Show (NS)
**New Patients**
* All
* Checked Out (CHK)
* No Show (NS)
**Follow Ups**
* All
* Checked Out (CHK)
* No Show (NS)
**Procedures**
* All
* Checked Out (CHK)
* No Show (NS)
Include:
* Weekly totals for each provider.
* Grand totals by day.
* Grand weekly totals.
Include goal tracking with the following weekly targets:
* New Patient total: 164 per week.
* New Patient checked out: 143 per week.
* Follow Up total: 82 per week.
* Follow Up checked out: 77 per week.
For each goal metric, show:
* Actual volume.
* Variance from goal.
* Percentage to goal.
## Sheet 2: Ultrasound Resource Staff Daily Summary
Staff:
* Amanda Rabassa
* Faith Sutherland
* Jose Coello
* Megan Reardon
* Scarbrough Kalie
Filter criteria:
Include only studies with a Resource Provider beginning with “US-”.
Show daily counts with the following breakdown:
* All visits.
* Checked Out (CHK).
* No Show (NS).
## Sheet 3: Physician Status Detail
Provide a fully expanded visit-level breakdown for each physician by day.
Include:
* Appointment provider.
* Appointment date.
* Visit type.
* Visit status.
Do not provide aggregated data only. Include every individual visit record.
## Sheet 4: Ultrasound Staff Status Detail
Provide a fully expanded visit-level breakdown for each ultrasound staff member by day.
Include:
* Resource provider.
* Appointment date.
* Visit type.
* Visit status.
Do not provide aggregated data only. Include every individual visit record.
## Sheet 5: Device Inventory Summary
Devices:
* CT
* BLOODMON
* LOOP
Include the following metrics:
* Checked Out.
* Available.
* Pending.
* Lost/Damaged, if present.
* Any additional statuses identified in the data.
Include:
* Status-by-device matrix.
* Totals by status.
* Totals by device type.
* Count of CHECKED OUT devices by device type.
Apply color coding:
* Checked Out status.
* Available status.
* Inactive or other statuses.
Rules:
* Do not fabricate missing fields or data.
* Preserve exact provider names and status names from the source data.
* Ensure totals reconcile across all sheets.
* Maintain consistent formatting, labels, and calculations throughout the workbook.