Question

Bianca Pascoe has provided the following statement as background and advice in terms of the recommendations you can provide to her organisation.

The number of goods sold by “The Local” is in excess of one million per year with deliveries being about

40% of that figure. The amount of goods sold has decreased marginally in recent years. “The Local” is wholly owned but Bianca and her staff have a standard of living to

maintain so there is some pressure to raise overall sales whilst keeping costs, particularly

delivery costs, in check.   

Bianca continues: It is your job to use the sample data from last year’s overall sales to do

some statistical analyses and interpretations, investigating what the current overall sales of

the business are and providing insights that will guide future business decisions. She

specifically asks: Can you put together a statistical report about the overall sales and

deliveries of “The Local”?

1. Provide a table in which you summarise complete descriptive statistics on ‘Overall sales’

and ‘deliveries’ of the goods represented by your sample data set (including but NOT limited to measures

of central location, measures of variability, etc.). What insights do these statistics provide? Additionally,

please state the coefficient of variation for ‘sales’ and ‘deliveries’ for the entire dataset. Comment on the

results you observe.  

            

2. Prepare frequency distributions (remember to use Sturge’s rule and create the appropriate

similarly sized classes) and accompanying histograms and ogives for these quantitative sets. Think about and provide additional analyses/diagrams that may be of interest. What insights do these

statistics provide?  

    

3. Analyse ‘Overall sales’ and ‘deliveries’ for any relationship, providing a scatter plot.  

Comment on the existence of a relationship, how you came to that conclusion, if a

relationship exists further comment on its strength and, in any case, what this means in terms of managing the retail outlet.  

4. Develop cross tabulation or contingency tables to provide information on:  

a. Overall sales and fat/sugar content, please only analyse those goods that have items that

exhibit Regular and Low Fat/Sugar values.  

      

b. Item Type and deliveries. Remember quantitative values must be presented as classes

in cross tabulation or contingency tables. Be Careful to explain any patterns or anomalies

you find in your tables.  

       

5. Prepare a pie chart to graphically represent the proportion of overall sales by each item

type (create classes of overall sales for this purpose). Interpret the graph comment on any

issues you perceive.  

Product ID	Fat/Sugar Content	Item Type	Overall Sales	Deliveries
FDV28	Regular	Frozen Foods	272	122
FDF34	Regular	Snack Foods	397	151
FDN49	Regular	Breakfast	399	192
FDP38	Low Fat/Sugar	Canned	405	174
FDT36	Low Fat/Sugar	Baking Goods	459	184
FDX38	Regular	Dairy	575	213
DRJ59	Low Fat/Sugar	Diet Drinks	579	266
FDE35	Regular	Potato Crisps	586	170
FDZ02	Regular	Dairy	587	317
NCK06	Regular	Household	606	321
FDX48	Regular	Baking Goods	618	235
FDG40	Low Fat/Sugar	Frozen Foods	645	213
FDA49	Low Fat/Sugar	Canned	698	181
FDV11	Regular	Breads	700	224
NCI29	Regular	Health and Hygiene	709	284
FDE59	Regular	Potato Crisps	719	223
NCK05	Regular	Health and Hygiene	735	323
DRN35	Low Fat/Sugar	Diet Drinks	755	219
FDE17	Regular	Frozen Foods	756	212
NCI31	Regular	Others	769	400
DRI25	Regular	Soft Drinks	774	333
FDU33	Regular	Snack Foods	781	211
FDY40	Regular	Frozen Foods	788	292
DRK35	Low Fat/Sugar	Diet Drinks	797	215
FDK04	Low Fat/Sugar	Frozen Foods	802	401
FDR43	Regular	Fruits and Vegetables	806	258
FDY12	Regular	Baking Goods	810	227
NCG43	Regular	Household	833	425
FDA44	Regular	Fruits and Vegetables	849	297
DRB25	Regular	Soft Drinks	858	360
FDW38	Regular	Dairy	863	345
FDV48	Regular	Baking Goods	864	415
FDW12	Regular	Baking Goods	871	226
FDW13	Low Fat/Sugar	Canned	883	459
FDO60	Low Fat/Sugar	Baking Goods	892	464
FDT43	Regular	Fruits and Vegetables	935	234
DRL35	Low Fat/Sugar	Diet Drinks	952	400
FDE22	Low Fat/Sugar	Snack Foods	959	422
FDW24	Low Fat/Sugar	Baking Goods	972	311
DRD25	Low Fat/Sugar	Soft Drinks	1019	255
NCJ19	Regular	Others	1031	454
FDX23	Low Fat/Sugar	Baking Goods	1040	541
FDD10	Regular	Snack Foods	1071	364
FDU26	Regular	Dairy	1073	354
FDP39	Low Fat/Sugar	Meat	1091	513
DRH25	Low Fat/Sugar	Soft Drinks	1091	578
DRC25	Regular	Soft Drinks	1117	559
FDY03	Regular	Meat	1125	563
FDU46	Regular	Snack Foods	1125	349
FDH27	Low Fat/Sugar	Dairy	1151	633
FDB27	Low Fat/Sugar	Dairy	1182	355
FDZ33	Low Fat/Sugar	Snack Foods	1182	579
FDR49	Low Fat/Sugar	Canned	1198	503
FDX27	Regular	Dairy	1229	430
FDV04	Regular	Frozen Foods	1257	679
FDH21	Regular	Seafood	1268	418
FDY35	Regular	Breads	1286	514
FDP24	Low Fat/Sugar	Baking Goods	1333	720
FDR02	Low Fat/Sugar	Dairy	1334	374
FDL38	Regular	Canned	1338	455
FDC59	Regular	Potato Crisps	1342	523
NCK53	Regular	Health and Hygiene	1389	542
DRD37	Low Fat/Sugar	Soft Drinks	1398	489
FDY60	Regular	Baking Goods	1438	733
NCH54	Regular	Household	1438	374
FDU32	Regular	Fruits and Vegetables	1462	731
FDK15	Low Fat/Sugar	Meat	1488	491
FDE53	Low Fat/Sugar	Frozen Foods	1491	581
FDS48	Low Fat/Sugar	Baking Goods	1505	497
FDY07	Regular	Fruits and Vegetables	1516	379
FDR48	Low Fat/Sugar	Baking Goods	1518	516
FDA50	Low Fat/Sugar	Dairy	1545	773
FDE10	Regular	Snack Foods	1574	787
FDR26	Low Fat/Sugar	Dairy	1594	558
NCB06	Regular	Health and Hygiene	1598	575
NCJ17	Regular	Health and Hygiene	1619	550
FDJ07	Low Fat/Sugar	Meat	1631	881
FDH35	Low Fat/Sugar	Potato Crisps	1645	543
FDQ14	Low Fat/Sugar	Dairy	1648	593
FDB34	Low Fat/Sugar	Snack Foods	1657	746
FDQ56	Regular	Fruits and Vegetables	1678	839
FDH14	Regular	Canned	1686	506
NCJ43	Regular	Household	1744	942
FDR07	Regular	Fruits and Vegetables	1809	923
FDP01	Regular	Breakfast	1830	769
FDH47	Low Fat/Sugar	Potato Crisps	1847	720
FDS37	Low Fat/Sugar	Canned	1854	686
FDD36	Low Fat/Sugar	Baking Goods	1896	720
FDF16	Low Fat/Sugar	Frozen Foods	1921	730
FDG53	Low Fat/Sugar	Frozen Foods	1957	1037
FDM44	Regular	Fruits and Vegetables	1961	1039
NCI54	Regular	Household	1965	550
FDY24	Regular	Baking Goods	1995	1057
NCJ30	Regular	Household	2037	774
FDF33	Regular	Seafood	2049	1086
FDW20	Regular	Fruits and Vegetables	2094	1047
FDN15	Low Fat/Sugar	Meat	2097	860
NCJ18	Regular	Household	2133	619
FDB49	Regular	Baking Goods	2168	542
FDE11	Regular	Potato Crisps	2221	1088
DRO47	Low Fat/Sugar	Diet Drinks	2264	1155
FDP59	Regular	Breads	2285	686
FDX43	Regular	Fruits and Vegetables	2330	1235
FDX51	Regular	Meat	2349	1292
FDO24	Low Fat/Sugar	Baking Goods	2377	689
FDU47	Regular	Breads	2388	812
FDS12	Low Fat/Sugar	Baking Goods	2391	1076
FDU35	Low Fat/Sugar	Breads	2397	719
FDU57	Regular	Snack Foods	2408	819
DRE49	Regular	Soft Drinks	2429	1312
FDW47	Low Fat/Sugar	Breads	2437	1170
DRI47	Low Fat/Sugar	Diet Drinks	2445	1051
NCM43	Regular	Others	2447	856
NCH18	Regular	Household	2457	1302
NCH30	Regular	Household	2490	921
FDB17	Low Fat/Sugar	Frozen Foods	2535	1039
DRD24	Low Fat/Sugar	Soft Drinks	2553	1098
DRM23	Low Fat/Sugar	Diet Drinks	2587	1138
DRI01	Regular	Soft Drinks	2587	802
FDZ10	Low Fat/Sugar	Snack Foods	2657	1116
FDW26	Regular	Dairy	2669	774
FDE04	Regular	Frozen Foods	2696	755
FDX01	Low Fat/Sugar	Canned	2796	1314
FDZ21	Regular	Snack Foods	2800	868
DRK59	Low Fat/Sugar	Diet Drinks	2812	844
FDB32	Regular	Fruits and Vegetables	2816	732
FDC60	Regular	Baking Goods	2834	1247
DRJ23	Low Fat/Sugar	Diet Drinks	2836	936
FDP19	Regular	Fruits and Vegetables	2842	1222
DRN47	Low Fat/Sugar	Diet Drinks	2876	1582
FDJ41	Low Fat/Sugar	Frozen Foods	2878	1266
NCF54	Regular	Household	2932	1583
NCK29	Regular	Health and Hygiene	2956	946
FDU58	Regular	Snack Foods	2993	1377
FDZ12	Low Fat/Sugar	Baking Goods	3006	1293
NCH55	Regular	Household	3036	759
FDZ51	Regular	Meat	3047	975
DRM47	Low Fat/Sugar	Diet Drinks	3057	856
FDE05	Regular	Frozen Foods	3062	1439
FDJ28	Low Fat/Sugar	Frozen Foods	3079	1447
NCK19	Regular	Others	3100	837
FDC35	Regular	Potato Crisps	3106	1677
FDZ09	Low Fat/Sugar	Snack Foods	3112	934
FDB58	Regular	Snack Foods	3120	1654
NCM55	Regular	Others	3147	1699
FDZ45	Low Fat/Sugar	Snack Foods	3175	1111
FDK51	Low Fat/Sugar	Dairy	3180	827
FDG33	Regular	Seafood	3264	1697
FDF52	Low Fat/Sugar	Frozen Foods	3284	1182
FDV36	Low Fat/Sugar	Baking Goods	3289	1612
FDC15	Low Fat/Sugar	Dairy	3300	1749
FDU23	Low Fat/Sugar	Breads	3302	826
FDV60	Regular	Baking Goods	3339	1469
FDM25	Regular	Breakfast	3340	1102
FDZ26	Regular	Dairy	3346	870
FDB28	Low Fat/Sugar	Dairy	3362	1849
NCG18	Regular	Household	3384	1861
FDB22	Low Fat/Sugar	Snack Foods	3384	1117
FDY02	Regular	Dairy	3419	1436
NCH06	Regular	Household	3449	1897
FDM39	Low Fat/Sugar	Dairy	3582	896
NCC54	Regular	Health and Hygiene	3615	1844
FDQ39	Low Fat/Sugar	Meat	3631	1852
FDS13	Low Fat/Sugar	Canned	3710	1187
FDL14	Regular	Canned	3739	1159
DRA12	Regular	Soft Drinks	3829	1723
FDV31	Regular	Fruits and Vegetables	3882	1359
NCH42	Regular	Household	3905	1445
FDE28	Regular	Frozen Foods	3916	1958
FDT11	Regular	Breads	3943	1498
FDX12	Regular	Baking Goods	4097	1967
NCH07	Regular	Household	4120	1318
FDR37	Regular	Breakfast	4196	1175
FDT13	Low Fat/Sugar	Canned	4334	1777
FDP27	Low Fat/Sugar	Meat	4364	1658
FDD47	Regular	Potato Crisps	4432	1330
NCL29	Regular	Health and Hygiene	4437	2041
FDZ03	Regular	Dairy	4474	1253
FDY39	Regular	Meat	4594	2251
FDW40	Regular	Frozen Foods	4844	2277
FDB60	Low Fat/Sugar	Baking Goods	4860	1215
FDA43	Regular	Fruits and Vegetables	4877	1561
FDJ57	Regular	Seafood	5015	2207
FDC46	Low Fat/Sugar	Snack Foods	5164	2014
FDW56	Regular	Fruits and Vegetables	5195	1455
DRE01	Regular	Soft Drinks	5332	2506
DRF36	Low Fat/Sugar	Soft Drinks	5350	2408
FDK28	Low Fat/Sugar	Frozen Foods	5411	2868
FDV59	Low Fat/Sugar	Breads	5661	1585
FDI38	Regular	Canned	5798	2087
DRJ11	Low Fat/Sugar	Diet Drinks	6051	1513
DRL01	Regular	Soft Drinks	6310	2209
FDX39	Regular	Meat	6332	1710
FDO11	Regular	Breads	6972	2719
FDC02	Low Fat/Sugar	Canned	7029	1898
DRG49	Regular	Soft Drinks	7086	2551
FDB15	Low Fat/Sugar	Dairy	7646	4205
FDY26	Regular	Dairy	7834	3682
FDG47	Regular	Potato Crisps	8132	4147
FDP15	Low Fat/Sugar	Meat	9228	3599

