Situation

This client is a furniture protection warranty company that sells extended coverage plans through furniture retailers. When a customer files a claim, the company manages the repair or replacement — and on the financial side, tracks the premium income, loss payouts, banking activity, and cession (reinsurance transfer) data that runs through the business.

Producing accurate period-end financial reports required pulling from multiple source datasets and getting them to align. The existing Excel workbook was built over time and had grown fragile. Input methods weren't consistent, connections between tabs were unreliable, and the logic for handling new reporting periods had to be managed manually. Updating the file for a new year meant tracking down values across the workbook and re-validating downstream totals — work that took more time than it should and introduced risk at each step.

The team had internally explored rebuilding the cession template but found the formula complexity and the interdependence of the datasets were beyond what could be handled alongside daily responsibilities. They needed outside help to get the workbook into a state they could trust and maintain.

Solution

ProsperSpark rebuilt the workbook to connect all four reporting datasets — Premium, Loss, Banking, and Cession — through a structured, maintainable architecture using Power Query and Excel formulas. The goal was to replace the patchwork of manual inputs and fragile connections with a workbook that could handle period-end reporting reliably and be updated without rework.

Key work included:

• • Connected Premium input data to Loss report data with automatic type detection, so the two datasets could be linked without manual matching
  • Rebuilt the Banking input section with a structured table approach, including automatic detection of account type, location, and number through a separate reference table
  • Built automation to handle the transition to new reporting periods and new years, replacing a manual process of finding and updating values across the file
  • Reconnected all Cession report pages to the Banking and Premium input sources, rebuilding the formulas to eliminate dependency on the intermediate input tabs
  • Added output charts for key reporting views
  • Conducted calls, testing cycles, and revisions with the client team throughout, including formula fixes and final validation against the original file

The engagement also included a follow-on enhancement to extend reporting data into additional pages of the cession report through a new external file connection, building on the original architecture.

Results

The client ended the project with a workbook that could be trusted to produce consistent period-end reports. What had previously required manual coordination across multiple datasets and fragile formula logic now ran through a structured, connected workflow.

Rolling over to a new reporting period no longer required tracking values across tabs and re-validating totals by hand. The Banking and Premium inputs feed the Cession report pages directly, so changes in one place flow through correctly instead of requiring updates in multiple locations.

The internal team had already confirmed the logic was working by comparing reports from the new file against the original file during final testing. That validation step gave the team confidence before fully transitioning off the old process.

Because the engagement was fixed-price and the project did not include formal before-and-after time tracking, the impact estimates below are based on the scope of work completed and the manual steps removed.

Estimated improvements:

• • 60-80% less manual prep for period-end financial reporting
  • 50-75% faster reporting cycle time per period
  • Near elimination of multi-file reconciliation work for Banking, Premium, Loss, and Cession data
  • Significantly reduced risk of formula errors and version inconsistency when updating for new periods