MSc - Data Analytics for Managers - MMSCDM5A0323

Lab 1 (Individual)

Instructions: Students will answer the following questions in a software of their choosing (e.g., Excel, R,
Stata, SPSS, etc.). Students without previous experience in these software applications should use
Microsoft Excel. Please submit the completed lab assignment by the end of class. The instructor will
select and grade a single question from the lab assignment.

1) A major United States consulting company has 60 offices worldwide. The following are the revenues,
in million dollars, for each of the offices for the last fiscal year. The average annual operating cost per
office for these, including salaries and all operating expenses, is $36 million.

As a result of intense competition from other consulting firms and declining markets, management is
considering closing those offices whose annual revenues are less than the average operating cost.

DATA:

49.258 43.660 32.190 39.220 35.150

29.532
34.410 54.257 39.590 42.920 33.658
37.125
38.850 28.120 60.120 37.258 31.820
25.324
41.070 37.740 41.070 54.653 36.260
29.584
42.920 59.250 46.250 24.050 27.750
62.543
38.110 47.730 34.040 39.590 69.352
58.965
46.250 34.410 42.653 46.990 38.850
46.235
38.110 41.070 35.520 35.890 53.650
59.210
50.690 24.790 42.550 31.080 42.365
20.210
50.690 41.440 27.010 20.030 46.250
33.56
49.258 43.660 32.190 39.220 35.150
29.532
34.410 54.257 39.590 42.920 33.658
37.125
38.850 28.120 60.120 37.258 31.820
25.324
41.070 37.740 41.070 54.653 36.260
29.584
42.920 59.250 46.250 24.050 27.750
62.543
38.110 47.730 34.040 39.590 69.352
58.965
46.250 34.410 42.653 46.990 38.850
46.235
38.110 41.070 35.520 35.890 53.650
59.210
50.690 24.790 42.550 31.080 42.365
20.210
50.690 41.440 27.010 20.030 46.250
33.56
49.258 43.66 32.19 39.22 35.15 29.532
34.41 54.257 39.59 42.92 33.658 37.125
38.85 28.12 60.12 37.258 31.82 25.324
41.07 37.74 41.07 54.653 36.26 29.584
42.92 59.25 46.25 24.05 27.75 62.543
38.11 47.73 34.04 39.59 69.352 58.965
46.25 34.41 42.653 46.99 38.85 46.235
38.11 41.07 35.52 35.89 53.65 59.21
50.69 24.79 42.55 31.08 42.365 20.21
50.69 41.44 27.01 20.03 46.25 33.56
Questions:

(1a) Worldwide revenues represent what type of data?

Ans: Numeral data
(1b) What are the average revenue and margin per office for the consulting firm before any closure?
What are the sample and population variance for just worldwide revenues?
 Sample Variance = 115.1693
 population variance= 113.2172

(1c) How many offices have revenues lower than $36 million and thus risk being closed?
Offices have revenues lower than $36 million is
Ans: 21

(1d) If management makes the decision to close that number of offices determined in Question 1c
above, estimate the new average revenue per office.
Ans: The new average revenue per office is 46.276

(1e) Create a bar chart of showing the difference between revenue and average operating costs for each
location. (Hint: first calculate the difference, then created the bar chart).

Revenue Between Average operating costs

40

30

20

10

0
1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52 55 58

-10

-20

2) An engineering firm uses senior engineers, junior engineers, computing services, and assistants on its
projects. The billing rate to the customer for these categories is given in the table below together with
the hours used on a recent design project.

Category Senior Junior Computing

Assistants
engineers engineers services
Billing rate ($/hour) 85.00 45.00 35.00
22.00
Project hours 23,000 37,000 19,000
9,500
Senior Junior Computing
Category Engineers Engineers Services Assistants
Billing rate
($/hour) 85 45 35 22
Project hours 23,000 37,000 19,000 9,500

(2a) If this data was used for quoting on future projects, what would be the correct average billing rate
used to price a project (Hint: Use a weighted average)?
Ans: 50.77966

(2b) If the estimate for performing a future job were 110,000 hours, what would be the billing amount
to the customer?
Ans: 5585763

(2c) What would be the billing rate if the straight arithmetic average were used?
Ans : 46.75

