Hi Everyone,
Can anyone help me with the attached excel sheet by calculating the FIFO basis debtors ageing amount on the scales of <30 days, >31-60 days and so on.
Would be grateful if anyone could provide the formula as per the data in the attached excel sheet.
Regards
King BD
SD DASHBOARD TRIAL.xls
w/o seeing where your dates are, here are three you can apply, one in each col HF, HG, HH and HI respectively.
=IF(TODAY()-A226<30,"Y","")
=IF(AND(TODAY()-A226>30,TODAY()<61),"Y","")
=IF(AND(TODAY()-A226>60,TODAY()-A226<92),"Y","")
=IF(TODAY()-A226>90,"Y","")
the assumption being that your reference date is in cell A226.
Last edited by Sam Capricci; 11-01-2013 at 07:59 AM.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Dear Sambo Kid,
Thanks for the reply. But I aint looking for a date wise aging of debtors. I can do the date wise aging against due dates/invoice dates.
I actually needed to calculate aging based on FIFO net sales value minus net payment value to derive net debts as on date i.e. most recent payments are adjusted against oldest debts.
To give you an example, say ABC' 3 months dealings are as follows:
September
Opening Bal. - $ 20,000
Current sales - $ 18,500
Current payment - $ 6,000
Closing bal - $ 32,500
October
Opening Bal. - $ 32,500
Current sales - $ 24,000
Current payment - $ 8,000
Closing bal - $ 48,500
November
Opening Bal. - $ 48,500
Current sales - $ 17,000
Current payment - $ 4,000
Closing bal - $ 61,500
By manual calculations, aging would be as follows:
<30 - $ 17,000
>31-60 - $ 24,000
>61-90 - $ 18,500
>91 & above - $ 2,000
Can you formula which can calculate these data automatically?
Heading into a meeting so I can't, but will look back in on this when I get out to see if anyone else happened along to assist you. Sorry, wasn't completely clear on your needs.
Hi - have put in the formulas in your excel (attached). Have also added a line with the data from your post to check if it works correctly. Let me know.
Cheers
AW
Hi Amit,
Thanks for the formula. It works just as I required.
Thanks a gazillion.
Cheers
King_BD
Am glad. You may want to edit the title of your post to say [SOLVED].
And please click on "Add Reputation" for me.
Many thanks
AW
Hi
if amount is showing -ve ( i.e. excess collection) what will be the foumula
skpuhan,
It has come to our attention you have violated Rule 4 of our Forum RULES. Don't Private Message, Visitor message or email Excel questions to moderators or other members. (Or Access, Word, etc.)
All questions and answers will benefit other posters like yourself when discussed in public threads. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.
Breaking this rule is considered harassment by most of our contributors and thus cannot be tolerated. Repeat offense could lead to permanent ban, so do take this caution to heart.
Post your question in a public thread and our many contributors will come to you to assist, especially if the title is accurate (see Rule #1) and you include a sample desensitized workbook that makes it easy for others to try and help.
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ben Van Johnson
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks