Microsoft Office Tutorials and References

In Depth Information

**Session 2.2**

Figure 2-35

To complete this task:

1. Open the
Sonic1
workbook located in the Tutorial.02\Cases folder included with

your Data Files, and then save the workbook as
Sonic2
in the same folder.

2. In the Documentation sheet, enter your name and then enter the date using the

TODAY() function.

3. Switch to the Payroll worksheet. In cell C13, enter a formula to calculate the number

of years the first employee has worked at Sonic Sounds. Use an absolute reference for

cell B2. Divide the difference by 365. (
Hint
: You need to subtract the date the

employee was hired from the current date, which is in cell B2, and then divide the

difference by the number of days in a year. For the purposes of this exercise, do not

try to account for leap years.)

4. Use Auto Fill to calculate the number of years the remaining employees in the table

have worked for the company.

5. In the range F13:F45, insert a formula to calculate the 401(k) contributions for

each employee. The formula should determine that if the number of years

employed is greater than or equal to 1, then the contribution is equal to the con-

tribution percentage in cell F4 multiplied by the employee’s salary; otherwise, the

contribution is zero.

6. In the range G13:G45, enter a formula to calculate the health insurance cost for each

employee by testing whether the name of the employee’s plan is equal to the name of

the health plan in cell B4. If it is, then the cost of the health plan is equal to the value

of cell C4; otherwise, the cost is equal to the value of cell C5.

7. In the range B7:B9, enter the formulas to calculate the total salaries, 401(k) contribu-

tions, and health insurance costs.

8. Print the contents of the Payroll worksheet.

Explore

Explore