Your training and consulting specialists!
|
|
|
|
Excel ’97 Objective:
Overview: A data table in Excel is an effective analytical tool used to determine what effect a single variable may have on the results of a formula holding all other inputs constant. A variable is a value that changes. A constant is a value that does not change. For example, perhaps you are purchasing a new car. The cost of the car is $20,000. The interest rate is 7%. You would like to know what effect the length of the loan will have on your monthly payments. The payment will change based on a 24, 36, 48 or 60 month loan. A data table will show you these results.
A Data Table is an arrangement of columns and rows. The first row in the table always contains the formula or a reference to a formula (the gray area in figure 1: Data Table.) This must be adjacent to the input column (the yellow shaded area) and one cell above the output column (the light blue shaded area.). The first column in the table holds the variable data that will be substituted into the formula once we use the Data Table command. These cells must always contain values—never text. The input cell is critical when creating a data table (the green shaded area in figure 2.). It is the cell containing the original variable and represents the cell for future inputs from the input column. The input cell must be referenced by your formula cell.
Once all the necessary parameters are complete you must select the table area and apply the Data Table command. The selection area includes the Input column. Output column and the Formula cell. The figure below illustrates the correct selection of cells.
To apply the Data Table command select Data from the menu bar and then select the Table… command. Since our inputs are stored in columns, you must specify that the column input cell is D5. When you click on OK, the Data Table command will calculate the resulting payment adjacent to each variable input in the column. The figures below show how to specify the column input cell and the completed table.
Figure 5: Completed Data Table Let’s Review the Steps
Student Exercise 8Create a Data Table to calculate what your payments will be if the interest rate is changing. The loan amount is $25,700, the term of the loan is 36 months. The original interest rate is 7% but is expected to rise as much as 4% over the next 3 years. You would like to see what the effect 0.5% increases in the interest rate would have on payments. |
|
Send mail to reigotech@reigo.com with questions or comments about
this web site.
|
