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
- 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.
- 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.
- 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.
- 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
Book a callGovernance 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.