I need help with phase III

AutoSave O Off) A Q - GA RETIREMENT PLAN Excel Problem description Spring 2020... . O Search Sarpong, Gideon SG X File Home Insert Design Layout References Mailings Review View Help Share Comments LO X Cut Find Calibri 11 ~ A" A Aa* A FEE 21 AaBbCcDc AaBbCcDc AaBbC AaBbCc] Aab AaBbCCD Gc Replace Paste Copy BIUx x A LA 1 Normal 1 No Spac.. Heading 1 Heading 2 Title Subtitle Format Painter Select * Dictate Clipboard Font Paragraph Styles Editing Voice For the year ended December 31, 2019, the employee census consisted of the following: RETIREMENT PLAN Excel Problem - Spring 2020 plan Ahead, Inc. established a defined contribution 401(k) plan for its employees effective January 1, 2019 Employee Wages as of Date of Birth Date of Hire Total Wages June 30, 2019 401(k) Deferral Hours Worked in 2019 Separation Date Contributions to the plan consist of employee 401(k) deferrals, an employer discretionary matching contribution and a discretionary profit sharing contribution. Rolando Blackman 5/6/1959 1/1/2012 $350,000 $100,000 $16,500 2,080 Kiera Hughes 11/15/1974 6/20/2015 $36,000 $15,000 $1,000 2,080 Larry Findley 1/11/1981 7/1/2018 $225,000 550,000 512,500 2,080 Employees age 21 or older are immediately eligible to participate in 401(k). However, to be eligible to participate Jon Kopf 5/24/1976 5/12/2011 560,000 560.000 $9.000 2,080 6/1/2019 in the matching portion of the retirement plan, an employee must be age 21 and have been employed for 1 year. Kevin Babe 4/12/1990 6/25/2016 $20,000 59 800 50 2,080 An employee enters the plan on the January 1 or July 1 after or coinciding with meeting the eligibility Elaine Bland 2/20/1987 5/15/2018 $165,000 $10,000 54,000 1,400 Rick VanHarn 6/28/2000 8/10/2018 535,009 $17.000 53,500 2,080 requirements Kevin Sherley 12/19/1977 9/5/2015 5175,090 585.000 $16,500 1,000 For the year ended December 31, 2019, Plan Ahead decided to match employee deferrals 50%% up to a 6%% deferral Chelsea Webb 7/1/1998 10/10/2016 $290.000 $150,000 $22,000 2,080 on their eligible compensation. The corporation also decided to contribute $400,000 as a profit sharing Betty Walters 9/7/1993 11/19/2013 $155,000 $25,000 2,080 contribution. Employees that make more than $110,000 (compensation benchmark) are to receive 5.6%% of the Andrew Crowder 11/24/1990 4/25/2018 $38.000 $32.500 16,000 1,400 Wayne Saubert 2/1/1954 8/11/2012 $4,000 $4,000 $400 900 7/3/2019 difference in their compensation (defined here as total eligible wages) over the $110,000. The remaining amount 2/1/2019 is allocated to all eligible participants on a pro-rata basis based on their compensation, or the compensation Robert Bell /26/1971 5/3/2014 $42.000 $21.000 $4,200 980 8/15/2018 $19,500 1,400 benchmark, if they participated in the 5.6%% allocation. Samantha Frye 8/5/1975 $127,000 Thomas Snedeker 10/1/1980 4/16/2012 $84,000 $40,000 SO 2,080 Denise Evans 7/1/1986 10/4/2016 $47 000 $24,000 $6,000 720 Michael Wade 9/20/1981 1/3/2018 $90,000 $40,000 $14,000 2,080 There are no additional requirements to be eligible to share in the matching contribution; however, a participant Lauren Hicks 1/2/1998 6/20/2016 $255,000 $50,000 $15,000 1,800 must qualify for matching, be employed on the last day of the plan year, and work at least 1000 hours in the Steven Childers 7/11/1967 2/13/2018 $12 500 $10.000 $7.000 240 2/14/2019 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 etirement 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 will provide the data above in spreadsheet form within the DZL Course Content area. plan year. Assuming that an employee qualifies for the matching, treat all deferrals of compensation as being Eventually, you will create a separate spreadsheet, and .. made during a period(s) in which the employee qualifies for the matching funds 1) Efficiently calculate the matching contribution for each eligible participant. 2) Efficiently allocate the profit sharing among the eligible participants. 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 But first, let's just focus on doing the analysis as to which employees qualify for the matching. I refer and he/she only qualifies for Matching or Profit Sharing as of July 1, the amount earned for the second half of the to this part as "phase I" year will be used for the Matching and Profit Sharing calculations, as long as that amount does not exceed The remaining tasks are described here: $250,000 phase II = how much Matching does each employee receive? Phase I, Phase II, Phase III, Phase IV Phase III = who qualifies for the Profit Sharing and when? Phase IV = how much Profit Sharing does each employee receive? Page 2 of 2 554 words Focus + 70% Type here to search O Eli ead L X W 7:10 PM 4/9/2020