ACG 3141 – Data Analytics Case
Payroll Expenses
Background
It is July of 2015, and you are a junior staff accountant working for Knight Accounts, Inc., who provides bookkeeping, tax, audit, and consulting services for small to mid-sized companies in the southeastern United States. One of your clients, IntegrateCo, is a company that installs and services integrated building management systems. Building management systems are computer-based systems installed in buildings to control all mechanical and electric equipment in the building. Examples of mechanical and electric equipment controlled by the building management system include lightning, air conditioning and heating, security systems, power systems, sound systems, video cameras, door-locking systems, etc. IntegrateCo serves both large and small customers. It is privately held and expects to have $15 million in revenue in 2016.
Noah Summers, who is the CFO of IntegrateCo, has requested specific information regarding payroll expenses for the first half of 2015, and you have been selected to fulfill this request. To help you do this, Noah emailed payroll data to you from IntegrateCo’s accounting information system in a CSV file. This file contains the output of data from the first six months of 2015. He also provided the following descriptions of the data in the file:
·Pay_Date: This is the date that the employee was paid.
·Payroll_ID: This is a unique identifier for the date that the payroll was run for all employees. Each unique date should have a unique Payroll_ID number.
·Emp_No: This is a unique identifier for each employee. These numbers are not reused, so all employees have their own number.
·Home_Div: This is the home division number to which each employee belongs.
·Work_Div: This is the work division number to which each employee belongs.
·First_Name: This is the first name of the employee being paid.
·Last_Name: This is the last name of the employee being paid.
·Job: This is a unique code that combines several pieces of information into one field. The first two numbers represent the year that the project began. So 14 means the project started in 2014. Then, sometimes, there is a department identifier (H or S) that is included. The next three digits represent the job code (e.g., 001, 010, 012). After the job code, sometimes there is a location code (B or SG). Here are several examples:
- 14005 – The 14 represents the year the project began and 005 represents the job code 005 (Installation). There is no department identifier or location code included.
- 15S002 – The 15 represents the year the project began, S represents the department and 002 represents the job code 002 (Sales). There is no location code included.
- 14S001SG – The 14 represents the year the project began, S represents the department, 001 represents the job code 001 (Office) and SG represents the location code.
- 15007B – The 15 represents the year the project began, 007 represents the job code 007 (Customer Train) and B represents the location code. There is no department identifier included.
·Reg_Hrs: This is the number of regular hours reported by the employee that apply to the job code during the time period.
·OT_Hrs: This is the number of overtime hours reported by the employee that apply to the job code during the time period. Overtime hours are those worked in excess of 40 hours in a week.
·Reg_Pay: This is the total amount of pay the employee received for the Reg_Hrs worked during the time period. This amount is for each job code.
·OT_Pay: This is the total amount of overtime pay the employee received for the OT_Hrs worked during the time period. This amount is for each job code.
·Futa: This is the federal unemployment tax paid by the employer during the period pertaining to the regular and overtime paid to the employee for the particular job.
·Suta: This is the state unemployment tax paid by the employer during the period.
·Fica_Medc: This is the tax removed for the Federal Insurance Contributions Act. This money is used to pay forSocial SecurityandMedicare.
·Work_Comp: This is the amount paid from employees’ paychecks to cover workers’ compensation insurance.
Instructions
Using the data provided in the CSV file, please submit an Excel spreadsheet that answers the following questions about the payroll data for the first six months of 2015.
1.What were the employer payroll expenses paid by IntegrateCo forFICA (i.e., Social Security
and Medicare), FUTA, and SUTA?
Use a SUM function in Excel to report how much IntegrateCo paid for each expense.
2.How many different employeesare on IntegrateCo’s payroll?
Use an Advanced Filter in Excel to list only the unique employee numbers (see
https://support.office.com/en-us/article/count-unique-values-among-duplicates-8d9a69b3-b867-490e-82e0-a929fbc1e273
). Use the Sort command to sort the employee numbers in ascending order, then use the COUNT function to count how many unique employee numbers exist. 94
3.How much regular pay and overtime pay did IntegrateCo pay to each individual employee?
Calculate the amounts for regular and overtime pay separately using the SUMIF or SUMIFS function.
4.Based on the regular and overtime pay, what is the total Social Security tax and Medicare tax you would have expected IntegrateCo to pay as the employer’s payroll taxes?
Create a formula to calculate these expected tax payments separately for Social Security and Medicare taxes for each individual employee and then use the SUM function to create separate totals for the expected Social Security and Medicare tax payments. Assume (a) a maximum contribution threshold in 2015 of $118,500 for Social Security tax (6.2%), and (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.
5.Assuming the same employees receive the same regular and overtime pay in the second half of 2015.
a.How much do you forecast the company’s total Social Security and Medicare tax expenses will be?
b.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.)
c.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, and the FUTA rate is 6.0% less a 2.5% credit for state unemployment tax payments. 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.
Additional Instructions & Deliverables
This is an individual assignment. You may discuss the assignment with other students, but everyone must turn in their own solutions.
If you are unfamiliar with how to do the required Excel commands and functions, apply the ‘try-three-places rule’ outlined in the syllabus before seeking assistance from the TA or me. I will not cover how to do these in class, but there are excellent resources available on YouTube, LinkedIn Learning, and other places that can walk you through everything you will need to do.
To get started, you will need to import the comma-delimited data from the CSV file into Excel and then prepare your responses to the questions above in the spreadsheet. As a hint, you can use the “Text to Columns” tool under the Data tab to import the raw data. The deliverable for this case is an Excel spreadsheet which should be saved as “Section#_LastNameFirstInitial” (e.g., Section3_JohnsonJ).
The responses to each question in the spreadsheet should be clearly labeled, and the spreadsheet formatting should appear organized and professional, just like you were submitting these responses to a client. You may use text boxes if you would like to provide textual responses to supplement your calculations.
Your responses for all the questions should be contained in a new worksheet separate from the raw data. Your responses for Questions 2 through 5 should be contained in a single table, with responses to each question provided in separate columns. The first column should list the employee numbers in response to Question 2, the next columns should provide responses to Question 3, etc.