I'm needing help doing a payroll Register, Earnings Reports, Journal Entries and General Ledger accounts for this in excel?
Boston Company recently hired you to prepare the monthly payroll along with other accounting tasks on October 1. You will need to create an Excel workbook for your Payroll Register, Earnings Report, General Journal and General Ledger. You will find attached several pdf forms that will assist you in preparing their final returns. All employees are paid monthly with the Manager and President being paid a salary and the other two being paid an hourly rate. Both you and the Salesperson are not considered white collar employees, so the company is responsible to pay you overtime for time worked beyond 40 hours per week. NOTE: All excel workbooks should be created from scratch and no template should be used. Using a template is considered collusion and will result in an automatic zero for the project. . 0 The following taxes are levied upon Boston Company and its employees Federal Income Taxes (FIT) - Please use the attached tax tables. Use the wage-bracket method when able o Use the percentage method when needed Nebraska State Income Taxes (SIT) - 6.84% Nebraska State Unemployment Taxes (SUTA) 3.08% on the first $9,000 of wages Federal Unemployment Taxes (FUTA) - .6% on the first $7,000 of wages FICA (OASDI) - 6.2% on the first $142,800 of gross earnings FICA (HI) - 1.45% on total gross earnings. . O Other deductions The President and Manager currently put money into the company sponsored SIMPLE Retirement Plan. As a new employee you will not be allowed to participate. While the company does match these, that is handled by a CPA company and is not your responsibility to keep track of. You will be required to use it when calculating the employees net pay and other deductions. The State of Nebraska for SIT allows for the same benefits as the Federal government for FIT calculations. . Taxes are deposited according to the following schedule Federal Taxes - OASDI, HI and FIT taxes must be deposited on or before the 15th of the month following the month in which the taxes were withheld State - States taxes are deposited quarterly. The must be remitted along with the 941N by the month following the end of the quarter SUTA taxes are deposited quarterly. The must be remitted along with the 941N by the month following the end of the quarter FUTA taxes are deposited in accordance with Federal guidelines. . . NOTE: Please be sure you use a fictitious SSN for your W-2. Company Information: Boston Company 1010 Prdv Road Grand Island, NE 68803 Federal ID #00-0000880 State ID #000-00-5500 1 Employee Information: Noah Boston, President, SSN# 000-00-4813, 315 N Shore Drive, Grand Island, NE 68803 James Jones, Manager, SSN# 000-00-6458, 3200 Indian Road, Grand Island, NE 68803 Kylee Miller, Sales, SSN#000-00-5694, 1919 Avenue L, Grand Island, NE 68801 {Student} Employee Information to date: Hourly Rate Annual Salary 176,304 150,396 Martial Status M S SIMPLE W/H Allow Deduction 3 900 0 500 Noah Boston James Jones Kylee Miller Student} M 1 21.00 15.00 S 0 Earnings 132,228 112,797 36,750 Noah Boston James Jones Kylee Miller {Student} OASDI 8,198.14 6,993.41 2,278.50 YTD as of 9/30/2021 HI FIT SIT 1,917.31 13,134.55 9,014.40 1,635.56 12,030.60 7,515.31 532.88 2,655.00 2,463.70 SIMPLE 8,100.00 4,500.00 Net 91,863.60 80,122.12 28,819.92 Balances in General Ledger accounts at 9/30/2021: Account # 100 210 211 Account Cash FICA Taxes Payable - OASDI FICA Taxes Payable - HI FUTA Taxes Payable SUTA Taxes Payable FIT Payable SIT Payable SIMPLE Payable Wages Expense Payroll Tax Expense Balance 128,962.00 3,882.24 907.94 126.00 6.16 3,091.13 6,331.14 212 213 214 215 240 510 520 281,775.00 22,513.40 2 The following transactions and events occurred during the last quarter of the year 10/15 Prepare a journal entry to record the deposit of Federal Taxes from the prior month and post to the appropriate General Ledger. (NOTE: the amount due is the same as the balance carry forward from the General Ledger. This include the deposit of FICA and FIT taxes) 10/31 SUTA Taxes were paid for the previous quarter 10/31 SIT Taxes were paid for the previous quarter 10/31 Prepare the payroll for the last day of the period of October. There were 5 weeks during October, and you worked the following hours during those 5 weeks (42, 36, 49, 31, 49) Kylee recently went to part-time and worked the following hours (21, 35, 16, 28, 17) NOTE: Be sure you record the both the employees and the employers Journal Entries. PROCESS: Prepare Register, then Employee Earnings, prepare your Journal Entry and then Post to the General Ledger 11/15 Prepare a journal entry to record the deposit of Federal Taxes from the prior month and post to the appropriate General Ledger. 11/20 Kylee submitted a new W-4 that changed her allowances to 2. This will take effect on the Nov 30 paycheck. 11/30 Prepare the payroll for the last day of the period of November There were 4 weeks during November, and you worked the following hours during those weeks (50, 45, 40, 47) Kylee worked the following hours (15, 30, 35, 26) 12/4 Joel submitted a new W-4 that change exemptions to 1 12/15 Prepare a journal entry to record the deposit of Federal Taxes from the prior month and post to the appropriate General Ledger. 12/31 Prepare the payroll for the last day of the period of December There were 4 weeks during December, and you worked the following hours during those weeks (40, 45, 48, 46) Kylee worked the following hours (20, 15, 25, 16) Prepare all journal entries needed in January to pay taxes due. Be sure you date them properly. NOTE: Do not post these to the General Ledger Required: . O Using the above transactions complete the Payroll Register, Earnings Reports, Journal Entries and General Ledger accounts you created using your excel knowledge. (please be sure these are created in 1 excel workbook) Complete the proper yearend tax forms by using the attached pdf files: Form 941 (Pages 1 & 2 Only) Form 941N (Nebraska) Forms Ul-11T & Ul-11W (Nebraska) Form 940 Form W-2 (one for each employee) Form W-3 0 O o 0 3 Answer the following questions on a blank excel worksheet in your workbook. Be sure to show your work for each calculation. o The total payroll tax expense incurred by the employer on salaries and wages paid during the quarter ended December 31 was o The total payroll tax expense incurred by the employer on the total earnings of Noah Boston during the fourth quarter was o Boston has decided to give all current employees (including himself) a bonus payment during January equal to 6% of their total gross pay for last year. Determine the total amount he will pay in bonus payments and total amount of payroll tax expenses associated with these bonuses. Boston Company recently hired you to prepare the monthly payroll along with other accounting tasks on October 1. You will need to create an Excel workbook for your Payroll Register, Earnings Report, General Journal and General Ledger. You will find attached several pdf forms that will assist you in preparing their final returns. All employees are paid monthly with the Manager and President being paid a salary and the other two being paid an hourly rate. Both you and the Salesperson are not considered white collar employees, so the company is responsible to pay you overtime for time worked beyond 40 hours per week. NOTE: All excel workbooks should be created from scratch and no template should be used. Using a template is considered collusion and will result in an automatic zero for the project. . 0 The following taxes are levied upon Boston Company and its employees Federal Income Taxes (FIT) - Please use the attached tax tables. Use the wage-bracket method when able o Use the percentage method when needed Nebraska State Income Taxes (SIT) - 6.84% Nebraska State Unemployment Taxes (SUTA) 3.08% on the first $9,000 of wages Federal Unemployment Taxes (FUTA) - .6% on the first $7,000 of wages FICA (OASDI) - 6.2% on the first $142,800 of gross earnings FICA (HI) - 1.45% on total gross earnings. . O Other deductions The President and Manager currently put money into the company sponsored SIMPLE Retirement Plan. As a new employee you will not be allowed to participate. While the company does match these, that is handled by a CPA company and is not your responsibility to keep track of. You will be required to use it when calculating the employees net pay and other deductions. The State of Nebraska for SIT allows for the same benefits as the Federal government for FIT calculations. . Taxes are deposited according to the following schedule Federal Taxes - OASDI, HI and FIT taxes must be deposited on or before the 15th of the month following the month in which the taxes were withheld State - States taxes are deposited quarterly. The must be remitted along with the 941N by the month following the end of the quarter SUTA taxes are deposited quarterly. The must be remitted along with the 941N by the month following the end of the quarter FUTA taxes are deposited in accordance with Federal guidelines. . . NOTE: Please be sure you use a fictitious SSN for your W-2. Company Information: Boston Company 1010 Prdv Road Grand Island, NE 68803 Federal ID #00-0000880 State ID #000-00-5500 1 Employee Information: Noah Boston, President, SSN# 000-00-4813, 315 N Shore Drive, Grand Island, NE 68803 James Jones, Manager, SSN# 000-00-6458, 3200 Indian Road, Grand Island, NE 68803 Kylee Miller, Sales, SSN#000-00-5694, 1919 Avenue L, Grand Island, NE 68801 {Student} Employee Information to date: Hourly Rate Annual Salary 176,304 150,396 Martial Status M S SIMPLE W/H Allow Deduction 3 900 0 500 Noah Boston James Jones Kylee Miller Student} M 1 21.00 15.00 S 0 Earnings 132,228 112,797 36,750 Noah Boston James Jones Kylee Miller {Student} OASDI 8,198.14 6,993.41 2,278.50 YTD as of 9/30/2021 HI FIT SIT 1,917.31 13,134.55 9,014.40 1,635.56 12,030.60 7,515.31 532.88 2,655.00 2,463.70 SIMPLE 8,100.00 4,500.00 Net 91,863.60 80,122.12 28,819.92 Balances in General Ledger accounts at 9/30/2021: Account # 100 210 211 Account Cash FICA Taxes Payable - OASDI FICA Taxes Payable - HI FUTA Taxes Payable SUTA Taxes Payable FIT Payable SIT Payable SIMPLE Payable Wages Expense Payroll Tax Expense Balance 128,962.00 3,882.24 907.94 126.00 6.16 3,091.13 6,331.14 212 213 214 215 240 510 520 281,775.00 22,513.40 2 The following transactions and events occurred during the last quarter of the year 10/15 Prepare a journal entry to record the deposit of Federal Taxes from the prior month and post to the appropriate General Ledger. (NOTE: the amount due is the same as the balance carry forward from the General Ledger. This include the deposit of FICA and FIT taxes) 10/31 SUTA Taxes were paid for the previous quarter 10/31 SIT Taxes were paid for the previous quarter 10/31 Prepare the payroll for the last day of the period of October. There were 5 weeks during October, and you worked the following hours during those 5 weeks (42, 36, 49, 31, 49) Kylee recently went to part-time and worked the following hours (21, 35, 16, 28, 17) NOTE: Be sure you record the both the employees and the employers Journal Entries. PROCESS: Prepare Register, then Employee Earnings, prepare your Journal Entry and then Post to the General Ledger 11/15 Prepare a journal entry to record the deposit of Federal Taxes from the prior month and post to the appropriate General Ledger. 11/20 Kylee submitted a new W-4 that changed her allowances to 2. This will take effect on the Nov 30 paycheck. 11/30 Prepare the payroll for the last day of the period of November There were 4 weeks during November, and you worked the following hours during those weeks (50, 45, 40, 47) Kylee worked the following hours (15, 30, 35, 26) 12/4 Joel submitted a new W-4 that change exemptions to 1 12/15 Prepare a journal entry to record the deposit of Federal Taxes from the prior month and post to the appropriate General Ledger. 12/31 Prepare the payroll for the last day of the period of December There were 4 weeks during December, and you worked the following hours during those weeks (40, 45, 48, 46) Kylee worked the following hours (20, 15, 25, 16) Prepare all journal entries needed in January to pay taxes due. Be sure you date them properly. NOTE: Do not post these to the General Ledger Required: . O Using the above transactions complete the Payroll Register, Earnings Reports, Journal Entries and General Ledger accounts you created using your excel knowledge. (please be sure these are created in 1 excel workbook) Complete the proper yearend tax forms by using the attached pdf files: Form 941 (Pages 1 & 2 Only) Form 941N (Nebraska) Forms Ul-11T & Ul-11W (Nebraska) Form 940 Form W-2 (one for each employee) Form W-3 0 O o 0 3 Answer the following questions on a blank excel worksheet in your workbook. Be sure to show your work for each calculation. o The total payroll tax expense incurred by the employer on salaries and wages paid during the quarter ended December 31 was o The total payroll tax expense incurred by the employer on the total earnings of Noah Boston during the fourth quarter was o Boston has decided to give all current employees (including himself) a bonus payment during January equal to 6% of their total gross pay for last year. Determine the total amount he will pay in bonus payments and total amount of payroll tax expenses associated with these bonuses
