Case Study: Automating Client Data Management for Wealth Management Firm

Client type: Financial Services - Wealth Management

Project type: One off project

Overview

The project involved creating an automated system to review client data from multiple spreadsheets, ensuring that key attributes such as ISA allowance usage, portfolio status, rebalancing needs, and death beneficiary nominations were accurate and up-to-date. Additionally, the project required email consistency checks across three platforms to ensure no discrepancies or missing email addresses. This solution aimed to streamline client data management, reduce manual checks, and provide a clear overview of each client's status.

Problem

The client needed to analyse data from various spreadsheets and platforms to ensure accurate and consistent information for each client. Specifically:

• ISA Allowance: The client needed to identify whether each client had used their ISA allowance or had remaining availability.

• Portfolio Rebalancing: They needed to determine if client portfolios were within tolerance or required rebalancing.

• Portfolio Updates: Some client portfolios were outdated and needed updating to the latest version.

• Death Beneficiary Nominations: The system needed to verify whether each client had nominated a death beneficiary.

• Email Consistency: Emails were stored across three platforms. Ensuring email consistency across all platforms and flagging missing emails was a critical need.

These tasks were previously handled manually, making the process time-consuming and prone to errors. The client required an automated solution to check all these aspects for each client and highlight any discrepancies or missing information.

Solution

A comprehensive system was developed in Excel to automate the review of client data across multiple sources, ensuring consistency and accuracy. The solution consisted of several key components:

• ISA Allowance Verification:

• A formula was created to automatically check whether a client had used their ISA allowance by cross-referencing the client’s ISA allowance data with predefined thresholds. Clients with available allowances were flagged, enabling easy identification of those who could still contribute to their ISAs.

• Portfolio Rebalancing Check:

• The system reviewed portfolio data to determine if any of the client’s investments required rebalancing. A formula was developed to check each portfolio’s relative weight and flag portfolios that fell outside of the predefined tolerance range, indicating a need for rebalancing.

• Portfolio Version Update:

• The system compared each client’s portfolio version to the current version and flagged those that were outdated. This ensured that portfolios were reviewed and updated as necessary.

• Death Beneficiary Verification:

• For each client that had a pension, the system cross-referenced death beneficiary nominations to ensure that clients had a designated beneficiary for their pensions. If no beneficiary was found, the system flagged the client for follow-up, ensuring that this critical aspect of estate planning was not overlooked.

• Email Consistency Check:

• Email addresses were stored in three platforms: back office, client investment platform, and an email campaign system. The Excel solution compared email addresses across all three platforms to ensure they were consistent. If discrepancies were found, or if an email was missing in any platform, the system flagged the variance. A formula was also developed to handle cases where no email was found across all platforms, marking the client for further review.

• Automated Variance Detection:

• The solution included robust variance detection to highlight discrepancies in data across the different platforms. If emails or other attributes were mismatched between platforms, the system automatically flagged the source of the variance, making it easy for the client to identify where corrections were needed.

• Comprehensive Client Summary:

• The final output was a single client portfolio summary sheet that consolidated all key data points for each client, showing whether they had used their ISA allowance, whether their portfolio was up-to-date, whether rebalancing was required, if they had nominated a death beneficiary, and whether their email addresses matched across systems.

Outcome

The project successfully automated the previously manual process of checking client data across multiple platforms. The client now has a streamlined, automated system that:

• Highlights ISA allowance availability.

• Detects portfolio rebalancing needs and whether portfolios are up to date.

• Details whether death beneficiaries are in place.

• Checks for email consistency across platforms.

• Flags discrepancies and missing data for follow-up.

By automating these checks, the client significantly reduced the time spent on manual data reviews and improved data accuracy. The system provided actionable insights into portfolio management, ISA contributions, and client communication, enabling the client to proactively address any discrepancies and maintain up-to-date records for each client.

In addition to the Excel solution, a comprehensive "How-To" guide was created. This guide enables users to easily make modifications, add new data, or replicate the formulas across other sheets. It ensures that users can seamlessly adapt the solution to evolving needs without requiring external support.

Want to learn how Lagom Consulting can help you achieve similar success?

Get in touch today to discuss how our marketing and management consulting services can support your business growth.