(35%) The table on the next page represents data for the profits, sales, size and number

Question: Question 5: (35%)

The table on the next page represents data for the profits, sales, 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 answering the following questions.

1. Summarise the distribution of profits of the twenty branches and comment on the results?

2. Is there evidence that the average number of lines stocked per store is significantly different from 78?

3. If you divide the branches in two groups with, one of branches with sales above £150,000, and the other with sales below that value, is there a significant difference between the profits of the groups?

4. Based on this sample, provide a 99% confidence interval, and comment on the outcome, for the profits of the twenty branches.

5. Is there evidence of association between the profit and the other variables?

6. Develop three regression models to predict the profit based upon each of the other factors (variables) individually. Which of these is best? What are the limitations of your best model? How can you improve this analysis?

Cass Business School			QM 2009/10
Profit (£000s)	Sales (£000s)	Size (000s sq. ft.)	Lines
42.13	748.82	6.0	150
6.32	140.78	1.4	75
38.47	702.11	5.0	170
-0.32	41.54	1.0	75
3.65	96.85	1.2	75
7.77	166.93	1.5	75
4.31	109.05	1.3	75
4.53	263.92	1.1	80
-2.69	50.84	1.1	75
3.22	90.08	1.2	75
9.03	190.59	1.4	80
-2.59	91.75	1.2	75
6.39	141.57	1.4	80
24.39	377.04	3.5	160
13.92	198.69	1.5	100
2.13	62.78	1.3	75
17.48	265.28	2.1	110
7.21	91.80	1.3	85
15.62	231.60	2.5	120
33.61	548.31	4.5	200