Learn What-If Analysis Tools: Scenario Manager & Data Tables – Step By Step Guide - Microsoft Tutorials - Office, Games, Crypto Trading, SEO, Book Publishing Tutorials

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?

This is part 2 of chapter 6 of the Free Online Excel Tutorial Series at Microsoft Tutorial website. In the 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.

Select your changing cells and click the bolt again

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.

Enter the interest rate for the changing cells

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.

Scenario summary for one variable data table

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.

Worksheet for more than one variable data table

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.

Select your changing cells for the multiple variable and click the bolt again

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.

Enter value pairs for the changing cells

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.