Excel Magic Trick 1133: Aging Accounts Receivable Reports: PivotTable & Unique Identifier Download Excel File: people.highline.edu/mgirvin/ExcelIsFun.htm See how to create Aging Accounts Receivable Reports On Multiple Sheets With PivotTable where or not there are duplicate records: 1) (01:49 min) Days Late Helper Column Formula: TODAY function and relative cell reference 2) (02:46 min) Report Category Helper Column Formula using VLOOKUP 3) (03:45 min) Unique identifier Helper Column Formula using COUNTIF, expandable range and Join Symbol 4) (05:55 min) PivotTable based on Invoice Column 5) (08:48 min) PivotTable based on Unique identifier column
Amey Dabholkar Your Google account settings do not allow me to reply directly to your comment. To answer your question: Aging means how many days past the due date the invoice is. Accounts Receivable Customers must pay their bill by a certain date, if they pay late, they would be listed in one of the aging reports. If they are 1-30 days late, they are listed in the first report. 31-60, the second. And so on. Further, once the customer is so late, like 300 days, the company stops wasting its time trying to collect and write the Accounts Receivables amount as "Bad Debt" and runs that expense through the Income Statement.
Peace be on you Can you calculate Outstanding Balance of a customer on fifo method for a given period in brackets i.e., 0-30, 31-60, 61-90, 91-120, 121-180 and above 180.
If you upload a video that contains every accounting procedure like software that would be awesome for us. like we will post the journal entries and everything comes out automatically in the income statement , balance sheet and fo fourth
Dear Ty for the video.. Much appreciated for your contribution ..... Query, How you are applying Vlookup when look up will not match with the source array of the given table. For instance Look up value is 143 but in the array there is no 143 VALUE TO FETCH THE DATA. Can you please explain how you arrived the data under report table. Ty
"Show Report Filter Pages" How have I missed this all my life?!?!? In Order of Importance: 1) THANK YOU! Thank you!!! 2) Kicks myself. 3) Uses new found toy.
I don't think we need PivotBy because there is a unique list of Invoice Numbers. I would still add days late and age of account to table and then use a formula like: =SORT(CHOOSECOLS(FILTER(AAR,AAR[Age]=K7),1,2,6,3),3,-1) where K7 has age of account to show.
How did you map this aging with 'Payment Date'. If I understood this tutorial correctly you have shown the aging calculation from the difference between 'InvoiceDate' and 'Due Date'.
Great video! How would you keep track of the 5-10% retainage? We usually do not get paid that until we have completed our order at 100% but I am looking for a better way to keep track of that. Thanks in advance!
Sir, I hv been a regular using your tips and solution. thank you. Sir, I would like to do stock analysis on Date-wise based on stock item showing Opening bal+ Purchases - Issues = Closing stock using pivot table . please do help me. i can not get it .
Great stuff as always! Would be interested to see your thoughts on customer payment stats backwards as support for sales negotiations. I have my own solution, but Im pretty sure you would give some good pointers. Thumbs up! ;)
You would need to add a column to the raw data for the payment, then column with a formula taking the payment from the balance of the Invoice, then refresh the Pivot tables. This will update with the payments
PLEASE HELP ME TO MY ASSIGNMENT Discussion Forum: Case Problem 1 - Credit Control Reporting Project Scenario 1 Case Study: Credit Control Reporting Project The Challenge: This credit control department had little or no visibility of the age of debts owed by clients and the outstanding balances or credits made to clients. A debtor report for executive management takes up to two weeks to compile from various data sources and formats and all credit transactions have to be recorded and summarized manually. When we engaged with the client, much of the credit control manager’s time was dedicated to manually collating data from various sources. Up to 2 days a week could be spent on this task. The client needed a way to provide key internal management direct visibility into debtor and credit information for all clients across up to 50 branches for any user defined period. Debts were to be viewed on a 30, 60, 90 day breakdown basis. Augustus Hall Limited was selected to design, develop and advice on that which would allow the creation of easy-to-use, understandable reports that would integrate data from several cross- platform databases and could be exported to a format suitable for manipulation while allowing all key staff to instantly access the credit control reports/information in the format they needed. Recommended Solution? Recommended Results?
Excel Magic Trick 1133: Aging Accounts Receivable Reports: PivotTable & Unique Identifier
Download Excel File: people.highline.edu/mgirvin/ExcelIsFun.htm
See how to create Aging Accounts Receivable Reports On Multiple Sheets With PivotTable where or not there are duplicate records:
1) (01:49 min) Days Late Helper Column Formula: TODAY function and relative cell reference
2) (02:46 min) Report Category Helper Column Formula using VLOOKUP
3) (03:45 min) Unique identifier Helper Column Formula using COUNTIF, expandable range and Join Symbol
4) (05:55 min) PivotTable based on Invoice Column
5) (08:48 min) PivotTable based on Unique identifier column
Thanks to those who posted this it is really very helpfull for us
You are welcome!
ExcelIsFun
Really Appreciated videos
You are welcome!
Your explanations are the best. Thank you again for another awesome video :)
You are welcome! Thanks for your great tips also!
Amey Dabholkar Your Google account settings do not allow me to reply directly to your comment. To answer your question: Aging means how many days past the due date the invoice is. Accounts Receivable Customers must pay their bill by a certain date, if they pay late, they would be listed in one of the aging reports. If they are 1-30 days late, they are listed in the first report. 31-60, the second. And so on. Further, once the customer is so late, like 300 days, the company stops wasting its time trying to collect and write the Accounts Receivables amount as "Bad Debt" and runs that expense through the Income Statement.
Woooow God bless you i learn to much from your video.. thank you
Thank you I really appreciate the time you take for making that. You have a special gift to explain concise and clear.
Wow! This was one of the best videos on Microsoft Excel that I have every seen. Please do keep up the excellent work!
Your videos and supporting files are of great help.
Thanks
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
Awesome video❤
Very Nice explanation
Awesome Mike with EXCELlent video on Aging accounts
thanks a lot i am really surprising how to memorize all this information
See you. Thanks a lot.
VERY NICE
nice
thank you! This so great .
You are welcome!
thank u sooo much. very helpful
Peace be on you
Can you calculate Outstanding Balance of a customer on fifo method for a given period in brackets i.e., 0-30, 31-60, 61-90, 91-120, 121-180 and above 180.
If you upload a video that contains every accounting procedure like software that would be awesome for us. like we will post the journal entries and everything comes out automatically in the income statement , balance sheet and fo fourth
how do u add in show report filter pages in tool bar , i cannot add this from add on in excel
Can you please upload on video on Comparison report....Ex Last month performance Comparison to current month Comparison.
You said Pivot table is not ideal for aging reports, the same is true for the slicer method ( Trick 1129) then? thanks for the great tutorials!!
Dear Ty for the video.. Much appreciated for your contribution .....
Query,
How you are applying Vlookup when look up will not match with the source array of the given table.
For instance Look up value is 143 but in the array there is no 143 VALUE TO FETCH THE DATA.
Can you please explain how you arrived the data under report table.
Ty
"Show Report Filter Pages" How have I missed this all my life?!?!?
In Order of Importance:
1) THANK YOU! Thank you!!!
2) Kicks myself.
3) Uses new found toy.
Glad you found the new amazing toy : )
Greetings to your respected person. Is it useful to apply the video to pivotby ❤
I don't think we need PivotBy because there is a unique list of Invoice Numbers. I would still add days late and age of account to table and then use a formula like: =SORT(CHOOSECOLS(FILTER(AAR,AAR[Age]=K7),1,2,6,3),3,-1) where K7 has age of account to show.
@@excelisfun I hope you can still make a video for him to benefit from using the modern equation in Office 365
How did you map this aging with 'Payment Date'. If I understood this tutorial correctly you have shown the aging calculation from the difference between 'InvoiceDate' and 'Due Date'.
I have learn a lot from all your videos. Will you do a series on google doc spreadsheet? None of the RUclips on this topic are as good as your videos.
I do not know Google Docs. Sorry. I am glad that you have learned from the videos?
P.S. Did you Thumbs Up the video?
Hi nice one plz share me more vedious
Great video! How would you keep track of the 5-10% retainage? We usually do not get paid that until we have completed our order at 100% but I am looking for a better way to keep track of that. Thanks in advance!
Sir, I hv been a regular using your tips and solution. thank you. Sir, I would like to do stock analysis on Date-wise based on stock item showing Opening bal+ Purchases - Issues = Closing stock using pivot table . please do help me. i can not get it .
who is this one person disliked the video????? WHO IS IT
Hey, Thankx for the video but unfortunately I can't get my desired results. please help to resolve this mystery.
Great stuff as always! Would be interested to see your thoughts on customer payment stats backwards as support for sales negotiations. I have my own solution, but Im pretty sure you would give some good pointers. Thumbs up! ;)
I have never done something like that. You should start a RUclips Channel and start posting videos!!! So we can all learn from each other!!!
Hmm, I'll leave that to the expert...
I can not download the file. How do I do it?
Nice video... but what is aging accounts?
Amey Dabholkar good question. Customer amount is how old
How to calculate cash discount based on payment terms. Can anyone explain it
Dear, I am getting "Dang, Trista - 2 " twice. I am not getting "Dang, Trsita -1" and "Dang, Trsita -2". Please help
After recieving the payment what will be the next step , is there any impact on this report.
Hope you answer my question
You would need to add a column to the raw data for the payment, then column with a formula taking the payment from the balance of the Invoice, then refresh the Pivot tables. This will update with the payments
can you do a calculation of payroll in excel
I have many videos about that topic:
ruclips.net/p/PL2359D28773B08D86
P.S. Did you Thumbs Up the video?
i cannot download :( sadly..
I'm looking for excel support/consulting for my company. Contact info?
I am not currently doing consulting. Here is the best site I know for consulting:
mrexcel.com
PLEASE HELP ME TO MY ASSIGNMENT
Discussion Forum: Case Problem 1 - Credit Control Reporting Project
Scenario 1
Case Study: Credit Control Reporting Project
The Challenge:
This credit control department had little or no visibility of the age of debts owed by clients and the outstanding balances or credits made to clients. A debtor report for executive management takes up to two weeks to compile from various data sources and formats and all credit transactions have to be recorded and summarized manually.
When we engaged with the client, much of the credit control manager’s time was dedicated to
manually collating data from various sources. Up to 2 days a week could be spent on this task.
The client needed a way to provide key internal management direct visibility into debtor and credit information for all clients across up to 50 branches for any user defined period. Debts were to be viewed on a 30, 60, 90 day breakdown basis.
Augustus Hall Limited was selected to design, develop and advice on that which would allow the creation of easy-to-use, understandable reports that would integrate data from several cross- platform databases and could be exported to a format suitable for manipulation while allowing all key staff to instantly access the credit control reports/information in the format they needed.
Recommended Solution?
Recommended Results?