Save money, dazzle clients with Excel

We work with a lot of numbers in our role as advisors. Part of the challenge is to make numbers meaningful to our clients without having to re-invent the wheel at every review. Here are a few features of Microsoft Excel we use in our practice to save time and wow our clients.

To see some of these features in action, view our Client Dashboard™ video.

Conditional formatting

This is a feature that’s been around for a long time, however, in the past it felt like only those with a PhD could figure it out. Fortunately in Excel 2007, Microsoft made it much easier to add conditional formatting. Essentially, if the data in a particular cell meets a specified condition, the data in another cell is formatted a certain way.

For example, in our Client Dashboard™ we compare a client’s current retirement savings with where we projected she would be. If she is ahead of schedule by $25,000, the numbers automatically appear in green font. If she’s behind schedule, the font is red. This allows clients to quickly see on the Dashboard where they’re ahead of schedule (green) and where they need to focus their attention (red).

Charts

It can be said that “a chart is worth a thousand words”. Telling a client that their $347,000 RSP is behind plan by $3,000 might give the impression of being way off track to a client who needs a more visual explanation.

In our Client Dashboard™, we also include a bar chart that shows clients where they are today and where we projected they would be. In the scenario above, the difference between the bar showing where the client is ($347,000) and the bar showing where we projected he would be ($350,000) would probably be negligible to the human eye. This could help a client visually put things into perspective and understand that in reality, he’s very much on track. Once you have your data inputted on your spreadsheet, adding a simple chart takes less than a minute.

Checkboxes

Checkboxes don’t just look neat, they’re very practical. In our Client Dashboard™ we use checkboxes to visually show our clients potential risks.

For example, if they have the right amount of critical illness insurance in place, we check off “Protected”. But if they’re short on disability insurance, we check off “At risk”. We then use conditional formatting, explained earlier, so that the answer selected appears in green or red. The checkboxes save us significant time as we do not need to re-write “you are covered for critical illness insurance” at every review.

Macros

Any repetitive task can be sped up significantly by creating a macro. A macro records you performing a series of mouse movements, typing and formatting text or cells to accomplish a task.

Once recorded, instead of repeating the series of steps over and over for each piece of data, you simply click on the macro and it’s done for you. For example we regularly export a report to Excel to monitor cash balances in client accounts. We need clients to have sufficient cash to pay our monthly management fee, but on the other hand we don’t want large cash balances to sit in accounts for too long. We created a macro that sorts our data alphabetically by client name, and then calculates what % of a client’s portfolio is sitting in cash.

If there’s too much cash, the account is highlighted in blue. If there’s too little cash, it’s highlighted in red. If there just the right amount of cash, it’s highlighted in green. With a macro the report is formatted in about 3 seconds and we can scan through our entire client base quickly. Macros work well in both Excel and Word.

Learning how to use Excel

I might sound like an Excel guru, but the secret is that most of what I’ve been taught in Excel was learned by Google’ing “How do I _____ in Excel?”

If you want to use any of the ideas mentioned above, Google them and there will be no shortage of how-to-guides and even videos showing you how to create in Excel.

If you prefer to outsource, basic Excel programming is also taught to first year business students in most university and college programs. Paying a summer student a few hundred dollars to create a custom rebalancing calculator, automated checklist or macro could save you and your assistant tons of time going forward, freeing up more time to spend with clients.

Mathieu Paradis, B.Comm., CFP, CLU, FMA is co-founder of AdvisorPractice.com which offers advisors practical solutions to transition to a financial planning practice and offers a 12-week training program. He is a financial advisor and offers his clients comprehensive life goals financial plans.