Please see attached excel sheets.

Using the information from the employee data for Oliver Pittman:

1) 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.

2) 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.

3) Calculate the percentage (%) of income that is being contributed to retirement in B20.

Liberty Bank & Trust Lynchburg, VA Branch Payroll Data for Week of Jan 1-7 Dependents Wage Hours Worked Regular Pay Gross Pay Taxable Pay Withholding Tax Net Pay Yearly Gross Pay 545.64$ 35,016.80 $518.00 155.40 $673.40 573.40 $ 5 Cortez, Mark 6 Higgins, Judy $12.95 86.01 41.75 8 3 1,450.35 468.72 75,418.20 24,373.44 Howard, Francis 8 Lindsey, Lamar 9 Manning, Joe 10 Meyer, Delores 11 Pittman, Oliver 12 Schultz, Leslie 2 12,365.60 45,926.40 63,100.96 25,707.76 48,313.20 21,621.60 44,497.96 479,708.32 28.17 183.30 278.37 $22.08 158.28 1,113.48 412.07 14 Vasquez, Cassandre 15 Watson, Rosemary 16 Williams, Lynne 782.40 4 855.73 8139 10 $1.086 06 $9.22516 8,125 16 177328 57196 $6,87992 4 Taxable Pay Assumptions Tax Rate Summary Statistics 21 Base Work Hours Net Pay Gross Pay (yr) 39.83 $ 573.33$ 39,975.69 50 $ 1,035.34 $75,418.20 20 $194.89 $12,365.60 23 FICA rate 24 Deduct per Depend50.00 25 Weeks per year Liberty Bank & Trust Lynchburg, VA Branch Payroll Data for Week of Jan 1-7 Dependents Wage Hours Worked Regular Pay Gross Pay Taxable Pay Withholding Tax Net Pay Yearly Gross Pay 545.64$ 35,016.80 $518.00 155.40 $673.40 573.40 $ 5 Cortez, Mark 6 Higgins, Judy $12.95 86.01 41.75 8 3 1,450.35 468.72 75,418.20 24,373.44 Howard, Francis 8 Lindsey, Lamar 9 Manning, Joe 10 Meyer, Delores 11 Pittman, Oliver 12 Schultz, Leslie 2 12,365.60 45,926.40 63,100.96 25,707.76 48,313.20 21,621.60 44,497.96 479,708.32 28.17 183.30 278.37 $22.08 158.28 1,113.48 412.07 14 Vasquez, Cassandre 15 Watson, Rosemary 16 Williams, Lynne 782.40 4 855.73 8139 10 $1.086 06 $9.22516 8,125 16 177328 57196 $6,87992 4 Taxable Pay Assumptions Tax Rate Summary Statistics 21 Base Work Hours Net Pay Gross Pay (yr) 39.83 $ 573.33$ 39,975.69 50 $ 1,035.34 $75,418.20 20 $194.89 $12,365.60 23 FICA rate 24 Deduct per Depend50.00 25 Weeks per year