Accounts Receivable Aging Report

[Guest]

I have a excel file with 8000 rows. It is an worksheet of AR invoice aging.
I want to be able to create spreadsheets for the following. 1. Total Company
Aging - (summary of this spredsheet) 2. Summary Aging per Customer #
(summary of each customer) I have a total of 400 customers in this file. I
don't know how to use VGA - just formuals. I can go the vlook ups, but I am
just a beginner with those. Any suggestions/ideas as to how I can manage
this data? If you have other ideas I would like to hear them. I know
someone out there has done this before. At my old job I had some programers
that could do this for me. Here I am that person if I want the report.
HELP!! Thanks!
EX: these are the column headings that I have. I have to manually age
into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120 >120

 

[Franz Verga]

I have a excel file with 8000 rows. It is an worksheet of AR invoice
aging.
I want to be able to create spreadsheets for the following. 1. Total
Company Aging - (summary of this spredsheet) 2. Summary Aging per
Customer # (summary of each customer) I have a total of 400 customers
in this file. I don't know how to use VGA - just formuals. I can go
the vlook ups, but I am just a beginner with those. Any
suggestions/ideas as to how I can manage this data? If you have
other ideas I would like to hear them. I know someone out there has
done this before. At my old job I had some programers that could do
this for me. Here I am that person if I want the report. HELP!!
Thanks!
EX: these are the column headings that I have. I have to manually
age into the correct column because the system can't do it.
Customer Name AR # Custome Service# Invoice Date Invoice Total
Invoice# PO# Invoice Terms Due Date Current 1-30 31-60 61-90 91-120

Hi Davies,

maybe you could upload an example file to www.savefile.com


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Hi Franz what is savefile.com? I am not familiar with that. - Davies

 

[Roger Govier]

Hi
I assume from your layout that Invoice Date is in Column D and Current
is in Column J.
I would be inclined to do away with the columns for the aging, and just
use a single formula in column J, which I would give the heading Period.
In J2
=MIN(4,INT((TODAY()-D2)/30))
copy down column as far as required
This would calculate a period number, 0 for current, 1 for 1 to 30
days, 2 for 31 to 60 etc.

Then I would use a Pivot Table for my summary.
Mark the block of data>Data>Pivot Tables>Next>Next>Layout
Drag Customer Name to the Row area
Drag Period to the Column area
Drag Invoice Total to the Data Area
Click OK>Next to put summary on a new sheet

You will then have your data summarised by period and Customer, with a
Total for the company for each period and overall.

For more help on Pivot Tables, including using a Dynamic Range to define
the data area, take a look at Debra Dalgleish's site and scroll to the
section on Pivot Tables.
http://www.contextures.com/tiptech.html

 

[Guest]

Hi Roger,
You assumed correctly. I am trying your suggestion however my Column J is
returning all '4'. I am not familiar with that formual. I understand the
functionality of it but I can't figure out why I am getting 4. Here is an
example:

(D) (J)
Invoice Date Current
06/09/2006 4
Formula in J = =MIN(4,INT((TODAY()-D2/30)))

It should return a 1 correct?
Thank you for your suggestions!

 

[Roger Govier]

Hi

You didn't copy my formula, you typed it and got a bracket in he wrong
place

= MIN (4, INT( (TODAY()-D2) /30) ) spaces inserted for clarity
not
=MIN(4, INT( (TODAY()-D2/30)) )

The former will return 1, where D2 contains 09 Jun 2006

 

[Franz Verga]

Hi Franz what is savefile.com? I am not familiar with that. - Davies

it's a site for uploading files and sharing them. In this way you don't need
to attach the file, just post or e-mail the link...

--
(I'm not sure of names of menus, options and commands, because
translating from the Italian version of Excel...)

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[PY & Associates]

Have a look at this example, adjust to suit

http://www.savefile.com/files/4350447