Accrued Revenue Ageing Formula

[ryansm05]

Hi,

From the below, I need to work out the ageing of the balances. The figures in column M (highlighted yellow) are the values I'm looking to achieve, which incorporates the following:

1) The Bf balance in column C. For Project 1 this is 61 days for the £5,000.

2) The formula will understand that this balance was cleared for Project 1 in February (G4). Subsequently the ageing of Project 1 in Feb 19 reverts to 0.

3) The values in H4 and I4 (£2,500 x 2) are generated in Mar 19 and Apr 19 respectively, and therefore the ageing restarts.

4) At 31/05/2019 (M2) the ageing of Project 1 is 92 days. This is because the balance started to generate in H4 and has not yet been cleared.




I appreciate this is quite a complex request, so if anyone can help, it'd be much appreciated.

Cheers
Ryan

 

[steve the fish]

If you are going to put sample data on the forum please paste something we can copy paste straight into excel not a picture. Anyway lets take project 1. What happens if the brought forward balance is not cleared in full eg if they had only paid say 4000 in Feb? What is the answer required then?

 

[ryansm05]

If you are going to put sample data on the forum please paste something we can copy paste straight into excel not a picture. Anyway lets take project 1. What happens if the brought forward balance is not cleared in full eg if they had only paid say 4000 in Feb? What is the answer required then?

Apologies for that Steve.

In your example, since the balance is not cleared in full, I would want the ageing to include the BF figure. Do you think that’s possible?

Thanks!
Ryan

 

[steve the fish]

There must be something less intimidating but this works for your examples at least:

=IF(SUMPRODUCT(--(SIGN(F4:J4)=-1),-F4:J4)>=D4,SUM(INDEX($F$2:$J$2,MIN(IF(COLUMN(F4:J4)-COLUMN(F4)+1>MAX(IF(F4:J4<0,COLUMN(F4:J4)-COLUMN(F4)+1)),IF(F4:J4>0,COLUMN(F4:J4)-COLUMN(F4)+1)))):$J$2),SUM($F$2:$J$2,C4))

Requires CTRL-SHIFT-ENTER not just enter. Look for {} appearing around the formula if entered correctly.

 

[ryansm05]

Hi Steve,

thanks so much for this - the formula is icnredible!


However, although it work it works in almost every instance, there are a few examples where it doesn't which are highlighted red.
For example, Project 2 should be 151 + 61 as the balance has not yet been reduced to 0 at any point. It's the opposite with Project 7. It seems they're almost defaulting to the 151.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31[/TD]
[TD]28[/TD]
[TD]31[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project[/TD]
[TD][/TD]
[TD]BF (days)[/TD]
[TD]BF (£)[/TD]
[TD]Jan 19[/TD]
[TD]Feb 19[/TD]
[TD]Mar 19[/TD]
[TD]Apr 19[/TD]
[TD]May 19[/TD]
[TD]Jun 19[/TD]
[TD]Jul 19[/TD]
[TD]Aug 19[/TD]
[TD]Sep 19[/TD]
[TD]Oct 19[/TD]
[TD]Nov 19[/TD]
[TD]Dec 19[/TD]
[TD][/TD]
[TD]Total[/TD]
[TD]Ageing (days)[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]Project 1[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2000[/TD]
[TD]3200[/TD]
[TD]2600[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7800[/TD]
[TD]92[/TD]
[TD]Correct[/TD]
[/TR]
[TR]
[TD]Project 2[/TD]
[TD]
[/TD]
[TD]61[/TD]
[TD]16000[/TD]
[TD]-7000[/TD]
[TD]-4000[/TD]
[TD]9000[/TD]
[TD]-6903[/TD]
[TD]-1126[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]5971[/TD]
[TD]151[/TD]
[TD]Incorrect: the ageing should be 212 (151 + 61) as the balance has not yet been reduced to 0[/TD]
[/TR]
[TR]
[TD]Project 3[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]23465[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]23465[/TD]
[TD]31[/TD]
[TD]Correct[/TD]
[/TR]
[TR]
[TD]Project 4[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8000[/TD]
[TD]92[/TD]
[TD]Correct[/TD]
[/TR]
[TR]
[TD]Project 5[/TD]
[TD]
[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7500[/TD]
[TD]-2300[/TD]
[TD]5000[/TD]
[TD]-2850[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]7,350[/TD]
[TD]151[/TD]
[TD]Incorrect: the ageing should be 120
days as the balance has not yet been reduced to 0[/TD]
[/TR]
[TR]
[TD]Project 6[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]9185[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9185[/TD]
[TD]31[/TD]
[TD]Correct[/TD]
[/TR]
[TR]
[TD]Project 7[/TD]
[TD]
[/TD]
[TD]91[/TD]
[TD]21425[/TD]
[TD]3740[/TD]
[TD]-25165[/TD]
[TD]3138[/TD]
[TD]-57[/TD]
[TD]0[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]3081[/TD]
[TD]151[/TD]
[TD]Incorrect: the ageing should be 92
days as the balance was reduced to 0 in Feb 19. [/TD]
[/TR]
[TR]
[TD]Project 8[/TD]
[TD][/TD]
[TD]31[/TD]
[TD]8000[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8000[/TD]
[TD]182[/TD]
[TD]Correct[/TD]
[/TR]
[TR]
[TD]Project 9[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]10000[/TD]
[TD]21500[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]31500[/TD]
[TD]61[/TD]
[TD]Correct[/TD]
[/TR]
[TR]
[TD]Project 10[/TD]
[TD][/TD]
[TD]31[/TD]
[TD]28388[/TD]
[TD]-28388[/TD]
[TD]0[/TD]
[TD]4160[/TD]
[TD]24227[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]28387[/TD]
[TD]92[/TD]
[TD]Correct[/TD]
[/TR]
[TR]
[TD]Totals[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

=IF(R7=0,0,IF(SUMPRODUCT(--(SIGN(E7:P7)=-1),-E7:P7)>=D7,SUM(INDEX($E$5:$P$5,MIN(IF(COLUMN(E7:P7)-COLUMN(E7)+1>MAX(IF(E7:P7<0,COLUMN(E7:P7)-COLUMN(E7)+1)),IF(E7:P7>0,COLUMN(E7:P7)-COLUMN(E7)+1)))):$P$5),SUM($E$5:$P$5,C7)))

Let me know if you have a work around?!

Thanks again,
Ryan

 

[ryansm05]

Hi Steve,

I've realised that probably wasn't helpful - so I've uploaded the file to DropBox: https://www.dropbox.com/s/u43rntfp1iwrbvr/Acc Rev Formula.xlsx?dl=0

Hope this is easier to follow!

Thanks
Ryan

 

[ryansm05]

Sorry to bump this - but if anyone else could help, it would be much appreciated (it almost very nearly works as needed).

Thanks!
Ryan