Revenue Calculation

[esimmonds1]

Hi,

I am hoping someone can help with this, I am creating a deferred revenue recognition schedule and I have most of the formula done however I need to add the invoice date into the mix.
When we bill customer we can often bill them before the renewal date starts and after the renewal date has started, so I need a formula that can do a catch-up because of when it may be invoiced

upload file.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
8	Invoice	Amount	Alloc Start	Alloc End		Days	DailyAmt	Craveout	01/31/22	02/28/22	03/31/22	04/30/22	05/31/22	06/30/22	07/31/22	08/31/22	09/30/22	10/31/22	11/30/22	12/31/22	01/31/23	02/28/23		Total	Diff
9	03/22/22	$1,000.00	02/15/22	08/15/22		182	4.3956	200.00	0.00	0.00	397.80	131.87	136.26	131.87	136.26	65.93	0.00	0.00	0.00	0.00	0.00	0.00		1,000.00	0.00
10	12/25/21	$600.00	01/01/22	06/30/22		181	2.6519	120.00	202.21	74.25	82.21	79.56	82.21	79.56	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		600.00	0.00
11	03/01/22	$900.00	03/04/22	08/03/22		153	4.7059	180.00	0.00	0.00	311.76	141.18	145.88	141.18	145.88	14.12	0.00	0.00	0.00	0.00	0.00	0.00		900.00	0.00
12	02/10/22	-$750.00	01/01/22	03/31/22		90	-6.6667	-150.00	0.00	-543.34	-206.67	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00		-750.00	0.00
13
14
15								Also there is a craveout portion that is only included in the alloc start date column, however could be invoice date column if after start date
16
17								If the item is invoiced before the start date then it would start booking in the start date
18								If the item is invoiced after the start date then it would start booking in the invoice date column including the catchup amount.
Ashley craveout addition

Cell Formulas
Range	Formula
F9:F12	F9	=D9-C9+1
G9:G12	G9	=(B9-H9)/F9
H9:H12	H9	=B9*0.2
I9:I11,J10:V11,K12:V12,L9:V9	I9	=$G9*((MAX(I$8-$C9,0)-MAX(EOMONTH(I$8,-1)-$C9,0))-(MAX(I$8-$D9,0)-MAX(EOMONTH(I$8,-1)-$D9,0))+(EOMONTH(I$8,0)=EOMONTH($C9,0)))+$H9*+(EOMONTH(I$8,0)=EOMONTH($C9,0))
K9	K9	=$G9*((MAX(K$8-$C9,0)-MAX(EOMONTH(K$8,-1)-$C9,0))-(MAX(K$8-$D9,0)-MAX(EOMONTH(K$8,-1)-$D9,0))+(EOMONTH(K$8,0)=EOMONTH($C9,0)))+$H9*+(EOMONTH(K$8,0)=EOMONTH($C9,0))+261.54
X9:X12	X9	=SUM(I9:W9)
Y9:Y12	Y9	=X9-B9
J12	J12	=$G12*((MAX(J$8-$C12,0)-MAX(EOMONTH(J$8,-1)-$C12,0))-(MAX(J$8-$D12,0)-MAX(EOMONTH(J$8,-1)-$D12,0))+(EOMONTH(J$8,0)=EOMONTH($C12,0)))+$H12*+(EOMONTH(J$8,0)=EOMONTH($C12,0))+-356.67