Excel formula for calculating cash flow per distribution

Pro wrote:

I need a formula for doing cash flow projection.

I have the s-curve (Cumulative Time vs Cumulative Cost).

The total cost is provided and the time period during which this will happen.

Now I need to find the monthly cash flow per the s-curve (or distribution curve).

[....]

The excel file can be downloaded from https://skydrive.live.com/redir?resi...Ff3a7TlgmVDaxI

The file shows the following data:

With your current design, the monthly distribution might be:

The formulas are (in column C; copy across through column K):

C16:  =(C13-$C$5)/($C$6-$C$5)

C15:  =IF(C13>=$C$6, $C$4,

          $C$4*FORECAST(C16,OFFSET($G$5,0,MATCH(C16,$G$4:$Q$4,1)-1,1,2),

          OFFSET($G$4,0,MATCH(C16,$G$4:$Q$4,1)-1,1,2)))

C14:  =C15-N(B15)

Rows 15 and 16 are added for clarification and simplification.

C16 calculates the cumulative percentage of time represented by the date in C13.

C15 calculates the cumulative cost of that percentage time by finding the column in G4:Q4 that is less than or equal and linearly interpolating [1] using that column in G5:Q5 and the column to the right.

MATCH finds the column in G4:Q4, and OFFSET selects the two adjacent columns in G5:Q5.  We must handle C13>=C6 as a special case (the date in row 13 is the end date) because there is no adjacent column to the right of Q5.

C14 calculates the net cost for the month by subtracting the previous month's cumulative cost from the current month's cumulative cost.

If you prefer to avoid the helper cells in rows 15 and 16, the formula in C14 could be (gulp!):

C14:  =IF(C13>=$C$6, $C$4,

          $C$4*FORECAST((C13-$C$5)/($C$6-$C$5),

          OFFSET($G$5,0,MATCH((C13-$C$5)/($C$6-$C$5),$G$4:$Q$4,1)-1,1,2),

          OFFSET($G$4,0,MATCH((C13-$C$5)/($C$6-$C$5),$G$4:$Q$4,1)-1,1,2)))

          -SUM($B$14:B14)

However, I suspect the following is the intended design:

That is, the intent is to calculate the cash flow for each month from Apr through Dec.

The formulas in C14:C16 are the same.  The corrections to the design are:

1. The end date in C6 is 31-Dec-12, not 1-Dec-12.
2. The formulas in row 13 are as follows:

C13:  =EOMONTH(C5,0)

D13:  =IF(C13>=EOMONTH($C$6,0),"",EOMONTH(C13,1))

Copy D13 across through K13

C13:K13 can be formatted as Custom mmm-yy to show only the month and year.

Note:  You might expect I14 to be 139.35 instead of 139.34, based on the cumulative costs in H15 and I15.  The difference is due to rounding -- actually, the lack thereof.  Such off-by-one (or so) "errors" are not uncommon, especially with percentage calculations.  They are usually explained by a footnote in pro forma accounting reports.  You could try to correct for it.  But if you hide rows 15 and 16 or if you resort to the single-formula solution, no one is likely to notice. ;-)

[1] It could be argued that a linear interpolation is not really appropriate for a sigmoid distribution.  The accuracy depends on how close the adjacent data is and how well the interpolation fits the s-curve.  However, IMHO, it is the best we can do since you do not provide a formula for the distribution and, indeed, the distribution looks arbitrary when we graph it, probably based on historical data.