Below are the directions I was given. I am hoping you can help me again. Once I see the solution I gain a much better understanding.

1.Unhide the Retirementworksheet in the Payroll file. Choose 1 employee from the Payroll worksheet to create a retirement plan for. This worksheet will help you calculate how much is being saved for retirement, and how that will affect the amount that can be withdrawn through the retirement years.

a.Begin by determining the yearly/monthly gross income that this employee can expect given his/her current pay and hours.

b.Many companies offer to match retirement contributions when their employees participate in a 401(k) plan. In this scenario, the employer will contribute up to 5% of the employee's gross income if the employee also contributes 5%. (E.g. employees contributing 3% of their income would receive an additional 3% from the company and employees contributing 7% would receive an additional 5% from the company, etc.)

c.Use a formula to add a contribution amount of your choice as well as any company match in cells B14:B15. Use an IF function to automatically determine the company match based on your chosen percentage. Be sure to use the assumption cells B22:B23 in your formulas. Test various contribution amounts to ensure that your formula works in all cases.

d.Roth IRAs can be used for low to upper middle class workers to fund retirement with post-tax dollars (i.e. net pay). Consider adding additional funds to a Roth IRA in cell B16. Total the contributions to your retirement in cell B17 and calculate the monthly contributions (D12:D17). The monthly contribution will now appear in cell B2.

e.Calculate the % of income that is being contributed to retirement in B20.

Liberty Bank & Trust Lynchburg, VA Branch Payroll Data for Week of Jan 1-7 Name No. of Dependents Cortez, Mark Higgins, Judy Howard, Francis Lindsey, Lamar Manning, Joe Meyer, Delores Pittman, Oliver Schultz, Leslie Spencer, Kelly Vasquez, Cassandra Watson, Rosemary Williams, Lynne Totals 2 1 3 1 1 1 3 2 3 2 1 2 12.95 16.6 26.37 13.02 18.5 11.89 22.08 26.38 13.01 19.56 14.85 20.62 FICA rate Deduct per Depend Weeks per year $ Hours Worked Regular Pay 48 48 50 36 40 20 40 44 38 45 28 41 $ Taxable Pay (weekly) Assumptions Base Work Hours Overtime rate Hourly Wage 40 1.5 6.20% 50.00 52 Overtime Federal Gross Pay Taxable Pay Withholding Tax Pay 518.00 $ 155.40 $ 673.40 $ 573.40 $ 664.00 199.20 863.20 813.20 1,054.80 395.55 1,450.35 1,300.35 468.72 468.72 418.72 740.00 740.00 690.00 237.80 237.80 187.80 883.20 883.20 733.20 1,055.20 158.28 1,213.48 1,113.48 494.38 494.38 344.38 782.40 146.70 929.10 829.10 415.80 415.80 365.80 824.80 30.93 855.73 755.73 $ 8,139.10 $ 1,086.06 $ 9,225.16 $ 8,125.16 86.01 $ 203.30 325.09 62.81 103.50 28.17 183.30 278.37 51.66 207.28 54.87 188.93 $ 1,773.28 FICA Net Pay 41.75 $ 53.52 89.92 29.06 45.88 14.74 54.76 75.24 30.65 57.60 25.78 53.06 $ 571.96 545.64 606.38 1,035.34 376.85 590.62 194.89 645.14 859.87 412.07 664.22 335.15 613.74 $ 6,879.92 Summary Statistics Tax Rate # of Hours $0 $179 10% 15% Average $730 25% $1,767 $8,013 28% 33% Net Pay 39.83 $ Gross Pay (yr) 573.33 $ 39,975.69 Highest 50 $ 1,035.34 $ 75,418.20 Lowest 20 $ 12,365.60 194.89 $ Notes 1. The base workweek is 40 hours. Regular pay is based on hourly wage and hours worked up to 40. 2. Overtime pay is based on overtime hours, the hourly wage, and the overtime rate. 3. The gross pay is the total of the regular pay and overtime pay. 4. The taxable pay is the difference between the gross pay and the product of the number of dependents and deduction per dependent. 5. Federal withholding tax is calculated on the taxable pay based on the tax table. 6. FICA is calculated on the employee's gross pay. 7. The net pay is based on the gross pay, federal withholding tax, and FICA. Yearly Gross Pay $ 35,016.80 $ 44,886.40 $ 75,418.20 $ 24,373.44 $ 38,480.00 $ 12,365.60 $ 45,926.40 $ 63,100.96 $ 25,707.76 $ 48,313.20 $ 21,621.60 $ 44,497.96 $ 479,708.32