Plan Ahead, Inc. established a defined contribution 401(k) plan for its employees effective January 1, 2015.

Contributions to the plan consist of employee 401(k) deferrals, an employer discretionary matching contribution and a discretionary profit sharing contribution.

Employees age 21 or older are immediately eligible to participate in 401(k). However, to be eligible to participate in the matching portion of the retirement plan, an employee must be age 21 and have been employed for 1 year. An employee enters the plan on the January 1 or July 1 after or coinciding with meeting the eligibility requirements.

For the year ended December 31, 2015, Plan Ahead decided to match employee deferrals 50% up to a 5% deferral on their eligible compensation. The corporation also decided to contribute $100,000 as a profit sharing contribution. Employees that make more than $115,000 (compensation benchmark) are to receive 5.7% of the difference in their compensation (defined here as total eligible wages) over the $115,000. The remaining amount is allocated to all eligible participants on a pro-rata basis based on their compensation, or the compensation benchmark, if they participated in the 5.7% allocation.

There are no additional requirements to be eligible to share in the matching contribution; however, a participant must qualify for matching, be employed on the last day of the plan year, and work at least 1000 hours in the current year to share in the profit sharing allocation. Normal retirement per the plan is the date the participant reaches age 65. If a participant retires, there are no additional requirements to share in the profit sharing allocation.

Recognized plan compensation does not exceed $245,000. Compensation is only recognized from plan entry date to the end of the plan year for participants that become eligible during the plan year. Assuming that an employee qualifies for the matching, treat all deferrals of compensation as being made during a period(s) in which the employee qualifies for the matching funds.

The $245,000 limit on plan compensation should be applied after any assessments that involve calculating how much an employee earns in the second half of the year. In other words, if an employee earns $300,000 for the entire year and he/she only qualifies for Matching or Profit Sharing as of July 1, the amount earned for the second half of the year will be used for the Matching and Profit Sharing calculations, as long as that amount does not exceed $245,000.

For the year ended December 31, 2015, the employee census consisted of the following:

Date of Birth

Date of Hire

Total Wages

Wages as of June 30, 2015

401(k) Deferral

Hours Worked in 2015

Separation Date

Michael Blackman

5/6/1965

1/1/2009

$350,000

$100,000

$16,500

2,080

Kiera Hughes

11/15/1970

6/20/2012

$36,000

$15,000

$2,000

2,080

Larry Ennis

1/11/1977

7/1/2014

$225,000

$50,000

$12,500

2,080

John Miller

5/24/1972

5/12/2008

$60,000

$60,000

$9,000

2,080

6/1/2015

Kevin Babb

4/12/1986

6/25/2013

$20,000

$9,800

$0

2,080

Patsy Gregg

2/20/1983

5/15/2015

$165,000

$15,000

$4,000

1,400

Rick VanHarn

6/28/1996

8/10/2014

$35,000

$17,000

$3,500

2,080

David Tucker

12/19/1973

9/5/2012

$175,000

$85,000

$16,500

2,080

Chelsea Webb

7/1/1994

10/10/2013

$290,000

$150,000

$20,000

2,080

Betty Walters

9/7/1989

11/19/2010

$155,000

$25,000

$8,000

2,080

Andrew Wheeler

11/24/1986

4/25/2014

$38,000

$32,500

$6,000

1,400

7/3/2015

Douglas Fowler

2/1/1950

8/11/2009

$4,000

$4,000

$400

160

2/1/2015

Robert Bell

2/26/1967

5/3/2011

$42,000

$21,000

$4,200

980

Samantha Frye

8/5/1971

8/15/2014

$127,000

$0

$16,500

1,400

Thomas Snider

10/1/1976

4/16/2009

$84,000

$40,000

$0

2,080

Denise Evans

7/1/1982

10/4/2013

$47,000

$24,000

$6,000

720

Christopher Wade

9/20/1977

1/3/2014

$90,000

$40,000

$14,000

2,080

Lauren Hicks

1/2/1994

6/20/2013

$250,000

$50,000

$15,000

1,800

Which employees qualify for the matching?

Someone please show me how to do this in excel! Please create another column that states either Qualifies or Does not qualify.

Could you also show the cell formula so I can do this on my own? Thanks