Answer 1

1.) Descriptive Statisitcs table for overall Sales is given below:

Mean	2507.01
Median	2242.5
Variance	2856124
Standard Devaition	1690.007
1st Quartile	1174.25
3rd Quartile	3294.5
Inter Quartile Range	2120.25
Min	272
Max	9228
Range	8956

Coefficient of Variation(CV) = (Standard Deviation / Mean) * 100

M_Sales = 2507.01

SD_Sales = 1690.007

CV_Sales = 67.41

M_deliveries = 997.605

SD_deliveries = 725.27

CVdeliveries= 72.70

2.)

Frequency Distribution:

-> Fat/Sugar Content

There are more products which contain regular sugar as compared products which contain low sugar

-> Product Types

-> Overall Sales

From Sturge Rule we wil determine number of bins required for Overall sales

Formula: 1 + 3.3* log n

n = 200

So, number of bins required = 9

Bins, lower value, upper value and count are:

Bins	Lower value	Upper Value	Count
1	272	1267.111111	55
2	1267.11	2262.22	45
3	2262.22	3257.33	47
4	3257.33	4252.44	26
5	4252.44	5247.56	12
6	5247.56	6242.67	6
7	6242.67	7237.78	5
8	7237.78	8232.89	3
9	8232.89	9228.00	1

