# Objective 7. Create a Data Table

[Page 821 ( continued )]

### Objective 7. Create a Data Table

A data table is a range of cells that shows how changing certain values in your formulas affects the results of those formulas. Data tables make it easy to calculate multiple versions in one operation, and then to view and compare the results of all the different variations. For example, banks may offer loans at different rates for different periods of time, which require different payments. Using a data table, you can calculate the possible values for each argument.

[Page 822]

A one-variable data table changes the value in only one cell . For example, use a one-variable data table if you want to see how different interest rates affect a monthly payment. A two-variable data table changes the values in two cellsfor example if you want to see how both different interest rates and different payment periods will affect a monthly payment.

#### Activity 3.15. Designing a Two-Variable Data Table

Recall that the PMT function has three arguments: Present value (Pv), Rate, and Number of periods (Nper). Because Darron would still like to borrow \$180,000 and purchase eight trucks , in this data table the present value will not change. The two values that will change are the Rate and Number of periods. Possible periods will range from 36 months (3 years) to 60 months (5 years ) and the Rate will vary from 8% to 6%.

1.

Double-click the Sheet2 tab , rename it Payment Table and then press . Right-click the Sheet3 tab , and then from the shortcut menu, click Delete .

2.

With the Payment Table as the active sheet, widen column A to 170 pixels . Widen column B to 70 pixels . Select columns C:I and widen them to 72 pixels .

3.

In the range A2:B4 , enter the following titles and data. Recall that you format numbers as you type by typing them with their symbols as shown:

 Amount of Loan \$180,000 Period (months) 36 Interest rate (per year) 7.00%

4.

Click cell C8 . Type 24 and then press . Type 30 and then press . Select the range C8:D8 . Point to the fill handle and drag to the right through cell I8 to fill in a pattern of months from 24 to 60 in increments of six months.

5.

In cell B9 , type 8.000% and then press . Type 7.875% and then press .

The display of both values is rounded to two decimal places.

6.

Select the range B9:B10 . Point to the fill handle and drag down through cell B25 to fill in a pattern of interest rates in increments of .125 from 8.00% down to 6.00%. With the range B9:B25 still selected, on the Formatting toolbar click the Increase Decimal button and then compare your screen with Figure 3.34.

##### (This item is displayed on page 823 in the print version)

Row 8 represents the number of monthly payments, and column B represents a range of possible annual interest rates. These two arguments will be used to calculate varying payment arrangements for a loan of \$180,000.

[Page 823]

7.

Click cell A1 . Type Truck Loan Options - Rates versus Months and then press . Merge and center this title across the range A1:I1 . Change the font to Arial Black and the font size to 14 .

8.

Click cell C6 . Type Payment Options and then press . Merge and center this title across C6:I6 . Change the font to Arial Black and the font size to 12 .

9.

Click cell C7 . Type Months and then press . Merge and center this title across C7:I7 . Change the font to Arial Black and confirm that the font size is 10 .

10.

Click cell A9 , type Rates and then press . Select the range A9:A25 . Display the Format Cells dialog box, click the Alignment tab , and under Text control , click Merge cells . Under Text alignment , click the Horizontal arrow , and then click Right (Indent) . Under Text alignment , click the Vertical arrow , and then click Center . Compare your dialog box with Figure 3.35.

[Page 824]

##### Figure 3.35.

11.

In the Format Cells dialog box, click OK . Change the font to Arial Black and the font size to 12 . Click Save .

#### Activity 3.16. Using a Data Table to Calculate Options

In this activity, you will create a table of payments for every combination of payment periods, which are represented by the column titles under Months , and the interest rates, which are represented by the row titles to the right of Rates . From the resulting table, Darron can find a combination of payment periods and interest rates that will enable him to go forward with his plan to borrow \$180,000 and purchase eight trucks.

1.

Click cell B8 , type = and notice that the Name Box displays PMT , the last function that you used. Click the Name Box to open the Function Arguments dialog box for the PMT function.

The PMT function is entered in the upper left corner of the table, so that when the data table is completed, the months in row 8 and rates in column B will be substituted into the formula to fill the table with the range of months and interest rate options that are displayed.

2.

In the Rate box, type b4/12 to divide the interest rate per year by 12 to convert it to a monthly interest rate.

3.

Press to move the insertion point to the Nper box. Type b3 and then press .

The periods are already stated in months and need not be changed.

[Page 825]

4.

In the Pv box, type -b2 and then click OK .

The payment \$5,557.88 is calculated for the values in cells B2, B3, and B4. This is the same payment that you calculated on the first worksheet. Now it displays as a positive number because the loan amount in cell B2 was entered as a negative number.

5.

Select the range B8:I25 . From the Data menu, click Table . In the Table dialog box, in the Row input cell box, type b3 and then press . In the Column input cell box, type b4 and then compare your screen with Figure 3.36.

##### Figure 3.36.

The months in row 8 will be substituted for the value in cell B3, and the interest rates in column B will be substituted for the value in cell B4.

6.

In the Table dialog box, click OK . Click cell F13 and examine the formula in the Formula Bar . Compare your screen with Figure 3.37.

##### (This item is displayed on page 826 in the print version)

The table is filled with payment options that use the month and interest rate corresponding to the position in the table. Thus, if Darron chooses a combination of 42 months at an interest rate of 7.500%, the monthly payment will be \$4,886.10.

The data table is one of a group of Excel's what-if analysis tools.

[Page 826]

7.

Click cell B8 . On the Standard toolbar, click the Format Painter button , and then select the range C9:I25 to apply the same format. Select the range C8:I8 , click Bold , and then click Center . Click anywhere to deselect the range, and then compare your worksheet to Figure 3.38.

##### (This item is displayed on page 827 in the print version)

By using a table of options, you can see that Darron must get a loan for at least 48 months (4 years) for any of the interest rates in order to buy all eight trucks and keep the monthly payment within his budget of \$4,500.

[Page 827]

8.

Display the Page Setup dialog box. Set the Orientation to Landscape , center the worksheet Horizontally , and then create a Custom Footer with the file name in the left section.

9.

Save your workbook. Check your Chapter Assignment Sheet or Course Syllabus , or consult your instructor, to determine if you are to submit your assignments on paper or electronically using your college's course information management system. To submit electronically , go to Step 11, and then follow the instructions provided by your instructor.

10.

Be sure you have saved your workbook. Right-click either sheet tab and click Select All Sheets . To print the worksheets, on the Standard toolbar click the Print Preview button, and press or as necessary to confirm that each worksheet is centered horizontally on its page and that the file name displays in the left section of the footer. On the Print Preview toolbar, click Print , and then in the Print dialog box, click OK . If you have been instructed to print the formulas, refer to Chapter One on how to do so.

11.

Close your workbook and close Excel. If you printed formulas, do not save the changes that were made while printing formulas.

 End You have completed Project 3C