Situation

The client’s internal fraud detection department relied on auditors and data analysts to review multiple exported files, connect the right records, and prepare internal audit reports for fraud analysis.

The problem was not just volume. It was the operating environment.

The team had limited direct access to the source data. Mock data was vague and incomplete. Some external software versions were older. Some internal systems were proprietary. Final reports also had strict upload and formatting requirements. On top of that, previous internal efforts to automate the process had not fully solved the problem.

The client came to ProsperSpark for help building a reporting process that could work inside those constraints while still producing reliable, repeatable outputs.

Solution

ProsperSpark started by reviewing the client’s existing workflow, available reporting files, and required output structure. From there, we worked through follow-up questions and testing to understand how the fraud analysis process worked in practice, not just on paper. That was important because the tool had to function within a highly controlled environment, with limited access to live data and incomplete mock files.

We built the solution in Excel using Power Query, VBA, and supporting query logic. The goal was to create a workflow that could take multiple raw reports, process them in a consistent way, connect related data points, and prepare clean, structured outputs for the internal audit team.

The build included automated connections to multiple source reports so users could bring data into one controlled workbook instead of manually working across separate files. Once imported, the data was cleaned, standardized, and organized into a format the team could use more easily for analysis and reporting. This reduced the need for analysts to manually combine files or rework inconsistent inputs before starting their review.

We also created a Sellers Data Overview tab that brought together the most relevant information in one place. This gave users a clearer working view of the records they needed to investigate and helped reduce time spent jumping between source files. An ISP trusted field was added as part of that overview to support the team’s review process and provide another layer of usable context.

A major part of the solution was the relationship analysis layer. ProsperSpark built a relationship table that connected sellers based on shared attributes such as bank accounts, card names, addresses, phone numbers, and IP-related details. We also created a relationship visualization that sorted those links by strength, making it easier for the team to identify meaningful patterns and investigate potentially connected activity.

To support final report creation, we built logic that populated a Word template from the Excel-based data. We also added a VBA macro that dynamically named and saved reports, helping the team produce outputs in the required format with less manual handling. Checksums were included as well to verify imports and help catch bad source loads before they affected downstream reporting.

Because the live environment included nuances that could not be fully replicated with test data, ProsperSpark first developed the tool against mock files, then tested it with the primary user, and later went onsite to troubleshoot and validate the process using live data. That real-world testing helped resolve issues tied to production conditions and ensured the final tool worked as expected in the client’s environment.

Once the workflow was stable, ProsperSpark trained the client’s data analysts on how to use and maintain the system. The final result was not just a working automation, but a reporting process the internal team could continue running with minimal outside support.

Results

The finished solution gave the client a more consistent and scalable way to produce internal audit reports for fraud analysis.

Instead of pulling details from multiple files and manually assembling outputs, the team now had a structured workflow for consolidating source data, reviewing seller relationships, validating imports, and generating report-ready documentation. That reduced friction for auditors and analysts and made the reporting process easier to repeat across cases.

By centralizing key reporting steps in one controlled workbook, the solution also improved consistency across the process. Source-data preparation, relationship analysis, file naming, and final output creation could all follow a more standardized path. That helped reduce variation from case to case and lowered the risk of missed steps during report prep.

The solution also proved itself in the client’s real operating environment, which mattered given the restricted data access, proprietary systems, and limits of the mock data used during development. It was not just workable in testing. It held up where the team actually needed to use it.

After rollout and training, the internal team was able to use and maintain the tool with minimal outside support. That made the process more sustainable over time and reduced reliance on external help for routine reporting tasks and upkeep.

Just as important, the project built enough internal confidence that the team became a reference point for additional work with the client and related groups. That helped reinforce the value of the solution beyond the original scope.

Because the intake form does not include approved before-and-after KPI values, the quantified improvements below are estimates based on the workflow steps automated and the manual effort removed.

Estimated Improvements

• • 70% to 90% fewer manual steps in audit report preparation
  • 50% to 80% faster turnaround on fraud-analysis reporting
  • 60% to 85% less time spent consolidating and preparing source data
  • 80% to 95% less reliance on outside support after implementation