Fixing the Excel Tool Behind a Financial Advisory Firm’s Core Deliverable

Situation

This client is a global M&A advisor and management consulting firm that has spent more than 35 years building a data-driven edge for the AEC industry. They’ve advised more than 550 firms, completed 250+ M&A transactions, and hosted over 100 executive conferences. A significant part of what makes them valuable to clients is proprietary: a financial and operational benchmarking database built from annual surveys of CEOs and CFOs across the industry.

Each year, the firm distributes survey forms to AEC firm leaders, collects the responses, and runs them through a Peer Benchmarking Analysis (PBA) tool that compares each firm against relevant peer groups. The outputs feed client presentations, board reports, and strategic planning engagements. The quality and accuracy of those analyses matters—clients make real decisions from them.

The survey process ran through a set of interconnected Excel workbooks: a CEO survey master file, CFO survey forms, a respondent database, version control tools, and the macro-enabled PBA tool that processed everything and produced client-ready outputs. The system worked, but it had grown complex over time. Named ranges, VBA macros, and formula linkages ran across multiple files in ways that weren’t always easy to follow.

Two problems surfaced. First, the Peer Group 5 calculations in the PBA tool contained formula errors that were producing inaccurate results. Second, the team needed to add a new benchmarking metric—EBITDA per FTE—to the analysis, which required reformatting and new formula work inside a workbook that was already tightly structured.

On top of that, database connectivity between the survey files and the PBA tool was unreliable, adding friction to a process that already required careful handling.

The firm needed someone who could get into the system quickly, understand how the pieces fit together, make precise improvements without introducing new problems—and be available as needs continued to evolve.

Solution

ProsperSpark started with a full review of the workbook ecosystem before touching anything. That meant analyzing three macro-enabled workbooks—the PBA tool, the CEO survey master file, and version control—alongside four supporting non-macro workbooks including the survey forms and respondent database. The goal was to understand the structure, named ranges, VBA code, formula linkages, and how data moved between files before making any changes. In a tightly connected system like this, that discovery work is what makes everything else reliable.

From there, the work progressed in iterative blocks and covered:

• • Troubleshooting and resolving database connectivity issues between the survey files and the PBA tool, reducing failed imports during survey processing
  • Correcting the Peer Group 5 formula errors, including validation to confirm the fix held across representative data sets
  • Building out the EBITDA per FTE metric in Exhibit 4a—new formula development, reformatting, and integration with the existing PBA structure—then testing with sample survey data across multiple peer groups
  • Reviewing and validating the VBA code and macro behavior, including survey generation and database population logic
  • Ongoing advisory support across multiple blocks of hours as additional needs and questions surfaced

Each phase was tested before delivery. The iterative model kept the work manageable and let the team absorb changes without disrupting an active survey cycle.

Results

The firm ended the engagement with a more accurate, more extensible PBA tool and a cleaner survey workflow. The Peer Group 5 errors were eliminated. The EBITDA per FTE metric was integrated cleanly and validated with representative data. Database connectivity issues that had been causing friction were resolved.

More broadly, the team gained reliable outside support for a proprietary system that’s hard to bring anyone up to speed on quickly. Being able to make targeted improvements—without disrupting an active tool that drives real client deliverables—is what mattered most.

Because no formal before-and-after tracking was in place, the time-savings estimate below is directional, based on the manual steps removed.

Estimated and Confirmed Improvements

• • Peer Group 5 formula errors corrected, removing inaccurate outputs from benchmarking analyses (Confirmed)
  • New EBITDA/FTE metric added and validated without disrupting existing exhibit structure or workflow (Confirmed)
  • Database connectivity issues resolved, reducing failed or incomplete survey imports (Confirmed)
  • 50–75% less time spent on manual survey data handling and prep (Estimate)