Forum Discussion
Multiple scenarios in one graph (using Scenario Manager)
One more scenario is with dynamic arrays
where
years: =TRANSPOSE(tData[[#All],[Year]]) scens: =TRANSPOSE(INDEX(tVar[#Headers],1,SEQUENCE(,COLUMNS(tVar)-1,2))) data: =TRANSPOSE(tData[Value])* TRANSPOSE(INDEX(tVar,1,SEQUENCE(,COLUMNS(tVar)-1,2))/ INDEX(tVar,2,SEQUENCE(,COLUMNS(tVar)-1,2) ) )
To answer my own question, I got something working using Power Query. For my own reference and for others with the same problem, below is how I did it. (See also the workbook attached for the complete result).
On Sheet1, put a table with the basic data (no formulas), and a table with the variables for each scenario.
What I want to do, is create a chart showing Value*a/b for each scenario, showing both scenarios at the same time.
Below the intended end result, blue is scenario 1, orange is scenario 2:
I want the chart to update when I change the values for a and b in the variable table.
And I don't want to copy/paste values, or add columns per scenario to my data table (think of a case with not 1 but many columns per scenario, and this for 10 scenarios, the table would become huge).
Here are the steps I followed:
Step 1: a query for the data table
Create a new query (using "from Table/Range" in the Data section of the ribbon):
Press "Close & Load To", and select "Connection only".
Step 2: a query for the variables table
Do the same for tVar.
Step 3: a query to fetch the value of variable "a" for Scenario1
Now right click the tVar query in the Power Query Editor and select "Reference".
In the resulting query, right click on the value for variable a, Scenario 1, and select "Drill down"
The result is no longer a table, but the value itself (10):
Rename this query to "a1".
Steps 4-6: do the same for b1, a2 and b2
The result is this:
Step 7: make a table with Value*a/b for scenario 1
The same way I referenced tVar, I now referenced tData.
In the new query that is created this way, add a Custom Column, with formula
= [Value]*a1/b1
Rename this new query to tScen1.
Step 8: duplicate the previous query and change it to display Scenario2
Right click on query tScen1 and select duplicate.
The only thing to change in the duplicate is a1 -> a2 and b1 -> b2 in the formula
Rename this query to tScen2.
Step 9: Merge the last two queries into a new query representing both scenarios
Merge Queries as new:
This results in a new query "Merge1"
Rename "Merge1" to "tResult", and expand "tScen2"
only expand the calculated column:
The result is almost what we want:
Rename the two columns to the right for clarity:
Close and load creates a table in a new worksheet, based on which we can create the chart showing the two scenarios at once:
Conclusion
If I now want to change the variables, I just have to change them in my worksheet Sheet1 and Refresh All.
If I want to change the formula, I'll have to either adapt queries tScen1 and tScen2, or adapt tScen1, delete tScen1 and repeat step 8.
I still would prefer an easier way, eg. Scenario Reports that automatically update when the data is changed, but apparently that isn't possible at the moment.
I found an even better solution for displaying multiple scenarios simultaneously in one chart, without Power Query, based on these two articles: "https://www.dummies.com/software/microsoft-office/excel/build-drop-scenarios-financial-model/" and "https://www.dummies.com/software/microsoft-office/excel/conveying-message-charting-scenarios-financial-model/", apparently from the book "Financial Modeling in Excel For Dummies".
(I had to reverse engineer the second article, because it's incomplete and has an error in it, "Highlight cells A24:F2" should be "A24:F27").
The workbook attached contains a step-by-step explanation.
In short, it looks like this:
- The blue table is where the calculation is done (for one scenario at a time), fetching values from the orange table (C12 and C13), where the variables live.
- In B1, there's a "scenario switcher" (a simple data validation list), to choose the active scenario
- the values in C12 (variable a) are fetched from D12:H12 using an XLOOKUP with lookup value B1 (active scenario). Similar for C13 (variable b). This makes the worksheet and the calculated values respond to the drop-down in B1, and lets you change scenarios easily. But it still doesn't allow for simultaneous showing of all scenarios in one graph.
- the really smart part is the data table in B16:H21 (What-if Analysis > Data Table). It looks like a two-variable Data Table, but it's not. Instead, it's a one-variable Data Table with as "Column input cell" B1 (and no row input cell).
The data table then allows for the simultaneous showing of all scenarios in one graph.
What's great about this solution is that you can change the formula in the blue table, and all scenarios follow. And instead of copy-pasting the results for each scenario in a separate (static) table, the data table follows dynamically. And you can of course easily see, and display, the variable values for each scenario.
Riny_van_Eekelen wrote:bartvana Followed this tread the last couple of days. Was wondering why you need such a complicated approach at all. A simple structured table with the calculations, linked to the variables for each scenario (your orange table) achieved all in one go. And it expands automatically. Add an extra year end the graph will expand with it. See attached.
But perhaps I'm missing the point all together.
Thanks for answering!
My example is indeed extremely simplified in an attempt to make my point clear, and in its simplified form, it does allow for a solution like yours (which is also interesting of course).
But imagine you don't have one column with a formula, but 7, each with a different formula, and you want these 7 columns to adapt to 5 scenarios. You'd have 7*5 = 35 columns in the table. Plus, if you'd want to adapt one formula, you'd have to repeat this adaptation 7 times, which is monkey work I always try to avoid.
One more scenario is with dynamic arrays
where
years: =TRANSPOSE(tData[[#All],[Year]]) scens: =TRANSPOSE(INDEX(tVar[#Headers],1,SEQUENCE(,COLUMNS(tVar)-1,2))) data: =TRANSPOSE(tData[Value])* TRANSPOSE(INDEX(tVar,1,SEQUENCE(,COLUMNS(tVar)-1,2))/ INDEX(tVar,2,SEQUENCE(,COLUMNS(tVar)-1,2) ) )bartvana Followed this tread the last couple of days. Was wondering why you need such a complicated approach at all. A simple structured table with the calculations, linked to the variables for each scenario (your orange table) achieved all in one go. And it expands automatically. Add an extra year end the graph will expand with it. See attached.
But perhaps I'm missing the point all together.
