Completing Your Client Data Spreadsheet
Business Data and Client Information
Now that you’ve started to put together your client data all in one place, you should have gained some more insight into how your business is situated as a whole, and what opportunities are waiting within.
The majority of the time, gathering the data serves as a good exercise and reminder about things that you were already holding in your head, but hadn’t yet put down into a format like this. Hopefully this gives you some relief and more head-space to work with on this transformative journey.
Last time we spoke about this, we left it fairly open ended with regards to the format of your spreadsheet and the data that we needed from you. Now, we’re going to get into the nitty gritty bits that will help us to help you.
Step by Step
There are a few crucial metrics that we will need going forward, in order to make the most of our analysis and time spent learning about you and your clients. Often this will involve certain metrics, but just as importantly, we need to understand the nuance behind it all. For example, if there’s a client that you have a great relationship with, let us know, or if there’s someone who’s been on this journey with you form the beginning, we also want to know about that. You really are painting a picture at this step, so the more detail you can include for us, the better.
- Name & Age
- Name can be just first names, first and last, it doesn’t really matter. As long as there is a way for us to make sure that we’re talking about the same people when we’re looking at the spreadsheet together.
- Age will usually come out of your CRM as a D.o.B or as a double digit, the age by itself makes the data much easier for us to sort and deal with, so that is our preference. If you are combining records for multiple clients into one entity, please use the age of the eldest client unless the age difference is substantial (E.G. more than 25 years).
- Service Packages
- A majority of the time we find that people stick to service packages, but just not the ones that they have outlined for clients, because everyone needs different things. A breakdown of your service packages (however you deliver them) with the price range and what it entails, along with a breakdown of which clients are receiving and paying for which package is all that we need here.
- Primary Adviser
- This is only relevant if we are working with you along with other advisers. If all of the clients that we will be reviewing are serviced by you, then this does not apply. If some clients are looked after by other advisers then we will need to know which clients are looked after by which adviser.
- Review Date
- It’s not always so easy to get this data out of certain CRM’s, so if you don’t have this handy, then a month and a year will do fine for our purposes. What we are after here is the flow of reviews that you have throughout the year. The scheduling of changes with your clients is important for us to make sure that the process allows for iterations.
- Opt-in Status
- This will mainly be used to track which client groups that you have addressed about their services for that year, and what their response was to continuing their service under the new fee structure.
- Occupation
- What your clients do, and what position they old within their job can go a long way when it comes to learning about them. This isn’t always something that you go into depth about, but to the best of your knowledge add in their occupation and at what level they are at. E.g. James Barden is CFO for a tech company just outside Melbourne and his wife works as a paralegal for a firm in Melbourne CBD.
- Love Factor & Difficulty
- This is where your personal relationship with each client comes into play. We want you to rate your overall relationship with the client, out of 10. How much you enjoy working with them, and how much you know about each other should be the two main factors in coming up with this score.
- Difficulty can be good in the work that we do, because it keeps us engaged and challenged, but that’s not the kind we’re talking about here. For this measurement, rank out of 10 how hard it is to get things done with each client. With 1 being a constant battle to get a hold of them and get things back from them, and 10 being a breeze that doesn’t seem to take much effort at all.
- Business Turnover & Income
- If your clients own their own business or control a large share of a business, then this is important to include (as accurately as you can), especially if you are giving Advice to them about this business. Otherwise, do your best to list their income or if you don’t have the exact figure, ballpark the range for us.
- Net worth & Assets
- Sometimes this can be obvious from the FUA and FUM details above, but not always, so add in Net worth and Assets as you have them available.
- Total Liabilities
- This is something that you may not always have, but if you can ballpark a figure or refer to your notes with the clients to find this it is very helpful to gauge their overall situation.
- Revenue from Mortgage
- If the client has their mortgage with you, add in any revenue that you receive from it.
- Insurance Premiums & Revenue
- Add in any information about the clients insurances that they have with you and the revenue that you receive from it.
- FUM & FUA
- These two things are among the most important metrics that we use when looking at your clients. Funds under Management and Funds under Advice are key to understanding the underlying principles that we take you through. To explain a little further, anything that you take action on within the business is under your management, and anything that you give Advice on but don’t directly manage in the business is under Advice.
- Existing Advice Fees
- What we’re looking for here is the client’s impact on your revenue, and when compared with their service package it can tell us what they are paying for what they are receiving. This can be broken down into a few key sources:
- Recurring Advice Fees
- One off Project Fees
- What we’re looking for here is the client’s impact on your revenue, and when compared with their service package it can tell us what they are paying for what they are receiving. This can be broken down into a few key sources:
- Total Client Fees
- This will be a total of commissions from insurance and mortgages as well as any ongoing advice fees totaled for each client group. Prices should be either all incl GST or all excl GST.
- Client Notes
- Add in any information that you can about the client from your notes. A copy paste from your last meeting notes will do fine, or a summary of what you have been through with the client to date, whichever is easiest.
- Projects & Project Fees
- If you have been through any special projects with a client, add in the name of the project (i.e. what you went through) and what type of fees that you charged for it (i.e. $10,000.00). These would include organising their estate planning or charging more for a complex year.
- New Rows
- Anything that has ‘NEW’ in front of it is for recent changes that have occurred through reviews or something similar. (i.e. At last review the client’s fees were increased by $2000, so in new fees it would read the old fee of $3000 + the new fee of $2000 for $5000 total) This is the same for NEW project fees as well as any NEw revenue from Insurance or Mortgages. This won’t be where you put all of your client updates, but while gathering this data, if there are any recent changes, be sure to add them in here for context around previous conversations with your clients.
Bringing It All Together
This is by no means a small amount of data, or effort required on your part, so before we jump any further ahead in the process, make sure that you let us know if you are having trouble with anything, or need some more time to pull it all together.
We’re here to work with you, so if you need 30 minutes to get your head around what it is we’re actually after, or can’t figure out how to make your CRM do what you need it to, just give us a call or shoot us an email and we’ll be more than happy to jump in and assist where we can. It’s worth it for us to wait a couple of extra days for the data to review it in it’s entirety then doing bits and pieces throughout a few weeks.
Although, with that being said there are a few golden rules we like to follow when it comes to putting spreadsheets together, especially when it comes to numbers and finances.
- Wherever you don’t have the data, ballpark as accurately as possible, and mark down that the number is an estimate.
- Don’t merge cells. This causes a lot of time to be spent on reformatting and stops the sorting of data dead in it’s tracks.
- Use what’s familiar. If it’s Excel or Google Sheets, either way we’re happy with what will make this easiest for you.
- Groups of Clients. If there is a couple or a family that you give advice to as a collective, group them together and total their data. This gives us a full picture rather than a puzzle to put together.
- Better safe than sorry. If there’s something that you think might be useful, but are debating whether to include it, just add it in. If we don’t need it it’s a click of a button to hide it, but if we do need it, there’s potentially much more time co-coordinating to get that data to our end.
We have included this template from last time in case you need to copy over some headings to get you started with some structure for your spreadsheet. Click to the right and you will be taken to the Google Sheet template.
Lastly, we strongly suggest that you make your spreadsheet a cloud document. That is to say you use Microsoft OneDrive for Excel or Google Sheets so that your document is in the cloud as opposed to being saved on your computer.
An alternate method would be to use desktop Excel and to save your document in Dropbox.
The reason for this is so that you can share it with us at The Social Adviser. You may even choose to share it with your peers for input (although there is no requirement to do so).
Additionally, you will be referring to this spreadsheet throughout our work together, and making changes as you proceed, so make this a working copy that is separate to what you may already be using as a business tool internally.
Monthly and weekly updates to this data will be something that we discuss once we get together to go through this, but keep them in the back of your mind for now.