Solved

Graphic to Plot Results from a Dynamic Table

Posted on 2012-03-13
19
151 Views
Last Modified: 2012-03-15
EE Professionals,

I have a nice WS that, when you input the Time Horizon, it auto generates the number of months considered in the financial analysis.  Additionally, I can add a new Use Case with another macro (button in left corner of WS).

Here's the challenge.  How do I set up another WS that shows the graphical results of both a chart that may change dimensions and concurrently, may add additional tables (new Use Cases)?

I'm looking for ideas on this.

Thank you in advance,

B.
Workbench-v26.xlsm
0
Comment
Question by:Bright01
  • 10
  • 9
19 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Bright - can you create a starting point chart?  "A chart" is relatively generic and I can think of any number of things to chart that aren't what you want.

Cheers,

Dave
0
 

Author Comment

by:Bright01
Comment Utility
Dave,

Thanks for jumping in. Attached is an example of the graphic.  Would really like a two axis graphic with one series line and the other bar.  And it would be great if it could include the specific Business Scenario (as you add them or clear them) and the associated ROI calculations.  I put  them outside the graphic to show you.....but really they should be incorporated into the graphic as a title or result image.  May not be "doable".  That's why I was looking for ideas.

Hope this helps.  Let me know if you have other questions... I think the hard part here is actually how do you get it to generate the graphic based on the number of scenarios and the dynamic nature of the Time Horizons (which may be different by scenario).

Thanks again,

B.
Workbench-v27.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Dave,

I've been giving this some thought and here's what I'm thinking;  It may be very complicated to plot out ALL of the data points in a dynamic WS.  What if we were to create a WS that's a single template and then add the ability to select a specific "Scenario", regardless of how many you have, but when you select it (button?), it plots the dynamic range in that particular scenario into the template Graphic WS.  

Does that make sense?  It's slightly limiting since you'd have to do only one at a time....but I think the other way (all the Scenarios) may be too complex.

B.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
IMHO, the only way to plot all (or a set) scenarios is to sum all cash flows (assuming the scenarios can be combined) and have one line.  Otherwise, there's too much on the chart for comprehension.
0
 

Author Comment

by:Bright01
Comment Utility
Agree.  However, understand, each Scenario (originally rows 3:5) would be it's own graphic; and because you could have multiple Scenarios (by adding Use Cases) this would be very complex.

So again, perhaps by building a single WS Graphic Template, with some sort of control/check box, that allows you to take that single string of data (the Cashflows) and plot it against the template?  That way when you replicate another "Use Case" with the same control, you could click it and it would auto plot into the template one at a time.  Error checking would mean that you could only have one graphic produced at a time but you would have the option of unchecking one and checking another to get another graph plotted.

If that doesn't make sense, this one may be too difficult to make simple.

Thoughts?

B.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - create some real data and give this a shot selecting from one to another scenario.  How close did I get?

Dave
Workbench-v27.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
Real close! However, now add another Use Case (button on the upper left); you will see that is copies the one above (by the way, it should clear it so it has no data in the copy (but that's another posting)...... so now how do you create another graphic?   The only way I thought of was to have a check box that would only allow one check box active at a time (regardless of WS) and when checked, it would plot those flows associated with that Case only.

Make sense?

B.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I modified so the DV list would allow selection of more scenarios.

Just select the scenario/business case from L5 on the ROIGraph sheet.

Dave
Workbench-v27.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
You've almost got this!  Let me see if I can further clarify this.

1.) The Model allows you to add as many Scenarios as you like (it copies the entire Scenario_Template to a new Tab and numbers it Scenario1, Scenario2, Scenario3, etc.).  This is not part of the model I have sent you. But you may recall this post.

2.) Then within each Scenario, you can add as many Use Cases as you like (it copies the top Use Case from rows 3:5 all the way out).  That's on the current Scenario_Template with the button at the left.  

So your approach, to have a single ROI Graphic where you can select the Scenario (Tab) and any of the Use Cases that may be created, would work!  What would have to happen is that a macro would have to look at the Scenario# Tabs and log the Tabs in a compiled list that would show up in the selection box.  It would also have to look at each Scenario# Tabs and log each of the Use Cases in a separate list.  By selecting the Scenario and the Use Case, it could plot any Case.

I still think this is very complex to ask for ........ you let me know when you think we're done with this one.

Thank you,

B.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
When you want to see a graph, you push the button on a given Scenario# tab, right?  That button could populate the sheet tab name in the ROIGraph tab (Range M7), and create the dynamic range for the pulldown at L5.

Alternatively, in the ROI Graph tab, you could select the Scenario sheet name, and then the Use Case.

What do you think?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Try this.  There's a couple macros in the ROI Graph Sheet's CodePage.

You have two drop downs in L4:L5.  Use this to add a couple additional Scenario sheets.  THEN, when you go to ROI Graph, select the Scenario Tab you want (which will auto-load the DV in the Scenario drop down) and then select scenario.  After having done that, your chart should look fantastic.

PS - you'll get a warning popup if the tab/scenario combination doesn't give the chart the right data.

See attached.

Dave
Workbench-v27-r1.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
I think that would work.  However, for the dynamic range at L5, it's really "Use_Case", not Scenario (my mistake). However, I like the way you come at this from both the Scenario Sheet to generate an ROIGraph tab (I assume it would be ROIGraph1, ROIGraph2, ROIGraph3, etc.); and the Graph Sheet allow you to pick a Use_Case would be great!.  I think that would work well.

That would be great!

B.
0
 

Author Comment

by:Bright01
Comment Utility
Dave,

This is really good!  One more turn of the crank --

1.) The Macro needs to select ONLY the Scenario with a number (this is because there are other sheets with Scenario NAME but the created/replicated Scenario all are Scenario#.

2.) Change Scenario on the Graphic Sheet to Use_Case with a # based on how many I have created.

Looking good.


B.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Ok - give me a workbook with a couple scenario tabs so I can finalize.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Never Mind - I'll just make a copy of Scenario_Template
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
Ok.  Please try it out.

Dave
Workbench-v27-r2.xlsm
0
 

Author Comment

by:Bright01
Comment Utility
OK...tested it.  Where you have Selection of "USE_CASE" on the Graph Tab, it is looking for the USE_CASE, in each individual Scenario (Scenario1, Scenario2, etc.); not the Scenario_Template.  That is because within each Scenario Tab, I can have a different number of CASES, as well as different values in each case.

When I then clear the WB, it deletes all of the WSs that are labeled Scenario#; and we start all over again, adding Scenarios and Use_Cases.

Make sense?

B.
0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Yep - that's correct.  The ROIGraph logic looks at everything from scratch, every time - finding scenario tabs, re: Scenario#, and once selected, sets the range name to find all use cases for that next drop down, looking on that scenario tab.  If you're using DynamicDV! the list will be sorted, and without blanks.

Dave
0
 

Author Closing Comment

by:Bright01
Comment Utility
Very good 1st gen. version.  I'm certain I'll be back with another post when I incorporate this graphic.  

Much thanks,

B.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now