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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,213
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,651
Messages
5,549,204
Members
410,905
Latest member
Extjel
Top