RETIREMENT PLAN Excel Problem - Spring 2020

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

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, 2019, Plan Ahead decided to match employee deferrals 50% up to a 6% deferral on their eligible compensation.The corporation also decided to contribute $400,000 as a profit sharing contribution.Employees that make more than $110,000 (compensation benchmark) are to receive 5.6% of the difference in their compensation (defined here as total eligible wages) over the $110,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.6% 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.If a participant retires, there are no additional requirements to share in the profit sharing allocation (i.e., he/she need not meet the hours of work requirement).Normal retirement per the plan is the date the participant reaches age 65 and discontinues employment.

Recognized plan compensation for matching and/or profit sharing can never exceed $250,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 $250,000 limit on plan compensation should be applied after any calculations that involve 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 $250,000.

Phase I, Phase II, Phase III, Phase IV

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

I will provide the data above in spreadsheet form within the D2L Course Content area.

Eventually, you will create a separate spreadsheet, and ...

1)Efficiently calculate the matching contribution for each eligible participant.

2)Efficiently allocate the profit sharing among the eligible participants.

But first, let's just focus on doing the analysis as to which employees qualify for the matching. I refer to this part as "Phase I".

The remaining tasks are described here:

Phase II = how much Matching does each employee receive?

Phase III = who qualifies for the Profit Sharing and when?

Phase IV = how much Profit Sharing does each employee receive?

i need help with phase III.