Best Practices for Tracking Agent Commissions with Spreadsheets

In terms of productivity applications, it’s hard to think of one with more impact on business than the humble spreadsheet. Excel and other programs are used in brokerages across the country to track agent commissions, and they work well, but do have drawbacks – not the least of which is accuracy. Studies show that 88% of spreadsheets contain errors.

You can overcome by investing some time to follow some of these best practices.

Minimize Touch Points

It’s common for spreadsheets to fly around the office via email, so it’s easy to end up with multiple versions. And it’s painful at best to try to reconcile the changes in two documents that have received separate updates. This is why it’s best to minimize the copies available. For example, place a shared copy on the network or in cloud storage.

It’s also a good idea to restrict the number of people who may edit the spreadsheet. When more people go in to make edits, it increases the chances of someone making a mistake due to not understanding how the spreadsheet works.

Lock and Protect

It’s frustrating when a formula, which is supposed to automatically calculate a value, is overwritten by someone who thinks they have to manually update the value. This is why it’s important to lock cells and protect worksheets. It is the best way to prevent someone from accidentally overwriting a formula, or from otherwise making changes to things that shouldn’t be changed.

The steps to both lock and protect in Microsoft Excel are well-covered by many websites, so we won’t rehash it here. This site is just one source of more information.

Store Fixed Values for Formulas in Other Cells

The commission tracking spreadsheet at many brokerages will typical embed commission percentages directly into formulas. This works, but if, for example, you change the commission split in one of your commission plans, someone has to update that percentage in every formula in the spreadsheet.

You can get around this by storing a commission percentage as a fixed amount, or constant, in another cell, and then referencing it in formulas. This simple act allows for a single update point. It reduces the chance of errors, and saves both time and money because you don’t have to fix errors when an agent’s check is wrong.

Take Advantage of Structured References by Creating Tables

Microsoft provides detailed information on how to do this, but the short of it is this: When you create a table for your data, Excel allows you to reference cells based on the table and column name instead of using what is essentially an arbitrary letter and number combination.

Institute a Periodic Review Process

No matter how bulletproof you make your spreadsheet, it’s still possible for errors to creep in, especially over time. It’s easy to mistype something, and mistakes in formulas are hard to spot.

It’s a good practice for someone else other than your accountant or bookkeeper to have a good understanding of the basic formulas in place and review them periodically to make sure the formulas you use to pay your agents are accurate.

Still Too Manual for You?

If all this is still too manual for you, with too many chances for errors, it may be time to consider automating your operations with a solution to replace spreadsheets. We have a quick guide to help you decide whether automation is right for your brokerage. Fill out this form to download it.

Guide: Is it time to upgrade?

Leave A Comment