Aging receivables..

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Aging receivables..

Post by vaxo »

Hello friends, I need to construct aging receivables with formula. In my workbook in the first sheet, there is credit and debit movement with specific accounts. And in a sheet2 opening and closing balance of this accounts. My question is how to construct aging receivables in sheet3 so that first opening balances were eliminated, then oldest accruals and what is left to show in sheet 3 with days increment .... thanks in advance.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Aging receivables..

Post by HansV »

I'm afraid I don't know anything about financial calculations. Could you provide two or three examples of what you want the result to be, with an explanation of how to get it?
Best wishes,
Hans

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Aging receivables..

Post by vaxo »

Yes, on this file is an explanation for one specific receivable account. And formula must do for all these accounts.. please help..
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Aging receivables..

Post by HansV »

I am very sorry, but I don't understand your description. I have no idea what you want to do.
Best wishes,
Hans

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Aging receivables..

Post by vaxo »

If something is not clear I would try to explain more specifically... apology for ambiguity.,.

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Aging receivables..

Post by vaxo »

https://1drv.ms/x/s!Ati9mJZRp9NliW6KimDHGcG_DP3_" onclick="window.open(this.href);return false;

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Aging receivables..

Post by vaxo »

In ah43:am191 need to be the formula for doing it what I explained...

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Aging receivables..

Post by HansV »

The workbook on OneDrive contains links to another workbook so all formulas return #VALUE! :sad:
Best wishes,
Hans

User avatar
Argus
GoldLounger
Posts: 3081
Joined: 24 Jan 2010, 19:07

Re: Aging receivables..

Post by Argus »

For an aging report, with different accounts and a need to group the receivables after age, how long they have been due, it seems a pivot table would be useful (one can then also change the date ranges to display a different summary etc.) (But there would probably be some formula involved as well.)

That said, an aging report with accounts receivable would be based on a certain amount of days for payment, let's say 30 days. The report would then show, for each customer, how much there is in each age bracket, "bucket", 0-30 days, 31-60 days etc.(based on difference between due date and, usually, the current date, i.e. the amount days overdue). (I assume it could become a bit more complicated since some customers may have different credit terms, say Net 60 days etc.)

However, I'm not sure what you are trying to do, it seems like something slightly different; with debit and credits listed for each account, and start & end balance for the year.

You mention subtracting from the opening balance until it is zero, if that happens, and I can understand that, but then you want this sorted into "buckets", 0-30, 31-60 etc. as an aging report mentioned above, somehow. Your example mentions the last day of the year, and then compares that with the date when there is a change in the account.

Your short example, for one account, only mentions the last 7 DR movements for the year (2017-11-03--2017-12-06), but doesn't mention the CR at 2017-11-21, so, if I understand things correctly there should a smaller amount due.

Anyhow, as I said, I don't understand what you are trying to do; take for example 2017-11-03 DR 2108, with 58 days (left until the end of the year), but the balance on the account could be something completely different, and I'd assume that would be the interesting part.

As mentioned, an aging report, as far as I understand it, simply lists amounts and overdue days (based on invoices) for each customer, all sorted in date brackets.
Byelingual    When you speak two languages but start losing vocabulary in both of them.

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Aging receivables..

Post by vaxo »

"Your short example, for one account, only mentions the last 7 DR movements for the year (2017-11-03--2017-12-06), but doesn't mention the CR at 2017-11-21, so, if I understand things correctly there should a smaller amount due." Not lat seven movement but remaining closing balance. In sheet2 rows q7:13 is remaining balances that are not paid. This is receivables that are remained in the year-end 2017. If you sum those rows q7:q13 it would be 7393. That is remaining closing balance which is not paid. In sheet one j3 is also this amount.

User avatar
Argus
GoldLounger
Posts: 3081
Joined: 24 Jan 2010, 19:07

Re: Aging receivables..

Post by Argus »

vaxo wrote:If you sum those rows q7:q13 it would be 7393. That is remaining closing balance which is not paid. In sheet one j3 is also this amount.
I noticed that.

I usually don't link to Youtube (outside Scuttlebutt), but please see these two examples. Sorry, I think I can't help since I don't understand.

Aging of Receivables Method
https://www.youtube.com/watch?v=Qg9XSsBHWvk" onclick="window.open(this.href);return false;

Below: calculating days overdue, then using pivot table and grouping.
Accounts receivable excel - Quick Aging Report using Excel
https://www.youtube.com/watch?v=FvrNRKy5UAQ" onclick="window.open(this.href);return false;
Byelingual    When you speak two languages but start losing vocabulary in both of them.

vaxo
4StarLounger
Posts: 432
Joined: 23 Mar 2017, 19:51

Re: Aging receivables..

Post by vaxo »

https://1drv.ms/x/s!Ati9mJZRp9NliW6KimDHGcG_DP3_" onclick="window.open(this.href);return false;
This in aging sheet rows AH43:AM43 must be formula... If you can transform this formula to more easly understand one...