Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Forecast problem
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(A 1)))*(A1:A100<=DATE(YEAR(A1);MONTH(A1);DAY(A1)+J2) *(B1:B100)) Can you help me with this problem?? Thanks |
#2
|
|||
|
|||
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(INDIREC T("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 ... -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "nfbelo" wrote in message ... 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(A 1)))*(A1:A100<=DATE(YEAR(A 1);MONTH(A1);DAY(A1)+J2)*(B1:B100)) Can you help me with this problem?? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Slight problem automating Excel in a service | Setting up and Configuration of Excel | |||
Excel Display Problem | Excel Discussion (Misc queries) | |||
Freeze Pane problem in shared workbooks | Excel Discussion (Misc queries) | |||
Row Autofit problem Excel 2003 | Excel Discussion (Misc queries) | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) |