Revenue Calculation - Can this be automated?

Mr Sloth

New Member
Joined
Nov 15, 2020
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFG
2October'21 Revenue Accrual
3Revenue TypeAmountStart DateEnd DateMonthsAccrued Revenue Calculation
4Product 11000029/10/202128/10/202212833.33
5Product 2500001/06/202108/10/202155,000.00
6Product 3450001/10/202130/11/202122,250.00
7Product 4300001/10/202131/12/202131,000.00
8Product 5120001/10/202104/04/20227171.43
9Product 650001/10/202131/10/20211500.00
10Product 74500001/01/202108/08/2021845,000.00
11Product 85000001/09/202131/12/2021425,000.00
12Product 91000001/06/202131/05/2023242,083.33
13Product 102000001/08/202120/12/2021512,000.00
Sheet1
Cell Formulas
RangeFormula
F4:F13F4=IF(D4>0,DATEDIF(D4,E4,"m"),0)+1
G4G4=C4/12
G5,G9:G10G5=C5
G6G6=C6/2
G7:G8G7=C7/F7
G11G11=C11/4*2
G12G12=C12/24*5
G13G13=C13/5*3
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
May be:
Book1
ABCDEFGHIJ
3nue TypeAmountStart DateEnd DateMonthsAccrued Revenue CalculationExpectation
4Product 110,000.0029/10/202128/10/202212833.33833.33
5Product 25,000.0001/06/202108/10/202155,000.005,000.00
6Product 34,500.0001/10/202130/11/202122,250.002,250.00
7Product 43,000.0001/10/202131/12/202131,000.001,000.00
8Product 51,200.0001/10/202104/04/20227171.43171.43
9Product 6500.0001/10/202131/10/20211500.00500.00
10Product 745,000.0001/01/202108/08/2021845,000.0045,000.00
11Product 850,000.0001/09/202131/12/2021425,000.0025,000.00
12Product 910,000.0001/06/202131/05/2023242,083.332,083.33
13Product 1020,000.0001/08/202120/12/2021512,000.0012,000.00
Sheet2
Cell Formulas
RangeFormula
F4:F13F4=B4*(DATEDIF(C4,MEDIAN(C4,D4,DATE(2021,10,31)),"m")+1)/E4
 
Upvote 0
Solution
Try the following

T202111a.xlsm
BCDEFGHIJKL
1October'21 Revenue Accrual
2Revenue TypeAmountStart DateEnd DateMonthsPriorOct-21Nov-21Dec-21Jan-22Feb-22
3Product 110,000.0029-Oct-2128-Oct-2212833.33833.33833.33833.33833.33
4Product 25,000.001-Jun-218-Oct-2154,000.001,000.00    
5Product 34,500.001-Oct-2130-Nov-2122,250.002,250.00   
6Product 43,000.001-Oct-2131-Dec-2131,000.001,000.001,000.00  
7Product 51,200.001-Oct-214-Apr-227171.43171.43171.43171.43171.43
8Product 6500.001-Oct-2131-Oct-211500.00    
9Product 745,000.001-Jan-218-Aug-21845,000.00     
10Product 850,000.001-Sep-2131-Dec-21412,500.0012,500.0012,500.0012,500.00  
11Product 910,000.001-Jun-2131-May-23241,666.67416.67416.67416.67416.67416.67
12Product 1020,000.001-Aug-2120-Dec-2158,000.004,000.004,000.004,000.00  
13149,200.0071,166.6722,671.4321,171.4318,921.431,421.431,421.43
14
1c
Cell Formulas
RangeFormula
H3:L12H3=IF(AND($D3<=H$2,SUM($G3:G3)<$C3),$C3/$F3,"")
G4G4=4*1000
G9G9=8*5625
G10G10=1*12500
G11G11=$C11/$F11*4
G12G12=2*4000
C13,G13:L13G13=SUM(G3:G12)
F3:F12F3=DATEDIF(D3,EOMONTH(E3,0)+1,"M")
 
Upvote 0
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?

1635936020448.png
 
Upvote 0
May be:
Book1
ABCDEFGHIJ
3nue TypeAmountStart DateEnd DateMonthsAccrued Revenue CalculationExpectation
4Product 110,000.0029/10/202128/10/202212833.33833.33
5Product 25,000.0001/06/202108/10/202155,000.005,000.00
6Product 34,500.0001/10/202130/11/202122,250.002,250.00
7Product 43,000.0001/10/202131/12/202131,000.001,000.00
8Product 51,200.0001/10/202104/04/20227171.43171.43
9Product 6500.0001/10/202131/10/20211500.00500.00
10Product 745,000.0001/01/202108/08/2021845,000.0045,000.00
11Product 850,000.0001/09/202131/12/2021425,000.0025,000.00
12Product 910,000.0001/06/202131/05/2023242,083.332,083.33
13Product 1020,000.0001/08/202120/12/2021512,000.0012,000.00
Sheet2
Cell Formulas
RangeFormula
F4:F13F4=B4*(DATEDIF(C4,MEDIAN(C4,D4,DATE(2021,10,31)),"m")+1)/E4
This formula is absolutely perfect, thank you so much!!!
 
Upvote 0
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 :).
 
Upvote 0
Try the following

T202111a.xlsm
BCDEFGHIJKL
1October'21 Revenue Accrual
2Revenue TypeAmountStart DateEnd DateMonthsPriorOct-21Nov-21Dec-21Jan-22Feb-22
3Product 110,000.0029-Oct-2128-Oct-2212833.33833.33833.33833.33833.33
4Product 25,000.001-Jun-218-Oct-2154,000.001,000.00    
5Product 34,500.001-Oct-2130-Nov-2122,250.002,250.00   
6Product 43,000.001-Oct-2131-Dec-2131,000.001,000.001,000.00  
7Product 51,200.001-Oct-214-Apr-227171.43171.43171.43171.43171.43
8Product 6500.001-Oct-2131-Oct-211500.00    
9Product 745,000.001-Jan-218-Aug-21845,000.00     
10Product 850,000.001-Sep-2131-Dec-21412,500.0012,500.0012,500.0012,500.00  
11Product 910,000.001-Jun-2131-May-23241,666.67416.67416.67416.67416.67416.67
12Product 1020,000.001-Aug-2120-Dec-2158,000.004,000.004,000.004,000.00  
13149,200.0071,166.6722,671.4321,171.4318,921.431,421.431,421.43
14
1c
Cell Formulas
RangeFormula
H3:L12H3=IF(AND($D3<=H$2,SUM($G3:G3)<$C3),$C3/$F3,"")
G4G4=4*1000
G9G9=8*5625
G10G10=1*12500
G11G11=$C11/$F11*4
G12G12=2*4000
C13,G13:L13G13=SUM(G3:G12)
F3:F12F3=DATEDIF(D3,EOMONTH(E3,0)+1,"M")
Thank you, this is an interesting method.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top