Excel Cookbook

Chapter 14. What-If Analysis

What-if analysis involves trying different values to examine their effect. For example, you may want to determine how changing an interest rate affects loan repayments or find the most efficient way to schedule your workforce.

The recipes in this chapter cover four kinds of what-if analysis tools available in Excel: Data Tables, Scenarios, Goal Seek, and Solver. The areas covered include:

• Producing results tables showing the impact of one or two variables assuming different values

• Saving sets of values as scenarios and switching between them

• Finding the cell values needed to meet goals and return specific results

• Solving optimization problems, such as maximizing profit given constraints, workforce scheduling, task allocation, and route planning

14.1 Creating a One-Variable Data Table

Problem

You want to create a table showing how substituting different values in a single cell changes the results of one or more formulas.

Solution

Suppose you have a worksheet that calculates the amount you would need to pay each month if you took out a fixed-rate loan, where cell B1 contains the loan amount, B2 contains the loan term in months, B3 contains the annual rate, and cell B4 uses the PMT function (see Recipe 10.1) to calculate the monthly payment. You want to see how changing the loan term affects the monthly payment and total amount to pay.

You can solve this problem by creating a one-variable data table: a range of cells that show how substituting ...