Revenue Recognition Schedule with multiple contract values

[SArielle]

Hi All, 

I have a question about how to create an revenue recognition schedule based on multi-year contracts with step up revenues.

I would like to create a workflow that could turn it into a monthly recognition report. I am currently doing this manually and it becomes excruciatingly painful 

Input :

Output

Thanks in advance!!!

[Yoshiro_Fujimori · Accepted solution]

Hi @SArielle ,

Here is one way of doing this.

I assume the values in Y1, Y2 columns are always split to 12 months.

It that is not the case, you may want to tweak the formula tools. Good luck!

Workflow

Output

[PangHC]

here the workflow. 

explanation by tool:

1. recordId - avoid duplicate same customer with multiple project

2. formula - calculate # month for row generate

3. row generator - generate all month

4. cross tab - to assign year to correct month (as cant use column by condition)

5. filter - assign to correct year value to month

6. summary tool + join tool - add number of months per year (handle for month less than 12, okay to remove if not relevant)

7. formula - get date for pivot later and divide value by #month (or 12 if all are full year)

8. cross tab - pivot

9. dynamic rename - change the header to nicer format 

In summary:

generate months and assign right value, then pivot it.