This coursework tests your basic sample analysis skills, using spreadsheet software. You may use Minitab or Excel at your preference. Your answers are to be presented in an essay/report format, for which you will use a word processor. In writing your report, please:
1) state and explain all assumptions, on which your answers are based;
2) clearly indicate your answer/recommendations
3) support any answers with the appropriate calculations to arrive at the answer
4)no evidence of use of excel will result in a fail mark for this assignment and therefore the coursework component of the module;
5) include selected printouts of formulae underlying computed values. Failure to demonstrate you have created appropriate formulations on excel will be severely penalised. Despite the fact that you will be submitting the Excel file as well, your report is a stand-alone document, meaning a reader should not be required to look at the Excel file to understand your analysis, findings and recommendations
6) please note that adequate usage of the excel calculations in the report is important. This means that the key data/findings need to be included in the report and appropriate referencing needs to be done, i.e. the relevant cell/table/range in the relevant tab of the excel file mentioned at the point of the report when it should be consulted.
The report will have a maximum of 5 pages (including any Appendixes; penalties will be applied for longer submissions – you are required to develop your judgement on what is and isn’t important). The file contacting the data for this coursework is posted on Moodle. There are two excel sheets in the file, use the data contained in the first sheet to answer question 1 and use the data contained in the second sheet to answer question 2.
Question 1.
A group of 44 students was asked to guess, to the nearest metre, the width of the lecture hall in which they were sitting. The true width of the hall was 13.1 metres. The students’ guesses are reported in the first excel spreadsheet in the file “Coursework dataset”. Answer the following questions:
a) Create a frequency table of the values of the guesses. Also, include relative
frequencies (percentages) and cumulative percentages in the table. Comments on your findings.
b) Calculate the sample mean, median and standard deviation of the guesses.
Comment on the characteristics of the sample of guesses.
c) Compute the sample skewness and kurtosis of the guesses and comment on the characteristic of the sample that you can learn from these statistics.
Question 2.
The second spreadsheet of the “Coursework dataset” file contains a sample of wealth data for the UK population. The data is presented in a grouped format. Please answer the following questions regarding this sample:
a) Discuss what would be the best way to present the data graphically.
b) Construct a histogram of wealth and comment on wealth distribution.
c) Compute the relevant measures of central tendency (hint: you’ll need to make an assumption regarding the size of the final class) and a measure of variation for this data.
d) Discuss and justify which of the metrics of central tendency above would be more appropriate to describe the data.
e) Compute the first and the third quartile of wealth and construct a boxplot. Comment on your results.
f) Compute the sample skewness and kurtosis and comment on the features of the sample distribution. Is it symmetric?
g) Calculate the proportion of population with a wealth above £100,000.
h) Calculate the probability that an individual selected at random will aheva wealth of no more than £ 50,000.
Question 3.
The third spreadsheet of the “Coursework dataset” file contains data on the salaries of employees in a bank, as well as some other variables which might have an impact on the employee’s salaries. Answer all the following questions:
a) Draw a histogram of SALARY. What does it tell you?
b) For each variable in the data set explain which type of variable it is (categorical, numerical…)
c) Calculate the sample mean, median, standard deviation and first and third quartiles of all the variables in column B to F. Also, draw boxplots of the variables side by side. What can you infer?
d) Draw a scatterplot with SALARY on the horizontal (X) axis and one of the other variables on the vertical (Y) axis. Do this for all the variables in columns B to G. Comment on your findings.
e) Prepare a summary table with the correlations between the variables and discuss the extent of these correlations.
You will need to submit a Word document with the report (see instructions above) and an Excel file with the calculations.
Question weighting:
Question 1 = 10%
Question 2 = 40%
Question = 50%.
Employee EducLev JobGrade YrsExper Age YrsPrior Salary Gender
1 3 1 3 26 1 $32,000 Male
2 1 1 14 38 1 $39,100 Female
3 1 1 12 35 0 $33,200 Female
4 2 1 8 40 7 $30,600 Female
5 3 1 3 28 0 $29,000 Male
6 3 1 3 24 0 $30,500 Female
7 3 1 4 27 0 $30,000 Female
8 3 1 8 33 2 $27,000 Male
9 1 1 4 62 0 $34,000 Female
10 3 1 9 31 0 $29,500 Female
11 3 1 9 34 2 $26,800 Female
12 2 1 8 37 8 $31,300 Female
13 2 1 9 37 0 $31,200 Female
14 2 1 10 58 6 $34,700 Female
15 3 1 4 33 0 $30,000 Female
16 3 1 3 27 0 $31,000 Female
17 3 1 6 30 0 $27,000 Female
18 2 1 8 37 9 $29,600 Female
19 3 1 5 44 6 $32,600 Female
20 2 1 4 29 3 $29,600 Female
21 3 1 4 36 2 $29,500 Female
22 2 1 3 28 3 $31,000 Male
23 1 1 5 45 0 $28,500 Female
24 2 1 3 33 4 $26,700 Male
25 3 1 3 24 1 $30,750 Male
26 3 1 3 27 1 $29,500 Male
27 2 1 16 60 6 $42,200 Female
28 1 1 13 48 0 $37,600 Female
29 1 1 12 40 6 $34,000 Female
30 2 1 4 33 7 $33,000 Female
31 1 1 7 35 4 $28,760 Female
32 1 1 11 44 0 $35,400 Female
33 3 1 3 43 8 $31,000 Male
34 2 1 18 46 2 $38,800 Female
35 2 1 14 42 0 $34,300 Female
36 1 1 19 47 0 $35,000 Female
37 3 1 3 25 2 $34,600 Female
38 2 1 2 30 4 $28,500 Female
39 1 1 11 40 0 $29,500 Female
40 3 1 3 26 2 $30,500 Male
41 3 1 5 32 1 $34,200 Female
42 1 1 15 51 0 $43,600 Female
43 5 1 7 35 0 $33,500 Female
44 3 1 12 37 1 $33,000 Female
45 1 1 18 44 0 $45,300 Female
46 1 1 17 53 3 $38,800 Male
47 1 1 10 40 0 $29,900 Female
48 3 1 5 51 10 $31,200 Male
49 1 1 15 42 0 $34,000 Female
50 2 1 2 53 0 $30,450 Female
51 1 1 3 58 3 $35,500 Male
52 1 1 4 44 10 $34,000 Female
53 2 1 7 31 0 $29,100 Female
54 1 1 8 64 0 $29,650 Female
55 3 1 15 47 1 $29,200 Female
56 3 1 9 37 0 $29,800 Female
57 2 1 16 46 0 $33,500 Female
58 1 1 8 55 0 $34,000 Female
59 1 1 9 39 0 $29,600 Female
60 3 1 18 51 0 $34,000 Female
61 2 2 3 37 8 $37,250 Female
62 2 2 6 30 3 $33,000 Male
63 3 2 4 26 0 $28,600 Female
64 5 2 5 41 1 $36,000 Female
65 3 2 4 34 4 $37,300 Female
66 2 2 7 57 4 $29,900 Male
67 1 2 11 53 8 $31,500 Female
68 3 2 5 32 4 $41,400 Female
69 1 2 17 44 5 $32,740 Female
70 3 2 3 25 1 $33,500 Male
71 1 2 5 31 9 $32,000 Female
72 1 2 9 50 0 $30,800 Female
73 5 2 3 47 3 $42,000 Female
74 3 2 4 35 0 $34,000 Male
75 2 2 16 43 0 $32,500 Female
76 2 2 9 46 10 $31,700 Female
77 5 2 3 35 0 $36,500 Male
78 3 2 4 22 0 $33,000 Male
79 2 2 8 58 0 $31,200 Female
80 5 2 8 40 0 $34,000 Female
81 3 2 6 30 0 $33,000 Female
82 5 2 4 29 4 $33,900 Female
83 1 2 3 31 9 $39,000 Female
84 2 2 12 52 18 $34,920 Male
85 5 2 3 33 5 $39,000 Male
86 1 2 8 49 0 $34,000 Female
87 2 2 6 34 7 $31,900 Female
88 5 2 3 26 1 $37,000 Male
89 5 2 4 28 0 $34,000 Male
90 5 2 3 35 2 $36,400 Female
91 1 2 15 47 1 $38,200 Female
92 1 2 15 51 0 $35,300 Female
93 3 2 3 26 2 $34,500 Male
94 3 2 12 33 0 $30,500 Female
95 4 2 2 27 2 $30,000 Male
96 5 2 8 34 0 $37,300 Female
97 4 2 5 29 0 $40,200 Female
98 3 2 5 27 0 $35,500 Male
99 1 2 11 43 0 $35,000 Female
100 3 2 4 36 3 $38,000 Female
101 1 2 9 38 0 $35,300 Female
102 2 2 14 60 0 $34,100 Female
103 3 3 4 43 5 $43,200 Female
104 2 3 15 48 5 $36,100 Female
105 5 3 7 32 3 $34,600 Female
106 3 3 5 31 0 $36,000 Male
107 5 3 7 29 2 $36,200 Female
108 3 3 7 35 0 $37,500 Female
109 3 3 4 37 12 $41,000 Female
110 2 3 10 43 0 $35,600 Female
111 3 3 5 33 5 $39,800 Female
112 4 3 11 58 4 $41,300 Female
113 3 3 9 44 7 $42,500 Female
114 3 3 4 37 8 $45,800 Female
115 5 3 5 48 6 $34,900 Female
116 5 3 4 26 0 $41,500 Male
117 3 3 5 25 0 $38,000 Female
118 4 3 6 38 0 $35,000 Female
119 3 3 6 41 0 $40,000 Female
120 3 3 5 29 0 $36,000 Male
121 2 3 9 59 0 $33,700 Female
122 2 3 5 29 4 $36,300 Male
123 3 3 3 27 2 $38,000 Female
124 5 3 4 30 0 $39,500 Female
125 2 3 7 34 5 $36,300 Female
126 3 3 8 35 2 $32,500 Female
127 2 3 12 50 6 $37,000 Female
128 5 3 3 33 1 $32,600 Female
129 3 3 4 26 0 $36,000 Female
130 5 3 3 36 0 $35,000 Female
131 5 3 3 33 5 $43,600 Female
132 3 3 8 47 0 $33,800 Female
133 1 3 21 51 0 $35,300 Female
134 1 3 16 42 6 $42,400 Female
135 5 3 5 31 0 $39,500 Male
136 2 3 25 62 10 $43,500 Female
137 5 3 6 46 1 $42,000 Male
138 3 3 21 60 9 $40,300 Female
139 4 3 6 43 5 $44,000 Male
140 1 3 25 53 2 $40,660 Female
141 3 3 13 38 1 $39,700 Female
142 5 3 6 39 5 $45,000 Female
143 5 3 7 35 0 $43,900 Female
144 4 3 8 40 3 $38,000 Female
145 5 3 5 32 3 $39,020 Female
146 5 4 5 33 3 $44,500 Male
147 5 4 4 30 1 $41,000 Male
148 5 4 6 37 3 $44,000 Male
149 5 4 6 30 0 $44,000 Male
150 5 4 5 32 4 $42,500 Female
151 5 4 7 37 3 $40,260 Female
152 5 4 5 29 1 $44,500 Male
153 1 4 13 50 9 $35,500 Female
154 5 4 6 29 0 $42,500 Male
155 5 4 7 32 0 $44,000 Female
156 5 4 6 31 2 $45,000 Male
157 2 4 15 47 4 $44,400 Female
158 3 4 17 44 0 $38,000 Female
159 5 4 4 27 0 $41,800 Male
160 1 4 23 55 0 $45,500 Male
161 3 4 5 52 4 $42,500 Male
162 5 4 3 50 12 $44,000 Female
163 3 4 19 59 8 $54,300 Female
164 3 4 26 47 0 $44,800 Female
165 3 4 6 43 4 $47,000 Male
166 5 4 15 41 0 $43,800 Female
167 1 4 12 39 4 $48,000 Female
168 5 4 9 39 0 $42,700 Female
169 3 4 14 40 1 $48,500 Female
170 3 4 16 49 0 $42,000 Female
171 2 4 16 53 1 $45,500 Female
172 3 4 11 37 0 $44,500 Female
173 2 4 13 40 2 $51,200 Female
174 5 5 7 34 0 $47,500 Male
175 5 5 8 37 0 $44,500 Female
176 5 5 8 31 0 $47,000 Male
177 5 5 6 41 10 $47,000 Male
178 3 5 17 46 4 $43,100 Female
179 5 5 8 37 2 $49,000 Male
180 5 5 8 33 0 $48,500 Male
181 3 5 8 35 5 $45,000 Female
182 5 5 16 49 5 $52,500 Female
183 5 5 6 33 2 $47,500 Male
184 5 5 7 31 0 $48,000 Male
185 5 5 8 49 4 $46,500 Male
186 5 5 12 40 2 $61,500 Female
187 5 5 9 37 2 $50,000 Female
188 5 5 12 46 2 $61,800 Female
189 4 5 16 43 0 $43,000 Female
190 5 5 11 36 1 $47,000 Male
191 5 5 9 37 6 $58,500 Female
192 5 5 16 40 7 $55,000 Male
193 3 5 24 54 3 $57,000 Male
194 5 5 17 57 1 $57,000 Male
195 5 6 14 49 0 $60,000 Male
196 3 6 13 41 0 $60,000 Male
197 5 6 19 59 4 $59,000 Male
198 5 6 12 51 0 $60,000 Male
199 5 6 20 45 0 $65,000 Male
200 5 6 20 56 1 $52,000 Male
201 5 6 22 57 0 $58,000 Male
202 4 6 21 53 0 $60,000 Male
203 5 6 39 65 0 $74,000 Male
204 3 6 34 60 0 $95,000 Male
205 5 6 36 61 0 $97,000 Male
206 5 6 32 62 0 $88,000 Male
207 5 6 35 59 0 $94,000 Male
208 5 6 33 62 0 $30,000 Female
TAKE ADVANTAGE OF OUR PROMOTIONAL DISCOUNT DISPLAYED ON THE WEBSITE AND GET A DISCOUNT FOR YOUR PAPER NOW!