What If Analysis Tools

 What-If Analysis

• What-If Analysis is a process used in decision-making and problem-solving.
   
• It involves changing the values in a set of assumptions or inputs to observe how those changes affect the outcomes or results.
   
• It is often used in business, finance, economics, and various scientific fields to predict the impact of different scenarios and make informed decisions.

Significance in an organization

The organization always have a target or an objective set to achieve it. However to ensure that organization is able to achieve its set target, requires information on which decision can be made. The What-IF feature of Excel or any other spreadsheet software is used to make reverse calculations and compare different likely scenarios. It can be done by changing values in the key cells to see how those changes will affect the outcome. The three tools that form the What-If analysis are Scenario, Data Table and Goal Seek.

What-If Analysis is a valuable tool that allows one to explore different possibilities and their consequences. It helps in making better-informed decisions by understanding the potential risks and benefits of various options.

What-If Analysis Tools:

1. Scenario Manager

Scenarios are a tool to test “what-if” questions. Each scenario is named, and can be edited and formatted separately. When the spreadsheet is printed, only the content of the currently active scenario is printed.

Scenario Manager in Excel is a powerful tool that allows you to create and analyze different scenarios by changing the values of input variables to see how those changes affect the output. This is particularly useful for performing "What-If" analysis, where you want to explore different outcomes based on various assumptions. For example, in a budgeting scenario, variables could include sales revenue, expenses, production costs, advertising costs, etc.

Using Scenario Manager in Excel

Step 1: Set Up Your Worksheet

First, create a spreadsheet with the necessary input variables and the formula that calculates the desired outcome. Example: Suppose you have a simple profit calculation formula:

Profit = Revenue − Cost  

Where:   Revenue depends on the selling price and the quantity sold.

               Costs depend on fixed costs and variable costs.

Heads	Value	Description
Selling Price (per unit)	50	Price at which each unit is sold
Quantity Sold	1000	Number of units sold
Variable Cost (per unit)	30	Cost associated with producing each unit
Fixed Costs	10000	Costs that do not vary with output
Total Revenue	50000	Calculation: Selling Price × Quantity Sold
Total Variable Cost	30000	Calculation: Quantity Sold × Variable Cost
Total Cost	40000	Calculation: Total Variable Cost + Fixed Costs
Profit	10000	Calculation: Total Revenue - Total Cost

Step 2: Access Scenario Manager

Go to the Data tab on the Excel ribbon.

In the Forecast group, click on What-If Analysis and then select Scenario Manager from the drop-down menu.

Step 3: Create a Scenario

1.      In the Scenario Manager dialog box, click Add to create a new scenario.

2.      Name your scenario (e.g., "Best Case", "Worst Case", "Most Likely").

       Scenario Name: "Best Case"

       Changing Cells: A1 (Selling Price), A2 (Quantity Sold), A3 (Variable Cost)

       Values for Best Case:

       Selling Price = 60

       Quantity Sold = 1500

       Variable Cost = 25

3.     Specify the changing cells (the cells where you will change the values for each scenario). For
       example, cells containing selling price, quantity, or variable costs.

4.      Enter the new values for the changing cells in this scenario.

5.      Click OK to save the scenario.

Step 4: Add More Scenarios

Repeat the process to add more scenarios, each with different sets of values.

Scenario Name: "Worst Case"

Values for Worst Case:

Selling Price = 40

Quantity Sold = 800

Variable Cost = 35

Scenario Name: "Most Likely Case"

Values for Most Likely Case:

Selling Price = 50

Quantity Sold = 1000

Variable Cost = 30

Step 5: View and Compare Scenarios

To view a scenario, select it from the list in Scenario Manager and click Show. Excel will update the spreadsheet with the values from that scenario.

To compare scenarios, click on Summary in the Scenario Manager. Excel will create a new worksheet with a side-by-side comparison of all the scenarios.

Scenario	Selling Price	Quantity Sold	Variable Cost	Total Revenue	Total Cost	Profit
Best Case	60	1500	25	90000	47500	42500
Worst Case	40	800	35	32000	38000	-6000
Most Likely	50	1000	30	50000	40000	10000

