Delaying revenue recognition by Month

[ace09]

Hello,

I am trying to create a model where a portion of revenue is consumed each month starting on the contract start date. The amount of consumption each month would be an input and could be changed if required. In the below example 85,000 is total revenue, 25,000 is consumed for 3 months. On the 4th month the remainder $10,000 is consumed.

For the second contract, 115,000 is total revenue, 25,000 is recognized starting Feb 1, 2019 to May 1, 2019. After that the remaining 15,000 is recognized.

[TABLE="width: 1000"]
<tbody>[TR]
[TD]Contract Start Date[/TD]
[TD]Total Revenue[/TD]
[TD]Jan 1, 2019[/TD]
[TD]Feb 1, 2019[/TD]
[TD]Mar 1, 2019[/TD]
[TD]April 1, 2019[/TD]
[TD]May 1, 2019[/TD]
[TD]June 1, 2019[/TD]
[TD]July 1, 2019[/TD]
[/TR]
[TR]
[TD]Jan 1, 2019[/TD]
[TD]$85,000[/TD]
[TD]$25,000[/TD]
[TD]$25,000[/TD]
[TD]$25,000[/TD]
[TD]$10,000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Feb 1, 2019[/TD]
[TD]$115,000[/TD]
[TD][/TD]
[TD]$25,000[/TD]
[TD]$25,000[/TD]
[TD]$25,000[/TD]
[TD]$25,000[/TD]
[TD]$15,000[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Input: 25,000



Any ideas on how i can do this using excel formulas?

Thanks!



****** id="cke_pastebin" style="position: absolute; top: 134px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 1000"]
<tbody>[TR]
[TD]$25,000[/TD]
[/TR]
</tbody>[/TABLE]
</body>

 

[Peter_SSs]

Any ideas on how i can do this using excel formulas?

Welcome to the MrExcel board!

Insert a new column C as shown.
Use cell C1 to record the monthly consumption.
Formula in D2 is copied across and down.
Change C1 to see the impact of a different monthly consumption.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Start	Total	25000	1/01/2019	1/02/2019	1/03/2019	1/04/2019	1/05/2019	1/06/2019	1/07/2019
2	1/01/2019	85000		25000	25000	25000	10000	0	0	0
3	1/02/2019	115000			25000	25000	25000	25000	15000	0
Sheet1

 

[ace09]

Awesome! Thank you

 

[Peter_SSs]

Awesome! Thank you

Cheers. Glad it worked for you. Thanks for letting us know.

In case you would like to "tidy up" those zero values on the right, you could change the formula to this.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Start	Total	25000	1/01/2019	1/02/2019	1/03/2019	1/04/2019	1/05/2019	1/06/2019	1/07/2019
2	1/01/2019	85000		25000	25000	25000	10000
3	1/02/2019	115000			25000	25000	25000	25000	15000
Sheet1