Answer To: Project 4: Conduct Quantitative Analysis Step 1: Refresh Your Math, Statistics and Excel Skills...
Rajeswari answered on Nov 14 2021
QR Questions_Responses
Apply Quantitative Reasoning
Now that you have completed your analysis, think about the patterns you have seen in the workforce. In this final section, you will answer five questions and write a short essay.
1. From the created histogram, it appears that a large share of employees have a salary between $61,000–$140,000 or $141,000–$190,000. This may indicate a reasonable promotion rate for new employees. Is this distribution unimodal or bimodal? Please explain.
THis distribution is unimodal . REfere histogram given in the last sheet we find that there is only one peak arrived so unimodal but right skewed.
2. The line chart, as detailed in your "Graph Charts" Excel spreadsheet, shows sales generally increasing over the years, although sales in the first two years were notably lower. Assuming that the sales are linear, please use the Forecast tool to find projected sales for 2019 and 2020. Hint: An easy way to do this is to highlight the sales from the data page and apply the Forecast tool to this data. Your output will be a chart and a new sheet with projected sales for average, upper, and lower range growth.
Forecast is done in excel using forecast formula (the formula and particulars are given then and there)
3. The standard deviation provides insight into the distribution of values around the mean. If the standard deviation is small, the more narrow the range between the lowest and highest value. That is, values will cluster close to the mean. From your descriptive statistics, describe your standard deviations. What does this tell you about the variables?
The standard deviation is one of the methods of dispersion to find how data is spread away from the mean. This is highest for salary as 33250 others are comparatively less. The higher standard deviation denotes that there are more entries which deviate from the average considerably or spread is more.
But to compare the variation the proper method is to find std deviation/mean and compare with different variables. This would give a precise idea about the spread more.
4. The company has a keen interest in the educational, race, and gender makeup of its workforce. Its emphasis is on a diverse, dynamic workforce. From your "Graph Charts" spreadsheet, describe your pie chart findings for these characteristics of the workforce. Describe how you would determine if the company was meeting expectations on these characteristics.
Of course we find that the company has diverse dynamic work force with different races, gender, eduation etc. But from pie chart we find that one category is extraordinarily more in each category. For eg, Males are nearly four times as females. Married people are double as that of single. Regionwise C plain is maximum. Designationwise cyber analysts are more than 50% of total employees
So this can be improved by giving equal importance to each category.
5. The company is conducting an analysis on how many positions to create to keep up with demand. Specifically, it wants to know an estimate of the number of positions per job title. From your Excel chart, identify the mode of the job title distribution. Describe your findings.
The job distribution shows that only cyber Managers are moe than 50% followed by foresnsic associates , cyber software engineer. Other designations are very less sometimes only 1. This can be avoided by designating more people of other categories also. Or job titles can be changed accordingly.
FINAL ESSAY:
Now that you have done all the work with data, you will write a short three- to four-paragraph summary of your analysis. This is important. While you have done a wonderful job with your analysis, you can never assume that the end user will be able to interpret the data the way it should be understood. Supporting narrative is helpful. Never simply provide a "raw data" dump. Instead, seek to provide information!
Structure your essay like this:
a. Write a one-paragraph narrative summary of your findings, describing patterns of interest.
b. Provide an explanation of the potential relevance of such patterns.
c. Provide a description of how you would investigate further to determine if your results are "good or bad" for the company.
Prepare your response in this workbook. (Simply expand this text box to accommodate your essay and other answers.)
We are given a data set which gives particulars of nearly 372 employees of different races, designations, sexes, educational background, etc. We find that employee number is to identify the employee uniquely. Next is Hire date which mentions the joining date. Role, education, salary, marital status, sex are categorical variables assigned some number for reference and convenience. Being categorical variables they are consolidated and show as pie charts. The numerical variables are salary, years of service and hourly rate. Years of service is calculated upto 2014end in integer forms only. Salary rate hourly is calculated as total salary by number of hours per years as 350 working days x8 hours each day = 2800 hours in total . Thus salary rate is got by dividing the salary by hours thus calculated.
These three numerical variables are shown in excel and for salary alone frequency distribution and histogram is shown in excel. From the histogram we find that the distribution is unimodal and right skewed.
From the pie chart we find that BA/BS is nearly 50% followed by AA. Males are nearly four times as females. Married people are double as that of single. Regionwise C plain is maximum. Designationwise cyber analysts are more than 50% of total employees. Thus if any improvement is to be done, we have to recruit more females and also regionwise equality can be promoted. Marital status is a personal issue and not of concern if this does not affect the business or production.
The histogram of salaries is skewed to the right and unimodal. This is not surprising as there are different categories with different years of services. Minimum and maximum are taken into account and bins prepared. Hence histogram starts with x =70000 and reaches upto maximum. The boxes if joined at the top of the midpoints will give an idea about the distribution. We see that it is unimodal but not symmetrical. Right skewness is there.
Thus we find out the methods to do descriptive statistics for numerical variables, histograms and deriving the shape of the distribution from histogram. The pie diagram is used for categorical variables which give immediate idea about the distributions of various categories.
COUNTIF was used to count the categorical variables by using the number assigned.
DATA
Workforce Profile Analysis
Section 1: Complete all Columns in Data Set
Format for:
Windows User: Format cells in column according to indicated format.
Number Date Text Currency Currency Number General Number General Number Number Number Text General
mm/dd/yy
Year Ending Vested Yr
12/31/14 10
Emp # Hire Date Role Salary Hrly Rate Yrs of Svc Ed Age Vested Race Gender Status State Region
1003 09/07/12 Sr Cyber Analyst $166,750 $59.55 2 19 29 1 1 1 IL
1054 09/08/09 Cyber Analyst $85,293 $30.46 5 14 30 1 1 1 IL
1057 9/30/01 Cyber Analyst $107,385 $38.35 13 16 50 1 1 1 IL
1106 5/9/02 Cyber Analyst $115,744 $41.34 12 14 48 1 1 1 IL
1136 12/14/11 Cyber Analyst $82,523 $29.47 3 14 24 1 1 2 IL
1150 04/26/13 Cyber Analyst $80,808 $28.86 1 16 21 1 1 2 IL
1190 5/9/02 Cyber Analyst $105,716 $37.76 12 14 48 1 1 1 IL
1191 8/31/99 Cyber Analyst $59,147 $21.12 15 14 53 1 1 1 IL
1226 06/02/07 Forensics Analyst $127,435 $45.51 7 16 29 1 1 1 IL
1237 08/08/07 Cyber Analyst $86,904 $31.04 7 14 38 1 1 1 IL
1290 10/22/06 Cyber Analyst $100,078 $35.74 8 16 40 1 1 1 IL
1314 1/22/08 Cyber Analyst $84,765 $30.27 6 14 35 1 1 1 IL
1343 04/15/10 Forensics Analyst $71,897 $25.68 4 16 22 1 1 2 IL
1343 12/18/07 Cyber Analyst $104,860 $37.45 7 16 36 1 1 1 IL
1365 04/20/05 Cyber Analyst $101,431 $36.23 9 14 44 1 1 2 IL
1388 12/18/07 Cyber Analyst $145,130 $51.83 7 16 36 1 1 2 IL
1394 04/30/00 Cyber Analyst $118,640 $42.37 14 16 51 1 1 1 IL
1422 05/30/09 Cyber Analyst $86,918 $31.04 5 14 32 1 1 1 IL
1429 06/30/10 Sr Cyber Investigator $127,692 $45.60 4 16 32 1 1 1 IL
1492 10/25/01 Cyber Analyst $105,936 $37.83 13 16 49 1 1 2 IL
1498 02/22/11 Cyber Analyst $93,574 $33.42 3 14 27 1 1 1 IL
1508 04/15/14 Forensics Analyst $115,182 $41.14 0 16 18 1 1 2 IL
1579 01/12/05 Cyber Analyst $102,580 $36.64 9 16 45 1 1 1 IL
1629 09/04/11 Physical Security $65,550 $23.41 3 12 24 1 1 1 IL
1680 01/06/13 Malware Reverse Engineer $151,226 $54.01 1 16 24 1 1 2 IL
1709 09/20/05 Cyber Analyst $110,522 $39.47 9 16 43 1 1 1 IL
1751 08/13/09 Cyber Analyst $115,536 $41.26 5 16 31 1 1 1 IL
1790 03/15/99 Malware Reverse Engineer $161,077 $57.53 15 19 54 1 1 1 IL
1813 01/15/03 Cyber Analyst $103,922 $37.12 11 16 47 1 1 2 IL
1119 08/15/12 Cyber Analyst $121,262 $43.31 2 16 22 2 1 2 IL
1140 07/10/09 Cyber Analyst $85,934 $30.69 5 14 32 2 1 1 IL
1145 02/16/09 Forensics Analyst $75,131 $26.83 5 14 24 2 1 2 IL
1267 2/5/08 Cyber Analyst $94,747 $33.84 6 14 35 2 1 1 IL
1523 05/21/11 Acctg/Fin $60,767 $21.70 3 14 35 2 1 2 IL
1623 03/04/12 Cyber Software Engineer $114,153 $40.77 2 16 28 2 1 1 IL
1819 10/06/09 Cyber Analyst $84,804 $30.29 5 14 30 2 1 1 IL
1006 08/05/06 Cyber Analyst $150,307 $53.68 8 19 41 3 1 1 IL
1024 08/17/11 Cyber Analyst $83,005 $29.64 3 14 26 3 1 1 IL
1043 06/18/09 Forensics Analyst $76,168 $27.20 5 16 27 3 1 1 IL
1058 05/06/07 Cyber Analyst $98,059 $35.02 7 14 38 3 1 1 IL
1061 03/04/13 Acctg/Fin $112,238 $40.09 1 16 28 3 1 2 IL
1072 08/08/05 Cyber Software Engineer $167,019 $59.65 9 19 51 3 1 1 IL
1084 4/30/00 Cyber Analyst $108,771 $38.85 14 14 52 3 1 1 IL
1108 06/15/07 Forensics Analyst $127,403 $45.50 7 16 29 3 1 2 IL
1116 12/30/08 Cyber Analyst $112,235 $40.08 6 16 34 3 1 1 IL
1141 06/18/09 Forensics Analyst $75,999 $27.14 5 14 24 3 1 1 IL
1147 09/15/04 Cyber Analyst $103,327 $36.90 10 16 45 3 1 1 IL
1153 02/05/12 Sr Cyber Investigator $154,843 $55.30 2 19 30 3 1 1 IL
1156 05/21/11 Cyber Analyst $83,395 $29.78 3 16 26 3 1 2 IL
1188 05/16/09 Cyber Software Engineer $143,198 $51.14 5 19 38 3 1 1 IL
1196 09/08/09 Cyber Analyst $135,327 $48.33 5 16 30 3 1 1 IL
1201 08/01/08 Cyber Analyst $143,003 $51.07 6 16 35 3 1 1 IL
1227 05/03/00 Investigator $85,234 $30.44 14 14 45 3 1 1 IL
1232 07/12/10 Physical Security $67,456 $24.09 4 12 28 3 1 1 IL
1261 04/13/13 Cyber Analyst $80,165 $28.63 1 14 20 3 1 2 IL
1299 06/06/05 Sr Cyber Investigator $169,628 $60.58 9 16 41 3 1 2 IL
1315 02/15/07 Cyber Analyst $89,672 $32.03 7 14 39 3 1 1 IL
1381 10/23/10 Public and Business Office Team $132,181 $47.21 4 16 52 3 1 1 IL
1389 07/23/05 Cyber Analyst $100,914 $36.04 9 16 43 3 1 1 IL
1405 08/13/07 Forensics Analyst $128,537 $45.91 7 16 34 3 1 1 IL
1418 07/04/11 Quality Assurance $133,981 $47.85 3 16 36 3 1 1 IL
1428 10/20/07 Cyber Analyst $95,934 $34.26 7 14 37 3 1 1 IL
1445 08/02/11 Public and Business Office Team $104,415 $37.29 3 16 34 3 1 1 IL
1452 11/06/06 Cyber Analyst $99,838 $35.66 8 14 39 3 1 1 IL
1461 10/12/10 Advertising $83,215 $29.72 4 16 28 3 1 2 IL
1466 05/04/12 Public and Business Office Team $114,329 $40.83 3 16 33 3 1 2 IL
1519 06/04/11 Malware Reverse Engineer $163,010 $58.22 3 16 33 3 1 2 IL
1539 12/15/12 Forensics Analyst $78,102 $27.89 2 14 21 3 1 2 IL
1541 04/09/07 Sr Cyber Investigator $139,128 $49.69 7 16 36 3 1 1 IL
1543 10/06/09 Cyber Analyst $85,002 $30.36 5 14 30 3 1 2 IL
1551 11/14/08 Sr Cyber Investigator $128,317 $45.83 6 16 33 3 1 1 IL
1565 03/16/07 Cyber Analyst $88,712 $31.68 7 14 38 3 1 1 IL
1575 02/04/09 Cyber Analyst $90,629 $32.37 5 14 34 3 1 1 IL
1591 7/12/08 Cyber Analyst $93,213 $33.29 6 14 35 3 1 1 IL
1597 11/20/11 Cyber Mgr $170,293 $60.82 3 19 34 3 1 1 IL
1625 09/12/09 Logistics $90,346 $32.27 5 16 31 3 1 1 IL
1630 09/12/08 Physical Security $77,650 $27.73 6 12 36 3 1 1 IL
1638 09/01/07 Cyber Analyst $96,787 $34.57 7 14 38 3 1 2 IL
1667 05/01/00 Advertising $105,089 $37.53 14 16 35 3 1 1 IL
1671 06/13/12 Cyber Analyst $81,809 $29.22 2 16 23 3 1 1 IL
1672 05/04/08 Sr Forensics Mgr $146,546 $52.34 6 16 38 3 1 1 IL
1688 06/02/04 Cyber Analyst $103,784 $37.07 10 16 46 3 1 1 IL
1736 04/01/09 Cyber Analyst $109,130 $38.98 5 16 33 3 1 2 IL
1741 03/30/09 Cyber Analyst $89,191 $31.85 5 14 34 3 1 1 IL
1761 09/15/12 Cyber Analyst $81,049 $28.95 2 16 22 3 1 2 IL
1764 6/17/01 Cyber Analyst $117,530 $41.98 13 14 50 3 1 2 IL
1767 05/20/05 Cyber Analyst $111,360 $39.77 9 16 44 3 1 1 IL
1779 06/01/05 Cyber Analyst $121,084 $43.24 9 16 43 3 1 1 IL
1783 07/06/06 Cyber Software Engineer $156,972 $56.06 9 19 50 3 1 1 IL
1787 07/21/07 Acctg/Fin $116,261 $41.52 8 16 45 3 1 1 IL
1793 09/08/08 Sr Public and Business Office Team Mgr $140,822 $50.29 7 19 50 3 1 2 IL
1830 1/15/00 Cyber Analyst $108,928 $38.90 6 14 52 3 1 1 IL
1840 2/16/00 Cyber Analyst $108,792 $38.85 14 14 52 3 1 1 IL
1844 03/05/13 Cyber Software Engineer $140,324 $50.12 14 16 24 3 1 1 IL
1318 06/18/09 Forensics Analyst $86,351 $30.84 1 14 27 4 1 2 IL
1323 02/05/05 Cyber Analyst $102,235 $36.51 5 14 44 4 1 2 IL 96
1402 08/17/11 Cyber Analyst $82,812 $29.58 9 16 26 4 1 1 IL
1784 09/21/09 Forensics Analyst $96,096 $34.32 3 16 25 4 1 2 IL
1048 09/19/13 Physical Security $61,655 $22.02 1 12 20 1 2 2 IL
1220 04/15/00 Sr Cyber Investigator $143,377 $51.21 14 16 55 1 2 1 IL
1262 06/02/09 Sr Cyber Investigator $148,014 $52.86 5 19 33 1 2 2 IL
1442 01/08/12 Admin $72,415 $25.86 2 12 25 1 2 2 IL
1506 12/06/10 Cyber Analyst $83,609 $29.86 4 14 28 1 2 1 IL
1517 11/12/12 Cyber Software Engineer $122,572 $43.78 2 16 26 1 2 2 IL
1550 02/19/14 IT Staff $75,067 $26.81 0 14 28 1 2 2 IL
1069 02/03/11 Cyber Software Engineer $179,938 $64.26 3 16 35 2 2 1 IL
1304 06/30/01 IT Staff $88,698 $31.68 13 14 43 2 2 1 IL
1555 10/23/13 Cyber Software Engineer $142,458 $50.88 1 16 26 2 2 2 IL
1246 07/27/00 Sr Cyber Investigator $142,315 $50.83 14 19 54 3 2 1 IL
1403 12/01/10 Public and Business Office Team $121,265 $43.31 4 16 42 3 2 1 IL
1444 06/15/02 Marketing $121,221 $43.29 12 16 42 3 2 1 IL
1476 05/23/11 Logistics $98,560 $35.20 3 14 32 3 2 1 IL
1567 08/07/12 Physical Security $62,960 $22.49 2 12 20 3 2 2 IL
1569 04/23/10 Quality Assurance $95,254 $34.02 4 16 42 3 2 2 IL
1589 04/15/13 Malware Reverse Engineer $101,233 $36.15 1 16 25 3 2 2 IL
1607 03/15/99 Cyber Mgr $175,375 $62.63 15 19 44 3 2 1 IL
1661 03/18/13 Public and Business Office Team $102,300 $36.54 1 16 28 3 2 1 IL
1673 03/07/13 Marketing $117,415 $41.93 1 16 35 3 2 1 IL
1775 11/13/09 Malware Reverse Engineer $161,441 $57.66 5 14 30 3 2 2 IL
1791 09/07/07 Physical Security $70,788 $25.28 7 12 40 3 2 1 IL 24
1851 08/05/07 Investigator $118,068 $42.17 7 16 30 3 2 1 IL
1014 06/03/09 IT Staff $98,818 $35.29 5 16 40 4 2 1 IL
1297 09/15/10 Investigator $112,765 $40.27 4 14 29 4 2 1 IL
1060 02/02/09 Cyber Analyst $99,931 $35.69 5 14 34 1 1 1 NE
1101 06/08/02 Investigator $125,994 $45.00 12 16 31 1 1 1 NE
1146 11/05/09 Cyber Analyst $74,680 $26.67 5 14 29 1 1 2 NE
1180 01/24/05 Cyber Analyst $102,469 $36.60 9 16 45 1 1 1 NE
1265 03/04/13 Acctg/Fin $109,632 $39.15 1 16 24 1 1 2 NE
1279 10/20/07 Cyber Analyst $96,057 $34.31 7 16 37 1 1 1 NE
1288 03/25/10 Public and Business Office Team $120,708 $43.11 4 16 41 1 1 2 NE
1289 09/05/11 Forensics Analyst $97,943 $34.98 3 14 19 1 1 2 NE
1320 05/07/11 Sr Cyber Investigator $147,380 $52.64 3 19 31 1 1 2 NE
1379 06/09/07 Marketing $121,191 $43.28 7 16 36 1 1 1 NE
1387 07/14/09 Forensics Analyst $126,163 $45.06 5 14 26 1 1 2 NE
1456 06/25/07 Cyber Analyst $97,522 $34.83 7 14 38 1 1 1 NE
1467 03/19/09 Cyber Analyst $99,634 $35.58 5 14 34 1 1 1 NE
1468 7/21/01 Cyber Analyst $126,291 $45.10 13 16 50 1 1 2 NE
1496 05/07/13 Physical Security $71,035 $25.37 1 12 19 1 1 2 NE
1507 06/27/04 IT Staff $88,517 $31.61 10 14 43 1 1 1 NE
1553 04/12/09 Cyber Analyst $88,398 $31.57 5 14 33 1 1 1 NE
1568 02/07/10 Sr Cyber Investigator $137,771 $49.20 4 16 32 1 1 2 NE
1659 11/02/06 Cyber Analyst $99,925 $35.69 8 14 40 1 1 2 NE
1687 12/15/05 Forensics...