Data Tables in What-If Analysis

Data Tables in What-If Analysis

Data Tables in Excel’s What-If Analysis feature help you explore the impact of changing one or two variables on your results. They are useful for testing scenarios, forecasting, and sensitivity analysis in a compact, visual format.

Types of Data Tables

1. One-Variable Data Table: Examines how changing one input affects a result.
2. Two-Variable Data Table: Examines how changing two inputs simultaneously affects a result.

How to Use a One-Variable Data Table

1. Set Up Your Data
   • Create a formula that depends on one input.
     Example:
   Profit = (Sales Price × Units Sold) - Fixed Costs Sales Price Profit 80 90 100 110 120 In this case, Profit depends on Sales Price.
2. Enter the Formula
   • Place the formula in a separate cell (e.g., C1).
3. Set Up the Table
   • List the input values (Sales Price) in a column below the header.
4. Apply the Data Table
   • Highlight the table range, including the formula cell (e.g., B1:C6).
   • Go to the Data tab, click What-If Analysis, and select Data Table.
   • In the Column Input Cell box, specify the cell that contains the input variable (Sales Price).
   • Click OK.
   Excel will calculate the Profit for each Sales Price value.

How to Use a Two-Variable Data Table

1. Set Up Your Data
   • Create a formula that depends on two inputs.
     Example:
   Profit = (Sales Price × Units Sold) - Fixed Costs
2. Here, Profit depends on both Sales Price and Units Sold.
3. Enter the Formula
   • Place the formula in a cell at the top-left corner of the table (e.g., B1).
4. Set Up the Table
   • List one input variable (Units Sold) vertically in the first column.
   • List the other input variable (Sales Price) horizontally in the first row.
5. Apply the Data Table
   • Highlight the table range, including the formula cell and input values (e.g., B1:F6).
   • Go to the Data tab, click What-If Analysis, and select Data Table.
   • In the Row Input Cell, specify the cell containing the horizontal input (Sales Price).
   • In the Column Input Cell, specify the cell containing the vertical input (Units Sold).
   • Click OK.
   Excel will calculate the Profit for every combination of Sales Price and Units Sold.

Tips for Using Data Tables

• Ensure the formula references the input variables properly.
• Use descriptive headers to make your table easy to understand.
• Combine Data Tables with Conditional Formatting to highlight trends or key results.

Advantages of Data Tables

• Quickly test multiple scenarios in a single view.
• Automatically updates when the original formula or inputs change.
• Saves time compared to manually calculating each scenario.

Data Tables are a powerful way to analyze the effects of variable changes on your results, making them ideal for decision-making and forecasting.