Question 1:
A company has been collecting data on its quarterly sales over the last six years, which has been summarised in attached excel file
Required:
a) Represent the Sales data graphically and briefly discuss the outcome.
b) Calculate a 3-point moving average and represent graphically. Compare and discuss with the outcome with the outcome in part a).
c) Calculate the seasonally adjusted series, explain any assumptions made, represent the new series graphically and compare with the graph shown in part a)
d) Discuss the meaning and justification for conducting Exponential Smoothing on a Time Series.
Question 2:
The table below represents data for the profits, sales, average shop size and number of product lines sold by the 20 branches of a retailing company. You have been asked to analyse the data, using the Data Analysis tool in Excel, and make recommendations, including the following:
a) Summarise the distribution of profits of the twenty branches and comment on the results, including identification of any particularly good or poorly performing branches.
b) Identify whether there is evidence that the average number of lines stocked per branch is significantly different from 140.
c) Identify whether there is a significant difference between the profits of two groups of branches, split by the level of sales, with the threshold being £600,000.
d) Based on this sample, provide a 99% confidence interval, and comment on the outcome, for the profits of the twenty branches.
e) Identify whether there is a relationship between the profits and the size of the stores, and if so what is the direction of the relationship and how strong it is.
|
Profit (£000s)
|
Sales (£000s)
|
Size (000s sq. ft.)
|
Lines
|
|
77.5
|
613.9
|
3.2
|
80
|
|
91
|
217.4
|
4.3
|
200 |
|
20.7
|
900.9
|
3.1
|
164
|
|
40.8
|
673.4
|
1.5
|
150
|
|
45.8
|
424.7
|
3.2
|
69
|
|
41.1
|
542.2
|
1.8
|
128
|
|
47.5
|
564.6
|
2.5
|
75
|
|
80.4
|
662.1
|
3.1
|
182
|
|
16.5
|
583.6
|
4.2
|
126
|
|
22.3
|
720.2
|
0.6
|
164
|
|
40.8
|
881.5
|
1.8
|
145
|
|
68.1
|
227.7
|
0.8
|
130
|
|
17.7
|
807.4
|
3.8
|
154
|
|
66.2
|
656.4
|
0.3
|
124
|
|
31.3
|
632.8
|
2.3
|
142
|
|
15
|
548.5
|
5 |
178
|
|
67.8
|
533.6
|
1.5
|
173
|
|
55
|
147.5
|
1.7
|
199
|
|
8.6
|
311.4
|
3.8
|
98
|
|
16.5
|
450.1
|
4.6
|
148
|
Attachment:- Data File.xls