How to Manage Your Financial Services Clients From a Spreadsheet (+ How SMS Can Help You)

Do you know how financial services increase revenue? 

They keep their clients happy. But what if you forget important dates and figures?

This is where simple solutions like spreadsheets come into play. You can create entire databases to manage clients and never have to rely on your memory ever again. Or expensive software.

This article will teach you how to create spreadsheets for your financial business. You will also learn how to maximise their usage.

Let’s get started. 

SMSCountry is the best bulk SMS service for financial service providers. Learn more about our SMS services.

Are spreadsheets still relevant for client management? 

1 in 8 people on Earth use Excel. In 2021, there were 3.9 million Excel users in the financial industry alone, which accounted for 27.2% of the total.

Spreadsheets may not be a comprehensive solution, but you can use them to:

• Analyse data quickly and efficiently.
• Track tasks and activities.
• Record meeting notes and track progress.
• Calculate metrics, and generate reports to gain insights into trends and performance.
• Structure the data in whichever way you like.
• Create a dataset you can access across various devices.

All this makes spreadsheets indispensable for your client management. 

• The Greatest Benefits of Bulk SMS For Financial Services & Banks
• The Best SMS Templates For Financial Services

How to manage your financial services clients from a spreadsheet?

Let’s take a look at how we can do this in Excel.

Setting up your spreadsheet for client management

Let’s set up a spreadsheet.

1. Designing an efficient layout

Start with gathering the essential information you want to track and manage. 

For example, if you are a personal wealth advisor, add a column about a potential wealth portfolio. This will help you see the potential revenue you can make. 

But if you provide tax consultation, you can segment your clients according to the tax slabs. Either way, you need an efficient layout to see your clients clearly. Also, such a layout will give you their information at a glance. 

This may require you to create multiple sheets for the same client. And then link data between sheets containing the different aspects.

Ensure you use one column for one data type and use descriptive labels. For example, when creating a lead sheet, enter all data separately. 

2. Utilising templates and spreadsheet functions

If you don’t know where to start, use any of the many financial services templates available online. You can also try the templates that come with your spreadsheet software. 

Once your sheet is set up, enter your data as you get them. 

For our example, we created a spreadsheet to manage leads and opportunities. You get a lot of leads and opportunities from your marketing efforts.  

Use basic formulas and functions like COUNTIF, SUM and AVERAGE to add meaning to the sheet. You can also use formulas to derive investment returns, net worth and financial ratios.

Use the “Sort & Filter” option to sort and filter data. This option only shows you the data that you select.

While “Conditional Formatting” helps you see trends within your data. Conditional formatting changes the format of data showing you specific information. You can flag overdue tasks or colour code different types of investments. The options will depend on your filtering requirement.

3. Considerations for different financial service businesses

To manage leads and opportunities, consider the following headers for the columns:

• Client name: as provided by the client. This can be a person’s name or an organisation’s.
• Contact name: the person who you spoke to.
• Job title: the job title of the contact person.
• Date of last contact: the date the contact person talked to you.
• Date of next contact: this date will depend on your project status. You can leave it empty or fill it with a tentative date.
• Lead source: the source of the lead.
• Lead status: the current status of the lead.
• Next action: you can pre-define a set of actions here or enter it manually based on the status of the lead.
• Email address: the email address of the client or contact person.
• Phone: phone number of the contact person.
• City: the city your client is located in.
• State (optional): the State your client is located in.
• Zip (optional): the Zip number.
• Country (optional): use this header if you serve clients from multiple countries.
• Notes: for additional notes that do not fit into the other headers.
• Size of the deal: the revenue you pitch to the client.
• Probability of the deal: the percentage you think the client will approve.
• Weighted forecast: the numeric value of the probability your client will approve.
• Deal stage: the stage you are at with the client.

If you run an investment management business, consider adding the following columns:

• Risk tolerance: the risk your client will tolerate when investing in the market.
• Investment goals: the goals your client wants to achieve with their investments.
• Return values: the ideal return values to achieve the goals.
• Individual holdings: the holding of each client. You can create separate columns for different kinds of holdings.
• Buy/sell dates: the date your client decides to buy/sell their investment.
• Buy/sell quantities: the quantities of their investment they buy/sell.
• Prices: the price your client buys/sells their investment.

If you provide financial planning services, consider adding the following columns:

• Income: the income of each client.
• Expense: the expense total of each client.
• Assets: the asset total for each client.
• Liabilities: the liabilities for each client.

Use separate sheets to track:

• Retirement planning: Use formulas to estimate retirement savings for each client.
• Education funding: Use formulas to reach an education funding goal.
• Debt reduction: Use formulas to create a loan amortisation schedule to reduce the debt for each client.

As an insurance service provider who uses spreadsheets to track clients, consider adding the following columns:

• Client insurance policies: the policy number your clients have purchased. 
• Policy details: the type of policy and any other highlights that can not be covered in other column headers. 
• Coverage amounts: the value that the policy insures your client for.
• Premiums: the price paid by your client.
• Renewal dates: the renewal date of the policy.
• Claims date: the date your client got their claim
• Claim amount: the amount paid out as claims by the insurance firm.
• Resolution: the resolution reached between the client and the insurance firm.
• Policy beneficiaries: the insurance policy’s beneficiaries in case the client is deceased.
• Change/updates to policy: all the updates rolled out by the insurance firm.

And lastly, if you run a tax consultancy, you should consider the following headers:

• Income sources: add columns for all kinds of income sources.
• Deductions: add columns for different deductions your client is subject to.
• Tax filing status: the tax paying status of your client.
• Tax filing deadline: the date after which your client may be charged a penalty.
• Estimated tax payment: your estimated calculation of your client’s tax.
• Tax paid: the actual tax paid by your client.
• Tax-related documents: any updates you receive related to tax. You can add links to files or documents also.
• Income statement: You can add a new sheet or link their statement in the column.
• Balance sheet: You can add a new sheet or link their balance sheet in the column.
• Cash flow statement: You can add a new sheet or link their cash flow statement in the column.

See the top bulk SMS service providers to send transactional and alert SMSes to your customers. See the top OTP service providers to help your customers secure online transactions.

Managing client data

Here’s how to manage your client’s data on a spreadsheet.

1. Data entry and verification

Enter client information into the spreadsheet accurately and consistently. 

To reduce errors and ensure data is verified, limit the entry values in cells. Use the ‘Data Validation’ tool to pre-define the values in a drop-down list.  

2. Regular updates and maintenance

As you add more data, it can become challenging to remember which part needs updating at what time.

So create a schedule, and set time aside to review and update it. You can do this at the end of every day or thrice a week. This will keep client information accurate, track activities, and update performance data. 

Remember, regular reviews provide an opportunity to close gaps and see risks. This exercise also opens up further engagement opportunities.

Tracking financial transactions 

Here’s how to track financial transactions on a spreadsheet.

1. Recording different types of transactions

The specific types of transactions vary from application to application. Here is a general overview of the type of transactions you can record in your spreadsheet:

• Deposits and withdrawals: record any cash deposits or withdrawals made by your client into or out of their accounts.

• Investment purchases/sales: track your client’s sale and purchase of financial assets like bonds, stocks or mutual funds.
• Dividends and Interests: record any income your client earns on investments made.
• Fees: track any fees your client must pay, like bank and advisory fees.
• Loans and their payments: record all loans your client takes out and any repayments they make.
• Contributions: keep track of your client’s contribution to retirement or savings accounts.
• Insurance premiums and claims: keep track of insurance payments and claims made by your client.
• Tax payments: track all their tax payments.
• Other expenses: record expenses like healthcare, donations and other business expenses.

Note: Any details you add to the transactions will depend entirely on your financial service. 

For instance, as a tax provider, you can record tax transactions in more detail and add more columns. However, as a personal financial advisor, a simple tax payment column may be enough.

2. Generating financial reports and statements

You can build summaries for your client using charts and graphs from spreadsheets. 

The dashboard displayed a summary of our leads and opportunities in our example. This showed us the potential revenue. 

You can also build specialised summaries for your clients. Based on the data, you can build:

• Transaction summary: this report summarises financial transactions for a specific period. It shows you the deposits, withdrawals, investments and expenses.
• Account summary: report showing the current balance with all the transactions to date.
• Performance report: this report visualises the investment performance of a client’s assets over a specific time. You can show projected numbers or past performance.
• Fee and expense report: this report shows all the fees and expenses charged to your client’s account.
• Tax report: this report summarises all the deductions, taxable income, and tax liabilities.
• Net worth statement: this is a simple report that shows you how much your client is worth by subtracting their liabilities from their assets.
• Investment portfolio report: this report shows you the client’s investment portfolio. 

Can I integrate my spreadsheet with other software? 

Yes, you can integrate spreadsheets with other software.

Spreadsheets, in our case Excel, support various integration methods like saving data as CSVs and creating database connections and APIs. 

A popular approach is using add-ins or plug-ins to connect to online services. SMSCountry provides an Excel SMS plugin that you can use to communicate with your clients from your spreadsheet.

