Split over contract period

[ailee15]

Hi, 

I have a multi-year contract period and would like to split the revenue over the contract period. 

e.g. if contract is from Jul 1 2016 to Jun 30 2020 and contract amount is 700,000 in first year and 710,000 in the 2nd year,

the revenue in 2016 will be 700,000 / 2 = 350,000 and the revenue in 2017 will be 700,000 / 2  + 710,000/2 = 705,000.

How do I create the workflow for this?

I have attached the excel with data sample. Thank you!

[danilang · Accepted solution]

Hi @ailee15 

Here you go

After removing the results columns from your input, the WF transposes Years and Months to get the data into a long format.  These are joined and unioned to give one row for each Contract and year.  The first Multi-row tool calculates the number of months to be allocated from the previous year.  The next one calculates the revenue for the current year taking into account any left over from the previous one.  A crosstab and rename to get the results in the correct format

Finally, these are joined back to your original data.

In the calculation for the revenue, I prorated the amounts based on the number of months in the start year. 

Dan

[ailee15]

Hi Dan, 

Thanks a lot! 

If I want to add the formula to calculate the # of months in each year, how can I do that? 

Can you please help me?