top of page
Search

Denial Management Dashboard

  • Writer: Edgard Ledea
    Edgard Ledea
  • Nov 19
  • 2 min read

This project analyzes claim denials across a sample of two thousand encounters to help identify the top drivers of revenue loss and workflow delays. The dashboard summarizes denial volume, denial dollars, and denial rate trends with simple filters for payer, service line, and CPT code.


I built this tool to help revenue cycle teams understand where denials are coming from and how they impact financial performance. The layout highlights the most important answers first so leadership can see the story at a glance.


ree

What This Dashboard Lets You Do

  • View total claims, total denied claims, denial dollars, denial rate, and average days to bill

  • Break down denial dollars by payer, denial code, and CPT code

  • Compare approved versus denied claims by service line

  • Track denial rate over time

  • Filter by payer, service line, CPT code, or claim status


Key Insights

  • Denial dollars concentrated in a few denial codes, especially CO-16 and CO-45

  • Payers show different denial patterns, with Aetna and BCBS carrying the highest denial dollars

  • Primary Care and Imaging had the largest volume of total claims

  • Denial rate trends stay between fifteen percent and twenty five percent depending on month

  • Average days to bill stayed close to five days, with a small number of unbilled encounters


How I Built It

The dataset included realistic billing fields such as payer, CPT code, charge amount, denial code, denial description, service line, billed date, and claim status. I introduced dirty data on purpose to simulate real revenue cycle conditions, including inconsistent date formats, missing billed dates, trailing spaces in CPT codes, and lowercase payer names.

After cleaning the data, I used Excel to create:

  • Helper columns for denied claims, denied dollars, clean claims, claim count, and days to bill

  • PivotTables for denial by code, denial by payer, denial by service line, and monthly denial trends

  • KPI cards for summary metrics

  • Linked slicers for interactive filtering

The dashboard is designed for fast interpretation, clear comparisons, and practical use in revenue cycle operations.


Tools Used

Excel - PivotTables and PivotCharts, Calculated Fields, Slicers, Conditional formatting, Data cleaning, and Standardization.

bottom of page