Revenue Calculation - Can this be automated?

[Mr Sloth]

Hi all,

Each month I have to do an accrued revenue calculation for uninvoiced revenue based on start and end dates that can range from 1 month to 2 years+.

Please see spreadsheet below where I have the amount, start and end date, amount of months the revenue relates to based on the start and end date and my accrued revenue calculation.

In the example below, this is assuming that I am doing the revenue for October'21.

The way I am doing the accrued revenue calculation at the moment is very manual. For example, for product 1, the revenue of 10,000 relates to 12 months and starts from October. Therefore, I would take 10,000 and divide by 12 to get 833. For product 8, the revenue relates to 4 months and starts from September'21. Therefore I would take 50,000 divide it by 4 and multiply it by 2 as there is 2 months of revenue I need to recognise (Sep'21 + Oct'21). For product 2, the start and end dates don't go beyond October'21, therefore I can recognise all of the revenue in one go.

Is there a formula I can use to do the accrued revenue calculation for me or is the only way to do this manually?

Many thanks in advance.

Accrued Revenue Calculation.xlsx
	B	C	D	E	F	G
2	October'21 Revenue Accrual
3	Revenue Type	Amount	Start Date	End Date	Months	Accrued Revenue Calculation
4	Product 1	10000	29/10/2021	28/10/2022	12	833.33
5	Product 2	5000	01/06/2021	08/10/2021	5	5,000.00
6	Product 3	4500	01/10/2021	30/11/2021	2	2,250.00
7	Product 4	3000	01/10/2021	31/12/2021	3	1,000.00
8	Product 5	1200	01/10/2021	04/04/2022	7	171.43
9	Product 6	500	01/10/2021	31/10/2021	1	500.00
10	Product 7	45000	01/01/2021	08/08/2021	8	45,000.00
11	Product 8	50000	01/09/2021	31/12/2021	4	25,000.00
12	Product 9	10000	01/06/2021	31/05/2023	24	2,083.33
13	Product 10	20000	01/08/2021	20/12/2021	5	12,000.00
Sheet1

Cell Formulas
Range	Formula
F4:F13	F4	=IF(D4>0,DATEDIF(D4,E4,"m"),0)+1
G4	G4	=C4/12
G5,G9:G10	G5	=C5
G6	G6	=C6/2
G7:G8	G7	=C7/F7
G11	G11	=C11/4*2
G12	G12	=C12/24*5
G13	G13	=C13/5*3

 

[bebo021999]

May be:

Book1
	A	B	C	D	E	F	G	H	I	J
3	nue Type	Amount	Start Date	End Date	Months	Accrued Revenue Calculation				Expectation
4	Product 1	10,000.00	29/10/2021	28/10/2022	12	833.33				833.33
5	Product 2	5,000.00	01/06/2021	08/10/2021	5	5,000.00				5,000.00
6	Product 3	4,500.00	01/10/2021	30/11/2021	2	2,250.00				2,250.00
7	Product 4	3,000.00	01/10/2021	31/12/2021	3	1,000.00				1,000.00
8	Product 5	1,200.00	01/10/2021	04/04/2022	7	171.43				171.43
9	Product 6	500.00	01/10/2021	31/10/2021	1	500.00				500.00
10	Product 7	45,000.00	01/01/2021	08/08/2021	8	45,000.00				45,000.00
11	Product 8	50,000.00	01/09/2021	31/12/2021	4	25,000.00				25,000.00
12	Product 9	10,000.00	01/06/2021	31/05/2023	24	2,083.33				2,083.33
13	Product 10	20,000.00	01/08/2021	20/12/2021	5	12,000.00				12,000.00
Sheet2

Cell Formulas
Range	Formula
F4:F13	F4	=B4*(DATEDIF(C4,MEDIAN(C4,D4,DATE(2021,10,31)),"m")+1)/E4

 

[Dave Patton]

Try the following

T202111a.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	October'21 Revenue Accrual
2	Revenue Type	Amount	Start Date	End Date	Months	Prior	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22
3	Product 1	10,000.00	29-Oct-21	28-Oct-22	12		833.33	833.33	833.33	833.33	833.33
4	Product 2	5,000.00	1-Jun-21	8-Oct-21	5	4,000.00	1,000.00
5	Product 3	4,500.00	1-Oct-21	30-Nov-21	2		2,250.00	2,250.00
6	Product 4	3,000.00	1-Oct-21	31-Dec-21	3		1,000.00	1,000.00	1,000.00
7	Product 5	1,200.00	1-Oct-21	4-Apr-22	7		171.43	171.43	171.43	171.43	171.43
8	Product 6	500.00	1-Oct-21	31-Oct-21	1		500.00
9	Product 7	45,000.00	1-Jan-21	8-Aug-21	8	45,000.00
10	Product 8	50,000.00	1-Sep-21	31-Dec-21	4	12,500.00	12,500.00	12,500.00	12,500.00
11	Product 9	10,000.00	1-Jun-21	31-May-23	24	1,666.67	416.67	416.67	416.67	416.67	416.67
12	Product 10	20,000.00	1-Aug-21	20-Dec-21	5	8,000.00	4,000.00	4,000.00	4,000.00
13		149,200.00				71,166.67	22,671.43	21,171.43	18,921.43	1,421.43	1,421.43
14
1c

