Page 668 - Excel 2013 All-in-One For Dummies

650 Using Data Tables
Note that when you factor in the expenses of 10% of the annual sales, the projected sales at an annual growth rate of 1.75% falls in cell B5 from $890,312.50 to $802,812.50.
To determine how changing both the growth rate and the percentage of expenses to sales will affect the projected sales for 2014, you create a two-variable data table. In setting up this table, you still enter the variable growth rates down column B in the cell range B8:B17. Then, you enter the variable expense rates across row 7 in the range C7:F7. This time, you bring forward the master formula by entering the formula =B5 in cell B7, the cell at the intersection of the row and column containing the two input variables.
After setting up the two series of variables in this manner, you are ready to create the table by selecting the cell range B7:F17 and opening the Table dialog box, as shown in Figure 1-3. For a two-variable data table, you must designate both a row input and column cell in the worksheet. In this exam- ple, the row input cell is B4, which contains the original expense-to-sales percentage, and the column input cell remains B3, which contains the origi- nal growth rate. After these two input cells are entered in the Table dialog box, you are ready to generate the data table by clicking the OK button.
Figure 1-3:
Creating a two-variable data table.