ROI Analysis

[excellrookie]

Okay. So my boss tasked me with coming up with a solution to an ROI analysis problem. I am decent at Excel but this task is definitely beyond my abilities, which is why I turn to you for help.

Here is the challenge:

I need to create a set of formulas that will do the following: given a date range, the formula will calculate the ROI for a specific "bucket" (more on buckets in a min). ROI is calculated as (REVENUE-COST) / COST

*I am attaching a sample of the spreadsheet so that you can see what the problem is.*

So lets break down what we are dealing with in this spreadsheet.

There are two buckets in this particular spreadsheet: SEM (starts on worksheet Buckets, c2 and c37) and Targeted Ads (starts on worksheet Buckets, c5).

If you go to the sheet Analysis you will see that there are five fields that need to be filled out by the person who needs this calculation:
-ROI Bucket to be analyzed
-Month to be analyzed

&

-ROI Bucket to be analyzed
-Quarter to be analyzed
-Year of quarter

I need to be able to fill out the information above either for the month analysis or the quarter analysis. Filled, this information would look like this:
-ROI Bucket to be analyzed: SEM
-Month to be analyzed: 1/1/2011

&

-ROI Bucket to be analyzed: Targeted Ads
-Quarter to be analyzed: Q4
-Year of quarter: 2010

What ever is the best way to solve this problem it will ultimately do the following:

For the month analysis:
Go back to the Buckets sheet, identify that for the month 1/1/11 solving for the SEM bucket:
-cell, in the worksheet Buckets, F17 and F46 contain the costs that need to be added up for the ROI equation
-cell, in the worksheet Buckets, F18 and F47 contain the revenues that need to be added up for the ROI equation

following the equation: (REVENUE-COST) / COST
The equation would add up these values and produce the answer: 33.33%


For the quarterly analysis:
-The formula needs to figure out which months are actually part of Quarter 4 of 2010 (October, November, December) and then follow the logic outlined above and produce the answer for the ROI of the bucket: Targeted ads.


This is a sample spreadsheet and does not reflect the size of the real version. I need to create a system that would allow for the addition of new buckets and or new campaigns down the road.

Ultimately what I was thinking was that I would create a new column B and on cell B17 write SEM COST and B18 SEM REVENUE. Then create a =SUMIF formula that would match all the SEM COSTs -> go to the particular month -> adds up the values -> gives an answer to the ROI analysis.


I know that this is probably not easiest to understand post, so please ask me questions to clarify any issues. I am completely open to different ideas on how to tackle this problem.

Thank you in advance for any help that I get.