Calculating Monthly Sales Commissions

[Jeff_D]

Looking for an effective way to calculate and track sales commissions without having to resort back to using Excel.

I have 1 Table with a list of all clients (populated with zapier when a sale is made). With a linked column to another table for the Sales Person who made the sale. I also have a date field for when the sale was made.

Clients are on a monthly subscription and sales commission is paid for every month they stay a client.
For example…
Month 1-3 = $100.00
Month 4-12 = $40.00
Month 4 Bonus = $100.00

My thought initially how this would flow (No Idea How to Actually do this in Airtable) was to some how calculate what Month the client was currently in, lookup what the rate is for that month and apply a basic calculation. Then I could round up all the commissions that would be due for the week. I would then need to track whether or not the commission was paid out or not.

Any thoughts are guidance would be much appreciated.

[Andrew_MacNeill]

So do you have another table that tracks each month that a client is subscribed or just the start date? (ie a client has to be “recreated” if they leave and come back).

If clients must be on a constant subscription, then you can have a Formula field that identifies the # of months they have been a subscriber (so Month({Today}) - Month({Date Started}) Current Month))

((YEAR(TODAY()) - YEAR({Date purchased})) * 12) + (MONTH(TODAY()) - MONTH({Date purchased}) )

(let’s say the above was called Months As a Customer)

So now you can create another formula that identifies which “range” they fall into (Month 1-3, 4-12., etc).
If({Price}<100,“Small”,if({Price}<1000,“Medium”,“Large”))

Now create a table that gives you the rate for each range.

Then do a lookup into that table (using the Lookup field type) to identify the Rate and now you can do the calculation of

Commission Rate * Months as a Customer

Does this help?