WSLHD Activity Based Costing

Data provenance: Cost weights, average LOS, average national cost, and AHPCS bucket percentages sourced directly from the IHACPA NHCDC Public Sector Cost Weights AR-DRG v11.0 2020-21 XLSX (799 AR-DRGs). Episode records are synthetic, sampled proportionally to real national separation volumes from the same dataset.

Actual vs NEP benchmark by hospital

Quarterly episode trend

Hospital summary — FY2023-24

Hospital	Peer Group	Separations	NWAU	Actual Cost	NEP Benchmark	Variance	Status

MDC:

Top AR-DRGs by volume

Actual vs NEP — top 8

AR-DRG detail — real NHCDC cost weights

Source: IHACPA NHCDC Public Sector AR-DRG v11.0 2020-21 | nhcdc_avg = published national average cost

AR-DRG	Description	Seps	Cost Wt	NHCDC Avg	NEP Bench	Actual Avg	Var%	Total	Status

Real AHPCS bucket percentages derived from actual NHCDC cost columns: Ward Medical, Ward Nursing, Allied Health, Imaging+Pathology, Pharmacy, Operating Rooms+Specialist Procedure Suites, and Overhead+Hotel+Depreciation.

Bucket distribution

Allocation — $M actual cost

Cost by MDC — top 10 by spend

Variance distribution — actual vs NEP benchmark

LOS distribution — overnight episodes

High-cost outliers — >1.8× DRG average (DNR QA candidates)

Hospital	AR-DRG	Description	LOS	Actual	DRG Avg	Ratio	Flag

ABF Revenue: Each episode generates funding = NEP ($6,468) × cost weight. Surplus = hospital delivered care below the national efficient price.

Department surplus / (deficit) — $000s

Department P&L detail

Department	Separations	Surplus/(Deficit) $000s	Margin %	Position

Production SQL Library — 6 queries aligned with WSLHD DNR Production and Business Sustainability. Compatible with PostgreSQL and SQL Server.

1. Hospital NWAU & cost summary

Total separations, NWAU, actual vs NEP by hospital

SELECT hospital, peer_group,
  COUNT(*) AS separations,
  ROUND(SUM(cost_weight),1)                       AS total_nwau,
  ROUND(SUM(national_cost)/1e6,3)                 AS nep_revenue_AUDm,
  ROUND(SUM(actual_cost)/1e6,3)                   AS actual_cost_AUDm,
  ROUND(SUM(actual_cost-national_cost)/1e6,3)     AS variance_AUDm,
  ROUND(AVG(actual_cost/NULLIF(cost_weight,0)),0)  AS cost_per_nwau
FROM episodes
GROUP BY hospital, peer_group
ORDER BY total_nwau DESC;

2. AR-DRG efficiency vs NEP benchmark

All 799 AR-DRGs — cost weight, actual vs NEP, efficiency flag

SELECT ar_drg, drg_description,
  COUNT(*) AS separations,
  ROUND(AVG(cost_weight),4)       AS cost_weight,
  ROUND(AVG(nhcdc_avg_cost),0)    AS nhcdc_national_avg,
  ROUND(AVG(national_cost),0)     AS nep_benchmark,
  ROUND(AVG(actual_cost),0)       AS avg_actual_cost,
  ROUND(AVG(variance_pct),1)      AS avg_var_pct,
  ROUND(SUM(actual_cost)/1e6,3)   AS total_cost_AUDm,
  CASE WHEN AVG(variance_pct)<-5  THEN 'Under-budget'
       WHEN AVG(variance_pct)>5   THEN 'Over-budget'
       ELSE 'At benchmark' END    AS efficiency_status
FROM episodes
GROUP BY ar_drg, drg_description
ORDER BY total_cost_AUDm DESC LIMIT 20;

3. AHPCS six-bucket step-down by MDC

Real NHCDC cost bucket percentages — Nursing, Medical, Allied, Imaging, Pharmacy, OR, Overhead

