Hello all,
I have been working with excel for over 15 years and feel I am advanced with the majority of the functions, but recently I have become utterly stumped on a chart I am trying to build and have come to the conclusion that it may not be possible in excel. Thought I would see if any of the experts on here have done something similar before, or have suggestions on how to solve it before I give up. Details are as follows:
X axis = time (Jan 1 through Dec 31)
Y axis left = Accounting ($0 to $250,000)
Y Axis right = Accounting ($0 to $250,000)
Data for each series:
-Project start date
-Project end date
-Project cost
-Date project cost is realized
-Project revenue
-Date project revenue is realized
Plots for each series:
1. Project start date (line coming off X axis)
2. Project cost (left Y axis) lining up with date where total cost of project is realized
3. Resulting revenue from project (right Y axis) lining up date where total revenue from project is realized)
4. Project end date (line returning back to x axis)
The point of this chart is to show the relationship between output (monetary and time) and input (monetary and time) over a set time frame for a number of potential individual projects. Therefore, the line needs to start off the x axis at the start date of the project. The next plot would be the total cost of the project (left Y axis) inline with the date (X axis) when the total funds for the project are realized. The line will then go to the point of the resulting revenue (Right Y axis) inline with date (X axis) of when the resulting revenue is realized. Finally, the line then regresses back to the x axis to the project end date.
I have seen this chart before, but never one generated in excel; only made in illustrator. Essentially, each series creates a line which looks like an upside U leaning to the right. Or, at least it should if the project is worth pursuing.
Well, I am going to keep trying tonight, but otherwise will start building it tomorrow in Illustrator. If anyone has any thoughts or guidance how this may be possible in excel, it would be immensely appreciated. Thank you all Cheers...
Krish
Bookmarks