Prev: Workbook pivot table design function
Next: Sum in another cell with reference to another
From: rushdhih on 21 Feb 2010 01:52
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

 | 
Pages: 1
Prev: Workbook pivot table design function
Next: Sum in another cell with reference to another