How to update a variable in a formula without altering past calculations

Hi @Tasha_Klich - this is the closest I can get to this:

Have a “commissions reference” table which notes the commission for one of your realtors and the start and end dates that this commission rate is valid for:

Then, when you note who sold each property don’t assign it to a person, but assign it to a person/commission rate:

You can look up the start and end dates of the person/commission rate and pull these into the property table, which then allows you to check if you have applied the correct rate. See row for Property B where the sold date is after the commission period end date and you get the indicator that the person/commission assignment isn’t the correct one.

This isn’t a perfect solution as I think we would all naturally want to assign a sale to a person and do all the lookups from the name alone, but I don’t think that is possible in Airtable right now in this scenario at least.

Here’s my prototype base of this if you think it will work for you:

JB

Hello @JonathanBowen, I am hoping you can help me with a similar issue.

I have two sales people that get paid a commission upon receipt of client payment by end of Quarter. They have different commission plans. The commission doesn’t kick in until they have sold (and clients have paid) their base salary amount. Also their commission plan changes upon the $1Million mark once they sold (and the clients have paid) 1 Million.

Sales Person A has a $100,000 base with a commision plan of 2.5% upon selling between 100,000 to $1M in sales that are paid and 2% after once they have sold more than $1M for that year. Once the year is over it starts again. Commissions are paid at the end of each quarter.

Conversely, Sales Person B has a $92,500 base with a commission plan of 2% upon selling between $92,500 to $1M in sales that are paid by client and 2% once they have sold more than $1M for that year. Once the year is over it starts again. Commissions are paid at the end of each quarter.

What I currently have is an Invoice Schedule sheet/Table that shows what has been paid by quarter and a deal owner listed per item. My DEAL OWNER VIEW filters out previous years and includes anything invoiced and paid. It is grouped by Deal Owner, Year Paid, Quarter Paid.

In the INVOICE SCHEDULE SHEET I have:
Quater paid column with the following formula
IF({Paid Date}, DATETIME_FORMAT({Paid Date},‘YYYY’) & " Q" & DATETIME_FORMAT({Paid Date},‘Q’))

Deal Owner Column - Lists each deal owner’s name

Sale Commissionable Amount - Lists the Sales Amount

Sales Commission % - lists the calculated Sales Commission based on the individual’s commission rate.
IF({Deal Owner}= “Person A”,{Commissionable Amount}.025,IF({Deal Owner}= “Person B”,{Commissionable Amount}.02))

I have also created a Commission Scale Sheet/Table

The Column are:

Deal Owner ID:
Person A - Base
Person A - 1M
Person A - Greater than 1M

  Person B - Base
  Person B - 1M
  Person B - Greater than 1M

Deal Owner Name
Sales Person A
Sales Person B

Commission Threshold
$100,000
$1,000,000
$1,000,001
$92,500
$1,000,000
$1,000,001

Commission %
2.5%
2.5%
2.0%
2.0%
2.0%
2.5%

What I can’t figure out is how calculate (via a formula the variables) and whether I need a third sheet connect all this info to calculate the variable of when their Sales Commissionable Amount reaches their base salary of $100K and $1M (for person A at 2.5%) and then salary of $92.5K and $1 M (for person B at 2%) and then when they each reach over $1M (person A at 2% and person B at 2.5%). As well as how to show the commissionable amount at the end of each quarter.

Thanks in advance for your assistance

  Person B - Base
  Person B - 1M
  Person B - Greater than 1M

Hi @Janet_Shivell - I think I would approach this with a slightly different base structure. Here’s my take:

A sales/invoices table:

So for each sale, you’ve got the amount and the salesperson. When it gets paid, you enter the paid date and this calculates the quarter when commission will be paid (same as your base). I’ve added a “this year” field to show if a commission payment relates to this year (1) or a previous year (0). This has the formula:

IF({Date paid}, IF(DATETIME_FORMAT({Date paid}, 'YYYY') = DATETIME_FORMAT(TODAY(), 'YYYY'), 1, 0))

In the salesperson table I’ve got this:

Here you can enter the base sales and the commission rates for the first $1m and the second $1m. “This years sales” is a conditional rollup of sales where “this year” = 1:

and the “commissionable sales” is the sales rollup less the base sales value. You can then calculate the value of sales at each threshold:

Sales at Rate 1
IF({Commissionable Sales} <= 900000, {Commissionable Sales}, 900000)

Sales at Rate 2:
IF({Commissionable Sales} > 900000, {Commissionable Sales} - 900000, 0)

And from these have another field to calculate the commission:

{Sales at Rate 1} * {1st $1M %}/100 + {Sales at Rate 2} * {2nd $1M %}/100

You might need to do some more work to figure out payments by quarter, but, if I’ve understood correctly, the quarterly payments are just the total earned in the year, less what has been already paid this year, so 4 additional columns for payment Q1, payment Q2 etc could be used to calculate the amount owed to a sales person at any point.

Hope this helps!

Hello @JonathanBowen,

Thank you for this information. I am wondering if you can share this airtable that you have the screenshots of so that I may replicate better.

Thanks,
Janet

Sure, no problem, I will DM you a link. I tend to delete these fairly regularly as my workspace gets cluttered, so won’t post it here as it won’t link through for ever!