Excel: WHAT-IF ANALYSIS – II

As we know excel includes many powerful tools to perform complex mathematical calculations, including what-if analysis. This feature can you help you to explore different scenarios without changing the values in your spreadsheet. This function is particularly useful for economics and management majors as well as business owners because you can see how different values affect models, formulas, and projected profits.

Excel’s what-if analysis tools:

  • Goal seek
  • Scenario Manager
  • Data table
  • Solver

In part 1 we already discuss about Goal seek and scenario manager. Let us discuss about Data table and solver.

Part 1: Goal seek and scenario manager

Part 2: Data Table and Solver Data Table

Data Table

A data table is a range that evaluates changing variables in a single formula. In other words, it’s a simple what-if analysis: how does changing an input value change the results? You can examine the possibilities with a quick glance. With a Data Table in Excel, you can easily vary one or two inputs and perform What-if analysis. A Data Table is a range of cells in which you can change values in some of the cells and come up with different answers to a problem.

There are two types of Data Tables −

  • One-variable Data Tables
  • Two-variable Data Tables

If you have more than two variables in your analysis problem, you need to use Scenario Manager Tool of Excel.

How to use One-variable Data Tables?

Use a one-variable data table if you want to see how different values of one variable in one or more formulas will change the results of those formulas. For example, you can use a one variable data table to see how different interest rates affect a monthly mortgage payment by using the PMT function.

Example 1:

Let’s consider you have the information on the purchase price of house, the down payment the person is willing to pay for his/her house, 12 month interest rate for his/her house, the repayment years refers to the number of years over which the loan will be repaid and payments per year that is 12 because typically loans are repaid in monthly years. Below mentioned screen is details of the data.

Now with that information let us assume the borrower wishes to see how their borrowing amount and monthly payment will vary based on the different values of repayments year.
So, we will data table for that:

1. now we consider some values as YEAR like mention below in the figure and give a heading as YEAR for better appearance.

2. So for each of these values we used to create a data table of Monthly Payment, Total to Repay Loan and Total Interest Paid.
Now we have to give the cell reference for Monthly Payment,Total to Repay Loan and Total Interest Paid.

3. Now you have to select the entire area or you can say range.

4. Then go to Data tab on the ribbon and locating What-if analysis Click on the What-if analysis button and choose data table from the menu.

Data Table dialog box appears.

  • Click the icon in the Column input cell box. The column input cell is that cell whose value is present in column.
  • So in this case the column input will be repayment years i.e 15 means C6.

You can see that the Column input cell is taken as $C$6. Click OK.
The Data Table is filled with the calculated results for each of the input values as shown below –

If you want the first line as heading then you have to follow some steps.
1. Click on the cell

2. Right click

3. format cell

4. then in number tab go to custom

5. select general and write the heading WITHIN THE “”.

After following, the steps:

Example 2:

In example let us discuss about row input cell which is follow all the steps as column input cell but at the place of choosing row input cell we have to take decision that which cell reference should be taken.

You want to buy a car cost of 9 lacs, but you want to make down payment for 20 percent and rest amount want to pay in equal monthly installment for 5 years at 10.5 percent yearly rate of interest. Here you want to know what will be the EMI for different interest rate.

Create a data table of different interest rate.

Give the cell reference of EMI

Then go to Data tab on the ribbon and locating What-if analysis Click on the What-if analysis button and choose data table from the menu.
Data Table dialog box appears

  • Click the icon in the Row input cell box. The Row input cell is that cell whose value is present in Row.
  • So in this case the Row input will be interest i.e 10.5% means C6.

Now Click OK.
The Data Table is filled with the calculated results for each of the input values as shown below –

Two-variable Data Tables

EXAMPLE:

A person wants to buy a car cost of 9 lacs, but he wants to make down payment for 20 percent and rest amount want to pay in equal monthly installment for 5 years at 10.5 percent yearly rate of interest. Here you want to know what will be the EMI for different interest rate. Now the person wants to know if the car price and interest are change then what should be the EMI.

1. 1st create a two variable data set with changing car amount in column and interest rate in row.

2. Select the whole range -> Go to data in Ribbon -> Find what-if analysis -> Click on that Select data table from menu

Before follow all the steps you have to refer the cell of EMI at the top left side corner.

3. Data Table dialog box appears.

  • Click the icon in the Row input cell box. The Row input cell is that cell whose value is present in Row. Here we have interest in row so we have to select interest.
  • Click the icon in the column input cell box. The column input cell is that cell whose value is present in column. Here we have CAR AMOUNT in row so we have to select CAR AMOUNT.

4. Click ok.

The Data Table is filled with the calculated results for each of the input values as shown below –

Once the data table is in place, you cannot change any of the referencing cells. Excel protects those cells. In addition, the data table formulas are a series of =TABLE() functions – this function is just for show. You cannot use it in any other context.

SOLVER

Solver is a Microsoft Excel add-in program you can use for what-if analysis. Use Solver to find an optimal (maximum or minimum) value for a formula in one cell — called the objective cell — subject to constraints, or limits, on the values of other formula cells on a worksheet. Solver works with a group of cells, called decision variables or simply variable cells that are used in computing the formulas in the objective and constraint cells. Solver adjusts the values in the decision variable cells to satisfy the limits on constraint cells and produce the result you want for the objective cell.

Challenge: How much I have to score in Internal exam, Mid term and Final exam to score first score. – 60%

Conditions:-

1. The max marks for internal exam is 25.

2. The max marks for midterm is 50.

3. The max marks for final exam is 100.

To perform solver, firstly we need to get the solver add in, that we can get by the steps given below.

Steps to get Solver add-in:

1. Choose File -> Excel Options

2. In the Excel Options dialog box, click the Add-Ins tab.

3. At the bottom of the dialog box, select Excel Add-Ins from the Manage drop-down list and click Go. Excel displays its Add-Ins dialog box.

4. In the Add-Ins dialog box, place a check mark next to Solver Add-In and click “OK”.

Steps to perform solver for above criteria:

1. Data -> Analysis -> Solver -> Solver parameters pop-up window.

2. (a) Set target cell: your target is to get 70% i.e. C7.
(b) Equal to: we have to get 60%, write 0.6.
(c) By changing cells: specify the range of cells which needs to change to get target value. (B3:B5).
(d) To add a constraint click on add it will generate one add constraint dialogue box which contains
cell reference, an operator, constraint .Then specify your conditions.

(e) *To set the first constraint: – cell reference: B2; operator : <=; constraint: 25. Likewise add all the conditions then click on “OK”.

3. Click on Solve.

4. Then solver results pop-up window will display.

(a) Keep solver solution: If you check this option solver will create report like answer report sheet.

(b)Restore original values: If you check this option it will replace the values in the same sheet.

5. Click “OK”

 

REFERENCES:

  1. http://people.revoledu.com/kardi/tutorial/What-If-Analysis/
  2. http://www.excel-easy.com/data-analysis/

 

About Kiran Kumar Sahoo:

Kiran is a B.Tech (Mechanical Engineering). Currently he is working as Analyst Intern with NikhilGuru Consulting Analytics Service LLP (Nikhil Analytics), Bangalore.

Please follow and like us:
error

Be the first to comment on "Excel: WHAT-IF ANALYSIS – II"

Leave a comment

Your email address will not be published.


*


error

Subscribe for Data Analytics Edge Newsletter & Share..:-)

error: Content is protected !!