Financial Model - Scenario results based on a switch to an executive summary

[age123]

Hi all,

I am facing an issue in my financial model. I created a worst, base and best case scenario and a switch to control the input variables. The financial statements all work well.
However, the problem is how to link the values into the Executive Summary sheet.

For instance, the Net Profit in the best is $12M, base $10M and worst $8M. How do I link the cells in the executive summary to show this.

For instance, 1. Best Case scenario, cell A1 = [LINK] will show $12M 2. Base Case scenario, cell A2 = [LINK] will show $10M

Right now, the Net Profit value is in a single cell, which dynamically changes based on the switch. How do I link the Net Profit from each of best / base / worst case scenario to different cells in the Executive Summary? Short of copying and paste... which means it's not dynamic.

Thanks.Fi

[Olly]

Try using Scenario Manager, which does exactly what you are trying to achieve, and provides summary reports to compare scenario outputs.

[age123]

Thanks for your reply.

However, my inputs variables are too many to use the Scenario Manager effectively. And it's also more for the end user, so using the Scenario Manager may not be too user friendly.
Is there a simple way of doing this dynamically link the results on the Executive Summary?

[Olly]

Sounds like you just need to copy the required output cell(s) and the scenario name to another range, whenever you "switch" your inputs.

Are you using VBA to 'switch' inputs?

[age123]

I'm trying to avoid using VBA.

What I have is a bunch of inputs, that's based on best, base and worst case:

E.g.
Units sold
Best 120 units [Cell C1]
Base 100 units [Cell C2]
Worst 80 units [Cell C3]

Selling price per unit = $50

In the revenue column, it's Selling Price per unit x Units Sold [e.g. Cell A1]
But the Units Sold cell selects the inputs based on the trigger button.
The trigger button is a simple drop down cell, to choose which scenario.
The Units Sold button is based on the Choose function.

When I select Best Case, Cell A1 will put via the Choose function from Cell C1. When I select Worst Case, it pulls from Cell C3.

What I want is a way to present dynamically in the executive summary the sales value:
Best case = 120x50
Base case = 100x50
Worst case = 80x50

Right now, the sales value will change dynamically based on the trigger.

For sales, using the scenario manager is easy enough. But the Net Profit is based on a bunch of variables like units sold, selling price, variable costs units etc. So, the final net profit is driven by a bunch of different variables.

In Youtube, this video can explain better. I can't put any link...
Scenario Analysis - How to Build Scenarios in Financial Modeling

[Olly]

Sounds like you have the following options:

- Scenario Manager (depending on the number of variable inputs)
- Multiple copies of the model for each scenario (multiple worksheets?)
- VBA to copy scenario outputs to summary
- Manually copy scenario outputs to summary

Hard to be more help without seeing your actual workbook. But you can't use formulas to look at values which no longer exist...

[age123]

Sounds like you have the following options:

- Scenario Manager (depending on the number of variable inputs)
- Multiple copies of the model for each scenario (multiple worksheets?)
- VBA to copy scenario outputs to summary
- Manually copy scenario outputs to summary

Hard to be more help without seeing your actual workbook. But you can't use formulas to look at values which no longer exist...

Ah... thanks. I'm guessing option 3 is probably the easiest then. Else, it's option 2 with hidden worksheets.
Was hoping there was a simple solution, but there probably isn't any.

Appreciate the help.

[age123]

Stackoverflow.com/questions/40734860/using-vba-code-to-copy-specific-value-from-a-cell-when-the-dropdown-box-changes

I found another person asking the same question. So, it seems this is not possible without VBA?

Else, is the code above good enough? Not familiar with Excel VBA and it seemed some revisions are needed on that code.

[Olly]

So, it seems this is not possible without VBA?

As I said...


It's hard to write code that will suit your needs without seeing your workbook. But something like this, in the "Model" worksheet module:

Please Login or Register  to view this content.

[age123]

Many thanks for the code.

I don't think I'm allowed to post any attachments here yet... unfortunately. Could you PM me your email or let me know where to post my spreadsheet to?

[Olly]

To attach a sample workbook: In your reply, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[age123]

file attached.

can the VBA trigger to update the Executive Summary if any of the best/base/worst case scenario variables is altered. For instance, if Selling Price is altered in the best case scenario?

[Olly]

Put this in the Workbook module:

Please Login or Register  to view this content.

Of course, the Exec Summary will only be updated for the currently selected scenario, regardless of which input variables are changed - so if you changed a selling price for "Best Case", this would only be reflected in the Exec Summary if "Best Case" is selected on sheet [FinStmt].

I'm not convinced any of this is the best approach...

[age123]

Put this in the Workbook module:

Of course, the Exec Summary will only be updated for the currently selected scenario, regardless of which input variables are changed - so if you changed a selling price for "Best Case", this would only be reflected in the Exec Summary if "Best Case" is selected on sheet [FinStmt].

I'm not convinced any of this is the best approach...

Many thanks for your hard work in coding I'll try to understand it bit by bit.

For the change in variables, I'm thinking of adding a excel button like "Update Scenarios" on the Executive Summary itself. Could the code then be amended to loop through each of the scenarios and copy paste the values into the Executive Summary?

I really appreciate your assistance so far, Olly. Hope I'm not asking for too much here. Still what might take you 1 minute to code, might take me an hour... if at all...

[age123]

Bump! Would appreciate whether the below is possible or not via VBA.

"For the change in variables, I'm thinking of adding a excel button like "Update Scenarios" on the Executive Summary itself. Could the code then be amended to loop through each of the scenarios and copy paste the values into the Executive Summary?"

[Olly]

Not tested (and it's half past beer-o'clock!), but something like this, in addition to the code already posted:

Please Login or Register  to view this content.

[age123]

Many thanks. Crossing my fingers and hoping it'll work