• Login / Signup

    • Welcome Back

    Google icon Sign in with Google
    OR
    Forgot Password?
    I agree to abide by Pharmadaily Terms of Service and its Privacy Policy

    Create Account

    Google icon Sign up with Google
    OR
    By signing up, you agree to our Terms of Service and Privacy Policy
    Instagram
    youtube
    Facebook

    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 Amount should 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 Completed is 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-10 must be 3–7 alphanumeric characters

      • CPT Code from 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

    1. Green for Completed claims

    2. Yellow for In Progress

    3. Red for empty coder fields

    4. Bold or highlight high-billing claims (> ₹2000)

    5. 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

    1. Bar chart for Billed Revenue by Coder

    2. Pie chart for Claim Status Distribution

    3. Line chart to show billing trend over days (optional)

    4. 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

    1. Lock formulas in Billed Amount column

    2. Lock header row

    3. Allow only manager to edit coder assignment or CPT code fields

    4. 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

    1. One Google Sheet file with 3 organized sheets

    2. Dashboard clearly formatted and interactive

    3. Protected areas and named ranges

    4. 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

    ❮ Previous Next ❯