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

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

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?

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

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...

Originally Posted by age123

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:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rScenario As Range
    Dim rNetProfit As Range
    Dim rOutput As Range
    
    With Worksheets("Model")
        Set rScenario = .Range("A1")
        Set rNetProfit = .Range("A2")
    End With
    
    If Not Intersect(Target, rScenario) Is Nothing Then
        Select Case rScenario.Value
            Case "Best"
                Set rOutput = Worksheets("Summary").Range("A1")
            Case "Base"
                Set rOutput = Worksheets("Summary").Range("A2")
            Case "Worst"
                Set rOutput = Worksheets("Summary").Range("A3")
            Case Else
                Exit Sub
        End Select
        rOutput.Value = rNetProfit.Value
    End If
End Sub

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

Put this in the Workbook module:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "FinStmt" And Target.Address = "$B$1" Or Sh.Name = "Input" Then
        With Application
            .EnableEvents = False
            Call UpdateSummary
            .EnableEvents = True
        End With
    End If
End Sub

Private Sub UpdateSummary()
    Dim r As Range
    Dim i As Integer

    i = Application.Match( _
        Worksheets("FinStmt").Range("B1").Value, _
        Worksheets("Working").ListObjects("Table3").ListColumns("SCENARIO").DataBodyRange, _
        0)
    With Worksheets("FinStmt")
        Set r = .Range("F4", .Cells(Rows.Count, "F").End(xlUp))
    End With
    Worksheets("ExecSumm").Cells(3, 1 + i).Resize(r.Rows.Count).Value = r.Value
End Sub

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...

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

Sub RunAllScenarios
    Dim i as Integer
    For i = 1 to 3
        Worksheets("Fin Stmt").Range("B1").Value =  Worksheets("Working").ListObjects("Table3").ListColumns("SCENARIO").DataBodyRange.Cells(i,1).Value
    Next i
End Sub