How Lewis County Proved Its Grant Office Brings Home Over $5M a Year
We consolidated 30-plus grant-tracking tables into a single relational system and built a public Power BI dashboard that shows residents and legislators exactly how much state and federal funding the county secures - and where every dollar goes.
Lewis County’s Planning and Community Development team pulls in state and federal grant money that funds projects across the county - more than $5 million every year from a single team. On paper the department reads like an operating expense. In reality, every dollar it spends multiplies into outside funding for the community. Quantify built the system that finally made that impact visible.
The challenge
The department’s value was hiding in plain sight. Its grant work was buried in more than 30 disconnected tracking tables spread across multiple years and staff members, with no clear way to show legislators or the public the return on the office’s effort.
The team already had strong tables and knew exactly what they wanted to track. But the tracking wasn’t unified across years or team members, it wasn’t visible to the public, and the tables weren’t relational - which made storytelling nearly impossible. The county wasn’t lacking data or results; it was lacking a way to tell the story.
What we built
We worked closely with the planning team to understand the subject matter and the working tables they already used, then designed a data model, built prototypes, and reviewed them with the subject-matter experts at every step.
We consolidated more than 30 tables down to just 7, with well-designed relationships between them, in a unified relational database built in SharePoint-hosted Excel. That workbook became the team’s single place for data entry and day-to-day grant tracking.
We then linked that workbook to Lewis County’s Azure data warehouse and built a Power BI dashboard for county leadership, along with a public-facing view that shows residents how projects get funded by winning state and federal dollars rather than local taxes.
- A public Power BI dashboard mapping funding across 13 municipalities and 5 county departments
- A SharePoint Excel tracking system with effort, success-probability, and ROI tracking
- Grant data in the county’s Azure data warehouse, ready for further analytics
- In-person training and Standard Operating Procedure documentation so the team owns the system
Technical stack: SharePoint-hosted Excel, Azure data warehouse, and Power BI.
The outcome
Three years of applications, once scattered across 30-plus tables, are now unified with clear award status - and the story the planning department always wanted to tell is finally visible to everyone.
A value multiplier, made obvious. The public dashboard shows total funding secured, mapped by location and ranked by project, making it clear that the office returns far more than it costs.
One source of truth, two audiences. County leadership gets the detail it needs to plan, while residents get a plain-language view of how projects get funded.
Data the team can act on. The consolidated system now supports personnel development, project planning, and budgeting - not just reporting.
“They were thoughtful in their process and mapped out every detail, communicating consistently to keep the project progressing. It helped us organize our anticipated and existing grants and use the data for personnel development, project planning, and budgeting. Efficient and rewarding!”
- Casandra Buell, Director of Planning & Community Development, Lewis County
“The efficiencies and data-driven metrics they provide enhance transparency and efficiency, ultimately improving the service delivery of local government.”
- Tim Hunt, County Administrator, Lewis County
Interested in results like these? Let’s talk.