Below is a description of each of the tabs in the Excel file, plus the required...

 

Transcribed Image Text:

Below is a description of each of the tabs in the Excel file, plus the required questions to be answered. It is up to the participant to create a chart and/or table summary for presentation to answer the required questions. The participant Tab Description Tab 1 Compensation: Total department salary by department number Tab 2 Headcount: Total headcount by department number Tab 3 Lists: Contains a listing of all of the departments with their assigned employee category or type plus the department's location. Please note that some departments contained on tab 3 are not contained on tabs 1 and 2. These Required Questions o Please provide an average salary per headcount for each location: Rural, City, & Island Dept Total Salary for Dept 2301100 $ 44,319 69,514 9,375 2321100 $ 2321300 $ 2321400 $ 2321500 $ 2321600 $ 2325100 $ 2325200 $ 2325300 $ 2325400 $ 2325500 $ 2325600 $ 2325700 $ 2327100 $ 2327200 $ 2327300 $ 2327400 $ 2331300 $ 2331500 $ 2333100 $ 2333200 $ 2333300 $ 2335100 $ 2335200 $ 2335300 $ 2335400 $ 2337100 $ 2337300 $ 2337400 $ 2341200 $ 2341300 $ 2341400 $ 2346100 $ 2346200 $ 2346300 $ 2346400 $ 86,169 51,935 81,400 21,348 93,331 27,744 30,823 25,010 136,035 27,277 7,016 80,384 12,162 84,416 97,599 25,550 22,227 64,292 47,922 23,322 72,996 52,456 9,776 38,819 67,615 54,085 47,626 22,540 55,105 108,080 42,726 71,770 34,279 Dept 2325100 2327100 2327300 2335400 2321300 2325500 2325700 2331500 2333100 2335100 2341300 2325300 2325400 2337100 2301100 2346200 2346400 2321100 2321500 2333300 2341200 2335300 2337400 2341400 2346100 2321600 2327400 2333200 2337300 2346300 2321400 2325200 2327200 2325600 2331300 2335200 Total Headcount for Dept Total Salary for Dept Average 1 21347.52812 1 7016.03872 1 12161.74679 1 9776.428461 12575.4 9375.167518 25009.58956 27276.9458 2 2 2 2 2 2 2 3 3 3 4 4 4 5 5 5 5 6 6 6 6 7 7 7 7 7 8 8 8 10 11 11 25549.75412 22226.7875 23322.14052 22540.04102 22185.8 27743.89441 30822.78382 38818.61583 32461.8 44319.3189 42725.59675 34278.637 40441.2 69513.99399 51934.57212 47922.2596 47625.9966 54249.2 52456.10421 54084.85568 55104.59844 108079.74 67431.3 #N/A 84415.93686 64292.40448 67615.14429 71769.74915 #N/A #N/A #N/A 80383.63029 #N/A #N/A 97599.109 72995.58539 #N/A Dept 2327100 2327200 2327300 2327400 Employee Categor Teacher Fireman Accountant Police 2331300 2341200 2341300 2341400 2346100 Construction Worker 2346200 Dog Catcher 2346300 2346400 2731300 2735300 2741300 2301100 2331500 2333100 2333200 2333300 2335100 2335200 Nurse Printer Car Dealer Politician Doctor Salesman Accountant Salesman Doctor Nurse Teacher Fireman Accountant Police Politician Printer Car Dealer 2335300 2335400 2337100 Dog Catcher Salesman 2337300 Construction Worker 2337400 2321100 2321300 2321400 Construction Worker 2321500 Doctor 2321600 Nurse 2325100 Fireman 2325200 Accountant 2325300 Politician Printer 2325400 2325500 2325600 2325700 Car Dealer Police Dog Catcher 2721500 Construction Worker 2725400 2725700 2727400 Doctor Teacher Salesman Baseball Police Fireman Locatio City City City City City City City City City City City City City City City Island Island Island Island Island Island Island Island Island Island Island Island Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Dept Employee Category 2301100 2321100 2321300 2321400 2321500 2321600 2325100 2341200 2341300 Nurse Teacher Salesman Construction Worker 2325200 2325300 2325400 2325500 2325600 2325700 2327100 2327200 2327300 2327400 2331300 2331500 2333100 2333200 2333300 2335100 2335200 2335300 2335400 2337100 2337300 Construction Worker 2337400 2341400 2346100 2346200 Doctor Nurse Fireman Accountant Politician Printer Car Dealer Police Dog Catcher Teacher Fireman Accountant Police Nurse Teacher Fireman Accountant Police Politician Printer Car Dealer Dog Catcher Salesman Doctor Printer Car Dealer Politician Construction Worker Dog Catcher Doctor Salesman 2346300 2346400 2721500 Construction Worker 2725400 2725700 2727400 2731300 2735300 2741300 Baseball Police Fireman Accountant Salesman Doctor Location Island Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural City City City City City Island Island Island Island Island Island Island Island Island Island Island City City City City City City City Rural Rural Rural Rural City City City Headcount Dept 2321100 2325100 2325400 2325500 2325700 2321300 2327100 2327200 2325200 2325600 2325300 2341300 2346200 2346400 2346100 2341400 2321400 2321500 2321600 2327300 2327400 2341200 2335100 2335200 2335300 2346300 2331300 2331500 2333100 2333200 2333300 2337400 2301100 2335400 2337100 2337300 Total Headcount for Dept 5 1 3 2 2 2 1 8 8 10 3 2 4 4 6 6 8 5 7 1 7 5 2 11 6 ONNIST 7 11 2 5 174 Salaries Total Salary for Dept $ 2325100 $ 2325200 $ 2325600 $ 2325300 $ 2325400 $ 2325500 $ 2325700 $ 2321300 $ 2321400 $ 2321500 $ 2321600 $ 2327100 $ 2327200 $ 2327300 $ 2327400 $ 2341400 $ Dept 2321100 2341200 $ 2341300 $ 2346200 $ 2346400 $ 2346100 $ 2346300 $ 2331300 $ 2331500 $ 2333100 $ 2333200 $ 2333300 $ 2335100 $ 2335200 $ 2335300 $ 2335400 $ 2337100 $ 2337300 $ 2337400 $ 2301100 $ 69,514 21,348 93,331 136,035 27,744 30,823 25,010 27,277 9,375 86,169 51,935 81,400 7,016 80,384 12,162 84,416 55,105 47,626 22,540 42,726 34,279 108,080 71,770 97,599 25,550 22,227 64,292 47,922 23,322 72,996 52,456 9,776 38,819 67,615 54,085 44,319 Below is a description of each of the tabs in the Excel file, plus the required questions to be answered. It is up to the participant to create a chart and/or table summary for presentation to answer the required questions. The participant Tab Description Tab 1 Compensation: Total department salary by department number Tab 2 Headcount: Total headcount by department number Tab 3 Lists: Contains a listing of all of the departments with their assigned employee category or type plus the department's location. Please note that some departments contained on tab 3 are not contained on tabs 1 and 2. These Required Questions o Please provide an average salary per headcount for each location: Rural, City, & Island Dept Total Salary for Dept 2301100 $ 44,319 69,514 9,375 2321100 $ 2321300 $ 2321400 $ 2321500 $ 2321600 $ 2325100 $ 2325200 $ 2325300 $ 2325400 $ 2325500 $ 2325600 $ 2325700 $ 2327100 $ 2327200 $ 2327300 $ 2327400 $ 2331300 $ 2331500 $ 2333100 $ 2333200 $ 2333300 $ 2335100 $ 2335200 $ 2335300 $ 2335400 $ 2337100 $ 2337300 $ 2337400 $ 2341200 $ 2341300 $ 2341400 $ 2346100 $ 2346200 $ 2346300 $ 2346400 $ 86,169 51,935 81,400 21,348 93,331 27,744 30,823 25,010 136,035 27,277 7,016 80,384 12,162 84,416 97,599 25,550 22,227 64,292 47,922 23,322 72,996 52,456 9,776 38,819 67,615 54,085 47,626 22,540 55,105 108,080 42,726 71,770 34,279 Dept 2325100 2327100 2327300 2335400 2321300 2325500 2325700 2331500 2333100 2335100 2341300 2325300 2325400 2337100 2301100 2346200 2346400 2321100 2321500 2333300 2341200 2335300 2337400 2341400 2346100 2321600 2327400 2333200 2337300 2346300 2321400 2325200 2327200 2325600 2331300 2335200 Total Headcount for Dept Total Salary for Dept Average 1 21347.52812 1 7016.03872 1 12161.74679 1 9776.428461 12575.4 9375.167518 25009.58956 27276.9458 2 2 2 2 2 2 2 3 3 3 4 4 4 5 5 5 5 6 6 6 6 7 7 7 7 7 8 8 8 10 11 11 25549.75412 22226.7875 23322.14052 22540.04102 22185.8 27743.89441 30822.78382 38818.61583 32461.8 44319.3189 42725.59675 34278.637 40441.2 69513.99399 51934.57212 47922.2596 47625.9966 54249.2 52456.10421 54084.85568 55104.59844 108079.74 67431.3 #N/A 84415.93686 64292.40448 67615.14429 71769.74915 #N/A #N/A #N/A 80383.63029 #N/A #N/A 97599.109 72995.58539 #N/A Dept 2327100 2327200 2327300 2327400 Employee Categor Teacher Fireman Accountant Police 2331300 2341200 2341300 2341400 2346100 Construction Worker 2346200 Dog Catcher 2346300 2346400 2731300 2735300 2741300 2301100 2331500 2333100 2333200 2333300 2335100 2335200 Nurse Printer Car Dealer Politician Doctor Salesman Accountant Salesman Doctor Nurse Teacher Fireman Accountant Police Politician Printer Car Dealer 2335300 2335400 2337100 Dog Catcher Salesman 2337300 Construction Worker 2337400 2321100 2321300 2321400 Construction Worker 2321500 Doctor 2321600 Nurse 2325100 Fireman 2325200 Accountant 2325300 Politician Printer 2325400 2325500 2325600 2325700 Car Dealer Police Dog Catcher 2721500 Construction Worker 2725400 2725700 2727400 Doctor Teacher Salesman Baseball Police Fireman Locatio City City City City City City City City City City City City City City City Island Island Island Island Island Island Island Island Island Island Island Island Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Dept Employee Category 2301100 2321100 2321300 2321400 2321500 2321600 2325100 2341200 2341300 Nurse Teacher Salesman Construction Worker 2325200 2325300 2325400 2325500 2325600 2325700 2327100 2327200 2327300 2327400 2331300 2331500 2333100 2333200 2333300 2335100 2335200 2335300 2335400 2337100 2337300 Construction Worker 2337400 2341400 2346100 2346200 Doctor Nurse Fireman Accountant Politician Printer Car Dealer Police Dog Catcher Teacher Fireman Accountant Police Nurse Teacher Fireman Accountant Police Politician Printer Car Dealer Dog Catcher Salesman Doctor Printer Car Dealer Politician Construction Worker Dog Catcher Doctor Salesman 2346300 2346400 2721500 Construction Worker 2725400 2725700 2727400 2731300 2735300 2741300 Baseball Police Fireman Accountant Salesman Doctor Location Island Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural Rural City City City City City Island Island Island Island Island Island Island Island Island Island Island City City City City City City City Rural Rural Rural Rural City City City Headcount Dept 2321100 2325100 2325400 2325500 2325700 2321300 2327100 2327200 2325200 2325600 2325300 2341300 2346200 2346400 2346100 2341400 2321400 2321500 2321600 2327300 2327400 2341200 2335100 2335200 2335300 2346300 2331300 2331500 2333100 2333200 2333300 2337400 2301100 2335400 2337100 2337300 Total Headcount for Dept 5 1 3 2 2 2 1 8 8 10 3 2 4 4 6 6 8 5 7 1 7 5 2 11 6 ONNIST 7 11 2 5 174 Salaries Total Salary for Dept $ 2325100 $ 2325200 $ 2325600 $ 2325300 $ 2325400 $ 2325500 $ 2325700 $ 2321300 $ 2321400 $ 2321500 $ 2321600 $ 2327100 $ 2327200 $ 2327300 $ 2327400 $ 2341400 $ Dept 2321100 2341200 $ 2341300 $ 2346200 $ 2346400 $ 2346100 $ 2346300 $ 2331300 $ 2331500 $ 2333100 $ 2333200 $ 2333300 $ 2335100 $ 2335200 $ 2335300 $ 2335400 $ 2337100 $ 2337300 $ 2337400 $ 2301100 $ 69,514 21,348 93,331 136,035 27,744 30,823 25,010 27,277 9,375 86,169 51,935 81,400 7,016 80,384 12,162 84,416 55,105 47,626 22,540 42,726 34,279 108,080 71,770 97,599 25,550 22,227 64,292 47,922 23,322 72,996 52,456 9,776 38,819 67,615 54,085 44,319

Answer rating: 100% (QA)

Here are the stepbystep calculations to answer the required questions from the provided Excel data 1 ... View the full answer