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.

step by step guide to learn what if analysis tools - scenario manager and data tables

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 at http://www.microsofttut.com/2017/04/how-to-calculate-loan-payment-using-pmt-function-in-excel.html, 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 at http://www.microsofttut.com/p/microsoft.html.

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:

Click http://www.microsofttut.com/2017/04/learn-what-if-analysis-tools-scenario-manager-and-data-tables.html to study full tutorial on what if analysis with screen to screen images.