Ensure your Insurance Agency is excelling in Excel!
There’s a good chance that you’re in Excel frequently, but your skills aren’t at expert level. The good news is, you don’t have to be at expert level for Excel to improve your business day to day, especially when you apply a few of the easy tactics below.
You’ll be able to save time through efficiency, have higher confidence in your decisions, and gain a better understanding of results as if you were an expert in Excel!
1) Email campaigns
When going to send mass emails, it’s ideal to make each email seem as personalized as possible. Doing that through a CRM is often difficult, aside from just adding the recipient’s name at the beginning of the email.
In Excel, there are ways to make each email seem much more personalized. A little extra effort can go a long way.
Option 1: Building a formula
The goal here is to get the unique text of the emails you want to send.
You can export your emails and names from your CRM into Excel. From there, you can add a personal message along with the bulk of them email in separate cells and create a very simple formula that combines all of them into the email text.
The only manual piece is the personalized text (which you’d have to do regardless). The name and email are automatically downloaded from CRM, and the main body of the email is the same in every email so it can just be copied to every recipient. Below is an example.
Obviously, there is only one entry here, but it really is as simple as copying the Generic Message Body (cell D2) and that formula in cell F2 down to every other entry you have!
Option 2: Writing a Macro
The goal here is to automatically send mass customized emails with the click of a button.
This actually takes a more expert level of Excel, but I’m including it in here to show the potential of Excel for email lists.
In Excel VBA, you can create a macro that grabs the name, email, and personalized message for each recipient, then copies an email draft in your Outlook that has the main body.
So, in this method, you have downloaded the name and email from your CRM, add in a personalized message, have a generic message body that everyone gets (already in the draft email), and a macro sends a bunch of unique emails at once! You can even add unique closing statements for everyone if you’d like too!
2) Marketing ROI and analysis
The goal here is to easily see what works best and what should be de-prioritized.
To do this, you simply need costs and conversion numbers.
You can typically easily find (or already know) how much you spend on certain marketing campaigns – partner ads, Facebook ads, email campaigns, etc. are all common sources that are easy to find costs for.
Slightly more challenging is seeing who became a client from each of those individual avenues. There are plenty of ways to do it, one of which is simply asking your new clients if you don’t have tracking in place. If you do have tracking in place, this becomes a lot easier and you’ll usually get better and more complete data.
Once you have the total costs and the clients you gained from each initiative, you can very easily get a rough cost per new client (cost per acquisition as it’s sometimes called).
Now, you don’t necessarily need Excel for that. You could probably just use a calculator. But to really get good and actionable information, Excel is certainly going to come in handy.
Keeping track of this in Excel will allow you to handle everything in one place, easily update it, track it, and analyze it however you want to.
You can set up a simple data grid (below), apply an average premium to each client in each initiative (or get actual premium dollars to export into Excel), and get all sorts of easy metrics to measure.
This is a fairly simple tracking and result, but even from something as quick as this, you can clearly see that Initiative 5 has the most bang for your buck.
To be really precise, exact numbers and total costs (including time spent by employees) should be factored in and trended over time… but to get at least a good feel, doing simple math is a great start to get insights into your marketing spend.
Even further analysis would encourage tracking this over time to see if changes in your approach change the results, or maybe some initiatives just take longer to develop, etc.
3) Premium Comparison grid
The goal here is to present a visual summary of client options.
When talking through coverage options with clients or potential clients, it can sometimes be difficult to keep track of the different carrier premiums – especially from the client’s perspective.
Even if you and the client both remember everything, the least expensive combinations might not jump out to you. With a small amount of work up front (or simply downloading an easy FREE template) and capturing the different premium options for the insured (which you are likely already doing), you can have a very helpful tool to help determine top options.
Below is a screenshot of a version of the tool. Note that it automatically highlights the first and second options (aka the two least expensive) for each line and total.
You could build in enhancements like noting specific discounts, add what losses are counted for which carrier, etc.
This is another fairly simple example of the benefits of Excel, but it still illustrates the point that something as easy as this tool (which you can download here for FREE) adds a nice visual representation of options for clients.
4) Cost Benefit Analysis – Hiring a New Sales Rep/Producer
The goal here is to project out the potential return on hiring a new producer.
Hiring a new employee is often a tough decision but gets a bit easier when you have a high degree of confidence on the financial benefit (or lack of) it would have for your agency.
To get that high level of confidence, creating a Cost Benefit Analysis (CBA) can really help. The cost side is the easier part so we’ll start there.
For costs, you’ll have a good idea of what you’ll pay them in commission percentage and base pay amount. Those will be your big costs, and then you can add in things like Administration costs (getting them an email address, a desk, etc.), and monthly fees from things like CRM subscriptions.
On the benefit side, don’t feel like you have to know exactly what they’ll bring in. That’s what you can create scenarios for!
For ease of simple numbers, let’s say your baseline is that the new producer will bring in $50k in premium a month. There’s a few ways to scenario test that.
1. Change their new business premium amount.
If you’re somewhat confident that an average producer will bring in that $50k, maybe you do $40k in a “low” scenario and $60k in a “high” scenario. The breadth of your range can reflect how confident you are in that $50k.
2. Change their growth rate.
Let’s say you’re set on $50k a month. For year 2 (and on), you have a range of growth rates that you could test out. The low scenario could be that they don’t grow at all (0% growth). Medium could be 10% and high could be 20%, or whatever is most appropriate.
3. Change their retention percentage.
We all know retention is where the money is at. We’ll keep the $50k per month in each year and only adjust the retention percentages in each scenario. Maybe something along the lines of 70%, 80%, and 90%.
Note – for the purposes of building a fairly simple CBA, yearly changes are the best so that you can use a true average, and discounting dollars back to present day (if you want) becomes easier.
You most likely want to be able to change all three of those levers (and maybe even more) so the best method is to create an input-based CBA. Something like the below.
Everything in light orange is an input and everything in rows 17 – 22 (the Employee and Agency sections) are formulas based on the inputs. From here you could create three almost identical sheets where you just change the inputs to create the low, medium, and high scenarios.
The point is, you can adjust whatever you want with an input based, scenario model!
Download that template here, for FREE!
Creating something like that does take a bit of sophistication, but the easier version is to make something that functions (with hardcoded numbers or not), copy the sheet twice for a total of three scenarios, and then change the few numbers you want in order to see how the agency benefits.
To download the above pictured CBA template for FREE, click here.
5) Visualize your Financial Performance
The goal here is to easily have a visual representation of some of your financial data.
Tracking your financial performance is always important. You may be sent summaries from each of the carriers, or have some tracking in an agency management system, but often times, you might get an excel file that’s the equivalent of loose leaf paper with some halfway organized commission and deposit numbers.
That is no way to keep track of financials.
With a little bit of Excel work you can turn that barely passable “summary" into a nice graph and accompanying table.
If you’re getting files on a regular basis, they are most likely in a very predictable format. Predictable is good – it means you can automate it! I don’t mean using AI and Machine Learning or anything (that’s definitely overkill).
If you have a bunch of different files that contain your financial data “summaries”, the easiest move is to combine them all into one master file.
Once you’ve got everything in one file, there a few ways to go about aggregating all the data.
Note – the goal of aggregating all the data is so you can easily update it with new data, and easily analyze it.
Without actually seeing how all of the information comes into your files, it’s somewhat useless to go over specific formulas you might use, so we’ll discuss a general theory that will most likely apply to different ways your data comes in.
The theory is to look for that similar style in each place you want to aggregate info from.
If it’s a bunch of different sheets, but the info is always in the same place, you can work with that.
If the info is always in the cell next to something (like the word “Total”), you can work with that.
If the info is always in the same column or row (but varying place in that column or row), you can work with that.
If you can find a pattern, you can work with it and make some formulas (or a macro if you’re familiar enough) to do all the annoying work for you. It just might take some creativity.
In practice, this “find a pattern” strategy normally results in making one formula (maybe one per data point you want) and then very slightly manipulating it for each unique place you want to grab data from. If you have it for one sheet, you can easily have it for all the sheets (by just changing the sheet reference in the formula!).
Once you’ve created your data table with all the slightly manipulated formulas and resulting data points, you might be satisfied just looking at that and seeing a trend there. Adding some conditional formatting (in the “Home" section) can help highlight your trend and have the numbers jump out a bit.
A more likely scenario is that you’re looking for some sort of a graph to visually represent your data. The easiest way to go about this is to create a pivot table and have a pivot chart automatically update from it!
That may sound complicated but it’s less than 10 button clicks and no necessary typing involved.
Brief overview of that process…
Highlight your data then go to Insert -> Pivot Table. Create the pivot table, drag your commission and deposit into the values section, and drag your time period into the rows section.
Then you go up top to Analyze (or “Pivot Table Analyze” depending on your Excel version) -> PivotChart. Select your preferred chart type, and you’re essentially done!
Easy enough!
There are, of course, a bunch of ways you could get fancy, but if you’re looking for a quick and easy summary just so you can get a visual representation of your year, this strategy will work!
Get everything in one file
Find the pattern
Aggregate the data
Make a pivot table and pivot chart
Enjoy your new analytical capabilities!
Other Uses
Of course, there are tons of other uses for Excel in an insurance agency (book roll tracking, performance monitoring, etc.) and some can get a bit more complex. Still 100% worth exploring though!
Hope these can help your agency. If you have any questions, feel free to contact us – we’d be more than happy to help you learn!
About Pineapple
Pineapple is a data analytics company ready to help you become data-driven! We help analyze and visualize your data in custom dashboards so you can see your full business performance at a glance, and provide analysis to drive your strategy. Our interactive dashboards will save you time, provide deeper insights & analysis, and help you make better business decisions.