Microsoft Office Tutorials and References
In Depth Information
In the Lab
The Future Value function (FV) in Step 4d returns to the cell the future value of the investment.
The future value of an investment is its value at some point in the future based on a series of
payments of equal amounts made over a number of periods earning a constant rate of return.
5. Add borders to the range B3:C13 as shown in Figure 4–69.
6. Use the concepts and techniques developed in this project to add the data table in Figure 4–69 to
the range E3:G14 as follows.
a. Enter and format the table column titles in row 3.
b. Use the fi ll handle to create the series of years beginning with 5 and ending with 50 in
increments of 5 in column E, beginning in cell E5.
c. In cell F4, enter =C13 as the formula. In cell G4, enter =12 * C10 * C8 as the formula
(using cell references in the formulas means Excel will copy the formats).
d. Use the Data Table command on the What–If Analysis gallery on the Data tab on the Ribbon
to defi ne the range E4:G14 as a one–input data table. Use cell C8 as the column input cell.
e. Format the numbers in the range F5:G14 to the Comma style format. Underline rows 3 and 4
as shown in Figure 4–69. Add borders to the range E3:G14 as shown in Figure 4–69.
7. Use the Conditional Formatting button on the Home tab on the Ribbon to add a red pointer that
shows the row that equates the years in cell C8 to the Years column in the data table. Use a white
font color for the pointer. Add the background color Light Yellow, Background 2 (column 3, row 1
in the Theme colors area on the Fill Color palette) as shown in Figure 4–69.
8. Change the document properties as specifi ed by your instructor. Change the worksheet header
with your name, course number, and other information requested by your instructor.
9. Spell check and formula check the worksheet. Use Range Finder (double–click cell) to check all
formulas.
10. Print the worksheet.
11. Print the formulas version of the worksheet.
12. Unlock the cells in the range C3:C8. Protect the worksheet. Allow users to select only unlocked cells.
13. Save the workbook using the fi le name Lab 4–1 Retirement Planning Sheet.
14. Hide and then unhide the Retirement Planning Sheet sheet. Hide and then unhide the Workbook.
Unprotect the worksheet and then hide columns D through G. Print the worksheet. Select
columns C and H and unhide the hidden columns. Hide rows 1 and 2. Print the worksheet. Click
the Select All button and unhide rows 1 and 2.
15. Close the workbook without saving
changes. Open the workbook Lab 4–1
Retirement Planning Sheet. Determine
the future value for the data in Table
4–7. Print the worksheet for each data
set. The following Future Value results
should display in cell C13: Data Set 1 =
$165,108.38; Data Set 2 = $549,735.86;
and Data Set 3 = $1,241,885.59. Quit
Excel without saving the workbook.
16. Submit the assignment as requested by
your instructor.
Table 4–7 Future Value Data
Data Set 1
Data Set 2
Data Set 3
Employee Name
Paula Rios
Sam Vinci
Gupta Ghandi
Annual Salary
$101,000.00
$78,000.00
$41,000.00
Percent Invested
2.00%
4.5%
6%
Company Match
2.00%
3%
3%
Annual Return
6.50%
7.00%
8.5%
Years
20
30
40
 
Search JabSto ::




Custom Search