This webpage explains how to use the scenario reporter. The scenario reporter allows you to record multiple scenarios in a sheet in an automatic fashion. It also allows you to modify the scenarios and to start over with a new set of scenarios. The scenario reporter uses a little VBA code which adds copies and pastes variables that you want to present. To use the scenario reporter you do not need to know any VBA; all you need to do is to open the file below and add a sheet to your model. This page explains how to do this in a step-by-step fashion. (Apparently my friend Hedieh uses this even though I did not use it much for a while).
First, lets look at what we are trying to do. The screenshot below shows the results of the scenario analysis. The list of scenarios was created by pressing the copy scenario button and changing various different factors.
Implementing the Scenario Reporter in Your File
To implement the scenario reporter, you first open the file available for download below. This file must be open for the scenario page to run in a similar manner as the generic macro file must be open to run macros like the copy to right and colour formatting macro.
After you open the file, copy the sheet named Scenario Output to your file. You can do this by using the ALT, E, M short-cut sequence as illustrated on the screenshot below. After pressing the short-cut sequence, use the drop-down to select your file and choose where you want to put the sheet. Sometimes after you copy the sheet you may have copy and paste the sheet name.
Once you copy the sheet into your file, you can format the scenario page and link variables from anywhere in your workbook to the scenario sheet. This means that you should change the titles in row 11 of the screenshot above and then link the variables that you want to present. You do not enter anything in the columns with the scenario number and the scenario name (you will enter this yourself). The screenshot below illustrates the process of linking variables and changing the look of the scenario page.
After you link the variables, you can copy the macro button title “Copy Scenario” to other pages of your sheet such as the summary page. Then you can run the macro and make a list of scenarios after you change different inputs. The screenshot below illustrates how you can create the scenarios. After you run the macro, it will take you back to the scenario page and you can start the process again for another scenario.
Video Explanation of Scenario Reporter
The video below provides and example of a finished file. Other exercises walk through how to start with a simple example and ultimately make a very flexible analysis and a nice summary page.