A company contributes to each eligible employee's retirement plan at a rate of 4% of the employee's annual salary. However, to be eligible for this benefit, an employee must have full-time status with two or more years of employment. A calculation for the retirement contribution requires a test of two conditions: Full or part time status and number of years of employment.

There are three retirement contribution possibilities to account for: 1. An employee works full time and has been employed for two or more years. The retirement benefit applies 2. An employee works full time but has NOT been employed for more than two years. The benefit does not apply. 3. An employee does not work full time. The benefit does not apply.

Calculate the number of years each employee has been employed. You will need to search for 1 or more functions that allow you to do this. Hire date is displayed as Month - Year. Format the cells as 'General' to view the results of the calcualtion properly.

Write a signle formula that calculates the retirement benefit for eligible employees. For non eligible employees write "Not Eligible". Make sure the cell is formatted to display currency.

The company supplies two health plan options: 1. Up to $10,000 of annual coverage for employees who choose the family paln. 2. Up to $8,000 of annual coverage for employees who choose the individual plan.

These benefits do not apply if the employee or employee and family is already covered by some other health plan. A calculation for health insurance requires a test of three conditions: Individual, family, Already Covered.

Write a formula to calculate the Health Plan Cost for each employee. If the cost does not apply then write "N/A"

Name Employment Status Health Plan Salary Hire Date Years Employed Annual Retirement Contributic Health Plan Cost Gopnik Part time Mahfouz Full time Bryson Full time Peters Full time DeVries Full time Talento Part time Yang Full time Marks Part time Heller Full time Family Family Individual Individual Individual Family Other Family Family $45,000 $120,000 $145,000 $100,000 $115,000 $55,000 $95,000 $15,000 $124,000 Nov-16 Jun-08 Jan-19 Mar-19 Apr-16 Aug-14 Jan-17 Feb-20 Sep-19