Overview
This lecture explains how to use Excel data tables as a what-if analysis tool to compare scenarios and calculate outcomes without advanced formula knowledge.
Introduction to Excel Data Tables
- Data tables are a what-if analysis feature in Excel used to compare different scenarios quickly.
- Useful for analyzing impacts of variables like saving amounts or interest rates on outcomes such as future value or break-even points.
Creating a One-Variable Data Table
- Use the FV (Future Value) function to calculate future savings with set deposits and interest rates.
- Fill a column with values (e.g., saving amounts increasing by $10) using the fill series tool.
- Select the entire table (including headers) and choose Data > What If Analysis > Data Table.
- For column-oriented tables, set the "Column input cell" to the variable's referenced cell (e.g., savings amount cell C5).
- Changing related inputs (e.g., the annual rate) will automatically recalculate the data table.
Creating a Two-Variable Data Table
- Place the formula in the top-left cell of the table.
- Fill row and column labels with the two variables (e.g., savings amount and interest rate).
- Set the "Row input cell" to the first variable's referenced cell (e.g., interest rate) and "Column input cell" to the second (e.g., amount).
- Table updates automatically when input values change.
Using Data Tables for Break-Even Analysis
- Set up fixed costs and profit per unit to calculate break-even points.
- List varying values for both profit per pizza and pizzas sold, then create a two-variable data table.
- Apply conditional formatting (e.g., color scales) for easy visualization of break-even outcomes.
Data Table Tips and Performance
- The table formula must reference a cell containing the relevant calculation, not necessarily contain the formula itself.
- For large tables, set calculation mode to "Automatic except for data tables" for better performance.
- Update data tables manually by selecting the table and pressing F9.
Key Terms & Definitions
- Data Table — An Excel tool for quickly comparing multiple input scenarios and their calculated outcomes.
- What-If Analysis — A process for exploring different scenarios by changing variable values in calculations.
- FV Function — Calculates the future value of an investment based on constant payments and a constant interest rate.
- Break-Even Point — The number of units sold or revenue needed to cover total fixed and variable costs.
Action Items / Next Steps
- Download the provided Excel file to practice building and modifying data tables.
- Experiment with one- and two-variable data tables using your own figures.
- Apply conditional formatting to visualize results in your tables.