Case Studies / Furniture protection warranty company
Automated Period-End Financial Reporting
- Client
- Furniture protection warranty company
- Services
- Excel consulting, VBA macros, Power Query development, Workbook architecture redesign, Formula development
- Tools
- Microsoft Excel, VBA, Power Query
A furniture protection warranty company needed its financial reporting workbook rebuilt from the ground up. Data from four separate report types — premiums, losses, banking, and cession activity — had to be connected, reconciled, and output-ready, but the existing file wasn't built to handle that consistently. ProsperSpark redesigned the workbook architecture, automated the data connections, and r
The challenge
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.
When four datasets have to reconcile at period-end, a fragile workbook isn't just inconvenient — it's a reporting risk.
What we built
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.
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
Once the new workbook was in use, the client came back to extend it. ProsperSpark connected their master fee list to the first page of the cession report through Power Query, with macros to select and refresh the source file. We also resolved how manually added Loss Report entries carry into the cession report so they’re captured correctly.
Full original write-up
Automated Cession Reporting Replaced a Manual 4-Dataset Reconciliation Process for Furniture Protection Warranty Company
75%
reduction in manual reporting pre, est.
60%
time saved per reporting perioD, est.
4
datasets connected into one automated workbook
The Bottom Line
A furniture protection warranty company needed its financial reporting workbook rebuilt from the ground up. Data from four separate report types — premiums, losses, banking, and cession activity — had to be connected, reconciled, and output-ready, but the existing file wasn't built to handle that consistently. ProsperSpark redesigned the workbook architecture, automated the data connections, and rebuilt the reporting logic so the team could run accurate period-end reports without manually managing multiple disconnected files.
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.
When four datasets have to reconcile at period-end, a fragile workbook isn't just inconvenient — it's a reporting risk.
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
Once the new workbook was in use, the client came back to extend it. ProsperSpark connected their master fee list to the first page of the cession report through Power Query, with macros to select and refresh the source file. We also resolved how manually added Loss Report entries carry into the cession report so they’re captured correctly.
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. After go-live, the client returned to extend the workbook. That’s a good sign it became part of their regular reporting 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
Let's make your tech work for you, not the other way around. Schedule a FREE no-pressure chat with us today.
Book nowAt a Glance
Client Furniture protection warranty company
Business Challenge
- Four financial datasets needed to reconcile at period-end, but the workbook couldn't connect them reliably
- Rolling into a new period meant manually hunting down and updating values across the file
- Formula complexity made an internal rebuild impractical
Services
- Excel consulting
- VBA macros
- Power Query development
- Workbook architecture redesign
- Formula development
Tools
- Microsoft Excel
- VBA
- Power Query
Market Considerations
- Warranty and insurance companies run recurring multi-dataset reports on tight close schedules
- When that lives in a manually managed workbook, period-end becomes a risk event
Key Takeaways
- Connect datasets through Power Query instead of managing them across tabs manually
- Build the workbook so it can grow with reporting needs
- Automate period transitions so new-year handling isn't a recurring rebuild
- A clean architecture pays off at every reporting cycle

More work
- Mid-sized lighting and energy solutions provider: 40% Faster Quote Turnaround for Commercial Lighting Distributor
Excel Consulting · Excel VBA Development · Microsoft Excel, Power Query and VBA
- Entertainment production company: A Production Company Processed Millions of Game Show Applications in Under a Week
Make automation design · Airtable architecture · Developer training · Implementation advisory support · Make, Airtable
Want results like these?
Tell us what's slowing your team down. We'll walk you through what we'd build.
