Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating revenue over fiscal quarters
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating revenue over fiscal quarters
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating revenue over fiscal quarters
On Feb 22, 4:22 pm, "merjet" wrote:
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculating revenue over fiscal quarters
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating revenue over months | Excel Worksheet Functions | |||
Converting dates into their respective fiscal quarters | New Users to Excel | |||
Help with a calculating revenue | Excel Discussion (Misc queries) | |||
Fiscal quarters for two years... | Excel Worksheet Functions | |||
Formating Fiscal Quarters, rather than Months | Excel Discussion (Misc queries) |