Analytics mindset

IntegrateCo

Part IV: 

After reviewing your initial report, Noah has several additional questions he wants answered. Please answer the following questions. 

Required

4. Answer the following questions. For all questions, name your final answer as qry_Part5_Q#_FINAL, where Q# represents the question number below. For example, the final answers to questions 1. and 2. should be qry_Part5_Q1_FINAL and qry_Part5_Q2_Final, respectively. If you use any subqueries to answer a question, name them as qry_Part5_Q#_DESCRIPTION, where the description is a brief, useful description about what you are doing in that query. Make sure to use aliases to name columns something meaningful.

1. Which employees are earning the most overtime pay and how much did they earn in the first six months of 2015? Prepare a query that displays the employee number, employee name (in one field list as FirstName LastName), the number of hours of overtime and the total amount of overtime pay. Only display employees who have more than zero hours of overtime. Sort the query so that those who had the highest amount of overtime pay are listed first.  

2. To reduce overtime pay, Noah is considering shuffling employees from one job code to another.  Which job codes have the most overtime pay in 2015? Display, in this order, all job code numbers, job code descriptions, the number of overtime hours and the amount of overtime pay.  Make sure to list all job codes that were in the job code table, even if they do not show any overtime hours. Do not list any job codes that are not listed in the job code table. Sort the results so the job codes with the most overtime pay are listed first, and then alphabetically by the job code description if there are any ties.  

3. Noah knows there are some errors with the new system used in 2016. What is the total number of transactions entered in 2016 without an employee number? What was the total gross wage amount for these “phantom” entries? Make sure to use a descriptive title for each column.

4. In 2015, were there any employees who incurred overtime when they had not already worked 40 regular work hours during the pay period (assume, for this problem, that all pay on the same date is for the same pay period)? Return the name of the employee (in one field, list it as FirstName LastName), the pay date, the total number of regular hours worked and the total number if overtime hours worked. Make sure only to list values if an employee worked less than 40 hours but listed some overtime hours. Sort the data by the employee name and then the pay date.  

5. What was the total employee cost for all projects in 2016? The total employee cost should include everything paid for wages, Social Security and Medicaid, FUTA, SUTA and workers’ compensation.

6. The company operates in two locations. Noah is concerned whether the company is paying a similar amount for employees at both locations. He prepared a pipe-delimited text file (Analytics_mindset_case_studies_locations.csv) for you that contains the names of all employees and their locations. This additional file contains two columns separated by a pipe, wherein the first column lists the name of the employee (FirstName LastName) and the second column provides the location where that employee works (either location one or two). Upload this data into Access and prepare a query that shows the total number of hours (include regular and overtime hours) employees in both locations worked in 2015 and 2016, the gross wages for each location in 2015 and in 2016, and the average pay for each location in 2015 and 2016 (i.e., pay per hour). Using a formula, round the average pay amounts to two decimal places. Sort the data so location one is listed first. Clearly label each column. Does there appear to be any difference in pay based on the location? Why or why not?  

7. Noah would like to know the percentage of each employee’s wages categorized into the different job codes for 2016. Display a chart that lists each employee in a separate row and each job code in a separate and unique column. Then, display the percentage of each employee’s 2016 pay that is associated with each job code (this means each employee’s row should sum to 100%, within 1% to 2% to accommodate for errors). Remove all observations that do not include an employee name. For the final display, round the values to two decimal places using a formula. Sort the results by the employee’s name.  

8. Noah wants to better understand monthly cash flows. To do this, he wants to know the monthly pattern of expenses for gross wages in 2015 (monthly wages are not available for 2016). That is, prepare a query that shows each month (sorted so January is listed first) and how much the amount of gross wages for that month differs from the average of the gross wages for the first six months. Positive values should show that the average for that month was more than the overall average. Using a formula, round your answers to two decimal places. Which month or months should Noah plan to have extra cash for higher-than-normal wage expenses?  

9. Noah wants to predict his likely gross wage expenses for the first six months for each job code in 2017. That is, Noah wants the query to prompt him to enter the job code number (e.g., 001, 002, 003) so the query will compute the expected gross wages for that job code in 2017. To make the prediction, the query will use the following formula:

The query should display the job code, the predicted 2017 gross wages for the job code, the percentage increase from 2015 to 2016, the gross wages for 2016 and the gross wages for 2015. Using a formula, round all variables to two decimal places. Create the query to make the calculation requested by Noah.

10. The 2016 data is not broken down by month. Noah wants to estimate what the monthly gross wages are by month. To do so, Noah requests that you apply the percentage of gross wages earned each month in 2015 to the 2016 total gross wages to estimate the monthly expenditures in 2016. Display the month (sorted so January is listed first) and the predicted gross wages per month for 2016 (round this number to two decimal points).

Data