4 Reasons Insurance Agencies Avoid Using Spreadsheets to Process Commissions

Why Commission Managers & Analysts Use Commissions-Specific Software for Processing

We all use spreadsheets for various tasks—impromptu list-making, quick number-running, etc. Spreadsheets are straightforward and still fill a need, but they don't come without issues.

While spreadsheets may still be useful for some things, insurance agency commission managers and analysts do not rely on them for processing commissions for four important reasons:

• They're error-prone
• They can be time-consuming
• They aren't a dependable audit trail
• They don't provide easy or effective performance analysis

When you process commissions for your agency, you’re downloading carrier statements and calculating payouts based on, at times, very complicated commission schedules.

Processing commissions isn’t just math; it’s a process in which you want to ensure accuracy, identify missed commission payments, communicate to your agents, and analyze overall performance.

Because processing commissions is about so much more than mathematical calculations, spreadsheets aren’t the best solution. Let's discuss the four reasons you shouldn't rely on spreadsheets and why commissions-specific software is better suited for the job.

Spreadsheets Are Very Prone to Error

Spreadsheets require quite a bit of manual data entry and manipulation. They do calculations for you, but you have to set up and maintain the formulas (which can be very complex). Since spreadsheet cells can be directly edited, they're open to error.

For this reason, if you do use spreadsheets in this capacity, be sure to lock cells with formulas that you don’t want changed (follow these steps from Microsoft for Excel; follow these steps for Google Sheets). Another option is to password protect the entire spreadsheet to ensure only those you want in the sheet can access it. 

In AgencyBloc, an industry-specific agency management system (AMS), commissions are posted on the policy level. To do this, all commission data is housed in a strategic way for the system to know who gets paid and how much in different scenarios without manual effort from you (after initial setup), reducing errors dramatically.

Initial setup includes creating rate tables that dictate how to calculate your agency’s splits. You then connect those rate tables to the associated policies, which are tied to agents. 

Once setup is complete, AgencyBloc processes commissions based on the statements you get from the carriers. With over 4,800 carriers mapped, AgencyBloc recognizes what the carrier pays based on the statement you download from the carrier website. All you have to do is import the statement, and AgencyBloc does the processing for you.

The structure around data and automation within the processing save you from errors and time spent fixing those errors.

Finding Missed Commissions

Beyond processing, another concern insurance agencies have when it comes to the accuracy of their commissions is whether they’re being paid accurately from carriers. 

Identifying missed commissions requires two things that are found in commissions software, like AgencyBloc:

• Data points needed to track and, thus, find mistakes:
  • Bill from date—the premium period you are getting paid FOR; NOT the month you are getting paid IN (called “Statement Date”) 
  • Effective date—used to know when the commission payments are supposed to begin
  • Premium amount (if applicable)—used to know how much commission payments are supposed to be
  • Number of lives (if applicable)—used to know how much commission payments are supposed to be
  • Commission frequency—used to know how often you should expect the commission amount
• Reports to identify whether you’ve been paid and if it was the amount you expected

With these, AgencyBloc is able to quickly find any missed or inaccurate payments. Learn more about the process in our blog: How to Find & Collect Missed Commissions With an Agency Management System.

Using spreadsheets, the process is very manual. Line-by-line, you’ll need to compare your carrier statement against your own calculations of what you’re owed. 

Once you identify the missed commissions, reconcile with the carrier with this information in hand on each policy: 

• Policyholder name
• Number
• Carrier
• Status
• Effective date

From tracking and processing to identifying missed commissions, using an AMS like AgencyBloc ensures your work is accurate.

Spreadsheets Are Time-Consuming

It takes considerably more time to process commissions in a spreadsheet than it does in commissions-specific agency software. Why? Everything is a lot more manual in spreadsheets than it is in software that’s built specifically to process commissions.

Pruitt Agency, an agency specializing in life and health insurance, now spends 75% less time processing commissions since they switched from spreadsheets. They said, “Before, we’d go line-by-line on hundreds and hundreds of policies to compare and report. Now it's just three clicks, and we can run any report we want.”

While there are many time-saving spreadsheet tips out there, even the most seasoned users find huge time-savings when they switch to commissions-specific software.

Spreadsheets Aren’t a Dependable Audit Trail

Depending on how you’re organizing all of your spreadsheets, you may not have an adequate audit trail. You need an easy way to look back on a specific time period at any given time, when necessary, but you also need a clean, agile spreadsheet—creating both in one file can be really difficult. 

In AgencyBloc, reports can be generated in seconds on countless data points, including commissions. Plus, commission statements are stored securely and can be locked to prevent any editing. In the event that you need to pull up a commission statement for a certain time period, it’s ready in seconds.

Spreadsheets ≠ Great Analyzation

Even though spreadsheets are great with numbers (when used correctly), it doesn’t mean they’re a great performance analysis tool. Of course you can analyze data in spreadsheets, but, again, it is a much more manual process than what you’ll have to do in commissions software. 

If you’re looking to glean insights from your spreadsheets, be sure to read up on tips for Excel data analysis that’ll help you clean up your spreadsheets and decide how you want to draw out the insights.

From your commission data you can learn:

• Who are my top producing agents?
• What products bring in the most money?
• Did I miss any commission payments?
• What are my projected commissions for next year?
• What have I received thus far vs. what I’m expecting to receive?

All of these questions are important to address for the health of your business, but it can be difficult to dissect the data in spreadsheets to get to the answers. 

AgencyBloc can show you real-time graphs and charts of your current commission data, and you’re able to run standard or custom reports using your own filters and criteria. Once the data is in there, the possibilities of what you can learn from it are endless.

Learn More about Commissions Software, like AgencyBloc

If you’re looking for a more efficient way to manage your commissions, an industry-specific AMS, like AgencyBloc, is what you’re looking for. You’ll be able to manage your book of business and process commissions, all in one place. 

Want to see what commissions processing looks like inside an AMS? Schedule a 1-on-1 demo!

---

This blog was originally published on March 24, 2016, and has been most recently updated and republished on June 9, 2021.

Posted by Kelsey Rosauer on Wednesday, June 9, 2021 in Process Commissions Efficiently

1. commissions
2. vendor vetting