Chapter 7: Analyze Alternative Data Sets with What-If Analysis

Spreadsheet formulas are very good at automatically updating results based on your input. For that reason, spreadsheets are one of the best tools for carrying out financial projections based on assumptions. Excel provides a whole raft of tools for just this purpose.

In this chapter, we will be covering:

• What-If Analysis for one-variable and two-variable data tables.
• The Scenario Manager which you can use to create and compare different scenarios for your data.
• The Goal Seek tool to set a goal and allow Excel to adjust other values to meet the goal.
• The Solver add-in tool which can be used to generate scenarios for more complex data.

In Excel, there are different types of what-if analysis you can carry out. In this chapter, we will cover four types that are commonly used in Excel.

• Data tables : This feature enables you to generate a series of projections based on one or two changing variables.
• Goal seeking : This feature enables you to set a predetermined goal and then choose the variables that will change to meet this goal.
• Scenarios : In this type of What-If Analysis, you create different scenarios using alternate figures which you can then compare side-by-side in a generated report.
• Solver : The solver is an Excel add-in that you can use to create more complex What-If Analysis, enabling you to use multiple variables and constraints.