Copying values from Cell [that is changing dynamically based on a Dropdown Box Trigger - Scenarios]]

[agewisdom]

Dear All,

Great forum here. I'm hoping for a solution for a problem I have here. I prefer a non VBA solution, but if that's not possible, maybe any guru's here can suggest a simple VBA. [I'm not good at VBA, sadly].

I am facing an issue in my three financial statement model over a period of 3 years. I created a best, expected and worst case scenario and a switch to control the input variables. The financial statements are driven by 10-12 variables ranging from product sold, direct variable costs, indirect expenses % and other assumptions. The base figures used in the financial statement model is driven via a CHOOSE function, which will select the assumption to used based on the scenario selected.

However, the problem is how to link the values into the Executive Summary sheet. For instance, the Sales will change based on the drop down button, if I select change from Best to Worst Case. What I want is to simply copy paste the Best Case into the Executive Summary dynamically. But this figure will change upon changing the drop down button.

If VBA is necessary, is it possible for the Executive Summary to be fully automated when certain input variables change in the best/expected/worst case scenario.

E.g. If best case, units sold is 100, but user changes it to 120, can the Executive Summary automatically refresh to update the new best case scenarios.

I included a simplified example of my model below.

 

[Nebu]

Hi:

May be you want to read through the following.

https://trumpexcel.com/scenario-manager-in-excel/

Thanks

 

[agewisdom]

Thanks. I have tried Scenario Manager but it doesn't fit my end user needs.

Firstly, there's too many variables. In my sample file, it's only two variables but in the actual file, there's more like a dozen.

Secondly, the end user just wants a simple Executive Summary rather than the Scenario Manager.

 

[bosco_yip]

Maybe........

1] In "ExecSumm" sheet B3, copied across:

=IFERROR(SUMPRODUCT(INDEX(Input!$B$11:$D$13,IF(FinStmt!$B$1&" Case"=B$2,FinStmt!$C$1,""),0)*INDEX(Input!$B$15:$D$17,IF(FinStmt!$B$1&" Case"=B$2,FinStmt!$C$1,""),0)),"")

2] In "ExecSumm" sheet B4, copied across :

=IFERROR(SUMPRODUCT(INDEX(Input!$B$11:$D$13,IF(FinStmt!$B$1&" Case"=B$2,FinStmt!$C$1,""),0)*INDEX(Input!$B$15:$D$17,IF(FinStmt!$B$1&" Case"=B$2,FinStmt!$C$1,""),0)*INDEX(Input!$B$19:$D$21,IF(FinStmt!$B$1&" Case"=B$2,FinStmt!$C$1,""),0)),"")

Regards
Bosco

 

[agewisdom]

Many thanks.

This would work if the amount of information to be displayed in the Executive Summary was limited. Unfortunately, the information to be summarised would be substantial, so this approach would be somewhat unwieldy.

Especially, if there are large amount of variables... e.g. Net Profit would be based on up t o 10-12 variables assumptions, so if each has a Best/Base/Worst scenarios, would be looking at sets of 12 variables x 3 years x 3 scenarios.