VBA to manage multiple scenarios in a financial model

[Gregg Wolin]

I have a model with a set of Assumptions (50/- inputs) which generates a set of Results. Let's call each set of Assumptions and associated Results as a "Scenario."

First: I would like the ability to copy each Scenario (of Assumptions and Results) into a Scenario Matrix (or table) with the click of a button (on another worksheet).

Second: As the Scenario Table grows, I'd like the ability to retrieve the assumptions associated with each Scenario and quickly repopulate the inputs of the model with the Scenario of my choosing (with a dropdown) at which point I can change one or more of the assumptions, click "Save" and add a new Scenario is added to the Scenario Table (as well as to the dropdown).

The attached file attempts to illustrates what i want to do (in case my poor word usements above don't suffice).

While i understand the basics of how to copy and paste the values in Step 1, I've had trouble finding direction on pasting into a "dynamic" matrix.

 

[Hui]

You can use a Data Table to manage all that without any VBA

Have a read of :
https://chandoo.org/wp/2010/05/06/d...o-simulations-in-excel-a-comprehensive-guide/

 

[Hui]

I have marked up your file to do what I described above

But I have only linked the Yellow Inputs to the model so far

 

[Gregg Wolin]

Thanks so much!!! This method should facilitate a big part of the functionality i seek. I will spend some time and see what happens once I fully implement the data table methodology into my model. Stay tuned!

 

[Gregg Wolin]

I'm struggling a little as I'm new to Data Tables.

The mockup i sent you didn't have the actual "Model" that generates the results that feed into cells D114-D151. If these cells were connected to the calculation worksheet, would your table show the model outputs for all 6 scenarios for which assumptions were entered (as if each Scenario were being run thru a stand-alone version of the model)?

Additionally, I'm having trouble replicating the steps in your tutorial for the multiway setup. Given the large number of assumptions, i need to transpose the Scenario table. I've attached the full working version just in case this changes your thoughts.

 

[Hui]

What I recommend is that you add a Table into your model somewhere that has a list of all the scenarios in a Column or Row and a list of all the Inputs you want to change for each scenario:

like in my example


or in columns


Add as many fields that you want to vary as you require

Next link the model inputs to the appropriate Yellow Selected Scenario cells

Then post the file back here

 

[Debaser]

There's also the built-in Scenario Manager.

 

[Gregg Wolin]

What I recommend is that you add a Table into your model somewhere that has a list of all the scenarios in a Column or Row and a list of all the Inputs you want to change for each scenario:

like in my example
View attachment 50968

or in columns
View attachment 50969

Add as many fields that you want to vary as you require

Next link the model inputs to the appropriate Yellow Selected Scenario cells

Then post the file back here

I'll give it a shot. Again, thanks for your help!

 

[Gregg Wolin]

What I recommend is that you add a Table into your model somewhere that has a list of all the scenarios in a Column or Row and a list of all the Inputs you want to change for each scenario:

like in my example
View attachment 50968

or in columns
View attachment 50969

Add as many fields that you want to vary as you require

Next link the model inputs to the appropriate Yellow Selected Scenario cells

Then post the file back here

 

[Gregg Wolin]

Better late than never Hui. While I got the data table working in my model from a while back, I cant seem to recreate it in the attached (its been a while). I reviewed your detailed blog post again and while the first example is pretty clear, I get lost when getting into more variables.

 

[Hui]

Gregg
I'm going to have to start charging commissions on this work ?

Have a look at the attached file

Check out the data Table area

Enjoy

 

[Gregg Wolin]

Gregg
I'm going to have to start charging commissions on this work ?

Have a look at the attached file

Check out the data Table area

Enjoy

This is awesome, thank you! A few questions so I (hopefully) won't have to ask for this help again....

1. Is there a way to show (via conditional formatting or otherwise) the boundaries of the "Data Table" the source and which cell is the "Row Input Cell" and which is the "Column Input Cell"? Does a data table show up as some type of object that can be searched for (like an actual excel Table)? Maybe creating Named Ranges that represent the Data Table boundaries as well as the Row and Column input cells?

2. If the input in G12 is anything other than "1" then Scenario 1 is omitted from the Data Table and the chosen scenario (i.e. Scenario 2, 3, 4 or 5) ends up being displayed twice. Is there a way that all 5 can show in the table regardless of which one is selected via G12?

3. I'd like to get rid of the "-" characters in the Data Table (although I know why are showing up). While I could make a different table that references the rows I'd like displayed, is there an easier way you would suggest.

Once again - thanks for you patience.

 

[Hui]

1. No, But just do it manually, that what I do
2. Yes, I'll repost in a minute
3. Select the Data Table area and Apply a format using Ctrl+1

 

[Hui]

Updated as requested

 

[Gregg Wolin]

Updated as requested

Thanks Hui!
In this data table, which cell is the row input and which is the column input?
Finally, just so I'm clear, once the data table is created, there's no way to see what had been entered and the row and column source (i.e. if I select a cell within the data table, then go to Data>What If > Data Table, the row and column source inputs are blank. You are saying there is no way to so see what those inputs were once the data table is created?)

 

[Hui]

The Data table is in Range AI10:AN30

Refer to the diagram below and the points below for s description o how it works

1. The data table is a Row Lookup Table and uses cells in Row 10 Columns AJ:AN
2. The DT places the looked up values 1 to 5 into cell G12, The Scenario Number
3. The model recalculates and the results are returned to Column AI11:AI30
4. The Cells in AI11:AI30 have custom number formats and so look like headings
5. The Cells in AI11:AI30 have formulas that return the values and the values are transferred into the Corresponding Column for each case



Thanx for the gift.
All donations I receive are collated and donated to a local charity each year