Projecting $ Transactions
Posted by Dwight on August 24, 2001 5:16 AM
A6:A25 are for entry of one of three transaction types: Loan, DDA, or Fee. B6:B25 are for entry of a corresponding date when the transaction is anticipated to occur. C6:C26 are for entry of the dollar amount of the transaction. I would like formulas in the range B2:D4 which show total dollar amounts for each category which are projected to occur in the next 30, 60, and 90 days respectively. For instance, B2 would be loans within 30 days, C2 loans within 60 days, D2 Loans within 90 days, B3 would be DDA within 30 days, and so on. In this example, if row 6 indicated a $500,000 loan projected to close on 10/1/01, then as of today that $500,000 would be part of a total displayed in C2 (Loans within 60 days).
Any help would be appreciated, and I would be glad to e-mail the example to anyone who might help complete.
Thanks.
Posted by Aladin Akyurek on August 24, 2001 5:37 AM
Dwight -- The range you want to house the formulas overlap with ranges that house your data. So maybe it's a good idea to e-mail the example.
Aladin
Posted by Aladin Akyurek on August 24, 2001 9:48 AM
The following constitutes an example of the kind of data that Dwight has to process:
{"Sales Projection",0,0,0,0,0,0,0;0,30,60,90,0,0,0,0;0,"days","days","days",0,0,"As of:",37127;"Loan",1000000,0,0,0,0,0,0;"DDA",0,50000,0,0,0,0,0;"Fee",0,0,35000,0,0,0,0;0,0,0,0,0,0,0,0;"Account","Type","Amount","Date",0,0,0,0;"a1","Loan",1000000,37134,0,0,0,0;"a2","DDA",50000,37179,0,0,0,0;"a3","Fee",10000,37195,0,0,0,0;"a4","Fee",25000,37205,0,0,0,0}
This sample occupies the A1:H12.
H3 contains today's date (computed with TODAY()).
The following formulas compute the desired results for the intersections of the category"Loan" and the categories 30, 60, and 90 days:
B4: =SUMPRODUCT(($B$9:$B$12=$A4)*(($D$9:$D$12-$H$3)<=B$2)*($C$9:$C$12))
C4: =SUMPRODUCT(($B$9:$B$12=$A4)*(($D$9:$D$12-$H$3)<=C$2)*($C$9:$C$12))-B4
D4: =SUMPRODUCT(($B$9:$B$12=$A4)*(($D$9:$D$12-$H$3)<=D$2)*($C$9:$C$12))-SUM(B4:C4)
Selecting B4:D4 and copying down up to D6 produces the results for the remaining categories.
Aladin
============
