What-if analysis is one of the most important benefits of
Microsoft Excel as a spreadsheet application. Performing what if analysis means
changing the values of certain key variables in a formula or worksheet. It can
be performed using some of MS Excel’s powerful tools such as Scenario Manager,
Goal Seek and Data Tables. In this tutorial, I will give you a step by step guide on how you can manipulate and use these MS Excel Business tools: Scenario Manager and Data tables in various fields.

##
**SCENARIO MANAGER**

Scenarios Manager is one of MS Excel’s powerful what-if tools
used to substitute values automatically in a worksheet. It is used to calculate
loan amortization on what if basis. An example of a logical question that
Scenario Manager that Scenarios answer are:

**If I decide to pay back a 12 year**

**$1000000 loan within 10 years on monthly basis, what then will be my monthly pay to amortize the loan and what will be my total payback amount?**

**previous part**, I explained How to use the PMT function to calculate loan payment or amortization. Also see the

**full tutorial course content of this Microsoft Excel Tutorial Series**.

###
**FUNCTIONS OF
SCENARIOS**

1. It is used in loan amortization analysis.

2. Scenario Manager is used to adjust values automatically in a
worksheet.

3. It is used to substitute values such as Loan amount, Interest
rate and Period automatically.

4. Scenario is used to summarize loan amortization.

5. It enables the user to calculate the monthly pay, total pay
and bank profit at the end of each period.

##
**GENERAL STEPS TO
CREATE A SCENARIO SUMMARY IN MS EXCEL**

Now, I will give you the step by step guide to create a
summary for loan amortization or payment using the scenario manager in
Microsoft Excel. I will also illustrate it further with a practical example.

###
**To Create a Scenario
Summary for Loan Payment in MS Excel:**

1. Prepare the loan amortization table and calculate the
monthly pay, total pay and bank profit using the PMT function as illustrated
above.

2. Click the

**Data**tab, then click the**What-if Analysis**drop down arrow located in the**Data tools**group. Then Select**Scenario Manager**from the resulting options as shown below.
3. This prompts the

**Scenario Manager**dialogue box. Click**Add**and then type in the scenario name or year.
4. Specify the

**Changing cell**that is the**loan amount**,**interest rate**, and**period**and click OK.####
**NOTE:**

You either type the cell references of the changing cell or
click bolt icon at the extreme right to make a selection and then click on the
bolt icon again to commit your selection as shown below.

5. Next, enter a value for each of the changing cells and click

**Add**.
6. Repeat steps 3 to 5 for other set of values given. You can
always add, edit, delete or merge any set of value by selecting the appropriate
action button as shown below.

7. Click

**Summary**and specify the**Result**cells that is the monthly pay, total pay and bank profit
8. Click OK.

##
**DATA TABLES FOR WHAT
IF ANALYSIS**

Data tables help you to see results of multiple inputs such
as scenario inputs at the same time. It is used to compare different data set
output in MS Excel and for scenario summary.

###
**TYPES OF MS EXCEL
DATA TABLES**

There are two types of data tables that can be used for
what-if analysis. They include:

1. One Variable Data table.

2. More than One Variable Data table.

##
**ONE VARIABLE DATA TABLE
WITH ILLUSTRATION**

This type of data table is constructed when dealing with
only one key variable. The key variable in this case can either be the interest
rate or the periods of payment.

For example, to compute the monthly payment, total payment
and bank profit on a 20 year $1000000 mortgage with five different interest
rates: 15%, 16%, 17%, 18% and 19%, you will first prepare a worksheet as shown
below.

Enter the following formulas in the appropriate cells as specified
below:

For the Monthly pay; in cell B5, type:

**=PMT(B3/12, B4*12, B2)**then press the Enter key.
For the Total pay; in cell B6, type:

**=B5*B4*12**then press the Enter key.
For Bank profit; in cell B7, type:

**=B6-B2**then press the Enter key.###
**To Prepare the
Scenario Summary for the above example:**

1. Click the

**Data**tab, then click the**What-if Analysis**drop down arrow located in the**Data tools**group. Then Select**Scenario Manager**from the resulting options.
2. This prompts the

**Scenario Manager**dialogue box. Click**Add**and then type in the scenario name or year. For example First interest, Second interest, Third interest, Fourth interest, First interest, etc.
3. Specify the

**Changing**cell reference, in this case,**interest rate**cell ($B$3) and click OK.
4. Next, enter a value for each of the changing cells. In this
case,

**15%**or**0.15**and click**Add**to add the values for the subsequent key variable as shown below.
5. Repeat steps 2 to 4 for other set of values given, that is
for 16%, 17%, 18% and 19% respectively.

6. Click on

**Summary**and select**Scenario Summary**. Then specify the**Results**cells range, which is**$B$5:$B$7**. Then click OK.
Your Scenario summary should resemble the one shown in the
worksheet below.

##
**MORE THAN ONE
VARIABLE DATA TABLE WITH ILLUSTRATION**

This type of data table is constructed when dealing with
more than one key variable. The key variable can be all or any two of the
following: Loan amount, Interest rate and Period of payment.

For example, Mr. Johnson is going for a loan of $250000 from
Diamond Bank to complete his house, the bank agreed to give him the loan on an
interest rate of 5% for a period of 4 years. If the bank Manager decided to
offer him $300000, $350000, $400000 and $450000 loan on an interest rate of
5.5%, 6%, 6.5% and 7% respectively, what will be his weekly pay, monthly pay,
total pay and bank profit on the weekly payment basis? Generate a scenario
summary for the above data.

###
**THE MORE THAN ONE
VARIABLE DATA TABLE SOLUTION**

First, prepare your worksheet as shown below.

Enter the following formulas in the appropriate cells as
specified below:

For the Monthly pay; in cell B5, type:

**=PMT(B3/52, B4*52, B2)**then press the Enter key.
For the Monthly pay; in cell B5, type:

**=PMT(B3/12, B4*12, B2)**then press the Enter key.
For the Total pay (weekly basis); in cell B6, type:

**=B5*B4*52**then press the Enter key.
For Bank profit (weekly basis); in cell B7, type:

**=B7-B2**then press the Enter key.####
**NOTE:**

in the above Total pay (weekly basis) and Bank profit (weekly basis)
formula, I multiplied the period by 52 and divided the interest rate by 52
because 52 weeks make one year.###
**To Prepare
the Scenario Summary for the above example:**

1. Click the

**Data**tab, then click the**What-if Analysis**drop down arrow located in the**Data tools**group. Then Select**Scenario Manager**from the resulting options.
2. This prompts the

**Scenario Manager**dialogue box. Click**Add**and then type in the scenario name or year. For example 1, 2, 3, 4, 5, etc.
3. Specify the

**Changing**cells range, in this case, the**Loan Amount**cell and the**Interest rate**cell (cell range = $B$2:$B$3) as shown below and click OK.
4. Next, enter a value for each of the changing cells. In this
case, the first key variable pair which is

**5%**or**0.05**for the**Interest rate**cell and**$250000**for the**Loan amount**cell as shown below. Then click**Add**to add the values for the subsequent key variable pairs.
5. Repeat steps 2 to 4 for other key variable pairs given, that
is $300000, $350000, $400000 and $450000 for the

**Loan amount**and 5.5%, 6%, 6.5% and 7% for the**Interest rate**respectively.
6. Click on

**Summary**and select**Scenario Summary**. Then specify the**Results**cells range, which is**$B$5:$B$8**. Then click OK.
Your Scenario summary for the two variable data table should
resemble the one shown in the worksheet below.

This is the end of part 2 of chapter 6 of this online tutorial series.

##

In the

## Recommended MS Excel Textbook

##
Get this book (Kindle format): **Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016**.

Click **Here** to know more about the book.

In the
Get this book (Kindle format):

**Designing Professional Spreadsheet Management Systems Using Microsoft Excel 2013 and 2016**.**Here**to know more about the book.

**next part (part 3)**, I will explain another What If Analysis and Excel Powerful tools – the Goal Seek tool in MS Excel.
Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click

**Here**to subscribe for free so that you will get our latest game updates in your email.
WHAT'S ON YOUR MIND?

WE LOVE TO HEAR FROM YOU!