Formula to Prorate a Quota

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
  • (Edited)

I need to figure out the best method to set this up.  It hurts my brain when I attempt to solution it, so I am hoping someone can help simplify.

I need this to calculate a prorated quota amount based on
the following:

Annual Quota:  (variable)

Plan Start Date:  (variable, but always 1st of the month)

Plan End Date:  (always 12/31/2019 annually)

 Each Quarter has a variable % to determine the prorated amount:   Q1, Q2, Q3 = 20% of annual Quota + Q4 = 40% of annual quota

 So as an example, a person with a total annual quota is $100,000 for the year starting on 3/1/19 would have 1 month in Q1+ the remaining Q2 – Q4 calculated by the % per above.

Calculates as follows:  Q1 ($100,000*20%/3 months*1 month) = $6,667 + Q2 ($100,000*20%)= $20,000 + Q3 ($100,000*20%)= $20,000 + Q4 ($100,000*40%)= $40,000 = $86,667 Prorated Quota for the year.   

 I want the user to be able to add the Start Date, total Annual Quota and check a box “Prorate Quota?” and it will return the $86,667 in the Prorated field. 


Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
So this is a down and dirty version - did some testing though and it matched your example and a couple other tests:

I'll try and add a couple notes for one example as well

//START

var date start = ToDate([Start Date]);
var date startD = FirstDayOfMonth([Start Date]);
var date yearEnd = ToDate("12/31" & Year($start));
var number quote = [Quota];

//Q1
($quota * 0.2) * (If( $start <= ToDate("3/30/" & Year($start)), Min(3, (4-Month($start))),0)/3) +  
//Q2
($quota * 0.2) * (If( $start <= ToDate("6/30/" & Year($start)), Min(3, (7-Month($start))),0)/3) +
//Q3
($quota * 0.2) * (If( $start <= ToDate("9/30/" & Year($start)), Min(3, (10-Month($start))),0)/3) +
//Q4
($quota * 0.4) * (Min(3, (12-Month($start)))/3)

//END

//Q1
//First set your quota against the multiplier for Q1

($quota * 0.2) *

//Then check to see if they were actually active during Q1 by comparing the end of Q1 in the current year against their start date

     If( $start <= ToDate("3/30/" & Year($start)), 

//If they were active during Q1 - figure out how many months they were active for

Min(3, (4-Month($start)))

//In a nutshell, it says from the end of Q1 - subtract what month they started in
//So if they started in February, this would read as Min(3,(4-2)) which returns 2, counting the 2 months in Q1. 

//Finally - close out your if() saying that if they weren't active in Q1, do nothing, and then divide that by 3 to get your actual % of the month value.

,0)/3)

**Another example of the Min() function -
If someone started in February, and you were looking at Q2 - it would read like

Min(3, (7-2))=> Min(3,5) => the full 3 months in Q2

Hopefully the explanation is not as confusing


Chayce Duncan | Technical Lead
(720) 739-1406 | chayceduncan@quandarycg.com
Quandary Knowledge Base
Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb
Wow,thanks Chayce!  I think it will work.  I just need to figure out to adjust the for the date field.  The first var date is good with a datetime field but the second won't take datetime.  how do I adjust?


I don't think you actually need that after I looked after my initial comment. I wrote that in there when I first started thinking through it but then when I got to the end didn't end up needing it. You can just remove it to avoid confusion. 

For reference though - since plan start date 2 is a date/time - you would just convert it to a date to have Quick Base look at it correctly. Like so:

FirstDayOfMonth(ToDate([Plan Start Date 2]))
Photo of Nikki

Nikki

  • 284 Points 250 badge 2x thumb
Got it! Thank you. I have it being fully tested, but kicked the tires quickly and it seems to be right on.  Very much appreciate the assistance!