a subsidiary of Comics, Inc., has a 401(k) Savings Plan for its employees

a subsidiary of Comics, Inc., has a 401(k) Savings Plan for its employees. For those of you who do not understand a 401(k) plan, it is a plan whereby an employee contributes and the employer may match his/her contribution and may even make an additional profit sharing contribution. Most of the employees of Shizam Products, Inc. are members of the Marvel extended family. Glenn Marvel, the brilliant founder, president, and CEO has asked you to do an annual valuation (accounting) of the plan’s assets. The accounting period is for the 2012 Plan Year which runs from January 1, 2012 through December 31, 2012. A comma delimited text file called “pensiondata.txt” is included with this assignment folder. This file includes information in the following order for each employee: Name, Date of Birth (DOB), Date of Hire (DOH), Date of Termination (DOT), hours worked, the percentage of plan assets held by each employee as of January 1, 2012, 2012 compensation, and the 2012 401(k) contribution made by the employee. Please follow these instructions to complete the project.Take the text file, convert it to an Excel spreadsheet, and name it “Contributions”.In a separate column, calculate each employees Matching Contribution to be received as a result of their 401(k) contribution. The matching formula is $1 for each dollar invested up to 3% of compensation. The matching contribution is only received if the employee is at least 18 years old by 12/31/12. Also, no one receives a contribution on compensation in excess of $250,000Calculate a Profit Sharing Contribution for each employee. The formula is 3% of compensation up to $100,000, plus 8.7% of allcompensation up to $250,000. [In short, if someone earned $250,000, his or her contribution would be 3% times 100,000 plus 8.7% times 250,000]. By the end of the plan year (12/31/12), an employee must be at least age 21, have worked 1000 hours for the year, been employed for at least 1 year, and not be terminated to receive the profit sharing allocation. Total employee and employer contributions for each employee, including his or her 401(k) contribution, may not exceed $55,000.Go to the second file attached to this assignment called Shizam401(k)Trust. In a second worksheet, include all information in columns A through F. Call the second sheet “Plan Assets”. This Worksheet provides the value of the Plan Assets as of January 1, 2012 and at the end of the period on 12/31/12.On the “Contributions” worksheet, allocate the “Plan Assets” for each employee as of January 1, 2012 based on each employee’s percentage of total assets. (His or her percentage of assets times $202,739.41)Allocate the total assets ($217,646.46) to each employee as of December 31, 2012, again based on each person’s share of “Plan Assets” (The same formula as #6).As Glenn has grown very suspicious of the stock market, he has begun investing all additional plan contributions in a Guaranteed Investment Contract yielding 5% a year. Money invested in each person’s account as of 1/1/12 remains invested in the stock market in the same manner before. To allocate earnings on new contributions invested after January 1, 2012, assume that the 401(k) contributions (the employee’s contributions) were in the account for ½ of the year (i.e.: deposited monthly throughout the year so that earnings accrue as if they were there ½ of the year). Matching and profit sharing contributions do not receive earnings. From there, determine each employee’s balance as of 12/31/12 (Take what you calculated in #7 plus the employees contributions, plus the interest on earnings calculated in #8, plus the profit sharing contribution, plus the matching contribution).Do a 401(k) employee statement on Excel for each employee. Think of the statement looking a little like a bank statement. Be original and make it attractive. Include all participant data (Name, DOB, DOH, etc), the opening balance, contributions, interest income, the income earned on “Plan Assets”, and the ending balance. Each statement should reference data from the other worksheets within the workbook. How many worksheets in the workbook? A lot!!Hope you found this exercise challenging, entertaining, and useful.Glenn E. Marvel, BA, MA, Founder, CEO, and President.