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.
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.
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.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!