Creating “Scenarios”

In certain cases it may be useful to view different results, based on the variables.

In Excel,we use “Scenarios” tool to show our results, based on several factors.

For example, we have our sales this year, and want to see the sales for next year with an increase of 10% and 5%, plus a reduction in turnover of 5%.

We use the “Scenario Manager” to show the different results.

To begin, we create a second “Worksheet” (expectations 2nd KW) where we will view the “Scenarios”.

We copy the column and row titles of the first “Worksheet” to the second.

In cell B2 of the new “Worksheet”, we give the following “Formula”:

KW = ‘1 st ‘! B2 + (KW ‘1 st’! B2 * ‘expectations 2nd KW’! $ C $ 9)

We make the cell C9 as an “Absolute cell reference” ($ C $ 9) because it should remain the same when we copy it in the next step.

Then we copy this “Formula” to other cells for the month of January, February and March.

We place the cursor in cell C9, and click “Tools” in our menu bar and select “Scenarios”.

The “Scenario Manager” dialog box opens.

Click on “Add”.

A new dialog box appears.

We give our “Scenario” a name.

Since we selected our cell C9 , the “Changing cells” part is already filled..

If you have forgotten to select, you can do so by clicking  .

In the “Comment” box, you can customize any comments if you wish.

Click OK.

In the third window we type the number that we want to see as our predicted revenue.

In our case, it is 10%.

Click OK.

We repeat the previous steps for a 5% increase and a 5% reduction.

All “Scenarios” are displayed in a list:

Click “Close” ino the “Scenario Manager” dialog.

We can easily go through all “Scenarios” by selecting it and clicking “Show”.

If the “Scenario” window does not open, you must first open this course.

Editing and deleting “Scenarios”

To modify a “Scenario” click “Tools” in our menu bar and select “Scenarios”

We select the “Scenario”, and click on the “Edit”, to our right.

In the dialog “Edit Scenario” we can make all the changes that we deem necessary.

Click OK when you are satisfied.

To delete a “Scenario”, we select the “Scenario”, and click on the “Delete” button.

“Merge Scenarios “

We can “Merge Scenarios” from another “Worksheet” or another “Workbook”.
This is only useful when the cells that change the performance of the “Scenario” are the same in both sheets.

To “Merge Scenarios” together we open the “Worksheet” where we want to merge the scenarios, then we open the “Scenario” we want in the other “Worksheet”, then choose “Tools” from the menu bar and select “Scenarios”.

In the “Scenario” dialog we click the “Merge …” button.

In the “Merge Scenarios” dialog we select the “Worksheet” that we want to merge for the “Scenario”.
Click on the dropdown list next to “Book” and select the “Worksheet”.

In the “Sheet” section of the dialog, we select the “Worksheet” that are to be used for our “Scenarios”.

At the bottom of the dialog, we see how many “Scenarios” there are in the “Worksheet” that we have selected:

Click OK to import.

“Scenario reports”

In Excel we can easily see the results from comparing the different “Scenarios” as a “Scenario Report”.

To create a “Scenario Report”, we first open the “Worksheet” from which we wish to report.

Then we click on “Tools” in the menu bar and click “Scenarios”.
We click the button “Summary …”.
In the “Scenario Summary” dialog we can choose from “Scenario Summary” or “Scenario Pivot Table Report.”
The first is a regular report, the second is a “PivotTable” report.

In the “Results cells:” section, we click on the “Expand” button  and select the cells we want to compare.

Click again on the “Expand”

And click OK.

“Goal Seek” (“Goal Seek”)

Excel has another tool, “Goal Seek”.

“Goal Seek” is used when we know the answer, but dont exactly know what we need to enter to get the answer.
for example. Suppose we know that 7 multiplied by something is 49 but we do not know with what, then we can use “Goal Seek”.

Example:
We want to buy a car, and this may cost us 500 Euro per month. We know that the interest rate is currently 6.50%. We also know that we want to spread the repayment in 48 months. How much can we borrow?
For that, we use “Goal Seek”.

To start “Goal Seek”, we click on “Tools” in the menu bar and select “Goal Seek”

The “Goal Seek” dialog opens.
In the “Set cell: :” section, we click on the “Expand”button  and select the cells to show the payment amount.

In the “To value”: we determine the amount and this number should be negative.

And the last box “By changing cell:” we give the cell in which we want the outcome.

Click Ok to see the result.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats