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.

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:

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.

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.

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.

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 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.

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.

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.

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.

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.

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.

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.

##
Recommended MS Excel Textbook

##

In the **next part (part 3)**, I will explain another What If Analysis and Excel Powerful tools – the Goal Seek tool in MS Excel.

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

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

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.

Labels: Microsoft Excel