“Scenario” Report

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

Click the button “Summary” in the “Scenario Manager” dialog box.

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

• “Scenario summary” (summary appears as a “Worksheet”)
• “Scenario PivotTable report” (published as a report in the form of a “PivotTable”)

In this example I choose the first option, “Scenario Summary”.
In the “Result Cells:” we give the cell (s) in which we are interested.
In this case, the cell C10, the Grand total cell

Click OK.
Our summary report is displayed in a new “Worksheet”:

 

“Goal Seek”

In a previous lesson, I showed you an example of how we used the function “PMT” or payment to calculate a loan payment.

In this example I will show you how to work the other way, using the “What if “… analysis.

I want to borrow an amount for the purchase of a car, but I think that 20,000 is a bit too high for my wallet.
I can only afford to borrow 250 per month so I see want to calculate how much of a loan the bank will give me for this payment.

I select the cell C7, in which our “Formula” is the “INTRATE” function, select the tab “Data” in the ribbon, click “What if …” analysis, and select “Goal Seek”.
This opens the dialog, which you were thinking, “Goal Seek”.

In the first box, I indicate the cell to be adjusted or C7 .

In the second box, I give the amount that I can pay monthly, I have to pay so I give a negative number, -250.

And in the third box I give the cell to be adjusted, in C3.

And click OK.

It opens a new window with the message that “Excel” has found a solution.

If Excel only had a solution on “how” I could pay, I would sleep easier 🙂

Featured Courses

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