Microsoft Office Tutorials and References
In Depth Information
In the Lab
6. Delete column B by right-clicking the column heading and clicking Delete on the shortcut menu.
7. Insert a new column between columns C and D. Change the column widths as follows: A = 25.00;
D = 13.00; and E through K = 9.71. Enter the new column D title YTD Soc. Sec . in cell D7.
8. Insert two new columns between columns F and G. Enter the new column G title Soc. Sec. in
cell G7. Enter the new column H title Medicare in cell H7.
9. Enhance the worksheet title in cell A5 by using a 36-point light blue Arial Rounded MT Bold (or a
similar font) font style as shown in Figure 3–87b.
10. Assign the NOW function to cell B6 and format it to the 3/14/2001 style.
11. Delete employee James, Delmar (row 12). Change Raul Aquire’s (row 8) hours worked to 2.5.
Change Casimir Kwasny’s (row 9) number of dependents to 7 and rate per hour to $8.25. Change
Tepin Ruiz’s (row 11) hours worked to 49.5 and Fred Holkavich’s (row 12) hours worked to 57.
12. Freeze column A and rows 1 through 7 by selecting cell B8, clicking the Freeze Panes button on
the View tab on the Ribbon, and then clicking Freeze Panes on the Freeze Panes menu.
13. In column D, enter the
YTD Soc. Sec. values listed
in Table 3–11.
14. Insert two new rows
immediately above the
Totals row. Add the new
employee data as listed in
Table 3–11 The Britney Music Emporium’s YTD Social Security Values
YTD Soc. Sec.
Table 3–12 The Britney Music Emporium’s New Employee Data
Rate per Hour
YTD Soc. Sec.
15. Center the range B6:B14. Use the Currency category in the Format Cells dialog box to assign a
Comma style (no dollar signs) with two decimal places and negative numbers within parentheses to
the range C8:K15. Assign a Percent style and two decimal places to the range L8:L15. Draw a thick
bottom border in the ranges A7:L7 and A14:L14.
16. As shown in Figure 3–87b, enter and format the Social Security (7.65% with a maximum of
$7,458.75) and Medicare tax (1.45%) information in the range A1:B3. Use format symbols where
17. Change the formulas to determine the gross pay in column F and the federal tax in column I as
a. In cell F8, enter an IF function that applies the following logic and then copy it to the range
F9:F14. If Hours Worked <= 40, then Rate per Hour * Hours Worked, otherwise
Rate per Hour * Hours Worked + 0.5 * Rate per Hour * (Hours Worked
40) or =IF(E8 <= 40,
b. In cell I8, enter the IF function that applies the following logic and then copy it to the range
I9:I14. If (Gross Pay – Dependents * 22.09 > 0, then 20% * (Gross Pay – Dependents * 22.09),
otherwise 0 or =IF(F8 – B8 * 22.09 > 0, 20% * (F8 – B8 * 22.09), 0)
C8 * E8, C8 * E8 + 0.5 * C8 *(E8