1. Assuming the same employees receive the same regular and overtime pay in the second half of 2015.
   1. How much do you forecast the companys total Social Security and Medicare tax expenses will be?
   2. How much do you forecast IntegrateCo will need to expense related to FUTA tax in the second half of 2015? (Assume that all FUTA paid in the first half of 2015 is the same as the FUTA expensed during that period. Also, we want to point out that the FUTA and SUTA amounts in the raw data for the first half of the year do not equal the correctly computed FUTA and SUTA amounts actually owed. Please ignore this discrepancy in the raw data.)
   3. How many employees wages are expected to exceed the maximum contribution threshold for Social Security tax?

For 5a and 5b, create formulas using the IF function in Excel to forecast Social Security tax expense, Medicare tax expense, and FUTA expense in the second half of 2015 for each individual employee. Then use the SUM function to total each forecasted tax expense. Assume (a) a maximum contribution threshold in 2015 of $118,500 for Social Security tax (6.2%), (b) all employees file as single taxpayers and that the Medicare tax on wages up to and including $200,000 is 1.45% and 2.35% above $200,000 (in practice only the employee is responsible for the additional 0.9% tax for Medicare; however, assume for this project the employer is also responsible for the additional 0.9% Medicare tax), (c) the FUTA rate is 6.0% less a 2.5% credit for state unemployment tax payments, and (d) both SUTA and FUTA have a maximum contribution threshold of $7,000.

For 5c, use the COUNTIF function in Excel. Assume a maximum contribution threshold in 2015 of $118,500 for Social Security tax.

Obviously, I don't need like the actual answers.

I'm just not sure how to do these formulas and need help with the excel formulas.

THANK YOU.

WILL RATE UP

A B D E F G H 2 Pay_Date Payroll_ID Emp_No Home_Div Work_Div First_Name Last_Name Job 3 1/2/2015 27310001 1 1 1 Antonio Torres 14H001 4 1/2/2015 27310001 1 1 1 Antonio 14H010 5 1/2/2015 27310001 1 1 1 Antonio Torres 145001 6 6 1/9/2015 27310003 1 1 1 Antonio Torres 14H001 1 | K L M Reg_Hrs OT_Hrs Reg_Pay OT_Pay Futa Suta 11 0 519.063 0 0.15 0 0 0 0 4.03 11 0 0 0 2.6 9 0 2026 03 0 1.39 N o Fica_Medc Work_Comp 12.82 39.7083195 0.8 0 0 1.16 0 0 0.8 4296 1549909888 072 Torres A B D E F G H 2 Pay_Date Payroll_ID Emp_No Home_Div Work_Div First_Name Last_Name Job 3 1/2/2015 27310001 1 1 1 Antonio Torres 14H001 4 1/2/2015 27310001 1 1 1 Antonio 14H010 5 1/2/2015 27310001 1 1 1 Antonio Torres 145001 6 6 1/9/2015 27310003 1 1 1 Antonio Torres 14H001 1 | K L M Reg_Hrs OT_Hrs Reg_Pay OT_Pay Futa Suta 11 0 519.063 0 0.15 0 0 0 0 4.03 11 0 0 0 2.6 9 0 2026 03 0 1.39 N o Fica_Medc Work_Comp 12.82 39.7083195 0.8 0 0 1.16 0 0 0.8 4296 1549909888 072 Torres