Forecast problem

[Guest]

Hi,
I'm having a big problem with a task that I have to do with Excel. Well, I
have to present a treasury plan (forecast) with several presumptions, for
example:

Imagine that in certain period of 5 to 8May several fuel uplifts were done
and the invoice correspondent of those uplifts is issued on 9May, but as you
have credit basis you only have to pay 6 days after the invoice (15May).

A B G H J
K
05-May-05 2300 05-May-05 - Invoice Credit
06-May-05 1200 06-May-05 - 4 6
07-May-05 0 07-May-05 -
08-May-05 100 08-May-05 -
09-May-05 3000 09-May-05 -
10-May-05 12000 10-May-05 -
11-May-05 0 11-May-05 -
12-May-05 1587 12-May-05 -
13-May-05 1600 13-May-05 -
14-May-05 1200 14-May-05 -
15-May-05 0 15-May-05 3600

In column H, I have get information about when we have to pay the supplier
(in order to perform a forecast) but the problem is that I have to drag a
formula for all year with perhaps some IF's. At this moment to get 3600 (H11)
I'm using:

SUMPRODUCT((A1:A100>=DATE(YEAR(A1);MONTH(A1);DAY(A1)))*(A1:A100<=DATE(YEAR(A1);MONTH(A1);DAY(A1)+J2)*(B1:B100))

Can you help me with this problem?? Thanks

 

[Max]

Here's a crack at it ..

Assumptions:

Col I = Invoice received on the date, with the number input, e.g.: 4,
meaning the amt due is for the past 4 days

Col J = Credit period, with the number input = days to date due, e.g.: 6
means payment's due 6 days from the date

Assuming col K's empty
Put in K1: =IF(J1="","",A1+J1)
Copy down
(Col K computes the payment due date)

Put in H1:
=IF(--ISNUMBER(MATCH(A1,$K$1:K1,0))=1,SUM(OFFSET(INDIRECT("B"&MATCH(A1,$K$1:
K1,0)-1),,,-INDEX(I:I,MATCH(A1,$K$1:K1,0)))),"")
Copy down

Col H will return the payment amount due, on the due date

Here's the sample file with the implemented construct:
http://flypicture.com/p.cfm?id=53300

(Right-click on the link: "Download File"
at the top in the page, just above the ads)

File: nfbelo_wksht_1_Forecast.xls

The "conversion" of the formulas within to suit your excel version
(semicolons instead of commas?) when you open the file should be automatic
...