We utalize PSA in our org as we are a consultancy, and PSA is how we keep track of all our employees assignemnts. WIthin the assignemnt we have a bill rate and a start date aand an end date. I have created a formula that will calculate the number of BUSINESS days between the start and the end date of the assignment, and then multiply that by the bill rate to give us a projected dollar amount that each assignment will generate for our company revenue wise.
With this we are ab le to project the total amount that each assignment will generate, but we want a way to break this down on a monthly basis.
So lets say the we have an assignment for $150.00 an hour and the start date of the assignment is today and will run until 9/31/2019. That means that we will have 15 work days left in this month, as well as July August and September to work days as well.
What I am hoping to do is create an automation that will look at the start date of the assignemnt and the end date of the assignment and thenm give me monthly totals on how much this assignment should generate revenue wise.
I have gone ahead and created a custom field for each month that will tell me exactly how many work days each month of 2019 has, and also created a field that gives me a daily rate per assignment.
My hope is to take the daily rate and multiply it by each months total amount of work days, based on the start and end date and then have a field for each month that will get filled automatically if that assignment start and end date falls within those months.
So for my example earlier we will have ($150.00 an hour * 8 hours a day) = $1,200 a day. We can then multiply this by 15 to tell us that we will generate $18,000 on this assignment for the month of June. We can then multiply $1,200 by 23 (the exact number of work days for July) to tell us that we will generate $27,600 in June. We can contunue to do these calulations for the next 2 months...
The part that I am stuck on is the automation part. I want the system to look at the start date and the end date of the assignemnt and know which months these totals should fall into automatically. So for this example the system should know to put $18,000 in the field titled June Revenue, and $27,600 in the field titled July Revenue, as well as the totals for August and September obviosuly.
I was trying to think of an IF statemtn that would work here but I am getting stuck.
Here is a screen shot of the cusotm object I have created so far..
1 réponse
You might want to look at either process builder or flow builder to be honest. Although I have a feeling there is an easier way to do this. Is there a reason you can't use the standard functionality like you get with CPQ scheduled forecasts?