The frequency decreases steeply after 4 indicating that pverall sales are vey less above 3300

-> Deliveries

Applyting Sturge rule here as well we get:

bins = 9

Bins	Lower value	Upper Value	Count
1	122	575.6666667	70
2	575.67	1029.33	48
3	1029.33	1483.00	42
4	1483.00	1936.67	22
5	1936.67	2390.33	9
6	2390.33	2844.00	4
7	2844.00	3297.67	1
8	3297.67	3751.33	2
9	3751.33	4205.00	2

This follows the same pattern as Overall sales as number of deliveries decreases steeply after 4th bin which there are very few deliveries above 1950

3.)

From second part itself we get to know that there exists a relationship between Overall Sales and deliveries. Plotting tge scatter plot,

The above scatter plot clearly indicates there exists a linear and strong relationship between delivery and Overall Sales.

As number of deliveries increases, Sales also increases.

To get strength of relationship, we will find correlation between two using excel formula: Correl(Deliveries, OverSales)

we get: 0.918

This again shows that there exists a strong linear relationship between overall sales and Deliveries.

4.)

a.)

Content	Overall Sales
Regular	297899
Low Fat/Sugar	203503

Products with Regular Sugar have higher sales as compared to lower one

b.)

Item Type	Deliveries
Frozen Foods	18498
Snack Foods	14619
Breakfast	3238
Canned	12386
Baking Goods	17956
Dairy	22486
Diet Drinks	10175
Potato Crisps	10421
Household	15091
Breads	10753
Health and Hygiene	7105
Others	4246
Soft Drinks	17183
Fruits and Vegetables	13311
Meat	16645
Seafood	5408

5.) Please find the Pie Chart below:

-> Health and Hygiene are low as compred to others which looks strange

-> People spend a lot on frozen foods an soft drink.

-> Majority is hold by 5 segments:

a. House hold

b. Soft drinks

c. Dairy

d. Baked items

e. Frozen Items