Excel Magic Trick 1128

Ageing Accounts Receivable Reports On Multiple Sheets With Array Formula

DESCRIPTION

See how to create an automated Accounting Ageing Accounts Receivable Reports On Multiple Sheets With Array Formula, Excel Table Feature and Formula Table Nomenclature (Structured References):
(00:16) Overview of end result: Automatic Ageing Reports
(00:53) Convert Source Data Table (in separate workbook file) to an Excel Table and name the Table.
(01:55) Import Source Data into Report File
(03:05) Add Helper Column to Destination Excel Table in the Report Workbook file. First look at Table Formula Nomenclature (Structured References). The correct formula for Days Late is: =TODAY()-[@[Due Date]].
(03:45) COUNTIFS formula to count records that are between 1 and 30 days past due. Including syntax for greater than or equal to criteria when the comparative operator and a cell must be joined.
(06:04) Array Formula to extract the “Number of Days Late” information for each customer, given two criteria. See how to use LARGE function and IF function and the array formula key stroke: Ctrl + Shift + Enter. GOOD FOR EXCEL 2007 or EARLIER. Learn more about Table Formula Nomenclature (Structured References).
(09:58) See how to use IF function rather than IFERROR function to create efficient Data Extraction Array Formulas.
(11:13) Array Formula to extract the “Number of Days Late” information for each customer, given two criteria. See how to use the AGGREGATE function rather than the LARGE function to gain the benefits: 1) No need for special keystroke, 2) avoid errors from implicit intersection. GOOD FOR EXCEL 2010 or LATER.
(14:53) Array Formula to extract Customer Name and Invoice Amount using INDEX, AGGREGATE, IF, ROW, COUNTIFS and ROWS functions.
(21:01) Learn how to make Table Formula Nomenclature (Structured References) Absolute.
(23:30) Copy Array Formula Report to Multiple Sheets.
(24:35) Test to see if data changed in the Source and Destination workbooks will be reflected in the Aging Reports automatically.

What is an Ageing Report:
Ageing 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 ageing 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 writes the Accounts Receivables amount as “Bad Debt” and runs that expense through the Income Statement.

WORKBOOKS

Download “Excel Magic Trick 1121-1140” Excel-Magic-Trick-1121-1140.zip – Downloaded 17 times – 2 MB




Be the first to comment

Leave a Reply