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 2 What was the total gross wage amount forthese "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 ,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 le 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 Excel and prepare a table (or tables) 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). Sort the data so location one is listed rst.Clearly label each column .You can exclude the phantom entries you found in question 3. 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 '5 2016 pay that is associated with each job code (this means each employee '5 row should sum to 100%, within 1% to 2% to accommodate for errors ). Remove all observations that do not include an employee name or employees with no hours in 2016 on the list. For the final display, round the values to two decimal places using aformula .Sort the results by the employee '5 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 table 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 aformula ,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 .To make the prediction .you will use the following formula GT??? w?77? Tl? Ht? T}? I"??? '3"! 2016 * G???? W???? 7?? 7h? T]? '3'??? T? 2016 G7??? w???? 77'? M? T}? 1"??? 77 2015 10. Make a table that displays 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