3) The table below gives the prices in Euros for various items in the European Union.

Stamp
Milk Renault Big Compact Can of
for
(1L) Mégane Mac disc (CD) Coke
postcard
 Austria 0.86 15,650 2.5 0.51 19.95 0.5
Belgium 0.84 13,100 2.95 0.47 21.99 0.47
Finland 0.71 21,700 2.9 0.6 21.99 1.18
France 1.11 15,700 3 0.48 22.71 0.4
Germany 0.56 17,300 2.65 0.51 17.99 0.35
Greece 1.04 16,875 2.11 0.59 15.99 0.51
Ireland 0.83 17,459 2.54 0.38 21.57 0.7
Italy 1.34 14,770 2.5 0.41 14.98 0.77
Luxembourg 0.72 12,450 3.1 0.52 17.5 0.37
The
Netherlands 0.79 16,895 2.6 0.54 22 0.45
Portugal 0.52 20,780 2.24 0.54 16.93 0.44
Spain 0.69 14,200 2.49 0.45 16.8 0.33

(3a) Determine the maximum, minimum, range, average, median, and sample standard deviation using
the sample values for each of the items listed.
Maximu Minimu Rang Averag Media
Items SD
m m e e n
0.233
milk 1.34 0.52 0.82 0.8342 0.81
6
Renault 2775.
21,700 12,450 9,250 16,407 16,288
Mégane 2
0.304
Big Mac 3.1 2 1 2.6317 2.57
3
Stamp for 0.066
0.6 0.38 0.22 0.5 0.51
postcard 2
 Compact 2.784
22.71 14.98 7.73 19.2 18.97
disc (CD) 5
0.241
Can of Coke 1.18 0.33 0.85 0.5392 0.46
6

(3b) What observations might you draw from these characteristics?

Milk Prices
Italy having the highest price in milk of 1.34 and lowest of Portugal od 0.52
Car Prices
Luxembourg having the lowest Renault Mégane of 12,450 while the highest Finland of 21,700.
Big Mac
Greece having the highest price at 3.1 and Luxembourg the lowest at 2.11
Stamp for postcard
Greece having the highest price at 0.6 and Luxembourg the lowest at 0.38
Compact disc (CD)
France having the highest price at 22.71 and Italy the lowest at 0.38
Can of Coke
Finland having the highest price at 1.18 and Spain the lowest at 0.38

4) A landscape gardener employs several students to help him with his work. One morning they come to
work and take their gloves from a communal box. This box contains only five left-handed gloves and
eight right-handed gloves.

(4a) If two gloves are selected at random from the box, without replacement, what is the probability
that both gloves selected will be right-handed?
Ans: 35.90

(4b) If two gloves are selected at random from the box, without replacement, what is the probability
that a pair of gloves will be selected? (One glove is right-handed and one glove is left-handed.)
Ans :51.28

(4c) If three gloves are selected at random from the box, with replacement, what is the probability that
all three are left-handed?
Ans:5.69

(4d) If two gloves are selected at random from the box, with replacement, what is the probability that
both gloves selected will be right-handed?
Ans:37.87

(4e) If two gloves are selected at random from the box, with replacement, what is the probability that a
correct pair of gloves will be selected?
Ans:47.34
5. Study Groups
Situation
5) A professor at a Business School in Europe teaches a popular programme in supply chain
management. In one particular semester there are 80 participants signed up for the class. When the
participants register they are asked to complete a questionnaire regarding their sex, age, country of
origin, area of experience, marital status, and the number of children. This information helps the
professor organize study groups, which are balanced in terms of the participant’s background. This
information is contained in the table below. The professor teaches the whole group of 80 together and
there is always 100% attendance. The professor likes to have an interactive class and he always asks
questions during his class.

When you have a database with this type of information, there are many ways to analyse the
information depending on your needs. The following gives some suggestions, but there are several ways
of interpretation.

(5a) What is the probability that if the professor chooses a participant at random then
that person will be:
(i) From Britain?
Ans:0.1875
(ii) From Portugal?
Ans:0.075
(iii) From the United States?
Ans 0.075
(iv) Have experience in Finance?
Ans 0.075
(v) Have experience in Marketing?
Ans:0.2625
(vi) Be from Italy?
Ans :0
(vii) Have three children?
Ans :0.2875
(viii) Be female?
Ans:0.475
(ix) Is greater than 30 years in age?
Ans:0.2
(x) Are aged 25 years?
Ans:0.0225
(xi) Be from Britain, have experience in engineering, and be single?
 Ans :0.0125
