From prospecting to closing deals, sales tasks need meticulous management. Surprisingly, one of the most daunting tasks isn’t client oriented. It can be keeping track of commissions. I’ve interacted with various salespeople – some are incredibly organized, keeping thorough records of every sale and corresponding commission in a spreadsheet. While others utilize CRM tools to track closed deals and align sales with commission reports. Monitoring commissions is essential for ensuring we are paid correctly; it’s also vital for goal setting. I’ve found the ‘Rule of 78’ a fantastic tool to estimate expected commissions to enhance what I can expect. By doing so, the tool helps free up our minds to focus on our primary objective – closing deals.
The ‘Rule of 78’ is a financial principle primarily used to calculate precomputed interest on a loan or residuals for sales. Its name stems from its initial application to loans with a 12-month term, as there are 78 unique permutations of monthly payments that can be made during that period. Residual sales income tracking can be intricate and demands careful record-keeping for analysis. Various options are available, depending on your business needs and available resources.
Commission Tracking Examples:
Excel Spreadsheets: A cost-effective solution, especially for small businesses that don’t need complex systems. Spreadsheets can be tailored to include columns for each residual sale and corresponding commission rate and can be used to calculate the total income earned in each period.
Accounting Software: Numerous accounting software packages offer features for tracking residual sales income. Suitable for businesses with complex commission structures or a larger sales team. Notable examples include QuickBooks, Xero, and Zoho Books. This can be a great way to keep track of money coming in and going out, but it can be costly. When I signed up for QuickBooks in 2020, the cost was around sixty dollars a month. That cost is ninety in 2023; it’s gone up from eighty in January, then eighty-five in May, and now in August, it’s ninety-it keeps going up.
CRM Software: Many CRM systems offer features to manage sales commissions, including residual ones—an excellent choice for businesses needing a unified scheme for all sales and commission data. Salesforce, Hubspot, and Zoho CRM are some of the popular options.
Outsourced Services: Some businesses delegate the tracking of residual sales income to specialized third-party service providers. Services can include basic tracking to advanced reporting and analysis. I’ve been paying a bookkeeper to track my commissions and manage the monthly payments for over a year now. Since paying for this service, my mind has been much clearer, but I’m just not a numbers person. If you are comfortable with the numbers and don’t have a whole lot to track, it makes sense to do it on your own for a few years. Bookkeepers can be costly. It all depends on your time valuation, so do the math and decide based on your situation.
I found that the Rule of 78 can help determine projected commissions which help with goal setting. I use Monday CRM, but I also track my long-term goals with a My Rule of 78 spreadsheet, which I review once a quarter to assess my progress and determine what I need to do to achieve those goals.
How it works
Assume a salesperson sells a product with a total price of $10,000. The company offers a 10% commission on all sales over 12 months. This implies that the salesperson will receive a $1,000 monthly commission for the following year. Let’s say the salesperson makes ten of these sales in one year with a total billing value of $100,000, all paying at 10%. That means the salesperson can expect a monthly commission check of $10,000 per month over those 12 months. Let’s say these are all three-year contracts, and in the second year, are all still billing $100,000. The salesperson does the same in that second year and closes another $100,000. The rule of 78 will allow the salesperson to see their next year and year after’s projected commissions.
To create my Rule of 78 goals, determine the amount of money you want to make monthly. If, for example, your goal is $10,000 monthly and you earn 10% of what you sell, then enter 100,000 into your spreadsheet and 10% as the amount you earn.
Step 1: Open a new spreadsheet program like Microsoft Excel or Google Sheets.
Step 2: Set up the column headers. In the first row of your spreadsheet, create the following column headers: “Sales Amount,” “Commission Rate,” “Commission Earned,” and “Cumulative Commission.”
Step 3: Enter your sales data. In the “Sales Amount” column, enter the sales figures for each period or transaction. Place each value in a separate row below the “Sales Amount” header.
Step 4: Enter the commission rates. In the “Commission Rate” column, enter the corresponding commission rate for each sales transaction. Each rate should be entered in the same row as its sales amount.
Step 5: Calculate the commission earned. In the “Commission Earned” column, you’ll calculate the commission for each transaction. Use the Rule of 78 formula to determine the commission for each period. The Rule of 78 formula is typically expressed as Commission = (Sales Amount * Commission Rate) * (Term – Period + 1) / (Term * (Term + 1) / 2). Apply this formula to each row in the “Commission Earned” column.
Step 6: Calculate the cumulative commission. In the “Cumulative Commission” column, calculate the incremental commission earned up to each period. For the first row, the cumulative commission will be the same as that earned for that period. For subsequent rows, add the commission earned for the current period to the cumulative commission earned in the previous period.
Step 7: Format the spreadsheet. Apply any desired formatting to the spreadsheet to improve readability. You can adjust column widths, apply bold or italics, add borders, or use color to make the spreadsheet visually appealing.
Step 8: Analyze the data. With the spreadsheet set up, you can now analyze the commission data. You can use various spreadsheet program features to generate charts or pivot tables to gain insights into the commission earned over different periods.
Tracking commissions in this way can help project five and ten-year goals and keep you organized month over month. I use the spreadsheet above to cross-check commission statements along with my CRM. It’s an excellent way to help you know where you are going with your sales commissions. Subscribe for more business and sales posts, have a lovely day.
