Formula to Prorate a Quota
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.