I am atempting to create a forecast of assignment amounts. On our assignment object I have created the ability to calculate the remaining scheduled amount left on the assignment. Basically this fiield takes into account how many business days are left betwen today and the end date of the assignment and then uses that number to multiply it by the bill rate on the assignment to give us a total figuere.
For example if the assignemnt ends on next friday the formula would be 6*bill_rate__c (becasue there are 6 BUSINESS days between today and next friday). This will then give us a whole number which will equal our SCHEDULED AMOUNT.
What I am attempting to do is configure a way to break this number out into a month by month figure. SO lets say that we have an assignment that ends on 10/15/2019, this would mean that there is 93 remaining week days between TODAY and the end date of the assignment. I then take 93 and multiply it by the bill rate (for this example lets say its $150.00) to get a total scheduled amount of $111,600.00.
I then want to be able to take the total of $111,600.00 and create a way for the system to show me on a monthly basis how much that scheduled amount should be. So I guess take TODAY - END DATE and then have a Number of months figure between those 2 dates and devide the total figure by that number? That might get me close..
I just wanted to post this here and see if anyone had any other good ideas I could implement. CHEERS!
1 件の回答
So something like this? Total_Amount__c / (MONTH( End_Date__c ) + (( 12 * (YEAR(End_Date__c)- YEAR(Start_Date__c)))) - MONTH(Start_Date__c))
