Distribute Projected Revenue Annually

[erhodes]

I have sales data of open opportunities from our CRM system. I am trying to take the amount from each deal and project out the annual revenue billings for each record. The tables below show an example of the data and an example (truncated to only show columns w/ values in this example) of the required output. 

Clients are invoiced annually. So in the example below; 'ABC' is for $1M over 2 years and is expected to close in Oct. 2017 I need the model to show for 'ABC' $500K in Oct-2017, and $500K in Oct-2018. 

'GEF' is for $2M over 1.5 years (18months) and is expected to close in Feb. 2018 I need the model to show $1.3M in Feb-2018 and $666K in Feb-2019. 

Does anyone have any idea how to accomplish this. I have been having to take the output from CRM and manually break each record into individual records for each year which isn't scalable or a viable long-term solution.  Any help would be greatly appreciated.

Thanks. 

[Ashish_Mathur]

Hi @erhodes,

Did you get the chance to try out my solution?

[erhodes]

Sorry. I didn't see your reply w/ a solution. I am testing it out today and will let you know. Thank you so much for the response.

[Ashish_Mathur]

Hi,

I have resolved the problem.  Please give me sometime to post my solution

[mattbrice]

Do you have a formula for determining what % of contract value is to be shown in a particular year?  How is that determined?

[erhodes]

The amount to be shown in a particular year is based on the duration. So a duration of 24 months would divide the total amount by 24 to determine the monthly amount and then multiply the monthly amount by 12 to determine the annual amount. The trick is when the duration is more than 1 year but not full years. for ex. if the duration is 18months than the amount charged at the start of year 1 is the monthly amount *12 while the amount charged at the start of year 2 is the monthly amount *6. 

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution here.

Hope this helps.