This template enables users to compile an age analysis from an invoice listing based on any user defined statement date. The age analysis can be compiled for all customer accounts or a specific customer account reference can be specified in order to include a single customer account in the age analysis calculations.
Note: This template is the ideal solution for compiling an age analysis from any list of outstanding invoices. Refer to our Tax Invoice & Debtors templates if you require a solution that produces automated tax invoices & customer statements and enables you to analyze all your customer accounts.
The following sheets are included in this template:
Ageing - define the appropriate statement date to display an age analysis for the invoice listing that is included on the Invoices sheet. You can also enter or copy any customer account reference in cell D3 in order to compile an age analysis for only the specified customer account.
Invoices - copy any invoice listing into the Excel table on this sheet in order to include the appropriate invoices in the age analysis calculation. This sheet contains 5 default columns but you can add as many additional columns as required and arrange the columns on this sheet according to the layout of your invoice listing.
Invoices
Before you copy your invoices to the Invoices sheet, we recommend that you rearrange the default columns on this sheet so that it is easy to copy your invoice listing into the template. You can insert new columns or move the existing columns to the appropriate positions, but it is imperative that you do not delete any of the default columns.
The Invoices sheet contains the following default columns:
Invoice Number - all outstanding invoices should be included in the invoice listing that you copy into the Excel table on this sheet and the invoice numbers can be in any format.
Invoice Date - the invoice dates in your data range should be in the same format as your regional date settings that are defined in the System Control Panel. If this is not the case, the template will not function correctly and you will have to convert the dates to the appropriate date format before including the data in this column.
Customer Account Reference - this column is used to calculate an age analysis for the customer account that is specified in cell D3 on the Ageing sheet. It must therefore be a unique reference that can be used to identify a single customer account, for example a customer account number or even the full customer billing name.
Invoice Amount - this column should include the total invoice value inclusive of sales tax. The Invoice Amount column should contain the values that must be used in the age analysis calculation on the Ageing sheet.
Ageing Value - this column contains a formula that will automatically be copied to all the rows that are included in the Excel table. The ageing value is determined based on the statement date that is specified in cell B3 on the Ageing sheet and the values range from 0 to 360.
Note: If you don't require an Ageing Value to be included for each individual invoice, you can safely delete this column. The Ageing Value column can however be used effectively as a filter column if you only want to view the invoices that form part of one of the ageing group totals on the Ageing sheet. This can be accomplished with the Auto Filter feature by simply clicking the selection arrow next to the column heading and selecting the appropriate ageing value that you want to base the filter on.
The total invoice value of all invoices that are included on the Invoices sheet is displayed below the sheet heading. If you use the Auto Filter feature to display only the invoices for a specific customer or ageing value, this total is automatically recalculated to only include the filtered invoices.
Age Analysis Definition
The age analysis on the Ageing sheet is calculated based on the statement date that is defined in cell B3. All the invoices on the Invoice sheet are also assigned an ageing value based on the date that is specified in this cell - the ageing values range from 0 to 360 and therefore includes a full year. All invoices that are dated in the previous year are included in the 360 ageing value.
The age analysis can be compiled based on any statement date. If you enter any date other than a month end date, the ageing groups are determined based on the day component that is included in the specified date. If you enter a month end date, the ageing is compiled based on calendar months.
For example: If the statement date is specified as the 27 of January 2010, all invoices dated between the 28 of December 2009 and the 27th of January 2010 are included in the 0 ageing group (current period), all invoices between the 28th of November 2009 and the 27th of December 2009 are included in the 30 ageing group (for 30 days) and so forth.
If the statement date is specified as the 28th of February 2010 (a month end date), the ageing values are determined based on calendar months. For example: all invoices dated between 1 February 2010 and 28 February 2010 are included in the 0 ageing group, all invoices dated between 1 January 2010 and 31 January 2010 are included in the 30 ageing group and so forth.
The Effective Date and Ageing Value columns on the Ageing sheet indicate the ageing periods that have been assigned based on the statement date that is specified in cell B3.
Age Analysis Calculation
The age analysis calculation is included in column D and E on the Ageing sheet. The calculation consists of an Ageing Group and the Balance that has been calculated for each ageing group.
If the Customer account reference in cell D3 on the Ageing sheet is left blank, the age analysis calculation includes all the invoices on the Invoices sheet but if you enter a specific customer account reference in cell D3, only the invoices that relate to the specified customer account are included in the age analysis calculation.
All Customers
As we mentioned before, you can simply leave the customer account reference (cell D3) on the Ageing sheet blank in order to calculate an age analysis for all customer accounts that are included on the Invoice sheet.
The total of this calculation should agree to the total invoice value (cell A2) on the Invoice sheet (as long as all filters have been removed from this sheet). If you then only want to view the invoices that form part of a specific ageing group, use the Auto Filter feature to filter the Ageing Value column on the Invoices sheet and select only the required ageing value. The Invoices sheet will then only include the selected invoices, the total invoice value will only include the filtered invoices and this value should agree to the ageing value on the Ageing sheet.
Single Customer
You can compile an age analysis for only a single customer by simply entering the appropriate customer account reference in cell D3 on the Ageing sheet. In order to view the invoices that form part of this calculation, simply use the Auto Filter feature to select the appropriate customer from the Customer Account Reference column on the Invoices sheet. The total invoice value in cell A2 on the Invoices sheet will then also only include the invoices of the selected customer and this value should agree to the total of the age analysis on the Ageing sheet.
