Calculating revenue over fiscal quarters

[joseph.mccastlain]

Hi all, here is what I am trying to do with the data below: I would
like to take the revenue received from the closed contract and spread
it out over the length of the contract, dividing it up by the fiscal
quarters. I have tried numerous ways to do this but cannot seem to
find the right array. I can do this with if/then statements but they
of course cannot search a range. Any help would be greatly
appreaciated!

Client Name Revenue from contract Quarter Contract date Length in
months Rev per quarter
Client 1 22,000,000 Q407 39172 12 5500000
Client 1 44,000,000 Q108 39173 36 3666666.667
Client 1 11,000,000 Q407 39142 24 1375000
Client 2 100,000,000 Q208 39326 60 5000000
Client 2 200,000,000 Q108 39203 60 10000000
Client 2 300,000,000 Q407 39139 60 15000000
Client 3 1,000,000 Q208 39270 12 250000
Client 3 2,000,000 Q308 39356 6 1000000
Client 3 3,000,000 Q408 39478 24 375000

Client 1
Terms Q3FY07 Q4FY07 Q1FY08 Q2FY08 Q3FY08 Q4FY08
12
24
36
48
60
72

 

[merjet]

This isn't very clear. Do you want the quarterly revenue for each row,
each client, or all clients combined? What is the significance of 12,
24, ... 72?

Hth,
Merjet

 

[joseph.mccastlain]

This isn't very clear. Do you want the quarterly revenue for each row,
each client, or all clients combined? What is the significance of 12,
24, ... 72?

Hth,
Merjet

Thanks for the response. I am looking for the quarterly revenue by
client and the 12,24,72 are the terms in months of the contract. I
developed the array to search the range and plug the revenue into the
proper cell but now i need to figure out how to replicate that revenue
across the length of the contract. See below...I need to figure out
how to show the Q4FY07 revenue as being streched out the length of the
contract (4 quarters) into Q1, Q2, Q3FY08 but stopping at Q4FY08.


Terms Q3FY07 Q4FY07 Q1FY08 Q2FY08 Q3FY08 Q4FY08
12 $5,500,000
24 $16,500,000
36 $3,666,667
48
60
72

 

[merjet]

Assume your data is in columns A-F of Sheet1. In row 1, starting in
column H, put Q3FY07, Q4FY07, etc. Then the following macro will
generate the revenues by quarter and contract. If you want revenue by
client and quarter, you can use the worksheet function SUMIF below the
macro's output.

Sub RevByQtr()
Dim iRow As Integer
Dim iCol As Integer
Dim ws As Worksheet
Dim bFlag As Boolean
Set ws = Sheets("Sheet1")
iRow = 2
Do
iCol = 8
Do
If Left(ws.Cells(iRow, 3), 2) = Left(ws.Cells(1, iCol), 2) And _
Right(ws.Cells(iRow, 3), 2) = Right(ws.Cells(1, iCol), 2) Then
bFlag = True
For iCt = 1 To ws.Cells(iRow, 5) / 3
ws.Cells(iRow, iCol + iCt - 1) = ws.Cells(iRow, 6)
Next iCt
End If
iCol = iCol + 1
Loop Until bFlag = True
bFlag = False
iRow = iRow + 1
Loop Until ws.Cells(iRow, 2) = ""
End Sub

Hth,
Merjet