12 Data Tables
A data table is a tool for “what-if” analyses. It allows you to quickly calculate results for formulas with one or two variables, using different values for those variables. This helps you track how small changes in input values affect the results of formulas that depend on those inputs. For example, you can analyze how changes in an item’s price affect the company’s sales. This type of analysis is often called sensitivity analysis. The only limitation is that you can use a maximum of two variables in the formulas.
Excel offers two types of data tables:
- One-variable data table: Uses one input variable and one or more formulas.
- Two-variable data table: Uses two input variables and one formula.
Both types of data tables operate in a similar way:
- Define a set of input values for the variables.
- Identify the formulas that depend on these variables.
- Execute the Data Table command.
Excel then substitutes each input value into the formulas, calculates the results, and displays them in a table format.
12.1 One-Variable Data Table
If you want to calculate the result of a formula with one variable for various values of that variable, you can do so by creating two columns: one for the variable’s values and the other for the formula’s results.
Figure 12.1 illustrates this for the formula
However, in such cases, it’s much more convenient to create a data table with one input cell.
In a column-oriented table, your variable values are arranged in a column, and the formula is placed one row above and one cell to the right of the column of values. You can enter additional formulas in the cells to the right of the first formula.
In a row-oriented data table, the variable values are in a row, and the formula(s) are in the cell one column to the left of the first value and one cell below the row of values. You can enter additional formulas in the cells below the first formula.
The input cell can be any cell on the worksheet. Excel uses this cell as a temporary storage location. The formulas must reference this input cell. The values for the variable are sent to this input cell, the result is calculated, and then it’s placed in the table.
Instead of formulas, you can also use references to formulas.
12.2 Rental Cottage
Here’s a simple example of a data table with one input cell.
A summer cottage can be rented for $450 a week. The weekly expenses are $175. Use a data table to calculate the revenue for 5, 10, 15, …, 50 weeks.
The data table will be in the range A7:B17. The formula referencing the input cell B5 will be in B7. The values for the variable will be in the range A8:A17, and the results will go into B8:B17.
Task 12.1 File: Rental_fixed.xlsx
Open the file.
In cell B7, enter the formula
=B5*(B3-B4).Select the range for the data table, A7:B17.
Choose Data tab > What-If Analysis (Forecast group) > Data Table.
- Click inside the Column input cell box, and then click on cell B5.
Excel will change the cell address to $B$5.
- Click OK.
TThe data table will be populated with the calculated values.
The formula in the result cells is an array formula: {=TABLE(,B5)}. You can verify this.
12.3 Calculating Multiple Formula Values
Here’s an example using a data table with one input cell to calculate values for multiple formulas.
The task is to calculate the y-values of four functions for different x-values.
The input cell will be B1. This is where the different x-values will be entered. The formulas used are shown in cells C2:F2, and the formulas themselves are in cells C3:F3. The x-values are in cells B4:B9.
Task 12.2 File: Function_values.xlsx
Open the file.
Enter the correct formulas in cells C3:F3:
- C3:
=2*B1-1 - D3:
=-3*B1+2 - E3:
=B1^2 - F3:
=B1^2-2*B1+1
- C3:
Select the range for the data table, B3:F9.
Choose tab Data > What-If Analysis (Forecast group) > Data Table.
Click inside the Column input cell box, and then click on cell B1.
Click OK.
The formula in the result cells is an array formula: {=TABLE(,B1)}. You can verify this.
12.4 Two-Variable Data Table
The two-variable data table uses a formula that contains two sets of input values. The formula must refer to two different input cells: the row input cell and the column input cell.
The overall layout resembles a data table with one input cell, but there are some important differences:
- A data table with one input cell can evaluate multiple formulas, while a table with two input cells can only calculate the results from one formula.
- The values for the variables are arranged in both a column and a row.
- The upper-left cell must contain the formula (or a reference to the formula).
12.5 Rental Cottage with Variable Price
Here’s an example of a data table with two input cells.
A summer cottage can be rented for $350-$600 per week, with the price varying by season. The weekly expenses are fixed at $175. Use a data table to calculate the revenue for 5, 10, 15, …, 50 weeks and rental prices of 350, 400, 450, …, 600.
The data table will be in the range A7:G17. The two input cells are B3 (rental price) and B5 (number of rental weeks). Cell A7 contains a formula that references the two input cells. The values for input cell B3 are in row B7:G7. The values for input cell B5 are in column A8:A17.
Task 12.3 File: Rental_variable.xlsx
Open the file.
In cell A7, enter the formula
=B5*(B3-B4).Select the range for the data table, A7:G17.
Choose Data tab > What-If Analysis (group Forecast) > Data Table.
Click inside the Row input cell box, and then click on cell B3.
Click inside the Column input cell box, and then click on cell B5.
Click OK.
The formula in the result cells is an array formula: {=TABLE(B3,B5)}. You can verify this.
12.6 Revenues from an Advertising Campaign
Here’s an example of a two-variable data table used to calculate the effect of an advertising campaign.
A company wants to run an advertising campaign by sending direct mail flyers to potential customers. We’ll use a calculation model to calculate the expected profit of this campaign.
The calculation model uses two variables: the number of flyers sent and the response percentage. The printing and shipping costs, as well as the expected revenue per response, are fixed values. The number of responses, total revenue, costs, and profit are calculated using formulas.
The range for the data table is B14:I23. The two input cells are B1 and B2. The values for input cell B1 are in column B15:B23. The values for input cell B2 are in row C14:I14. Cell B14 contains a reference to the total profit formula, which in turn references the two input cells.
File: Advertising_Campaign.xlsx
Open the file.
Select the range for the data table, B14:I23.
Choose Data tab> What-If Analysis (Forecast group) > Data Table.
Click inside the Row input cell box, and then click on cell B2.
Click inside the Column input cell box, and then click on cell B1.
Click OK.
The formula in the result cells is an array formula: {=TABLE(B2,B1)}. You can verify this.
12.7 Exercises
Exercise 12.1 Loan Repayment (tabl001)
A loan of $10,000 must be repaid over a certain number of months at an annual interest rate of 5%. The monthly repayment amount is calculated using the PMT function. The figure below shows an example for a repayment period of 36 months.
Create the model shown above in a new worksheet, and create a formula for the monthly repayment.
Create a data table that shows the monthly repayments for repayment periods of 12, 18, 24, 30, …, 72 months.
Create a new data table that shows the monthly repayments for repayment periods of 12, 18, 24, 30, …, 72 months, and annual interest rates of 3%, 3.5%, 4%, 4.5%, …, 6%.
Exercise 12.2 Sales Forecast (tabl002)
A worksheet contains a forecast of sales in 2014, based on sales in 2013 and an estimated sales growth rate.
Create the model shown above in a new worksheet, and create a formula for the sales forecast for 2014.
Create a data table that shows the sales forecast for growth rates of 1%, 2%, …, 10%.