This example demonstrates how one can use Scenario Manager to analyze the impact of different pricing and sales scenarios on  profit, helping one make more informed business decisions.

Try Yourself

Scenario: Analyzing Monthly Savings

Step 1: Set Up Your Worksheet

Create an Excel worksheet with the following structure:

Heads	Value	Description
Salary	50000	Monthly salary
Additional Income	10000	Income from other sources (e.g., freelance work)
Rent	15000	Monthly rent expense
Groceries	8000	Monthly groceries expense
Utilities	4000	Monthly utilities (electricity, water, etc.)
Transportation	5000	Monthly transportation expense
Miscellaneous Expenses	3000	Other monthly expenses
Savings Goal	5000	Desired monthly savings
Total Income	=B2+B3	Total monthly income (Salary + Additional Income)
Total Expenses	=SUM(B4:B8)	Total monthly expenses (Rent + Groceries +Utilities + Transportation + Misc. Expenses)
Actual Savings	=B10-B11	Difference between total income and total expenses
Savings vs Goal	=B12-B9	Difference between actual savings and the savings goal

Add the formulas to calculate total income, total expenses, and actual savings as shown above.

Now use this worksheet to see how changes in income and expenses affect your ability to meet your savings goal.

Step 2: Use Scenario Manager to create following scenarios.

Scenario Name: "Optimistic"

Changing Cells: A1 (Salary), A2 (Additional Income), A3 (Rent), A4 (Groceries), A5 (Utilities), A6 (Transportation), A7 (Miscellaneous Expenses)

Values for Optimistic Scenario:

Salary = 60000

Additional Income = 15000

Rent = 14000

Groceries = 7000

Utilities = 3000

Transportation = 4000

Miscellaneous Expenses = 2000

Scenario Name: "Pessimistic"

Values for Pessimistic Scenario:

Salary = 45000

Additional Income = 5000

Rent = 16000

Groceries = 9000

Utilities = 5000

Transportation = 6000

Miscellaneous Expenses = 4000

Scenario Name: "Most Likely"

Values for Most Likely Scenario:

Salary = 50000

Additional Income = 10000

Rent = 15000

Groceries = 8000

Utilities = 4000

Transportation = 5000

Miscellaneous Expenses = 3000

Scenario Manager in Excel can be used to evaluate how different income and expense levels impact monthly savings. By comparing multiple scenarios, you can identify the best strategies to meet financial goals and prepare for various financial situations.

2. Goal Seek

Goal Seek is a powerful tool in Excel that allows one to find the input value required to achieve a specific goal in a formula. 

It's a type of What-If Analysis that is particularly useful when one knows the result one wants, but one is unsure of the input needed to achieve that result.

Goal Seek is an essential tool in Excel for performing backward calculations. 

It helps one find the necessary input value to achieve a specific outcome, making it ideal for financial analysis, budgeting, sales forecasting, and more. 

By mastering Goal Seek, one can quickly and efficiently solve a wide range of practical problems in Excel.

Using Goal Seek in Excel

Step 1: Set Up Your Worksheet

Before using Goal Seek, ensure worksheet contains a formula that calculates an outcome based on one or more input values.

Heads	Value	Formula
Quantity Sold	200
Fixed Costs	$2,000
Variable Cost per Unit	$15
Selling Price per Unit	$0	(This will be the value Goal Seek adjusts)
Total Revenue	=B2*B5	(Quantity Sold * Selling Price)
Total Costs	=B3+(B4*B2)	(Fixed Costs + (Variable Cost per Unit * Quantity Sold))
Profit	=B6-B7	(Total Revenue - Total Costs)

Step 2: Open Goal Seek

1.      Go to the Data Tab: Click on the Data tab in the Excel ribbon.

2.    What-If Analysis: Click on What-If Analysis and then select Goal Seek from the drop-down menu.

Step 3: Set Up the Goal Seek Dialog Box

In the Goal Seek dialog box, you need to specify three things:

1.      Set Cell: The cell containing the formula whose value you want to set to a specific goal. In this case, select cell B8 (Profit).

