Payroll Project
Due in 2 segments – Please see instruction item #9 on the final page for more details.
Your accounting company has been hired to prepare the payroll and everything that goes along with it for Ol’Fashion Industries which has 4 employees. The necessary payroll information is listed below and in the Excel spreadsheet for the current year. The employees are paid monthly (last day of the current month) and were last paid Sept. 30th. Assume this is an Indiana company and use the Indiana withholding rates. Their SUTA rate is 3.5%.
Employee Gross Wages Marital Status Hire date Termination per check
Larry 14,800 Married/Joint 7 years ago
Keagan 6,700 Single 7 years ago
Martha 3,700 Single Aug. 1st Nov. 30th
Nancy 3,500 Married/Joint Sept. 1st
***All Medical Premium Insurance and Flexible Spending are part of a cafeteria plan***
Larry – Has $450/ pay deducted for 401K, $500/pay deducted for Medical Premium insurance , and $400 withheld for his Flexible Spending Account (FSA). Step 3 of W4 = $2,500. Spouse does not work.
Keagan - Has $300/ pay deducted for 401K, $250/pay deducted for Medical Premium insurance , and $150 withheld for FSA. Step 3 of W4 = $500
Martha- Has $50/ pay deducted for 401K, $200/pay deducted for Medical Premium insurance , and $150 withheld for FSA. Nothing for Step 3 or 4
Nancy- Has $50/ pay deducted for 401K, $350/pay deducted for Medical Premium insurance , and $400 withheld for FSA. Spouse makes the same as her (checked step 2 on W4)
**Employer will match 401K contributions up to $500. Employer’s portion of the Medical Premiums is twice what the employee pays.**
Instructions:
1. Using the attached Excel Spreadsheet tabs, complete the payroll register for Oct., Nov., and Dec. (There is a separate monthly tab at the bottom of the Excel file).
a. You may be creative and use your own preformatted payroll template sheets instead of the nonformatted within the attachments. However, make sure your sheets contain all the answers requested in the nonformatted attachments (ie. Check stubs).
2. Using the Excel Spreadsheet or template sheet, complete the payroll journal entries for each month. Also include the entries to record payment of taxes and other benefits to the respectable parties as outlined below.
** Note: Do Not include the Employers portion of 401K and Medical Premiums in with the Payroll Tax Expense. These are not taxes, but separate expenses and must be journaled separately.
3. The taxes and other payroll benefit expenses are paid:
a. F/W & FICA – every month (last paid Sept. 15th) next due Oct. 15th (Sept pay)
i. See opening trial balance for current payments due
b. St/W – every 2 months (last paid Sept. 15th) next due Nov. 15th (Sept and Oct pay)
i. For each 2 month item, use the separate Trial balance and your own T-Accts to help keep track of what is due (see below #5 trial balances)
c. 401K – every 2 months (last paid Sept. 15th) next due Nov. 15th (Sept and Oct pay)
d. Medical Prem. & FSA – every 2 months (last paid Sept. 15th) next due Nov. 15th (Sept and Oct pay)
e. FUTA & SUTA – do not pay this year
4. Create a checkbook record of each check written (included in each months Excel tab)
a. Start with check 500 for the Oct 15th payment and then continue with checks 501 – 504 for each employee’s check.
b. You may combine F/W and FICA for one check to the “Department of Treasury”
i. Note: You should also refer to the open trial balance to determine how much is in each account to be paid. Due to the prior accountant not paying the correct amount in Sept., the Sept balances due will be a little different than the Oct. balances calculated.
c. The following are the names of each department receiving checks
i. St/W = “Ind. Dept. of Treasury”
ii. 401K = TIAA Cref
iii. Medical Ins. Prem. = “Anthem”
iv. FSA = “Star Bank”
5. Complete an updated trial balance for the end of each month ending Dec. 31st (separate Excel taps provided).
a. Only update the balances for the entries you make. Thus leave all balance as is that are already filled in for Oct., Nov…etc.
b. Use the attached T-Acct sheet to help keep balances up-to-date
c. Note: If using your own payroll template, copy and paste each trial balance tab to your file.
6. Complete the W-2 for each employee (included in Dec. Excel tab)
7. Complete Form 941 for the 4th Qtr (included in Dec. Excel tab)
a. Show all work as formulas in current tab or in a separate tab for longer work explanations
8. Complete Form 940 (included in Dec. Excel tab)
a. Show all work as formulas in current tab or in a separate tab for longer work explanations
9. This project will be submitted over two (2) assignments. The first required submission will cover only the October tab (payroll register, journal entries, and checkbook register). This part will be worth the first 40 points.
Use the corrections provided from your instructions grading to update any incorrect October amounts and then continue with the remaining two months. The remaining part of the project is worth 110 points for a total of 150 points.
*Note, you will need to look ahead to Module 5 to fully complete the Federal Withholding amounts.