Scenario in Excel

Scenario:

A Scenario is a set of values that Excel saves and can substitute automatically on your worksheet. You can create and save different groups of values as scenarios and then switch between these scenarios to view the different results. If many employees have specific information that you want to use in scenarios, you can collect the information in separate workbooks, and then merge the scenarios from the different workbooks into one. Finally scenario summary report generates that incorporates information from all the scenarios.

Crating scenarios:

Since scenarios are based on comparison mainly on data analysis hence here we will take two particular columns where we want to track the changes step by step.

Step 1: Navigate Data tab and click What If Analysis drop down button and Select Scenario Manager… command under Forecast group (refer figure 1.0)

A Scenario Manager popup comes as shown below (refer figure 1.1)

Step 2: Click Add button and name first scenario and provide range which you would like to take for analysis as shown below figure 1.2

In the Scenario name dialog, name the scenario column2, and specify that cells B2 value that change between scenarios. Under comment it take user information with current date.

Protection – You can also protect your scenarios, so in the Protection section check the options that you want, or uncheck them if you don’t want any protection.

• Select Prevent Changes to prevent editing the scenario when the worksheet is protected.
• Select Hidden to prevent displaying the scenario when the worksheet is protected.

Step 3: Click OK button and it brings following dialog where you can control the value of selected range as shown below:

Step 4: Click Add button to add another scenario as shown below

Name, Cell reference and OK button:

Change the value and click OK if you want to add more scenario click on Add button and repeat the same process for as many times as you want.

Step 5: Summary, click on Summary… button and system will prompt for result cell with two options Summary or PivotTable as shown below

Select result cell reference and click OK but keeping Summary as default:

You’ll notice that Excel has automatically added Grouping levels for you, which will expand and collapse the view as you click the different selectors.

A note appears at the end of the summary report explaining that the Current Values column represents the values of changing cells at the time the Scenario Summary Report was created, and that the cells that changed for each scenario are highlighted in gray.

Let’s put code around to automate the process. Please note the given code works in VSTO environment hence following prerequisites are recommended:

• Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
• Create Excel Addin in C# or VB code style (Visual Studio Tools for Office)
• Excel 2010 or above
• Create a ribbon designer and put button

C# code example: Please make sure to select range (including all cells on which scenario needs to be created) before click on the button:

private void btnAddScenario_Click(object sender, RibbonControlEventArgs e)
{
	//get active workbook reference
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

	//retain selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Column name constant
	const string SCENARIO_INITIAL = "column";

	//Name appender
	int i = 1;

	//scenarios object
	excel.Scenarios oScenarios = oWorksheet.Scenarios();
	
	//Add scenario on each cell
	foreach (excel.Range oCell in oRange.Cells)
	{
		oScenarios.Add(SCENARIO_INITIAL + i.ToString(), oCell, oCell.Value,
			"Created by VBAOVERALL", true, false);
		i++;
	}
	//Add summary
	oScenarios.CreateSummary(excel.XlSummaryReportType.xlStandardSummary, oWorksheet.Range["H2"]);
}

VB.Net code example:

Private Sub btnAddScenario_Click(sender As Object, e As RibbonControlEventArgs) Handles btnAddScenario.Click
	'get active workbook reference
	Dim oWorksheet As excel.Worksheet
	oWorksheet = Globals.ThisAddIn.Application.ActiveSheet

	'retain selection
	Dim oRange as excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Column name constant
	const SCENARIO_INITIAL as string = "column"

	'Name appender
	Dim i as Integer
	i=1

	'scenarios object
	Dim oScenarios as excel.Scenarios
	oScenarios = oWorksheet.Scenarios()

	'Add scenario on each cell
	For Each oCell As excel.Range In oRange.Cells
		oScenarios.Add(SCENARIO_INITIAL + i.ToString(), oCell, oCell.Value,
		"Created by VBAOVERALL", true, false)
		i=i+1
	Next

	'Add summary
	oScenarios.CreateSummary(excel.XlSummaryReportType.xlStandardSummary, oWorksheet.Range("H2"))
End Sub

VBA code example:

Public Sub AddScenario()
    'bind sheet referenc
    Dim oWorksheet As Worksheet
    Set oWorksheet = ActiveSheet
    
    'Bind selection
    Dim oRange As Range
    Set oRange = Selection
    
    'Column name constant
    Const SCENARIO_INITIAL As String = "column"
    
    'Name appender
    Dim i As Integer
    i = 1

    'scenarios object
    Dim oScenarios As Excel.Scenarios
    Set oScenarios = oWorksheet.Scenarios

    'Add scenario on each cell
    For Each oCell In oRange.Cells
        Dim strColumnname As String
        strColumnname = SCENARIO_INITIAL & i
        'add scenario
        oScenarios.Add strColumnname, oCell, Array(oCell.Value), "Created by VBAOVERALL", True, False
        i = i + 1
    Next

    'Add summary
    oScenarios.CreateSummary xlStandardSummary, oWorksheet.Range("H2")
End Sub

Share on Facebook

Tweet