How to set up SMS client communication directly from the spreadsheet? 

Here’s how to set up the SMSCountry SMS plugin to send SMS from your Excel spreadsheet.

1. Setting up SMS from Excel for communication

To get started, find the Excel plug-in in your SMSCountry dashboard after you signup. Click on “Features” and “Excel” to get to “Click here to download Excel”. Once you have downloaded the plug-in, install it on your computer.

Contact our support team if you have issues finding and installing the plugin.

2. Integrating the SMS plug-in

As the installation completes, you should be able to see “SMSCountry” in the ribbon on Excel. Click on this to open the entire tab. You will see all the options available to you. Select “Login” to log in to your SMSCountry account. Ensure you have an active internet connection.

3. Automating the SMS

Once logged in, now you can set out to create your SMS. You can send SMS to your clients to inform them of updates to their accounts or to remind them of crucial dates. 

Click on “Send SMS”, which will show a pop-up where you can compose your SMS.

You can schedule your SMS for the future or preview and send it as soon as you compose it.

What are the benefits of spreadsheet management for SME financial services?

• Cost-effective solution: If you have a limited budget, spreadsheets like Excel and Google Sheets are cheap and affordable.
• Easy to use: Spreadsheets do not require complex technical expertise. You can work effectively on spreadsheets without formal training. 
• Familiarity and customization options: Spreadsheets offer a reduced learning curve. They also allow you to structure data according to your needs. You can format the columns according to your business needs and adapt them as you grow.
• Data analysis: You can perform simple calculations, create charts and generate reports. All this is useful in gaining insights into data trends and tracking KPIs for your clients.
• Tracking tasks: Log interactions into spreadsheets as it becomes available. You can also track deadlines and create meeting notes. Organise your client management workflow so that nothing is overlooked.

While CRMs and custom-made software offer a range of tools, they come with a recurring price tag. CRMs offer seamless integrations with other software and are often cloud-based solutions. 

If you are an SME or just starting in financial services, use spreadsheets to manage your clients. Not only will you keep your overheads low, but you will also get to learn the ins and outs of client management.

What are the limitations of using spreadsheets for financial services client management?

• Spreadsheets may become cumbersome as the volume of client data increases. Handling complex relationships and extensive transaction histories becomes challenging and time-consuming.

• Manual data entry and calculations are prone to errors besides being time-consuming. As your client base grows, you must automate to streamline processes.
• Data integrity can be challenging with multiple users or manual data entry. There is a higher risk of errors, inconsistencies, and accidental deletion.
• For Excel, collaboration opportunities are limited, especially when multiple team members need simultaneous access. 
• Generating complex reports requires advanced reporting capabilities. This includes graphical dashboards, trend analysis, or client-specific reports. Spreadsheets offer limited options to do this.
• Spreadsheets lack security features to protect sensitive client data. Ensuring access controls, data encryption, and compliance with privacy regulations is challenging.

Plus, spreadsheets, like Excel, are typically stored locally for security reasons. This limits access to information when working remotely. Cloud-based Spreadsheets, like Google Spreadsheets or CRMs, provide a flexible option to access data from anywhere. 

What is client management in the financial services industry?

Client management is managing relationships with clients to meet their financial needs. The process includes understanding and serving clients in the financial industry. 

Here is what you can expect to see in the financial services industry:

• They acquire and onboard new clients with marketing, lead generation, and prospecting. Financial services companies identify potential clients and convey value to them. In return, these clients engage with their services.
• They understand the unique financial needs of all their clients. They determine their client’s risk tolerance and investment objective. And then, they come up with tailored, personalised solutions.
• They communicate with their clients consistently, building trust and rapport. Financial services strive to create positive experiences by being attentive to customers.
• They offer comprehensive, timely advice and execute financial transactions on your behalf. Financial services companies ensure that your queries are addressed promptly.
• They make sure you work with them long-term. It is in their best interest to foster loyalty, communicate and enhance your experience. This also helps create a positive word of mouth.

Client management also entails educating about trends and news from the financial industry. 

To this end, financial services companies may arrange webinars, seminars or send newsletters.  All these measures drive business growth and maintain a competitive edge.

Stay connected to your clients with SMSCountry

You made it to the end.

This means you can now create a spreadsheet to manage your clients. You also have an idea of what to include in your spreadsheet. And can expand your spreadsheet without getting lost in the process.

But it gets better.

You can now deliver your customers a 5-star client experience with alerts, account updates and transactional messages via SMS. Learn more about our SMS services to help you delight your clients.

Frequently Asked Questions