ROI Analysis

excellrookie

New Member
Joined
Mar 23, 2011
Messages
17
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.


Since I cannot attach the file to this thread (or at least have not figured out how to do it) here is the link for a rapidshare file download

http://rapidshare.com/files/454072026/ROI_puzzle.xlsx
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
Your post is not so much a question as a request for a project to be developed. There's too much in here you are asking for that will have ramifications for a larger scope that is not fully outlined anyway. Plus, a friendly (and I mean that sincerely) tip, asking for people to download a link when you are unknown person does not generally meet with a high level of response.

As it is, this is just too large for a what the scope truly is, given the size of your post compounded by this statement:
"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."


It's your project, so if you do not want to hire someone to do it for you, try wrapping your arms around it as best you can. Get started on your own, and when you get stuck on a particular issue, or need advice on which direction to go for a particular isolated portion of the project, post back with what you have tried, why you have tried what you have tried, and what your expected results are.

There are a lot of helpful knowledgeable people on this board ready to help, just try to narrow down what you need to know as you work your way through the project.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,117
Messages
5,835,489
Members
430,358
Latest member
zzc1128

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top