Cell Formulas
Range	Formula
H3:L12	H3	=IF(AND($D3<=H$2,SUM($G3:G3)<$C3),$C3/$F3,"")
G4	G4	=4*1000
G9	G9	=8*5625
G10	G10	=1*12500
G11	G11	=$C11/$F11*4
G12	G12	=2*4000
C13,G13:L13	G13	=SUM(G3:G12)
F3:F12	F3	=DATEDIF(D3,EOMONTH(E3,0)+1,"M")

 

[Toadstool]

Hi Mr Sloth,

Can I check something with you before attempting a solution? I see that not all dates are beginning/end of months so shouldn't the Revenue Accrual be done by day?

Imagine Products 11 and 12 where the amount is 3,000 and the Start to End dates are 30 days range but Product 11 only has 3 days to the end of October while Product 12 has 29 days to the end of October. Shouldn't the accruals be 300 and 2900 respectively?

 

[Mr Sloth]

May be:

Book1
	A	B	C	D	E	F	G	H	I	J
3	nue Type	Amount	Start Date	End Date	Months	Accrued Revenue Calculation				Expectation
4	Product 1	10,000.00	29/10/2021	28/10/2022	12	833.33				833.33
5	Product 2	5,000.00	01/06/2021	08/10/2021	5	5,000.00				5,000.00
6	Product 3	4,500.00	01/10/2021	30/11/2021	2	2,250.00				2,250.00
7	Product 4	3,000.00	01/10/2021	31/12/2021	3	1,000.00				1,000.00
8	Product 5	1,200.00	01/10/2021	04/04/2022	7	171.43				171.43
9	Product 6	500.00	01/10/2021	31/10/2021	1	500.00				500.00
10	Product 7	45,000.00	01/01/2021	08/08/2021	8	45,000.00				45,000.00
11	Product 8	50,000.00	01/09/2021	31/12/2021	4	25,000.00				25,000.00
12	Product 9	10,000.00	01/06/2021	31/05/2023	24	2,083.33				2,083.33
13	Product 10	20,000.00	01/08/2021	20/12/2021	5	12,000.00				12,000.00
Sheet2

Cell Formulas
Range	Formula
F4:F13	F4	=B4*(DATEDIF(C4,MEDIAN(C4,D4,DATE(2021,10,31)),"m")+1)/E4

This formula is absolutely perfect, thank you so much!!!

 

[Mr Sloth]

Hi Mr Sloth,

Can I check something with you before attempting a solution? I see that not all dates are beginning/end of months so shouldn't the Revenue Accrual be done by day?

Imagine Products 11 and 12 where the amount is 3,000 and the Start to End dates are 30 days range but Product 11 only has 3 days to the end of October while Product 12 has 29 days to the end of October. Shouldn't the accruals be 300 and 2900 respectively?

View attachment 50406

Hi Toad, thanks for looking into this. In my case it has to be evenly spread out in each month. Bebo has provided the perfect formula for my needs .

 

[Mr Sloth]

Try the following

T202111a.xlsm
	B	C	D	E	F	G	H	I	J	K	L
1	October'21 Revenue Accrual
2	Revenue Type	Amount	Start Date	End Date	Months	Prior	Oct-21	Nov-21	Dec-21	Jan-22	Feb-22
3	Product 1	10,000.00	29-Oct-21	28-Oct-22	12		833.33	833.33	833.33	833.33	833.33
4	Product 2	5,000.00	1-Jun-21	8-Oct-21	5	4,000.00	1,000.00
5	Product 3	4,500.00	1-Oct-21	30-Nov-21	2		2,250.00	2,250.00
6	Product 4	3,000.00	1-Oct-21	31-Dec-21	3		1,000.00	1,000.00	1,000.00
7	Product 5	1,200.00	1-Oct-21	4-Apr-22	7		171.43	171.43	171.43	171.43	171.43
8	Product 6	500.00	1-Oct-21	31-Oct-21	1		500.00
9	Product 7	45,000.00	1-Jan-21	8-Aug-21	8	45,000.00
10	Product 8	50,000.00	1-Sep-21	31-Dec-21	4	12,500.00	12,500.00	12,500.00	12,500.00
11	Product 9	10,000.00	1-Jun-21	31-May-23	24	1,666.67	416.67	416.67	416.67	416.67	416.67
12	Product 10	20,000.00	1-Aug-21	20-Dec-21	5	8,000.00	4,000.00	4,000.00	4,000.00
13		149,200.00				71,166.67	22,671.43	21,171.43	18,921.43	1,421.43	1,421.43
14
1c

Cell Formulas
Range	Formula
H3:L12	H3	=IF(AND($D3<=H$2,SUM($G3:G3)<$C3),$C3/$F3,"")
G4	G4	=4*1000
G9	G9	=8*5625
G10	G10	=1*12500
G11	G11	=$C11/$F11*4
G12	G12	=2*4000
C13,G13:L13	G13	=SUM(G3:G12)
F3:F12	F3	=DATEDIF(D3,EOMONTH(E3,0)+1,"M")

Thank you, this is an interesting method.