(xii) From Europe?
Ans:0.7
(xiii) Be from the Americas?
Ans:0.15
(xiv) Be single?
Ans:0.4625

(5b) Given that a participant is from Britain then, what is the probability that that the person will:
(i) Have experience in engineering?
Ans:0.333
(ii) Have experience in purchasing?
Ans:0

(5c) Given that a participant is interested in finance, then what is the probability that person is from an
Asian country?
Ans: 0

(5d) Given that a participant has experience in marketing, then what is the probability
that person is from Denmark?
Ans :0.14285

(5e) What is the average number of children per participant?

Ans :1.7625
DATA:

Number Sex Age Country Experience Marital Children

1M 21 United States Engineering Married 0
2F 25 Mexico Marketing Single 2
3F 27 Denmark Marketing Married 0
4F 31 Spain Engineering Married 2
5F 23 France Production Married 0
6M 26 France Production Single 3
7M 25 Germany Engineering Single 0
8F 29 Canada Production Single 3
9M 32 Britain Engineering Married 2
10 F 21 Britain Finance Single 1
11 M 26 Spain Engineering Married 2
12 M 28 United States Finance Single 0
13 F 27 China Engineering Married 3
14 M 35 Germany Production Married 0
15 F 21 France Engineering Married 2
16 F 26 Germany Marketing Married 3
17 F 25 Britain Production Married 3
18 F 31 China Production Single 4
19 M 22 Britain Production Married 2
20 M 20 Britain Marketing Single 3
21 F 26 Germany Engineering Married 2
22 M 28 Portugal Engineering Single 1
23 M 29 Germany Engineering Single 0
24 M 35 Luxembourg Production Married 0
25 M 41 Germany Finance Married 3
26 F 25 Britain Marketing Single 0
27 M 23 Britain Engineering Married 3
28 F 23 Denmark Production Single 3
29 M 25 Denmark Marketing Single 2
30 F 26 Norway Finance Married 3
31 F 22 France Marketing Single 2
32 F 26 Portugal Engineering Married 3
33 F 28 Spain Engineering Single 3
34 M 24 Germany Production Married 2
35 M 23 Britain Engineering Single 1
36 M 25 United States Production Married 0
37 M 26 Canada Engineering Married 0
38 F 24 Canada Marketing Single 2
39 F 25 Denmark Marketing Single 0
40 M 28 Norway Engineering Married 3
47 F 25 France Marketing Single 0
48 F 26 Britain Marketing Married 3
49 M 24 Germany Production Single 2
50 F 21 Taiwan Engineering Married 1
51 F 31 China Engineering Single 3
52 F 35 Britain Marketing Married 0
53 M 38 United States Marketing Married 5
54 F 39 China Engineering Single 2
55 M 23 Portugal Purchasing Married 3
56 F 25 Indonesia Engineering Married 2
57 M 26 Portugal Purchasing Married 2
58 M 23 Britain Marketing Single 0
59 M 25 China Purchasing Married 3
60 M 26 Canada Engineering Single 0
61 F 24 Mexico Purchasing Married 3
62 M 25 China Engineering Single 0
63 F 28 France Production Married 1
64 M 31 United States Marketing Single 2
65 F 32 Britain Marketing Married 3
66 F 25 Germany Engineering Single 0
67 M 25 Spain Purchasing Married 2
68 M 25 Portugal Engineering Single 1
69 M 26 Luxembourg Production Single 3
70 F 24 Taiwan Marketing Single 0
71 M 25 Luxembourg Production Married 1
72 F 26 Britain Engineering Married 2
73 M 28 United States Engineering Single 3
74 F 25 France Engineering Married 0
75 M 26 France Production Single 0
76 F 31 Germany Marketing Single 0
77 M 40 France Engineering Married 3
78 F 25 Spain Marketing Single 2
79 M 26 Portugal Purchasing Married 1
80 M 23 Taiwan Production Single 1