2.      To Value: The target value you want to achieve. Enter 5000 here, since you want a profit of $5000.

3.     By Changing Cell: Select cell B5 (Selling Price per Unit).

4.      After entering these values, click OK.

5.      Step 4: Interpret the Results

6.      Excel will adjust the value in the "Selling Price" cell (B5) until the profit reaches exactly $5000. The result will appear directly in your worksheet.

Uses of Goal Seek

Budgeting: Determine the expense limit to stay within a certain budget.

Loan Calculations: Find the interest rate or loan amount needed to achieve a specific monthly payment.

Sales Targets: Determine the number of units needed to be sold to achieve a desired profit.

Investment Planning: Find the return rate needed to reach a financial goal over time.

3. Data Table

A Data Table in Excel is a tool used in What-If Analysis to analyze how changes in one or two input variables affect the output of a formula. It allows you to see multiple results for different scenarios at once, making it an efficient way to perform sensitivity analysis.

A data table is a range of cells in which one can change values in some of the cells and come up with different answers to a problem. 

A data table does not allow changing more than two inputs of a formula. However, these two inputs can have as many possible values (to be experimented) as one wants.

It is a planning tool for What-If Analysis feature. 

It creates the formula table as a separate set of cells gives all alternative results for the formula used. There are two types of data tables in Excel: One-Variable Data Tables and Two-Variable Data Tables.

Steps to Create a Two-Variable Data Table:

1. Set Up the Formula:

Enter the formula in a cell where both row and column input variables can change. This cell must be in the top-left corner of the table range.

2. Enter Input Values:

In the first row of the table, list the values for one variable (e.g., interest rates).

In the first column of the table, list the values for the second variable (e.g., loan terms).

3. Select the Table Range:

Select the entire table range, including the formula and both sets of input values.

4. Open the Data Table Dialog:

Go to the Data tab → What-If Analysis → Data Table.

5. Enter Input Cells:

In the Row Input Cell, select the cell that the row input values depend on.

In the Column Input Cell, select the cell that the column input values depend on.

6. Press OK: Excel will calculate and display the results.

Steps to Create a Two-Variable Data Table:

1. Set Up the Formula:

Enter the formula in a cell where both row and column input variables can change. This cell must be in the top-left corner of the table range.

2. Enter Input Values:

In the first row of the table, list the values for one variable (e.g., interest rates).

In the first column of the table, list the values for the second variable (e.g., loan terms).

3. Select the Table Range:

Select the entire table range, including the formula and both sets of input values.

4. Open the Data Table Dialog:

Go to the Data tab → What-If Analysis → Data Table.

5. Enter Input Cells:

In the Row Input Cell, select the cell that the row input values depend on.

In the Column Input Cell, select the cell that the column input values depend on.

6. Press OK: Excel will calculate and display the results.

 

Advantages of Data Tables in Excel:

Quick Analysis: You can analyze a large number of scenarios at once, saving time.

Easy to Update: If you change the underlying formula or input cells, the data table will automatically update the results.

Visualization: You can combine data tables with charts to visualize how different inputs impact your results.

Try Yourself:

Exercise: Calculating Future Value of an Investment with Different Interest Rates and Periods

Problem:

You are planning to invest $10,000 into a savings account. You want to see how the future value of your investment changes:

a) With different interest rates. Use a One-Variable Data Table in Excel to analyze this.

b) With different interest rates and over different time periods. Use a Two-Variable Data Table in
     Excel to analyze this.

Investment Details:

Investment Amount: $10,000

Interest Rates: 3%, 4%, 5%, 6%, 7%

Time Periods (Years): 5 years, 10 years, 15 years, 20 years

Task:

Use Excel’s FV function to calculate the future value of the investment based on different interest rates and periods.

Hint: = FV (B2/12, B3*12, 0, -B1)

Explanation:

B3 is the annual interest rate divided by 12 to get the monthly rate.

B4 is the number of years, multiplied by 12 to get the number of months.

0 represents no monthly contributions.

B1 is the principal investment (negative because it's a cash outflow).

The desired Output: