Create a monthly Amortization Schedule from Contract amount, start, end and booked date.
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thank you in advance for the help.
I have data in which each row is a contract line. It has the contract start date, end date, created date and contract value. My company realizes contract revenue on a monthly amortized basis. As in, a 1 year contract booked on (or before) 01JAN2020 for $12,000 with contract line start date of 01JAN2020 and end date of 31DEC2020 will yeild $1,000 in revenue for each of the 12 months in 2020.
However, if the contract is booked after the start month, the back revenue is realized in the month it is booked. for the example above, if I booked the same contract on 01FEB2020 than the February revenue would be $2,000 and the remaining months would be $1,000.
I'd like to create a work flow which converts my report of contract lines into a monthly amortization report.
Input-
Contract Number | Contract Line number | Start Date | End Date | Created Date | Amount |
123345 | 10 | 1/1/2020 | 12/32/2020 | 1/1/2020 | 12000 |
12346 | 10 | 1/1/2020 | 12/32/2020 | 2/1/2020 | 12000 |
12347 | 10 | 1/1/2020 | 12/32/2020 | 12/1/2019 | 12000 |
Desired output-
Contract | Line | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 | Jul-20 | Aug-20 | Sep-20 | Oct-20 | Nov-20 | Dec-20 |
123345 | 10 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 |
12346 | 10 | 0 | 2000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 |
12347 | 10 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 | 1000 |
Thank you for the help.
Also, the list of contracts will overlap time frames. so, it will need to return a 0 for revenue during a month the contract is not active of course.
Solved! Go to Solution.
- Labels:
- Developer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @BrianGBennett ,
the first step is to convert the dates to date format to enable calculation. I calculated the number of periods to divide the total amount by number of periods, to identify monthly amount. I used Formula tool / Multi-Row Formula tool to assign the amounts to periods. Cross-Tab tool moves values to columns.
I've attached a sample workflow. Hope this is helpful.
Best,
Roland
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I am still digesting it a bit, but so far this looks perfect. the looping of the period calculation is perfect, that is where I was stuck. Awesome work! Thanks for the fast reply!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Ok, I realized I need to have it calculate a daily rate rather than a monthly rate but still only apply the monthly revenue if booked before the end of the month. I think I only need to change the per period caclution and then the calculation to count the periods. should work?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I think it should work, assume you would change the period to day (and calculate amounts for days as well) and run an aggregation to monthly level before moving rows to columns, should require only small modifications.
