Cash Flow Analysis Using Offset

[madgrizzle]

I'm developing a spreadsheet to keep track of our revenue projection (invoices and receipts) and need an easy method to do so. We are used to projecting invoices on a per client basis by month

                  Jan            Feb            Mar            Apr
Client A         $1,000         $1,500         $1,200         $1,000 
Client B         $1,500         $2,000         $1,800         $2,000 
Client C         $1,500         $3,000         $2,400         $3,000 
Client D         $3,000         $2,000         $1,800         $1,000 
Client E         $1,000         $1,000         $-             $-   
Client F         $4,000         $3,000         $2,000         $1,000 
Total           $12,000        $12,500         $9,200         $8,000

Some clients pay within 1 month, others within 2 months (some, unfortunately take 3 months). I'd like to be able to add another column for cash receipt offset and then, for each month, calculate what the expected cash receipt is:

               Offset       Jan            Feb            Mar            Apr
Client A         1         $1,000         $1,500         $1,200         $1,000 
Client B         1         $1,500         $2,000         $1,800         $2,000 
Client C         2         $1,500         $3,000         $2,400         $3,000 
Client D         3         $3,000         $2,000         $1,800         $1,000 
Client E         2         $1,000         $1,000         $-             $-   
Client F         2         $4,000         $3,000         $2,000         $1,000 
Invoices                  $12,000        $12,500         $9,200         $8,000 
Receipts                   $0             $2,500        $10,000        $13,000

Where Feb cash receipt total is the sum of Clients A & B January's invoices (offset by 1 month), March is Clients A & B Feb invoices (1 month offset) and Clients C, E & F's Jan invoices (2 month offset), April is Clients A & B's Mar invoices (1 month), Clients C, E and F's Feb invoices (2 month) and Client D's Jan invoice (3 month).

I can do this by creating a duplicate table directly below the table that calculates the appropriate cash receipt per client per month using this formula:

IF(COLUMN(C2)>$B2+2,OFFSET(C2,0,$B2*-1),0)

and then sum it up. But this makes adding rows (additional clients more challenging) and I have to believe there's a way this can all be put into a single formula for each month without repetitiously adding multiple instances of the above formula into one formula like:

=IF(COLUMN(C2)>$B2+2,OFFSET(C2,0,$B2*-1),0)+IF(COLUMN(C3)>$B3+2,OFFSET(C3,0,$B3*-1),0)+IF(COLUMN(C4)>$B4+2,OFFSET(C4,0,$B4*-1),0)+IF(COLUMN(C5)>$B5+2,OFFSET(C5,0,$B5*-1),0)+IF(COLUMN(C6)>$B6+2,OFFSET(C6,0,$B6*-1),0)+IF(COLUMN(C7)>$B7+2,OFFSET(C7,0,$B7*-1),0)


Attached is a sample spreadsheet that shows what I have and what I'd like.