Modelling: What-If Analysis and Goal Seek

This section explores techniques for performing what-if analysis and goal seek within a model. These tools are crucial for understanding how changes in input variables affect output variables and for determining the input values needed to achieve a desired outcome.

What-If Analysis

What-if analysis involves changing input values in a model to see the resulting impact on the output. This allows for exploring different scenarios and assessing the sensitivity of the model to various factors.

In spreadsheet software like Microsoft Excel or Google Sheets, what-if analysis can be performed using several tools:

• Scenario Manager: Allows you to save and apply different sets of input values to a model.
• Goal Seek: Used to determine the input value required to achieve a specific target output.
• Data Tables: Creates a table of output values for a range of input values.
• What-If Sparklines: Displays a small chart within a cell to show trends based on changes in input variables.

Goal Seek

Goal seek is a powerful tool for finding the input value that results in a desired output. It works by iteratively adjusting the input value until the target output is achieved.

Syntax (in Excel/Sheets): =GoalSeek(set_cell, changed_cell, target_value, [tolerance], [absolute_reference])

Arguments:

• set_cell: The cell containing the value that you want to change to achieve the goal.
• changed_cell: The cell containing the output that you want to target.
• target_value: The desired value for the output cell.
• [tolerance]: (Optional) The acceptable level of accuracy for the target value. Defaults to 0.001.
• [absolute_reference]: (Optional) A Boolean value (TRUE or FALSE) indicating whether to refer to the set_cell using absolute cell references. Defaults to FALSE.

Example: Goal Seek Demonstration

Consider a simple investment scenario where we want to determine the initial investment amount needed to achieve a specific future value.

Scenario: You want to invest a certain amount today and have it grow to a target value after a specified number of years with a given interest rate.

Model Setup (Example):

Cell	Description
A1	Initial Investment (to be changed)
A2	Interest Rate (e.g., 0.05 for 5%)
A3	Number of Years
A4	Future Value (Target Value)
A5	Formula (FV = PV * (1 + r)^n)

Goal Seek Application:

1. Select the cell containing the initial investment amount (e.g., A1).
2. Go to the Data tab and click on "What-If Analysis" -> "Goal Seek".
3. In the "Goal Seek" dialog box:
4. Set cell: A1
5. To value: Enter the target future value (e.g., 10000).
6. By changing cell: A2 (the interest rate) - although we are changing A1, the goal seek will adjust A1.
7. Click "OK".

The Goal Seek tool will then iteratively adjust the value in cell A1 until the future value in cell A4 reaches the target value. The resulting initial investment amount will be displayed in cell A1.

What-If Analysis using Data Tables

Data tables allow you to see how the output of a formula changes when you vary one or two input variables.

Syntax (in Excel/Sheets): =DataTable(array_reference, [row_input], [column_input])

Arguments:

• array_reference: The range containing the formula you want to analyze.
• [row_input]: (Optional) The cell containing the row input variable.
• [column_input]: (Optional) The cell containing the column input variable.

Example: Suppose you have a formula in cell A1 that calculates a total cost based on quantity in cell B1 and unit price in cell C1. You can use a data table to see how the total cost changes for different quantities.

Steps:

1. Select the range where you want the data table to appear.
2. Go to the Data tab and click on "What-If Analysis" -> "Data Table".
3. In the "Data Table" dialog box:
4. Row input cell: Select cell B1 (quantity).
5. Column input cell: Select cell C1 (unit price).
6. Click "OK".

The data table will then display a table of total costs for different combinations of quantity and unit price.

Conclusion

What-if analysis and goal seek are essential tools for model building and analysis. They enable users to explore different scenarios, understand model sensitivity, and determine optimal input values to achieve desired outcomes. Understanding how to use these tools effectively is a key skill for any IT professional.