Forecasting formula

[l8kerfan]

START PROJECTED MONTHS PROJECT TOTAL
MONTH COMPLETION TO COMPLETION DURATION FEE
MONTH FROM CURRENT (MONTHS)

Dec-05 Sep-07 18 21 400,000
(E238) (F238) (G238) (H238) (I238)


BOOKED TOTAL BOOKED YEAR YEAR YEAR
PRIOR BOOKED 2006 2006(M8) 2006 2007(O8)
YEARS TO DATE YEAR FORECAST TOTAL FORECAST

0 140,206 140,206 123,060 263,266 (3,472)

(J238) (K238) (L238) (M238) (N238) (O238)

Ok, here's the problem. I trying to forecast the billing past 2006. The first part of my chart is pretty straight forward. I billed the first 3 months of 2006 = 140,206. I'm using this formula

=IF($I238<0,0,IF(YEAR($E238)>YEAR($M$8),0,IF(YEAR($F238)>YEAR($M$8),(((I238-K238)/(G238+1))*(13-$G$1))*S238,IF(YEAR($F238)=YEAR($M$8),IF(MONTH($F238)<MONTH($C$4),0,((I238-K238)/G238)*(G238))))))

to come up with the 123060 but it is wrong. When you take 400,000(total fee) - the 140,206 (total fee)= 259794 remaining. And when you divide the remaining balance by the remaining months 18. You get 14433 and when you multiply that by the 9 months remaining for 2006(unbilled portion of the year) you get 129,897. Also, the forecast for 2007 is showing a negative. The formula in that cell is

=IF($I238<0,0,IF($M238<0,0,IF(YEAR($E238)>YEAR($O$8),0,IF(YEAR($F238)=YEAR($O$8),(+$I238-$K238-$L238-$M238),IF(YEAR($F238)<YEAR($O$8),0,((+$I238-N238-J238)/IF($H238-$G238>0,$G238+1-10,$H238)*(IF(YEAR($F238)=YEAR($O$8),(13-(MONTH($F238))),12))*S238))))))

Sorry for the lengthy post. As I did not write these formulas but merely inherited them. Any help would be greatly appreciated.

 

[SkipVought]

Hi,

So how do you expect anyone to provide a cogent tip, if you do not provide a clear, concise and complete explanation of your situation?

I have no idea of the source data that your formula is referencing. What's in M8 and G1, O8? and what else? I can't set up a valid test.

Did you lay out your logic in pseudo code?

Here's your formula using names range names in accordance with your headings. Notice that you may be missing an ELSE...
[tt]
=IF(TOTAL_FEE<0, Then
0,
Else
IF(YEAR(START_MONTH)>YEAR($M$8), Then
0,
Else
IF(YEAR(PROJECTED_COMPLETION_MONTH)>YEAR($M$8), Then
(((TOTAL_FEE-TOTAL_BOOKED_TO)/(MONTHS_TO_COMPLETION_FROM_CURRENT+1))*(13-$G$1))*S238,
Else
IF(YEAR(PROJECTED_COMPLETION_MONTH)=YEAR($M$8), Then
IF(MONTH(PROJECTED_COMPLETION_MONTH)<MONTH($C$4),Then
0,
Else
((TOTAL_FEE-TOTAL_BOOKED_TO)/MONTHS_TO_COMPLETION_FROM_CURRENT)*(MONTHS_TO_COMPLETION_FROM_CURRENT)
)
)
)
)
)
[/tt]


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}