Microsoft Office Tutorials and References
In Depth Information
In the Lab
In the Lab continued
Instructions: With a blank worksheet on the screen, perform the following tasks.
1. Change the font of the entire worksheet to bold and apply the Trek theme to the worksheet. Change
the column widths to the following: A and D = 0.50; B = 20.00; C, F, and G = 13.00. Change the row
heights to the following: 2 = 16.50; and 3 = 32.25. The height of row 1 will be adjusted automatically
when a cell style is applied to the worksheet title.
2. In cell B1, enter Retirement Planning Sheet as the worksheet title. Merge and center cell
B1 across columns B through G. Apply the Title cell style to cell B1, change the font size to 24 point,
and change the font color to Orange, Accent 6 (column 10, row 1 in the Theme colors area on the
Font Color palette). Draw a medium black border around cell B1.
3. Enter the row titles in column B,
beginning in cell B3 as shown in
Figure 4–69. Add the data in
Table 4–6 to column C. Use the
dollar and percent signs format
symbols to format the numbers in
the range C4:C7.
4. Use the Create from Selection button
on the Formulas tab on the Ribbon
to assign the row titles in column B
(range B3:B13) to the adjacent cells in
column C. Use these names to enter the following formulas in the range C10:C13. Step 4e formats
the displayed results of the formulas.
a. Employee Monthly Contribution (cell C10) = Annual_Salary * Percent_Invested / 12
b. Employer Monthly Contribution (cell C11) = IF(Percent_Invested < Company_Match,
Percent_Invested * Annual_Salary / 12, Company_Match * Annual_Salary / 12)
c. Total Monthly Contribution (cell C12) = SUM(C10:C11)
d. Future Value (cell C13) = –FV(Annual_Return/12, 12 * Years, Total)
e. If necessary, use the Format Painter button on the Home tab on the Ribbon to assign the
Currency style format in cell C4 to the range C10:C13.
Table 4–6 401(k) Planning Sheet Employee Data