Description
https://www.youtube.com/watch?v=KG1gcyY4XPg&feature=youtu.be
This project will help you advise your company’s management on the cost of doing business, how to achieve success in income revenues, and make recommendations on the allocation of salaries across the company. Management will be able to use your analysis to fairly adjust salary anomalies, and also show profitability which might be used for further investments, hiring additional staff, or even employee incentive pay.
Over this two-week period, you will use mathematical operations and data analysis to solve problems and inform decision making. Your final assignment will be the creation of a comprehensive Excel workbook with supporting charts and graphs and a short analysis of the data.
This project will enable you to refresh and refine your skills in math and statistics before you tackle a real-world data set using Excel to analyze and display the data.
Quantitative reasoning uses a process similar to the qualitative research process in that you will first identify an issue or problem and then use mathematical formulas or an analytic tool to derive a solution. You will construct graphs, charts, and tables to display data and inform analysis and interpretation. You will evaluate the results of the information, draw analyses and validate them by applying them to the issue or problem.
This project will enable you to see the connection between data and how the use of quantitative analysis of that data informs solutions to practical problems with potential impact on your organization or industry.
There are 10 steps that will lead you through this project. Each step should take no more than two hours to complete. Begin by watching the video above, which introduces the project as it might occur in the workplace, and then continue with Step 1: Refresh Your Math, Statistics, and Excel Skills.
When you submit your project, your work will be evaluated using the competencies listed below. You can use the list below to self-check your work before submission.
- 1.1: Organize document or presentation clearly in a manner that promotes understanding and meets the requirements of the assignment.
- 1.2: Develop coherent paragraphs or points so that each is internally unified and so that each functions as part of the whole document or presentation.
- 1.4: Tailor communications to the audience.
- 1.5: Use sentence structure appropriate to the task, message and audience.
- 1.6: Follow conventions of Standard Written English.
- 3.1: Identify numerical or mathematical information that is relevant in a problem or situation.
- 3.2: Employ mathematical or statistical operations and data analysis techniques to arrive at a correct or optimal solution.
- 3.3: Analyze mathematical or statistical information, or the results of quantitative inquiry and manipulation of data.
- 3.4: Employ software applications and analytic tools to analyze, visualize, and present data to inform decision-making.
Step 1: Refresh Your Math, Statistics and Excel Skills
Everyone will begin this project with different background skills in math, statistics and Excel. Let’s start by thinking about what it means to engage in quantitative processesand the role these skills play in this project.
Next, assess your current baseline by refreshing your skills in math, statistics, and Excel. You will choose how much you already know and where you need to concentrate more attention in order to complete this quantitative analysis project.
After this refresher, you will create your own spreadsheet based on the template provided in the next step.
If you need help outside the classroom, you can register for the STAT 689 tutoring room (go to the Project 4 Discussion for registration information) in which you can access tutoring help and other resources to enable you to complete this project successfully. Help is free and immediate!
Step 2: Set Up Your Spreadsheet
Now that you’ve assessed and refreshed these important skills, you’re ready to begin. First download the Excel template and use it to set up your spreadsheet. This step has you set up your basic view in preparation for the use of several tools.
After you’ve formatted and set up your basic view and saved it with your name, you’re ready to move to the next step and add data.
Step 3: Add Data
With your spreadsheet set up and saved with your last name, you're ready to add data. In Section 1 on the Data page, complete each column of the spreadsheet to arrive at the desired calculations.
When you're ready, move on to the next step, where you will use functions to summarize the data.
Step 4: Use Functions to Summarize the Data
With your data built, you are now ready to start using some tools to summarize the data, using Countif and the Sum function to do the math. In this step, you'll begin to see patterns in the data and the story of the workforce.
Take a breather here if you need it. You should strive to work through the first four steps this week. Check in with your instructor.
With this step complete, you’re ready to begin your analysis.
Step 5: Analyze the Workforce
You’ve summarized the data. Next, you will employ descriptive or summary statisticsto analyze the workforce. Your summary tables described "how many." Now you will calculate mean, median, and mode for the categories of data, and derive the deviation, variance, and dispersion, and distribution. This is where it gets interesting!
You will be working in section 3 of the Data tab in the spreadsheet to complete the descriptive statistics for the five categories (Salary, Hourly Rate, Years of Service, Education, and Age). Using Excel formulas, complete the table.
After you have used Excel formulas to find this information, you will next use the Toolpak to find your summary statistics.
Step 6: Use the Analysis Toolpak
With your data set built, you will now use the Analysis Toolpak to do those same functions. This is a handy feature to know. Remember that there may be some minor differences in the answers depending on the version.
You should now have Tab 2 complete: Excel Summary Stats. Next, you’ll create charts and a histogram for Tabs 3 and 4.
Step 7: Create Charts and a Histogram
Where would we be without the ability to view data in charts? It is sometimes easier to grasp context of data if we can see it captured in an image. In this step, you will work with data to create charts, adding a tab for charts, and another for a histogram.
In this step, you will build Tab 3: Graphs—Charts and Tab 4: Histogram. After you complete these tabs, you’ll be ready to sort the data.
Step 8: Copy and Sort the Data
You’ve accomplished a lot with your data set, summary stats, charts, and histograms. Another skill you’ll need to be able to do is sort data in an Excel worksheet for reporting purposes. You’ll copy the data so that you can learn how to sort it. This is a good skill that applies to any Excel application.
In this step, you will create Tab 5: Sorted Data. When you’re finished, you’ll be ready to conduct your quantitative analysis.
See below for example of sorted spreadsheet.
Step 9: Conduct Quantitative Analysis
In this step, your hard work bears fruit. What does it all mean? Think back to your boss's reasons for tasking you with this project. Bring your powers of analysis to bear to determine what the data may be telling you. Apply your quantitative reasoning skills to answer five questions that demonstrate your interpretation of the data. The questions are located on the QR Questions_Responses tab in your workbook.
After you have answered the five questions, please finish the project by writing a short essay. Your short essay will include:
- a one-paragraph narrative summary of your findings, describing patterns of interest
- an explanation of the potential relevance of such patterns
- a description of how you would investigate further to determine if your results could be perceived as good or bad for the company.
Prepare your responses in your workbook on the QR Questions_Responses tab.
Good job! In the next step, you’ll submit your workbook and analysis.
Step 10: Submit Your Completed Workbook and Analysis
You’re now ready to submit your workbook and analysis for review and feedback. Review the requirements for the final deliverable to be sure you have:
- Excel Workbook with Six Tabs
- Tab 1: Data – completed data sheet (Steps 1–6 above)
- Tab 2: Excel Summary Stats (Step 6)
- Tab 3: Graphs – Charts (Step 7)
- Tab 4: Histogram (Step 7)
- Tab 5: Sorted Data (Step 8)
- Quantitative Analysis (Step 9; see detail below and move to first position upon completion.)
- Quantitative Analysis: Answers to Questions and Short EssayPrepare your response in this workbook. Create a tab for Quantitative Analysis, create a text box, and paste your answers to the questions in it. Move the Quantitative Analysis tab to the first tab position.Your final workbook tabs:
- Quantitative Analysis
- Data
- Excel Summary Stats
- Graphs–Charts
- Histogram
- Sorted Data
- Format to Be PrintedFormat this workbook so that all the spreadsheets can be printed.
Before you submit your assignment, review the competencies below, which your instructor will use to evaluate your work. A good practice would be to use each competency as a self-check to confirm you have incorporated all of them in your work.
- 1.1: Organize document or presentation clearly in a manner that promotes understanding and meets the requirements of the assignment.
- 1.2: Develop coherent paragraphs or points so that each is internally unified and so that each functions as part of the whole document or presentation.
- 1.4: Tailor communications to the audience.
- 1.5: Use sentence structure appropriate to the task, message and audience.
- 1.6: Follow conventions of Standard Written English.
- 3.1: Identify numerical or mathematical information that is relevant in a problem or situation.
- 3.2: Employ mathematical or statistical operations and data analysis techniques to arrive at a correct or optimal solution.
- 3.3: Analyze mathematical or statistical information, or the results of quantitative inquiry and manipulation of data.
- 3.4: Employ software applications and analytic tools to analyze, visualize, and present data to inform decision-making.
Explanation & Answer
See attached. Remember to add your name to the footer.
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 w
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
may indicate a reasonable promotion rate for new employees. Is this distribution unimodal or bimodal? Please explain.
The distribution is bimodal. Based on the histogram there appears to be presence of two peaks even though is one is larg
other. The two peaks indicate that the data is bimodal.
2. The line chart, as detailed in your "Graph Charts" Excel spreadsheet, shows sales generally increasing over the years, althou
years were notably lower. Assuming that the sales are linear, please use the Forecast tool to find projected sales for 2019 and
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 ne
projected sales for average, upper, and lower range growth.
Yr
Forecast(Sales)
Lower Confidence Bound(Sales)
2019
60307588.24
44693102.44
Upper Confidence Bound(Sales)
75922074.03
2020
62458985.29
45088920.15
79829050.44
3. The standard deviation provides insight into the distribution of values around the mean. If the standard deviation is small, the
range between the lowest and highest value. That is, values will cluster close to the mean. From your descriptive statistics, describe you
deviations. What does this tell you about the variables?
For salary, the standard deviation is 32350.2. This high standard deviation indicates that there is a high variation in
employees within the company. Similary, the standard deviation in hourly rate is also high.
The standard deviation of years of service is 4.07 and for age is 9.698. The standard deviations are relatively high indicati
spread in ages and years of service among the employees in the company.
The standard deviation of education is 1.7. The low standard deviation indicates that employees within the company do no
different education levels.
4. The company has a keen interest in the educational, race, and gender makeup of its workforce. Its emphasis is on a diverse, d
From your "Graph Charts" spreadsheet, describe your pie chart findings for these characteristics of the workforce. Describe how
the company was meeting expectations on these characteristics.
From your "Graph Charts" spreadsheet, describe your pie chart findings for these characteristics of the workforce. Describe how
the company was meeting expectations on these characteristics.
For education, the company is highly diverse and dynamic as it employs employees with varied education levels. Based on
no employees of any level of education are more than 50%, and there are varied categories of employees based on educat
For race the company is a bit diverse and dynamic. Although there are employees of various races employed, the company
by one race, caucasians, that make up about 61% of the workforce.
For gender the company is not diverse nor dynamic. Majority of the employees , about, 81% are male with females being a
The company can determine if it is meeting expectations by comparing itself with the industry proportions. If its proportio
than industry average then the company can conclude it is meeting expectations
5. The company is conducting an analysis on how many positions to create to keep up with demand. Specifically, it wants to know
number of positions per job title. From your Excel chart, identify the mode of the job title distribution. Describe your findings.
The modal job title is cyber analyst, it has the highest number of employees. Cyber analysts make up about 90% of the wo
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
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
understood. Supporting narrative is helpful. Never simply provide a "raw data" dump. Instead, seek to provide information!
Structure your essay like this:
a.
b.
c.
Write a one-paragraph narrative summary of your findings, describing patterns of interest.
Provide an explanation of the potential relevance of such patterns.
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
The results of the analysis indicate that there is high variation in terms of salaries, age, and years of service
employees. The high variation in salaries may thus be attributed to the high variation in experience among the
company can pride itself as being highly diverse and dynamic in terms of its employment based on education
improvement can be made in hiring based on race and much more improvement can be made based on gender
perfomance of the company is good as the company has increased and stabilized its sales levels over the years
straight like nature of sales levels in recent years indicates that the company is at maturity stage. Based on this,
should consider diversification so as to increase sales and avoid decline.
should consider diversification so as to increase sales and avoid decline.
The relevance of patterns especially those of perfomance shows where the company lies thereby
strengths and areas of weakness. Improvements can then be made on the areas of weakness while still maintaining
strengths. For example, the analysis of diversity and dynamity of the workforce showed that the company was doing
of education level but doing poorly in terms of gender. Another example is that the analysis of sales showed that
at maturity stage thereby necessitating diversification as an improvement measure.
To further determine whether current company results are good or bad, further research would be necessera
determine industry or competitor perfomance. Once competitor perfomance is acquired, the company's results
against the competitors so as to determine if the results of the compnay were better than those of competitors. If
results can be categorized as good, but if worse then the results should be categorized as bad.
n, you will answer five
141,000–$190,000. This
s one is larger than the
hough sales in the first two
and 2020. Hint: An easy
hart and a new sheet with
the more narrow the
describe your standard
ion in salaries among the
igh indicating a huge
mpany do not have very
erse, dynamic workforce.
ow you would determine if
ow you would determine if
s. Based on the pie chart,
d on education level.
he company is dominated
males being about 19%.
ts proportion s are better
o know an estimate of the
% of the workforce.
his is important. While you
e way it should be
any.
answers.)
of service among the
the employees. The
cation level. However,
gender. The overall
years. However, the
on this, the company
eby showing areas of
aintaining the areas of
as doing well in terms
that the company was
necesseray so as to
sults can be compared
ors. If better, then the
Yr
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
Sales
Forecast(Sales)
Lower Confidence Bound(Sales)
$527,000
$15,367,000
$26,481,000
$41,485,000
$46,274,000
$46,578,000
$48,061,000
$47,987,000
$48,057,000
$48,565,000
$48,467,000
$49,846,000
$49,252,000
$50,226,000
$50,844,000
$51,850,000
$51,702,000
$51,702,000
$51,702,000
$53,853,397
$45,435,626
$56,004,794
$44,674,209
$58,156,191
$44,516,792
$60,307,588
$44,693,102
$62,458,985
$45,088,920
Upper Confidence Bound(Sales)
Statistic
Alpha
Beta
Gamma
MASE
SMAPE
MAE
RMSE
Value
0.90
0.00
0.00
0.19
0.02
#######
#######
$90,000,000
$80,000,000
$70,000,000
$60,000,000
$50,000,000
$40,000,000
$30,000,000
$20,000,000
$10,000,000
$0
1999 2000 2001 2002
Sales
$51,702,000
$62,271,168
$67,335,379
$71,795,590
$75,922,074
$79,829,050
Forecast(Sales)
2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020
Forecast(Sales)
Lower Confidence Bound(Sales)
Upper Confidence Bound(Sales)
Workforce Profile Analysis
Section 1: Complete all Columns in Data Set
Format for:
Number
Emp #
1001
1002
1003
1004
1005
1006
1014
1015
1016
1018
1019
1020
1024
1036
1038
1043
1048
1049
1053
1054
1055
1056
1057
1058
Name
Date
mm/dd/yy
Hire Date
03/15/99
12/15/99
09/07/12
06/13/12
10/25/08
08/05/06
06/03/09
07/08/13
09/23/08
01/15/11
05/03/12
05/21/07
08/17/11
09/25/00
08/21/12
06/18/09
09/19/13
02/23/04
05/31/13
09/08/09
06/15/01
06/04/01
9/30/2001
05/06/07
Text
Role
CEO
CIO
Sr Cyber Analyst
Cyber Analyst
Cyber Analyst
Cyber Analyst
IT Staff
Investigator
Cyber Analyst
Acctg/Fin
Cyber Analyst
Cyber Analyst
Cyber Analyst
Cyber Analyst
IT Staff
Forensics Analyst
Physical Security
IT Staff
Cyber Analyst
Cyber Analyst
Controller
Cyber Analyst
Cyber Analyst
Cyber Analyst
5/20/2019
Currency
Salary
$290,210
$279,723
$166,750
$131,924
$142,687
$150,307
$98,818
$81,250
$92,719
$72,836
$82,157
$98,317
$83,005
$107,822
$85,451
$76,168
$61,655
$88,421
$80,241
$85,293
$149,836
$106,663
$107,385
$98,059
Currency
Number
Hrly Rate
$139.52
$134.48
$80.17
$63.43
$68.60
$72.26
$47.51
$39.06
$44.58
$35.02
$39.50
$47.27
$39.91
$51.84
$41.08
$36.62
$29.64
$42.51
$38.58
$41.01
$72.04
$51.28
$51.63
$47.14
Year Ending
12/31/2014
Yrs of Svc
16
15
2
3
6
8
6
1
6
4
3
8
3
14
2
6
1
11
2
5
14
14
13
8
10
1059
1060
1061
1062
1069
1070
1071
1072
1075
1079
1083
1084
1085
1088
1096
1097
1098
1101
1102
1104
1105
1106
1108
1109
1113
1114
1116
1117
1118
1119
1128
1130
1132
1133
Name
02/14/12
02/02/09
03/04/13
07/08/09
02/03/11
08/21/09
01/09/05
08/08/05
01/14/10
10/15/03
08/27/09
4/30/2000
03/15/99
10/03/11
03/06/06
08/14/05
01/13/14
06/08/02
04/08/00
02/01/05
09/06/12
5/9/2002
06/15/07
02/22/07
05/02/12
09/08/00
12/30/08
04/05/00
05/27/09
08/15/12
06/07/10
03/15/99
07/10/09
02/12/09
Acctg/Fin
Cyber Analyst
Acctg/Fin
Cyber Mgr
Cyber Software Engineer
Cyber Analyst
Cyber Software Engineer
Cyber Software Engineer
Cyber Analyst
Investigator
Cyber Analyst
Cyber Analyst
Public and Business Office Team
Sr Forensics Mgr
Cyber Analyst
Cyber Analyst
Cyber Software Engineer
Investigator
IT Staff
Cyber Analyst
Investigator
Cyber Analyst
Forensics Analyst
Cyber Analyst
Cyber Analyst
Sr Cyber Investigator
Cyber Analyst
Quality Assurance
Forensics Analyst
Cyber Analyst
Cyber Mgr
Cyber Analyst
Cyber Analyst
Public and Business Office Team
5/20/2019
$126,589
$99,931
$112,238
$162,803
$179,938
$95,498
$166,238
$167,019
$84,317
$71,459
$85,396
$108,771
$135,671
$173,974
$100,502
$110,571
$162,245
$125,994
$88,900
$112,450
$92,633
$115,744
$127,403
$99,201
$82,124
$151,695
$112,235
$111,526
$136,360
$121,262
$174,104
$79,438
$85,822
$93,337
$60.86
$48.04
$53.96
$78.27
$86.51
$45.91
$79.92
$80.30
$40.54
$34.36
$41.06
$52.29
$65.23
$83.64
$48.32
$53.16
$78.00
$60.57
$42.74
$54.06
$44.54
$55.65
$61.25
$47.69
$39.48
$72.93
$53.96
$53.62
$65.56
$58.30
$83.70
$38.19
$41.26
$44.87
3
6
2
5
4
5
10
9
5
11
5
15
16
3
9
9
1
13
15
10
2
13
8
8
3
14
6
15
6
2
5
16
5
6
11
1136
1138
1139
1140
1141
1142
1145
1146
1147
1150
1153
1156
1167
1169
1175
1180
1185
1186
1188
1189
1190
1191
1196
1198
1201
1202
1210
1215
1216
1218
1220
1220
1224
1226
Name
12/14/11
10/18/10
4/12/2008
07/10/09
06/18/09
06/17/09
02/16/09
11/05/09
09/15/04
04/26/13
02/05/12
05/21/11
11/15/11
01/22/09
09/25/02
01/24/05
04/16/09
08/14/05
05/16/09
02/12/10
5/9/2002
8/31/1999
09/08/09
09/17/10
08/01/08
03/15/99
09/10/06
05/06/07
07/12/06
07/23/09
10/16/02
04/15/00
09/23/13
06/02/07
Cyber Analyst
Forensics Analyst
Cyber Analyst
Cyber Analyst
Forensics Analyst
Cyber Analyst
Forensics Analyst
Cyber Analyst
Cyber Analyst
Cyber Analyst
Sr Cyber Investigator
Cyber Analyst
Cyber Analyst
Cyber Analyst
Cyber Analyst
Cyber Analyst
Cyber Analyst
Cyber Analyst
Cyber Software Engineer
Public and Business Office Team
Cyber Analyst
Cyber Analyst
Cyber Analyst
Forensics Analyst
Cyber Analyst
Acctg/Fin
Cyber Analyst
Cyber Software Engineer
Physical Security
Malware reverse engineer
Cyber Analyst
Sr Cyber Investigator
Cyber Software Engineer
Forensics Analyst
5/20/2019
$82,523
$124,017
$94,204
$85,934
$75,999
$86,901
$75,131
$74,680
$103,327
$80,808
$154,843
$83,395
$82,766
$90,871
$104,886
$102,469
$87,914
$100,636
$143,198
$110,520
$105,716
$59,147
$135,327
$123,484
$143,003
$125,123
$120,133
$166,673
$72,654
$164,504
$114,368
$143,377
$150,886
$127,435
$39.67
$59.62
$45.29
$41.31
$36.54
$41.78
$36.12
$35.90
$49.68
$38.85
$74.44
$40.09
$39.79
$43.69
$50.43
$49.26
$42.27
$48.38
$68.85
$53.13
$50.83
$28.44
$65.06
$59.37
$68.75
$60.16
$57.76
$80.13
$34.93
$79.09
$54.98
$68.93
$72.54
$61.27
3
4
7
5
6
6
6
5
10
2
3
4
3
6
12
10
6
9
6
5
13
15
5
4
6
16
8
8
8
5
12
15
1
8
12
1227
1228
1232
1233
1237
1246
1248
1254
1255
1257
1258
1259
1261
1262
1265
1266
1267
1269
1272
1276
1279
1280
1285
1288
1289
1290
1292
1295
1296
1297
1298
1299
1300
1301
Name
05/03/00
04/25/06
07/12/10
05/31/13
08/08/07
07/27/00
07/25/09
02/02/09
03/14/09
08/09/09
03/11/10
07/09/03
04/13/13
06/02/09
03/04/13
07/08/11
2/5/2008
03/14/07
09/18/10
05/04/11
10/20/07
08/11/14
04/04/11
03/25/10
09/05/11
10/22/06
05/13/01
07/08/12
05/05/00
09/15/10
09/23/11
06/06/05
04/08/09
07/10/09
Investigator
Cyber Analyst
Physical Security
Cyber Analyst
Cyber Analyst
Sr Cyber Investigator
Forensics Analyst
Cyber Analyst
Forensics Analyst
Cyber Software Engineer
Cyber Analyst
Sr Cyber Investigator
Cyber Analyst
Sr Cyber Investigator
Acctg/Fin
Cyber Software Engineer
Cyber Analyst
Malware reverse engineer
IT Mgr
IT Staff
Cyber Analyst
Acctg/Fin
Advertising
Public and Business Office Team
Forensics Analyst
Cyber Analyst
Cyber Analyst
Logistics
Forensics Analyst
Investigator
Quality Assurance
Sr Cyber Investigator
Logistics Mgr
Cyber Software Engineer
5/20/2019
$85,234
$120,320
$67,456
$80,577
$86,904
$142,315
$76,156
$79,755
$96,621
$135,463
$74,232
$130,234
$80,165
$148,014
$109,632
$115,825
$94,747
$176,577
$120,515
$85,469
$96,057
$79,442
$103,064
$120,708
$97,943
$100,078
$106,815
$85,670
$138,901
$112,765
$123,487
$169,628
$175,286
$80,637
$40.98
$57.85
$32.43
$38.74
$41.78
$68.42
$36.61
$38.34
$46.45
$65.13
$35.69
$62.61
$38.54
$71.16
$52.71
$55.69
$45.55
$84.89
$57.94
$41.09
$46.18
$38.19
$49.55
$58.03
$47.09
$48.11
$51.35
$41.19
$66.78
$54.21
$59.37
$81.55
$84.27
$38.77
15
9
4
2
7
14
5
6
6
5
5
11
2
6
2
3
7
8
4
4
7
0
4
5
3
8
14
2
15
4
3
10
6
5
13
1302
1303
1304
1308
1314
1315
1316
1317
1318
1319
1320
1323
1325
1329
1332
1339
1341
134...
Review
Review
