Microsoft Office Tutorials and References
In Depth Information
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
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.