How to update a variable in a formula without altering past calculations
0
1792
5
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
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!
