Conditional Accounts receivable Aging Analysis

R

rushdhih

Hi

I need a formula for conditional accounts receivable aging analysis. Funds
are provided to project managers in the field for operational expenses. Each
Project Manager has an established Imprest Amount.
When cash is advanced, an advance number is assigned and any settlements
received are applied against the outstanding advance. Additional advances
may be requested within the imprest amount.
Settlements received are set-off against the advances by referencing the
related advance number.
The columns in the worksheet are as follows:
Date (Advanced and Settled) , Advance Number, Advance Amount, Settled
Amount, Cumulative Balance
Eg:
DATE ADV# STL# ADV Amt STL Amt Balance
26-Oct-09 360164 663,957.00 1,943,600.00
19-Nov-09 369804 385,198.00 1,943,600.00
21-Nov-09 370632 56,400.00 2,000,000.00
10-Dec-09 360164 377561 (114,422.00) 1,758,360.00
14-Dec-09 360164 379248 (71,928.00) 1,686,432.00

What the formula needs to do:
The formula needs to sum settlements received and compare with the advance
number and amount, If the amount is not equal to zero then its should show
the original date of advance, advance number and amount outstanding against
the appropriate aging column (0 – 30 days, 31 – 60 days, 61 -90 days and over
90 days) as at date (should use Today function less original date of advance)

Thank you
Rushdhi
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top