[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.
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]
|
|
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.
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.
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.
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
|
|