SELECT mdc,
  ROUND(SUM(cost_nursing) /SUM(actual_cost)*100,1) AS pct_nursing,
  ROUND(SUM(cost_medical) /SUM(actual_cost)*100,1) AS pct_medical,
  ROUND(SUM(cost_allied)  /SUM(actual_cost)*100,1) AS pct_allied,
  ROUND(SUM(cost_imaging) /SUM(actual_cost)*100,1) AS pct_imaging_path,
  ROUND(SUM(cost_pharmacy)/SUM(actual_cost)*100,1) AS pct_pharmacy,
  ROUND(SUM(cost_or)      /SUM(actual_cost)*100,1) AS pct_or_sps,
  ROUND(SUM(cost_overhead)/SUM(actual_cost)*100,1) AS pct_overhead,
  ROUND(SUM(actual_cost)/1e6,3)                    AS total_cost_AUDm
FROM episodes GROUP BY mdc ORDER BY total_cost_AUDm DESC;

4. DNR QA — outlier detection

Episodes >3 SD above peer-group mean — DNR production review flag

WITH stats AS (
  SELECT ar_drg, peer_group,
    AVG(actual_cost) AS avg_cost, STDDEV(actual_cost) AS sd_cost
  FROM episodes GROUP BY ar_drg, peer_group
)
SELECT e.episode_id, e.hospital, e.ar_drg, e.los_days,
  ROUND(e.actual_cost,0)              AS actual_cost,
  ROUND(s.avg_cost,0)                 AS peer_avg,
  ROUND(e.actual_cost/s.avg_cost,2)   AS cost_ratio,
  'High Outlier — DNR Review'         AS flag
FROM episodes e
JOIN stats s ON e.ar_drg=s.ar_drg AND e.peer_group=s.peer_group
WHERE e.actual_cost > s.avg_cost + 3*s.sd_cost
ORDER BY cost_ratio DESC;

5. LOS variance & excess bed day cost

Excess LOS beyond P75 benchmark estimated at $1,500/bed day

WITH bench AS (
  SELECT ar_drg,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY los_days) AS median_los,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY los_days) AS p75_los
  FROM episodes WHERE same_day=0 GROUP BY ar_drg
)
SELECT e.hospital, e.ar_drg, e.drg_description, COUNT(*) AS n,
  ROUND(AVG(e.los_days),1)                               AS avg_los,
  ROUND(MAX(b.median_los),1)                             AS median_bench,
  ROUND(SUM(GREATEST(e.los_days-b.p75_los,0))*1500,0)   AS est_excess_cost_$
FROM episodes e JOIN bench b ON e.ar_drg=b.ar_drg
WHERE e.same_day=0 GROUP BY e.hospital,e.ar_drg,e.drg_description
HAVING COUNT(*)>=5 ORDER BY est_excess_cost_$ DESC LIMIT 20;

6. Department ABF P&L

NEP-funded revenue vs actual cost — financial sustainability view

SELECT department, COUNT(*) AS separations,
  ROUND(SUM(national_cost)/1e3,0) AS abf_revenue_000s,
  ROUND(SUM(actual_cost)/1e3,0)   AS actual_cost_000s,
  ROUND((SUM(national_cost)-SUM(actual_cost))/1e3,0) AS surplus_000s,
  ROUND((SUM(national_cost)-SUM(actual_cost))/SUM(national_cost)*100,1) AS margin_pct,
  CASE WHEN (SUM(national_cost)-SUM(actual_cost))/SUM(national_cost)>0.05 THEN 'Surplus'
       WHEN (SUM(national_cost)-SUM(actual_cost))/SUM(national_cost)<-0.05 THEN 'Deficit'
       ELSE 'Breakeven' END AS position
FROM episodes GROUP BY department ORDER BY surplus_000s;

Western Sydney Local Health District — Activity Based Costing