PHM Reliable Catering Weekly Payroll Report Employee Hire Date Withholding Allowances Rate per HourHours Worked Gross Pay Federal Tax State Tax Net Pay Amico, Kristin Saturday, January 3, 2015 1 18.5 45 878.75 Evans, Timothy Friday, February 6, 2015 2 14.25 28 399 Fernandez, Marissa Thursday, November 5, 2015 0 16 32.5 520 Hall, Richard Saturday, November 12, 2016 2 18.5 40 740 Mi, Emily Tuesday, August 9, 2016 2 14.25 30 427.5 Reed, Linda Saturday, April 15, 2017 3 19 52 1102 Smith, Caroline Friday, June 2, 2017 1 13 27.25 354.25 Totals 254.75 4421.5 0 0 0 Average 1.571428571 16.2142857 36.3928571 631.642857 #DIV/0! #DIV/0! #DIV/0! Highest 3 19 52 1102 0 0 0 Lowest 0 13 27.25 354.25 0 0 0 Table 3-11 PHM Reliable Catering New Employee Data Employee Rate Per Hour Hours Worked Withholding Allowances James, Michael 2 16.50 32.5 0 18.00 48 Lindstrom, Ashley 1. Open the workbook, Lab 3-2 PHM Reliable Catering Weekly Payroll Report. 2. Save the file as CS155Week6LastnameFirstname. Ensure that you use your Lastname and Firstname in filename. 3. Delete rows 12 through 14 to remove the statistics below the Totals row. 4. Delete column B. Set column A width to 31.00 and columns B through K to 11.00. Select row 3 and set text to wrap in this row using the Wrap Text button (Home tab Alignment group), and then set the row height to best fit. 5. Delete the record for the employee Evans, Timothy. Add two blank lines directly above the row for Mi, Emily, and add the information for the two new employees listed in Table 3-11. 6. Replace one of employee's names with your name. 7. If necessary, use the fill handle in cell E6 to copy the gross pay formula to the rows of the two new employees. 8. Add the Tax Rates information shown in Figure 3-87 in cells A15:B20 to your worksheet. 9. Change the font size in cell A1 to 28-point. Change the font size in cell A2 to 18-point. Change the font in cell A15 to 18-point italic and underlined. Change the row height for rows 1, 2, and 15 to best fit. 10. Insert three columns to the right of the Gross Pay column. Add the column titles Taxable Income, Social Security, and Medicare in cells F3:43. Center the contents of cells B3:K3. Calculate the Social Security and Medicare taxes in columns G and H by multiplying the tax rates in the Tax Rates table by the Gross Pay. Lab32.xlsx W4SpeechOutlin....docx W2Assignmente....docx Show all Х 11:44 AM Type here to search о . 12/26/2019 11. Federal tax calculations must take into account two tiers of income tax, which are applied to the taxable income. Calculate the taxable income, which is the Gross Pay - (number of withholding allowances * $90). 12. Calculate the federal tax withheld. If an employee has a taxable income of greater than or equal to $689, then the federal tax withheld equals $110.85 plus the federal tax rate found in cell B19 multiplied by the taxable income in excess of $689. If an employee's taxable income is $689 or less, the federal tax withheld equals the taxable income multiplied by the federal tax rate found in cell B18. Use the IF function to calculate the federal tax in Column I. 13. State tax is calculated as a percentage of federal tax. Use the tax rate in the Tax Rates table to calculate state tax in column J. 14. Calculate Net Pay in column K, as Gross Pay – Social Security, Medicare, Federal Tax, and State Tax. 15. Use the background color of your choice for the ranges A1:K2 and A15:B20. 16. Center the range B4:B11. Apply the currency style with two decimal places, no dollar signs, and negative numbers in black and parentheses to the range C4:C11 and E4:K12. 17. Apply a Thick Bottom Border to the range A3:K3. Apply a Thick Outside Border to the range A15:B20. 18. Change the sheet tab name to Weekly Payroll and the tab color to match the color used as background color in cell A1. 19. Preview the worksheet. In page setup fit the worksheet to one page in landscape orientation. Save file after completing and submit work by attaching file to week 6 assignment submission area Grading Criteria Assignment Maximum Points Deleted rows 12-14 and Delete column B 21 Deleted record. Two employees added, one students name and copied gross pay over to new entries 4 Column Widths: A - 31 pts and columns B-K to 11 pts Row 3 wrap text. Row height 1-3,15 best fit Tax Rates information shown in Figure 3-87 in cells A15:B20 5 4 Lab32.xlsx W4SpeechOutlin....docx A W w2Assignments....docx Show all Type here to search о E 11:44 AM 12/26/2019 ? L > Computer Applications for Business - Week 6 Assignment MS Excel: Update Payroll Using Microsoft Excel online or installed version of Microsoft Excel complete the following assignment NOTE: To complete the assignment you will need to start with this file, Lab 3-2 PHM Reliable Catering Weekly Payroll Report Problem: PHM Reliable Catering is a company that provides catering services to both small and large businesses. You have been asked to update the weekly payroll report to reflect changes in personnel, to update certain mandatory deductions, and to add overtime computations. The final worksheet is shown in Figure 3-87. Figure 3-87 File Home Insert Draw Page Layout Formulas Data Review View Help Tell me what you want to do Q13 f D F F G H PHM Reliable Catering Weekly Payroll Report Federal Withhold ing Allowanc Rate per Hour 1 18.50 0 16.00 2 18.50 16.50 0 18.00 2 14.25 3 19.00 1 13.00 391.04 Hours Taxable Worked Gross Pay Income 45 878.75 788.75 32.5 520.00 520.00 40 740.00 560.00 32.5 356 25 48 936 00 936.00 30 427.50 247.50 52 1.102.00 832.00 27.25 354 25 284 25 307.25 5,494.75 4,504.75 538 25 Socia Security 56.68 33.54 47.73 34.59 60.37 27.57 71.08 22.85 354.41 Medicare 13.62 8.06 11.47 8.31 14.51 6.63 17.08 5.49 85.17 Tax 134.79 72.80 78.40 49.88 170.13 34.65 145.12 37.00 722.81 State Tax Net Pay 26.96 646.70 14.56 15.68 586.72 9.98 433.50 34.03 656.96 6.93 351.72 29.03 839.64 7.40 281.52 144.56 4,187.80 3 Employee 4 Amico, Kristin 5 Fernandez, Marissa 6 Hall, Richard 7 James, Michael 8 Lindstrom, Ashley 9 Mi, Emily 10 Reed, Linda 11 Smith, Caroline 12 Totals 131 14 15 Tax Rates 16 Social Security tax 17 Medicare tax 18 Federal tax under $689 19 Federal tax $689+ 20 State tax (% of federal withholding Weekly Payroll -- 6.45% 1.55 143 24% 20% + . Lab32.xlsx W4SpeechOutlin....docx w W2Assignments....docx Show all х Type here to search O . *** a Aca 11:43 AM 12/26/2019
Purchase answer to see full attachment