Medical Claims Coding Dashboard Project
Objective
Design a comprehensive dashboard to manage and track medical claims, their coding, and billing status for a clinic. This project will mimic a real-world data flow between medical coders, clinic admins, and finance teams.
Project Overview
Your clinic processes dozens of insurance claims daily. Each claim contains diagnostic data (ICD-10), procedural coding (CPT), and billing information. The aim is to:
-
Track the status of each claim
-
Ensure coders assign valid codes
-
Monitor billed amounts and flag errors
-
Provide real-time dashboards to supervisors
Google Sheet Structure (3 Tabs)
1. Raw Data (Input Sheet)
| Claim ID | Patient Name | Diagnosis | ICD-10 Code | CPT Code | Units | Rate/Unit | Billed Amount | Status | Coder | Date Submitted |
|---|---|---|---|---|---|---|---|---|---|---|
| C001 | Aman Gupta | Hypertension | I10 | 99213 | 2 | ₹600 | ₹1,200 | Not Started | - | 2025-07-01 |
🔧 Dynamic Field:
Billed Amountshould be calculated using=Units * Rate/Unit
2. Dashboard (Interactive Visual Sheet)
A layout with:
-
Summary KPIs (Total Claims, Pending Claims, Total Revenue)
-
Bar chart: Billed Revenue by Coder
-
Pie chart: Claim Status Distribution
-
Pivot table: Number of claims by Status & Coder
-
Slicers: Filter data by Status, Coder, or Date
3. Coder Summary (Pivot Table View)
Generate views that:
-
Count how many claims each coder has completed
-
Summarize total billing handled per coder
-
Show average turnaround time if
Date Completedis included later
Detailed Tasks and Instructions
Step 1: Data Entry & Formulas
-
Enter mock data for 20–30 patients
-
Add formula in
Billed Amount:=F2 * G2 -
Add validation:
-
ICD-10must be 3–7 alphanumeric characters -
CPT Codefrom a list (e.g., 99211, 99212, 99213, 99214) -
Status from dropdown: Not Started, In Progress, Completed
-
Step 2: Data Validation
Use Data > Data Validation to:
-
Make “Status” a drop-down
-
Restrict CPT Codes to valid values
-
Ensure "Coder" name is selected from a list: Ajay, Radhika, Meera
-
Add a validation message: “Only registered coders can assign codes”
Step 3: Conditional Formatting
-
Green for Completed claims
-
Yellow for In Progress
-
Red for empty coder fields
-
Bold or highlight high-billing claims (> ₹2000)
-
Grey-out rows where status is “Not Started”
Step 4: Create Pivot Tables
Insert pivot tables to summarize:
-
Claims per coder by status
-
Revenue per CPT code
-
Total billed amount by Diagnosis
-
Status count by submission date (daily volume)
Step 5: Insert Charts
-
Bar chart for Billed Revenue by Coder
-
Pie chart for Claim Status Distribution
-
Line chart to show billing trend over days (optional)
-
Combo chart: Units vs Revenue
Use Insert > Chart and connect to pivot data.
Step 6: Add Slicers for Interactivity
Use slicers to let users filter dashboard by:
-
Status -
Coder -
Date Submitted
Place slicers above or beside your charts to keep layout clean.
Step 7: Sheet Protection
-
Lock formulas in Billed Amount column
-
Lock header row
-
Allow only manager to edit coder assignment or CPT code fields
-
Share the sheet:
-
Manager – Editor
-
Coders – Limited Editor
-
Supervisors – Viewer
-
Final Output Dashboard Must Include:
| KPI | Example |
|---|---|
| Total Claims | 30 |
| Completed Claims | 18 |
| Total Revenue | ₹54,600 |
| Top Coder (by Revenue) | Radhika |
| Top Billed Diagnosis | Diabetes (E11.9) |
Bonus Tasks (Optional for Advanced Learners):
-
Add conditional formatting to flag duplicate claim IDs
-
Add a chart showing Turnaround Time per coder
-
Use
=COUNTIF()to track missing ICD-10 or CPT entries -
Connect to Google Forms for claim entry by reception staff
Deliverables
-
One Google Sheet file with 3 organized sheets
-
Dashboard clearly formatted and interactive
-
Protected areas and named ranges
-
Instructions inside the sheet (use cell notes or a comment box)
Learning Outcome
By completing this project, students will:
-
Simulate a real-life medical operations task
-
Practice end-to-end Google Sheets capabilities
-
Prepare for actual data handling in clinics, health tech startups, or admin roles
