A Fruit and Vegetable Market Evaluation in Sydney and Melbourne - Report Example

Research Assignment College: Name: Students ID: Date: Course Name: Unit Code: Time: Instructor: Introduction This report presents an examination of data from a fruit and vegetable market that supplies fresh produce to supermarket outlets in NSW and Victoria. The market evaluation is focused in Sydney and Melbourne. The report shows findings for the Price per kilogram (Price$/kg) at each of the outlets, particularly, the relationship between Price$/kg and the type of produce; the season; and, quantity (sold per day). Variable List Name Description Quantitative Qualitative Discrete Continuous ID Number Order Number 　 　 ✔ Customer 1= Coles 　 　 ✔ 2= Private 3= Safeway City 1= Sydney 　 　 ✔ 2= Melbourne Season 1= Summer 　 　 ✔ 2= Winter Produce 1= Apple 　 　 ✔ 2= Orange 3= Potato 4= Tomato Quantity sold (tons/day) Tons sold per day 　 ✔ 　 Price$/kg Price in dollars per kilogram 　 ✔ 　 % of produce damage Percentage of produce damaged 　 ✔ 　 Data order number Customer City Season Produce Quantity sold (ton/day) Price $/kg % of Produce Damage 2 1 1 1 1 266.51 3.24 6.14 6 1 1 2 1 42.14 6.32 1.72 9 1 1 2 1 29.57 6.54 1.39 10 1 1 2 1 48.90 6.2 1.90 12 1 2 1 1 290.66 4.42 4.37 15 1 2 1 1 354.89 2.94 6.59 16 1 2 2 1 182.10 4.6 4.30 19 1 2 2 1 97.78 6.22 1.87 21 1 1 1 2 146.24 5.32 2.12 22 1 1 1 2 121.98 5.78 1.43 25 1 1 1 2 54.90 6.96 0.34 27 1 1 1 2 104.48 6.1 0.95 31 1 1 2 2 314.48 3.9 4.15 33 1 1 2 2 323.28 3.7 4.45 36 1 1 2 2 383.62 2.18 6.73 37 1 1 2 2 358.05 2.86 5.71 38 1 1 2 2 350.90 3.04 5.44 39 1 2 1 2 11.10 5.98 1.13 40 1 2 1 2 93.48 4.36 3.56 43 1 2 1 2 59.49 5.06 2.51 46 1 2 1 2 43.14 5.38 2.03 47 1 2 1 2 81.14 4.62 3.17 50 1 2 2 2 290.28 3.3 5.05 51 1 2 2 2 322.62 2.48 6.28 52 1 2 2 2 291.10 3.28 5.08 53 1 2 2 2 321.88 2.5 6.25 54 1 2 2 2 275.88 3.64 4.54 55 1 2 2 2 318.86 2.58 6.13 56 1 2 2 2 301.70 3.02 5.47 57 1 1 1 3 356.38 4.08 3.38 59 1 1 1 3 420.37 2.54 5.69 60 1 1 1 3 385.26 3.42 4.37 63 1 1 2 3 337.18 2.08 5.88 65 1 1 2 3 370.08 1.08 7.38 66 1 1 2 3 326.34 2.38 5.43 67 1 1 2 3 313.50 2.72 4.92 68 1 1 2 3 321.88 2.5 5.25 71 1 2 1 3 327.79 3.04 4.94 74 1 2 1 3 396.38 1.62 7.07 75 1 2 2 3 494.72 1.66 6.51 76 1 2 2 3 528.76 0.74 7.89 77 1 2 2 3 483.57 1.92 6.12 78 1 2 2 3 515.73 1.12 7.32 79 1 2 2 3 528.11 0.76 7.86 80 1 2 2 3 529.41 0.72 7.92 81 1 1 1 4 461.12 3.9 7.15 83 1 1 1 4 398.26 2.02 9.97 84 1 1 1 4 498.50 2.38 9.43 85 1 1 2 4 221.74 4.36 5.15 86 1 1 2 4 95.95 5.76 2.63 89 1 2 1 4 231.71 3.4 7.90 90 1 2 1 4 357.20 2.28 9.58 92 1 2 1 4 443.89 2.54 9.19 94 1 2 2 4 67.39 3.4 6.88 95 1 2 2 4 107.20 4.66 4.61 96 1 2 2 4 24.92 3.2 7.24 97 2 1 1 1 230.27 3.84 5.24 98 2 1 1 1 257.20 3.4 5.90 99 2 1 1 1 258.38 3.38 5.93 105 2 1 1 1 241.52 3.66 5.71 107 2 1 2 1 93.14 5.38 3.13 108 2 1 2 1 91.06 5.42 3.07 109 2 1 2 1 90.02 5.44 3.04 110 2 1 2 1 75.20 5.72 2.62 111 2 1 2 1 16.76 6.76 1.06 114 2 1 2 1 78.41 5.66 2.71 116 2 2 1 1 350.09 3.06 6.41 118 2 2 1 1 334.42 3.44 5.84 119 2 2 1 1 355.68 2.92 6.62 122 2 2 1 1 352.50 3 6.50 126 2 2 2 1 142.10 5.4 3.10 127 2 2 2 1 162.50 5 3.70 130 2 2 2 1 167.48 4.9 3.85 131 2 2 2 1 115.48 5.9 2.35 132 2 1 1 2 135.82 5.52 1.82 133 2 1 1 2 72.61 6.66 0.11 136 2 1 2 2 364.28 2.7 5.95 137 2 1 2 2 374.12 2.44 6.34 139 2 1 2 2 322.40 3.72 4.42 140 2 2 1 2 35.82 5.52 1.82 141 2 2 1 2 104.56 4.12 3.92 143 2 2 1 2 10.00 6 1.10 145 2 2 2 2 344.16 1.88 7.18 146 2 2 2 2 310.40 2.8 5.80 147 2 2 2 2 290.28 3.3 5.05 148 2 1 1 3 353.65 4.14 3.29 149 2 1 1 3 404.89 2.94 5.09 150 2 1 1 3 352.74 4.16 3.26 154 2 1 1 3 396.85 3.14 4.79 155 2 1 1 3 351.82 4.18 3.23 157 2 1 2 3 318.86 2.58 5.13 161 2 1 2 3 371.90 1.02 7.47 165 2 2 1 3 342.91 2.76 5.36 166 2 2 1 3 296.42 3.58 4.13 167 2 2 1 3 334.37 2.92 5.12 168 2 2 1 3 388.80 1.8 6.80 170 2 2 1 3 394.72 1.66 7.01 171 2 2 1 3 304.83 3.44 4.34 172 2 2 2 3 516.45 1.1 7.35 173 2 2 2 3 520.00 1 7.50 174 2 2 2 3 493.05 1.7 6.45 175 2 2 2 3 520.70 0.98 7.53 176 2 2 2 3 528.76 0.74 7.89 178 2 2 2 3 502.84 1.46 6.81 179 2 2 2 3 457.25 2.48 5.28 181 2 1 1 4 435.78 2.48 9.28 182 2 1 1 4 461.12 3.3 8.05 183 2 1 1 4 381.38 2.84 8.74 185 2 1 1 4 379.22 2.28 9.58 187 2 1 1 4 324.50 3.18 8.23 191 2 1 2 4 5.00 5.08 3.86 192 2 1 2 4 43.97 4.44 5.01 193 2 1 2 4 90.43 5.7 2.74 195 2 1 2 4 82.03 4.34 5.19 196 2 1 2 4 97.32 5.44 3.21 198 2 2 1 4 376.68 2.4 9.40 199 2 2 1 4 411.20 3.42 7.87 200 2 2 1 4 356.02 2.1 9.85 202 2 2 1 4 367.66 2.54 9.19 203 2 2 1 4 404.24 2.16 9.76 204 2 2 1 4 422.67 2.48 9.28 208 2 2 2 4 14.00 4.7 4.54 209 2 2 2 4 22.05 3.84 6.09 210 2 2 2 4 80.27 3.76 6.23 211 2 2 2 4 85.31 4.68 4.58 212 2 2 2 4 23.49 3.92 5.94 215 2 2 2 4 44.58 4.54 4.83 216 3 1 1 1 218.70 4.02 4.97 217 3 1 1 1 182.03 4.56 4.16 219 3 1 1 1 267.66 3.22 6.17 220 3 1 1 1 193.20 4.4 4.40 222 3 1 1 1 154.48 4.94 3.59 223 3 1 2 1 106.46 5.12 3.52 225 3 1 2 1 34.17 6.46 1.51 226 3 1 2 1 94.18 5.36 3.16 227 3 1 2 1 111.50 5.02 3.67 229 3 1 2 1 104.44 5.16 3.46 230 3 2 1 1 360.40 2.8 6.80 231 3 2 1 1 335.26 3.42 5.87 232 3 2 1 1 319.78 3.78 5.33 235 3 2 1 1 324.14 3.68 5.48 236 3 2 1 1 361.96 2.76 6.86 237 3 2 2 1 94.40 6.28 1.78 240 3 2 2 1 124.13 5.74 2.59 241 3 2 2 1 170.44 4.84 3.94 242 3 2 2 1 181.14 4.62 4.27 243 3 2 2 1 95.53 6.26 1.81 245 3 1 1 2 106.69 6.06 1.01 246 3 1 1 2 103.36 6.12 0.92 248 3 1 1 2 161.50 5.02 2.57 249 3 1 1 2 136.88 5.5 1.85 250 3 1 1 2 93.28 6.3 0.65 251 3 1 1 2 63.22 6.82 0.00 253 3 1 2 2 329.32 3.56 4.66 254 3 1 2 2 331.02 3.52 4.72 256 3 1 2 2 324.14 3.68 4.48 257 3 1 2 2 328.46 3.58 4.63 258 3 1 2 2 348.48 3.1 5.35 261 3 2 1 2 83.06 4.58 3.23 262 3 2 1 2 98.13 4.26 3.71 263 3 2 1 2 25.20 5.72 1.52 264 3 2 1 2 84.97 4.54 3.29 265 3 2 1 2 88.77 4.46 3.41 267 3 2 1 2 52.40 5.2 2.30 268 3 2 2 2 306.48 2.9 5.65 269 3 2 2 2 342.78 1.92 7.12 270 3 2 2 2 312.73 2.74 5.89 272 3 2 2 2 331.46 2.24 6.64 273 3 2 2 2 318.86 2.58 6.13 274 3 2 2 2 334.34 2.16 6.76 275 3 2 2 2 270.66 3.76 4.36 277 3 1 1 3 387.78 3.36 4.46 278 3 1 1 3 370.66 3.76 3.86 280 3 1 1 3 418.10 2.6 5.60 282 3 1 1 3 381.88 3.5 4.25 283 3 1 2 3 363.28 1.3 7.05 285 3 1 2 3 375.48 0.9 7.65 287 3 1 2 3 336.48 2.1 5.85 291 3 2 1 3 301.25 3.5 4.25 293 3 2 1 3 358.24 2.46 5.81 294 3 2 1 3 303.64 3.46 4.31 295 3 2 1 3 372.67 2.16 6.26 296 3 2 1 3 306.02 3.42 4.37 299 3 2 2 3 517.88 1.06 7.41 301 3 2 2 3 509.05 1.3 7.05 302 3 2 2 3 456.25 2.5 5.25 305 3 1 1 4 398.26 2.92 8.62 306 3 1 1 4 420.42 2.48 9.28 307 3 1 1 4 461.12 2.18 9.73 310 3 1 1 4 428.18 3.3 8.05 311 3 1 1 4 381.38 2.78 8.83 315 3 1 2 4 83.44 5.02 3.96 317 3 1 2 4 33.26 5.1 3.82 320 3 2 1 4 404.24 3.18 8.23 322 3 2 1 4 445.55 3.18 8.23 323 3 2 1 4 369.94 1.6 10.60 326 3 2 1 4 378.90 3.34 7.99 330 3 2 2 4 30.62 5.02 3.96 332 3 2 2 4 70.00 4.1 5.62 335 3 2 2 4 60.80 3.74 6.27 Pivot Table A Sum of Season Column Labels Row Labels 1 2 Grand Total 1 21 50 71 2 26 60 86 3 25 52 77 4 26 42 68 Grand Total 98 204 302 From the pivot table above, the total produce in winter exceeded the total produce in summer by 106. Also, for all types of fruits, the produce in winter was generally more than the summer produce. The only similarity is that 26 fruits were produced in summer both for the oranges and tomatoes, 26 was also the highest number of fruits produced by type in summer. In winter there was no any equal produce, however oranges were more produced at 60 fruits whereas tomatoes were the least produced at 42 fruits. Pivot Table B Column Labels 1 2 Row Labels Average of Price $/kg Average of Quantity sold (ton/day) Average of Price $/kg Average of Quantity sold (ton/day) 1 3.565714286 286.177619 5.6128 101.9612 2 5.46 83.54692308 2.968666667 324.5673333 3 3.1056 360.3368 1.534615385 443.7503846 4 2.717692308 399.9669231 4.514285714 65.89380952 Grand Total 3.725918367 281.5256122 3.569411765 247.1306863 a) Summer had the highest average of price in dollars per kilogram for oranges (5.46) where as winter had the highest average of price in dollars per kilogram for apples (5.6128). b) Winter had the highest average of quantity sold in tonnes per day for oranges (324.567) where as summer had the highest average of quantity sold in tonnes per day for apples (268.178). T-test a) Summary statistics for oranges in winter in price in dollars per kilogram Oranges Price $/kg in Winter Mean 2.968667 Standard Error 0.108383 Standard Deviation 0.593637 b) Hypotheses: H0: The average price$/kg in winter is less than the summer price of $5.50 H1: The average price$/kg in winter is not less than the summer price of $5.50 Assumption: The level of significance is 0.05 Test statistic: Computation: = -23.487. Absolute answer = 23.487 Critical value: = 1.701 Conclusion: Since 23.487 > 1.701, we fail to reject the null hypothesis and conclude that the average price$/kg in winter is less than the summer price of $5.50. Scatter Plot Nature and Relationship between Price $/kg and Quantity ton/day The scatter plot shows a downhill pattern moving from left to right indicative of a negative relationship between the price in dollars per kilogram and the quantity sold in tonnes per day. As the price in dollars per kilogram increases, the quantity sold in tonnes per day decreases. Regression Analysis a) Excel Output SUMMARY OUTPUT Regression Statistics Multiple R 0.861065 R Square 0.741433 Adjusted R Square 0.740127 Standard Error 76.88356 Observations 200 ANOVA   df SS MS F Significance F Regression 1 3356064 3356064 567.758 4.59E-60 Residual 198 1170394 5911.082 Total 199 4526459         Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Intercept 580.7437 14.36244 40.4349 1.24E-97 552.4207 609.0666 552.4207 609.0666 Price $/kg -86.8762 3.646023 -23.8277 4.59E-60 -94.0663 -79.6862 -94.0663 -79.6862 b) Regression equation Quantity sold in tonnes per day = 580.744 – 86.876 Price in dollars per kilogram c) R-squared and Standard error R-squared is 0.741. This represents the Quantity sold in tonnes per day that is explained by the model. The standard error is 76.884. This represents the average distance that the quantity sold in tonnes per day fall from the regression line. d) Gradient of regression line The gradient of the regression line is – 86.876. This represents variance in quantity in tonnes per day depending on the price in dollars per kilogram. e) Significance of Gradient and Intercept To test the significance of the gradient and the intercept, I pick on the last output from the sorted random data pictured in this paper (200th variable). The actual figures for the quantity sold in tonnes per day and the price in dollars per kilogram are 60.80 and 3.74 respectively. Using the regression equation, the following are the workings: Quantity sold in tonnes per day = 580.744 – 86.876 (3.74) = 255.828 From the above result, I can conclude that the value of the intercept and the gradient are not significant. f) Good model? I think the regression model is a good model. This is because the value of R-squared is quite high, also the regression sum of squares is high and the error the sum of squares is low. In addition, the p-value is low, the F-statistic is high and the adjusted R-squared is high. All these parameters are indicative of a good model. Conclusion In general, the quantity sold in tonnes per day and the prices in dollars per kilogram are directly related. An increase in the price in dollars per kilogram will result in a decline in the quantity sold in tonnes per day. Also, during summer the overall produce is generally low, but in winter, the overall produce is generally high. Apples perform badly in summer whereas oranges and tomatoes generally perform better. Oranges perform well during winter while tomatoes perform badly. References Freedman, D.H et al, 2007, Statistics, 4th edn, New York, W.W Norton & Company. Read More