All work

Case study, Allegheny County DHS

Budget forecasting and KPI governance

I rebuilt how eight departments forecast, track and review a $1B+ budget. Emergency budget reallocations fell by 80%.

Organization
Allegheny County DHS
Role
Budget Analyst II
Scope
$1B+ budget, 8 departments, 12 divisions, 4 vendor contracts
Tools
SQL, Power BI, Excel, Workday

What moved

Executive reporting cycle
80%
fewer mid-year budget reallocations
12 → 3 days
from month-end close to executive brief
55%
fewer ad-hoc data requests within 60 days

Eight departments, no shared baseline

Allegheny County DHS manages more than $1B a year. The work spans human services, housing, mental health, family preservation and community safety.

Each of the 8 departments tracked progress with its own metrics. There were no shared definitions. Leadership reviews turned into data reconciliation.

Analysts spent 12 business days each quarter pulling spreadsheets and checking conflicting numbers. They also answered 5 to 6 ad-hoc data questions a week.

Forecast errors caused 50 to 60 mid-year fund reallocations a year. About $100M in public funds sat misallocated at any given time.

What I did

The problem was not analytical capacity. Data, finance and department heads had no shared operating contract.

  1. Agreed 8 core KPIs

    I worked with 8 department heads and 4 contracted vendors. We defined 8 standard indicators for allocation velocity, contract burn rate, service utilization and compliance variance.

  2. Built a SQL forecasting and variance engine

    I queried 5 years of spend across all cost centers. The models cover seasonality, legislative changes and labor rates. An early warning fires when spend moves ±5% from plan.

  3. Shortened the executive review

    Interactive Power BI dashboards replaced 60-page PDF decks. Reviews moved from past numbers to forward-looking redeployment.

Adoption across 1,200 staff, without direct authority

Department heads protect their budget autonomy. Changes to reporting often meet skepticism.

I held 1-on-1 sessions with all 16 division leaders and finance partners. I showed how the model protected their funding from clawbacks and cut hours of audit preparation.

The rollout across 1,200 staff finished 3 weeks ahead of schedule, with a 2-person analytics core.

Before and after

Before

  • 50–60 emergency budget reallocations a year
  • About $100M in misallocated or stagnant accounts
  • 12 days to assemble quarterly review reports
  • 5–6 ad-hoc data inquiries a week
  • No automated link to Workday Financials

After

  • 10–15 planned reallocations a year
  • Capital deployed on schedule
  • 3 days from month-end close to executive brief
  • 55% fewer ad-hoc requests through self-serve BI
  • Early-warning alerts tied to the financial ERP

Governance is not about more reports. It is about data that is clean enough, fast enough and trusted enough that leaders spend the whole review deciding, not questioning the spreadsheet.

Arun S. Prema
Book a call