Graphic to Plot Results from a Dynamic Table

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

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

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.

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.

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.

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

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.

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

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.

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

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

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.

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.

Ok - give me a workbook with a couple scenario tabs so I can finalize.

Dave

Never Mind - I'll just make a copy of Scenario_Template

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.

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

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

Much thanks,

B.