Case study · a real build
Built for a 20+ community operator.
Client not named
From spreadsheet assembly to a shared foundation
We don't name our clients. Ask us about the build in a call.
M+
General-ledger lines
Ingested into the reference warehouse.
Location-mapping records
One identity for each community across five platforms.
+
Source systems
Scheduling, payroll, accounting, clinical and operational.
Workbook values filled
In a weekly workbook test, with every existing formula kept.
The starting point
Information was spread across six or more source systems, including scheduling, payroll, accounting and clinical and operational information. Recurring reporting meant spreadsheet assembly.
The work delivered
Ridgepole built a shared data foundation and automated recurring reporting. Then we used the foundation to show leadership where margins were under pressure, and what to check next.
Room to build next
The connected foundation can support more analysis, internal applications and workflows. Each next capability is scoped around the decisions and work it needs to support.
What the data showed
Where margins were under pressure
Real findings from the first analysis on the new foundation. We rounded the figures and left out every name, so the client stays private.
2x
Costs rose faster than revenue
Over two quarters, monthly operating cost rose about twice as much as revenue, and the operating margin fell about three points. Property taxes, marketing, care wages and bonuses drove most of the rise.
1/3+
Care wages jumped at one community
Care wages per occupied unit rose by more than a third in one quarter, while occupancy fell. Payroll hours, pay rates and staff mix are the next evidence to explain it.
Half
Three communities held half the bad debt
Their private-pay bad-debt charges were large enough to turn a whole revenue line negative. All three also ran an operating loss.
3x+
Bonuses ran at more than three times budget
Nearly every community booked more bonus expense than its budget, and the monthly amount kept rising. The next step reconciles it with the approved program, payroll and accruals.
1 site
One community where the problems met
Low occupancy, overtime, referral fees, bad debt and a double-digit drop in revenue per occupied unit, all at one site with a steady loss. We proposed one review and one action plan for it.
How each finding reached leadership
- The variance, explained
- A recommended action
- A named owner
- A measure to track the result
These are findings for management to confirm with Finance. They are not savings we claim.
What the checks caught in the source data
- The systems disagreed on how many communities there were. We built one dated list, with clear rules for what counts.
- A shared occupancy spreadsheet had broken formulas, and two capacity totals differed by dozens of units.
- Hundreds of ledger lines had no account mapping. We flagged them as a gap and did not guess.
- Budget versions and sign rules differed, which can flip the meaning of a revenue variance.
- More than 1,000 workbook values matched an independent recalculation, and the ledger bridge balanced every community-month.
Live tests
- Live plain-language question. A live natural-language query test returned real community workforce figures.
- Provenance on every run. Every data run carries its source, build, coverage and failure states.
- Fits existing workflows. The weekly workbook test filled values in place. Nobody had to give up their report.
What was built
- PostgreSQL warehouse in a private AWS cloud
- Accounting: general ledger, AP/AR, budgets
- Occupancy, floor plans and rate history
- CRM and sales funnel
- Facilities and maintenance work orders
- Workforce totals
- Explicit location mapping across platforms
- HIPAA technical safeguards
What would you like to understand or get done?
A question your systems can't answer. Work your team repeats. A tool you wish existed. Tell us where you want to start.