Help: Contingency based fee formula / cashflow analysis

[Telecom Consultant]

I am trying to build a spreadsheet that calculates cashflow forecasts
for contingency based fee billing.
Basically a completed engagement would be capitalized upon over "N"
years and more importantly billed in quarters.

If I finish an engagement in month one, I will see my first cheque in
month 2 and the rest of the cheques in months 5/8/11/14/17/20/23 based
on a two year agreement, but some are 1 year and some are 3 year.

The problem is I want to plug in a variable number of engagements (at
variable amounts and durations) for each month and then have the
spreadsheet calculate the quarterly amount and put it in the proper
collection month while taking into consideration the compounding
effect of multiple engagements. e.g. if I do one engagement in month 1
and then 1 in month 4, in month 5 I am receiving two payments, so on
and so forth.

I started working on this but I am ending up creating a massive sheet
and I am sure there is a better way to do this. Any help would be
greatly appreciated.

B

 

[Dave O]

Hi-
I lashed together a sample spreadsheet that may work for you, or should
at least provide something you can build on. Unfortunately I don't
have available time to describe in words the placement of each cell and
formula. Do you have a "blind" or generic email address so I can send
this thing to you? (I'm happy to send it along to whatever address you
specify; I use a blind address for spam avoidance.)

Essentially, the sprdsht tracks each engagement's billing, separately
tracks anticipated receipts by engagement (based on a number of billing
months in arrears that you specify), and separately sums each quarter's
anticipated revenue.

Does that sound like it will be helpful?
Dave O
cyclezen AT yahoo DOT com

 

[Dave O]

Alternatively, enter these values / formulas in the address indicated.
Note: format row 3 as dates.
Cell Formula / value
$E$3 38353
$F$3 =EOMONTH(E3,0)+1
Copy F3 out to column AN

$A$4 Billing
$A$5 Engagement 1
$F$5 200
$G$5 =F5+50
Copy G5 out to col AC

$A$6 Engagement 2
$K$6 149
$L$6 =K6+50
Copy L6 out to col U

$A$7 Total Billing
$E$7 =SUM(E5:E6)
Copy E7 out to col AN

$A$10 Receipts:
$B$10 Offset months
$A$11 Engagement 1
$B$11 1
$E$11 =OFFSET(E5,0,-$B11,1,1)
Copy E11 out to col AN

$A$12 Engagement 2
$B$12 2
$E$12 =OFFSET(E6,0,-$B12,1,1)
Copy E12 out to col AN

$A$13 Total Receipts
$E$13 =SUM(E11:E12)
Copy E13 out to col AN

$A$15 Revenue
$A$16 Engagement 1
$E$16 =IF(MOD(MONTH(E$3),3)=0,SUM(B11:E11),0)
Copy E16 out to col AN

$A$17 Engagement 2
$E$17 =IF(MOD(MONTH(E$3),3)=0,SUM(B12:E12),0)
Copy E17 out to col AN
$A$18 Qrtrly Rev
$E$18 =SUM(E16:E17)
Copy E18 out to col AN