Scenario Report

We can create a report based on the scenarios that we have made.
Click on the downward pointing arrow next to “What if analysis”, and choose “Scenario Manager”.
Click the “Summary” button in the “Scenario Manager” dialog box.

This opens the dialog “Scenario Summary”, with two options:

• “Scenario summary” (summary worksheet appears )
• “Scenario PivotTable report” (published as a report in a PivotTable)

In this example I’ll choose for the first option, “Scenario Summary”.

In the “Result Cells”, we specify the cell (s) where we want to see the result.
In this case, this is the cell C8 with the general total.
Click OK.
Our summary report is displayed in a new worksheet:

“Goal Seek”

In lesson 32, I had shown you an example how the PMT function calculates payments.
In this example I will show you how to do that using “What if … analysis”.
I want to borrow an amount for the purchase of a house.
I can only pay only €1,000 per month. so let us see how much I can loan.

I select the cell B4, in which we use the PMT function, select the “Data” tab in the “Ribbon”, click “What if …” analysis, and select “Goal Seek”.

This opens the “Goal Seek” dialog.

In the first field, I give the cell to be modified, that is B4. The current value is too high for me.
In the second field, I enter the amount that I can pay monthly, I enter a negative number as I have to pay, -1000.
In the third field I give in the cell that has to be modified, B1.
And click OK.
Excel now gives me the value of the loan amount for which the monthly payment is 1000 €.

Featured Courses

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