Help with a calculating revenue
 

Hi all,

I am trying to calculate revenue over quarters based upon two contract
dates (date executed and date closed). I need to calculate the total
value of the contract over the course of the years but divided by
quarters. The revenue needs to be strung out by quarters over the
given time period of the contract.

As an example, I have the data below. Column A is the client name,
Col B is the total revenue from the contract, Col C is the contract
date executed, Col D is the end of the contract, and Col E is the
revenue divided by total number of quarters in the contract. The
Q3FY07, Q4FY07, etc table will start in column F.

Client Rev Contract date contract close Rev / Quarter
Client 1 $22,000,000 3/31/2007
3/31/2008 5500000
Client 2 $44,000,000 4/1/2007 4/1/2009
3666666.667
Client 1 $33,000,000 3/1/2007 12/1/2008
16500000
Client 2 $100,000,000 9/1/2007
6/30/2008 6666666.667


Q3FY07 Q4FY07 Q1FY08 Q2FY08 Q3FY08 Q4FY08 Q1FY09


Of course, I cannot use macros in this sheet. Any help is greatly
appreciated!