🏋️ Nonprofit · Financial Modeling · SQL · Excel
Membership Capacity & Capital Expansion Analysis
Analyzed 3 years of membership and financial data for Westport YMCA to help leadership make a multi-million dollar capital allocation decision — backed by numbers, not gut feeling.
$25K
Revenue opportunities
The Problem
YMCA had a physical capacity of 12,000 members but 15,000 entries in their system — several hundred in pending status. Leadership didn't know if those pending members were real demand waiting to get in, or just old incomplete applications that had never been cleaned up.
They had a major financial decision ahead: spend millions expanding the campus, stay at current capacity and improve what they had, or use the capital to open a new daycare facility since daycare was their highest revenue program. Three completely different paths, completely different financial outcomes.
They needed data to make the call. Not assumptions, not gut feeling — actual numbers showing which option made the most financial sense.
The Goal
Clean the membership data to understand real demand vs ghost entries. Then model the financial outcome of each capital option — what it costs, what it returns, how long to break even — and give leadership a clear, data-backed recommendation before Q4 budget planning.
The Three Options Leadership Was Weighing
Option A — Full Campus Expansion
Expand physical facilities to accommodate more members. High capital cost, long break-even timeline, incremental revenue relative to investment.
Option B — Stay & Improve Quality
Keep current capacity but invest in improving programs and pricing. Lower risk, but limits revenue upside significantly.
Option C — New Daycare Facility Recommended
Open a satellite daycare location. Lower capital requirement, faster ROI, builds on the program already generating the most revenue.
What I Actually Did
01
Pulled 3 years of membership and financial data in SQL and Excel — membership counts, program revenue by category, operating costs, and billing history.
02
Cleaned the membership database — separated active members, lapsed members, genuine pending applications, and duplicate or dead entries. Turned 15,000 records into an accurate picture of actual demand.
03
Built a revenue model comparing 12,000 members at improved pricing vs. 15,000 members at current pricing — to understand whether capacity or pricing was the bigger lever.
04
Built an NPV model for Option A — full campus expansion. Modeled total capital outlay, projected incremental revenue from added members, and calculated years to break even at realistic occupancy assumptions.
05
Built an ROI model for Option C — satellite daycare. Lower upfront cost, faster return, and leverages the program already driving the most revenue per participant.
06
Benchmarked against 8 regional YMCAs — compared capacity utilization, revenue per member, program mix, and pricing to see how Westport compared to similar organizations.
07
Found $12K in annual operating savings along the way — cost items that were being paid but not generating proportional value.
08
Identified $25K in new revenue opportunities from program pricing adjustments — specific programs where YMCA was priced below comparable regional organizations.
The Recommendation
Based on the models, expanding the full campus didn't make financial sense in the short term. The capital cost was too high relative to the incremental revenue, and the break-even timeline stretched too far out. The stronger financial case was opening a satellite daycare facility — lower capital requirement, faster ROI, and it built on the program already generating the most revenue per participant. Leadership used this analysis in Q4 2024 budget planning to make their capital allocation decision.
Outcomes
💰
$12,000 in annual operating savings identified and presented to executive leadership with a clear explanation of where they were coming from.
📈
$25,000 in new revenue opportunities surfaced from program pricing gaps compared to regional peers.
🏗️
Capital decision made with data. Q4 2024 budget planning used this analysis as the foundation for their capital allocation call — not gut instinct.
🔍
Membership database cleaned. Leadership had an accurate count of real demand for the first time — separated from years of accumulated duplicates and dead entries.