Creating and Analyzing Data Tables for Scenario Analysis in Excel

Data Tables are a powerful feature in Excel used for performing What-If Analysis. They allow you to quickly see the impact of changing one or two variables on the output of a formula.

---

Types of Data Tables:

1. One-Variable Data Table
   Analyzes the impact of changing one variable.
2. Two-Variable Data Table
   Analyzes the impact of changing two variables.

---

Example 1: One-Variable Data Table

Scenario:

You want to see how changing the Interest Rate affects the Monthly Payment for a loan.

Dataset:

Loan Amount	100,000
Loan Term (Years)	10
Interest Rate	Variable
Formula	=PMT(B3/12, B2*12, -B1)

Steps:

1. Enter the Loan Amount (B1), Loan Term (Years) (B2), and Interest Rate (B3) in cells.
2. Write the formula =PMT(B3/12, B2*12, -B1) in another cell to calculate the monthly payment.
3. Create a list of possible Interest Rates (e.g., 4%, 5%, 6%) in a column.
4. Select the range containing the formula and interest rates.
5. Go to Data → What-If Analysis → Data Table.
6. In the dialog:
   • Leave Row Input Cell blank.
   • Set the Column Input Cell to B3 (Interest Rate).
7. Click OK.

Result: A table showing how the Monthly Payment changes with different interest rates.

---

Example 2: Two-Variable Data Table

Scenario:

You want to see how changing both the Interest Rate and Loan Term affects the Monthly Payment.

Dataset:

Loan Amount	100,000
Formula	=PMT(B3/12, B2*12, -B1)

Steps:

1. In a blank area, create a grid:
   • Column: Interest Rates (e.g., 4%, 5%, 6%).
   • Row: Loan Terms (e.g., 5 years, 10 years, 15 years).
2. Enter the formula =PMT(B3/12, B2*12, -B1) at the top-left of the grid.
3. Select the grid including the formula, interest rates, and loan terms.
4. Go to Data → What-If Analysis → Data Table.
5. In the dialog:
   • Set the Row Input Cell to B2 (Loan Term).
   • Set the Column Input Cell to B3 (Interest Rate).
6. Click OK.

Result: A table showing how Monthly Payments vary with different combinations of Interest Rates and Loan Terms.

---

Why Use Data Tables?

• Quickly analyze multiple scenarios.
• Identify trends or optimal conditions.
• Combine with charts for a visual representation of results.