You must use Formulas to transfer number from page 1 to page 2.
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 employees 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.)
K5 Liberty Bank&Trust Lynchburg, VA Branch Payroll Data for Week of Jan 1-7 Dependents Hourly Wage Hours Worked Regular Pay Overtime Pay Gross Pay Taxable Pay Withholding Tax Yearly Gross Pay 545.64 S 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 S 518.00 S 155.40S 573.40 S 673.40 $ 863.20 5 Cortez, Mark 6 Higgins, Judy 7 Howard, Francis 8 Lindsev, Lamar 9 Manning, Joe 10 Meyer, Delores 11 Pittman, Oliver 12 Schultz, Leslie 13 Spencer, Kelly 14 Vasquez, Cassandra 15 Watson, Rosemary 16 Williams, Lynne 664.00 1,054.80 468.72 740.00 237.80 883.20 1,055.20 494.38 782.40 415.80 824.80 199.20 395.55 190.80 337.59 53.52 763.20 1,350.35 368.72 640.00 137.80 783.20 1,213.481,113.48 394.38 829.10 $26.37 3.02 50 1,450.35 1,022.84 384.35 598.12 468.72 740.00 237.80 883.20 40 20 13.78 195.80 278.37 632.64 859.87 404.57 664.22 342.65 613.74 571.95992$6,896.81 22.08 $26.38 $19.56 $ 20.62 58.28 494.38 929.10 75.24 30.65 57.60 146.70 207.28 28 41 855.73 755.73 18.5433 4788139.11086.069225.16 8,025.16 1.756.39 571.95992 6,896.81 1,756.39 479708.32 18 Taxable Pay Assumptions Summary Statistics 20 1 Base Work Hours 2 Overtime rate 23 FICA rate 24 Deduct per Depend50.00 25 Weeks per year # of Hours Average 39.83 S 574.73$39,975.69 50 S 1,022.84$75,418.20 20 S209.28$12,365.60 6.20% S1,767 300 K5 Liberty Bank&Trust Lynchburg, VA Branch Payroll Data for Week of Jan 1-7 Dependents Hourly Wage Hours Worked Regular Pay Overtime Pay Gross Pay Taxable Pay Withholding Tax Yearly Gross Pay 545.64 S 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 S 518.00 S 155.40S 573.40 S 673.40 $ 863.20 5 Cortez, Mark 6 Higgins, Judy 7 Howard, Francis 8 Lindsev, Lamar 9 Manning, Joe 10 Meyer, Delores 11 Pittman, Oliver 12 Schultz, Leslie 13 Spencer, Kelly 14 Vasquez, Cassandra 15 Watson, Rosemary 16 Williams, Lynne 664.00 1,054.80 468.72 740.00 237.80 883.20 1,055.20 494.38 782.40 415.80 824.80 199.20 395.55 190.80 337.59 53.52 763.20 1,350.35 368.72 640.00 137.80 783.20 1,213.481,113.48 394.38 829.10 $26.37 3.02 50 1,450.35 1,022.84 384.35 598.12 468.72 740.00 237.80 883.20 40 20 13.78 195.80 278.37 632.64 859.87 404.57 664.22 342.65 613.74 571.95992$6,896.81 22.08 $26.38 $19.56 $ 20.62 58.28 494.38 929.10 75.24 30.65 57.60 146.70 207.28 28 41 855.73 755.73 18.5433 4788139.11086.069225.16 8,025.16 1.756.39 571.95992 6,896.81 1,756.39 479708.32 18 Taxable Pay Assumptions Summary Statistics 20 1 Base Work Hours 2 Overtime rate 23 FICA rate 24 Deduct per Depend50.00 25 Weeks per year # of Hours Average 39.83 S 574.73$39,975.69 50 S 1,022.84$75,418.20 20 S209.28$12,365.60 6.20% S1,767 300
