Accounts Receivable Aging Report

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

davies wrote:
> 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

Hi Davies,

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


--
Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

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


--
Regards

Roger Govier


"davies" <[email protected]> wrote in message
news:[email protected]...
>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
>

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

"Franz Verga" wrote:

> davies wrote:
> > 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
>
> Hi Davies,
>
> maybe you could upload an example file to www.savefile.com
>
>
> --
> Thanks in advance for your feedback.
>
> Ciao
>
> Franz Verga from Italy
>
>
>

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!

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

--
Regards

Roger Govier


"davies" <[email protected]> wrote in message
news:[email protected]...
> 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!

davies wrote:
> 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...

>
> "Franz Verga" wrote:
>
>> davies wrote:
>>> 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
>>
>> Hi Davies,
>>
>> maybe you could upload an example file to www.savefile.com
>>
>>
>> --
>> Thanks in advance for your feedback.
>>
>> Ciao
>>
>> Franz Verga from Italy

--
(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

Have a look at this example, adjust to suit

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


"davies" <[email protected]> wrote in message
news:[email protected]...
> 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
>