Comp25: Excel - Benefit Analysis

Assignment Goals

1. To create benefit a worksheet for your employees
2. To learn how to use Excel formulas that span multiple worksheets.
3. To practice using conditional formulas

Background

You created a basic payroll sheet during the first Excel homework for your employees. Now you must determine which employees are benefit eligible.

Steps to Take

1. Open the payroll.xls file you created for last week's homework assignment.

2. Save it as a new file called payroll-benefits.xls

3. Rename Sheet 1 to Payroll

4. Duplicate the Payroll worksheet as follows: With the control key held down, drag the "Payroll" worksheet tab to right to create a duplicate sheet.

5. Rename the new sheet Benefits.

6. Delete all data from the worksheet except for the employee ID and First/Last name

7. Add new headings as shown below:
   
   

8. Fill in the data in columns D & E as follows

   1. Randomly assign employees a status of either Full or Part for Full-time or Part-time. (Remember you can copy/paste cells for ease of input.)

   2. Randomly assign employees a number of years worked ranging from 1-10.

9. Complete the formulas for Columns F - I according to the different scenarios listed below.

   1. Health Care: Employees who are full time AND have worked more than five years are eligible to receive health care benefits. Either Yes they are eligible or No they are not.

   2. Retirement: Only full time employees receive benefits.  If they are eligible write "RET" in the cell or else leave the cell blank.

   3. Vacation:  All employees who have worked more than five years, receive $200 per year they have worked as a vacation credit, or else they get nothing. Be sure you calculate the full vacation credit for each employee.

   4. Dental-Vision: Employees can buy their dental and vision insurance if they are already receiving Health Care benefits for a $20 payroll deduction.  Either they pay out $20 or nothing.

10. At the bottom of your worksheet create two formulas that calculate how many full time and part time employees you have.

11. Your completed Benefits worksheet should look similar to the following:
    
    

12. Save your work.

13. Upload the Excel workbook via Blackboard.

Grading Guidelines

This assignment will be graded using the following guidelines:

All Steps Completed	50%
On Time	30%
Style	20%
	100%