How do I spread revenue across time

[Phillip Swegler]

I have multiple lines of data, one for each sales opportunity. Each opportunity line of data inculdes a different revenue start date, a number for total contract amount, an amount for implementation revenue, the number of months the contract is for. For each opportunity I need to be able to subtract the implementation revenue from the total contract amount, and divide the remaining amount by the number of months for a monthly revenue amount. Then I need to have that monthly revenue apply to each month in the future until the number of months is expired. The first month should include the implementation amount, and the newly calculated monthly amount.

Ex: a contract has a start date of 1/30/2020, for $1.1M, of which $.1M is implementation, for 10 months. This means the monthly amount is the $1.1M less the $.1M implementation amount, leaving $1.0M to be divided by 10 months of contract, which means the monthly amount is $.1M. The first month starting 1/30/2020 needs to show $.2M ($.1M for implementation and $.1M for the monthly revenue), and then February through October of 2020 should each have $.1M.

Before each opportunity is final, the contract amount, implementation fee, start date, and contract length can change multiple times. So I need to be flexable.

I need to display this in both a graph and table, as well as export the data to excel.

Any and all help is greatly appreciated