European Agribusiness - Scatter Plot between Employees and Sales - Research Paper Example

Part One: European Agribusiness Scatter Plot Figure Scatter Plot between Employees and Sales Figure above shows the scatter plot between salesand employees. It can be observed that there is a strong linear relationship between sales and the number of employees. This is evident from the fact that the data centres around the linear regression line fitted to the data as shown in figure 1. Correlation Coefficient The correlation coefficient between sales and number of employees is 0.922568. This indicates that there is a very strong linear relationship between the number of employees and the sales generated. Since correlation coefficient does not give information about the direction of causality, it is difficult for one to determine whether it is sales that cause the number of employees to rise, or whether it is the number of employees that causes the sales to increases. To understand the direction of causality, we derive the regression equation in the next section. Derivation of the Equation of the Regression Line Regression analysis measures the relationship between two variables. It measures how one variable (the dependent variable) depends on the other (the independent or explanatory variable). The regression model that establishes a relationship between sales and number of employees can be written as follows: and are parameters of the regression line. is the intercept of the regression line and is the slope coefficient of the regression line, which measures how sensitive sales is to the number of employees; is a random error term with zero-expected value. Assuming that has an expected value of zero, we can write the regression equation as follows: The following output is obtained by regressing sales as a function of the number of employees: Table 1: Regression output for Sales Against Number of Employees Coefficients Standard Error t Stat P-value Alpha 0.079911 0.629204 0.127004 0.900205 Beta 0.256194 0.023958 10.69332 1.01E-09 It can be observed that the alpha is 0.079911 while the beta or slope coefficient of the line is 0.25. This coefficient is significant at the 1 percent level of significance indicating the existence of a strong linear dependence of sales on the number of employees. From the foregoing, the regression equation can thus be written as follows: Sales = 0.079911 + 0.256194 x Number of Employees Company that least fits the Regression Line Code company name Alpha Beta Predicted Sales (billions) Actual Sales (billions) Residual Figure (billions) 1 Nestle 0.079911 0.256194 18.26971 22.7 4.430285 2 Heineken 0.079911 0.256194 10.04587 8.8 -1.24587 3 Groupe Danone 0.079911 0.256194 9.046716 8.6 -0.44672 4 Unilever 0.079911 0.256194 11.35247 8.6 -2.75247 5 Danish Crown Amba 0.079911 0.256194 6.971541 6.5 -0.47154 6 Groupe Lactalis 0.079911 0.256194 6.664108 6.4 -0.26411 7 Associated British Food 0.079911 0.256194 7.330213 5.7 -1.63021 8 Sudzucker 0.079911 0.256194 5.101322 5.8 0.698678 9 Carlsberg 0.079911 0.256194 6.664108 5.2 -1.46411 10 Scottish & Newcastle 0.079911 0.256194 3.922828 4.9 0.977172 11 Royal Friesland Foods 0.079911 0.256194 3.999686 4.7 0.700314 12 Campina 0.079911 0.256194 1.693936 3.6 1.906064 13 Oetker Group 0.079911 0.256194 4.025305 3.6 -0.42531 14 Barilla 0.079911 0.256194 1.873272 3.6 1.726728 15 Tate & Lyle 0.079911 0.256194 1.309645 3.5 2.190355 16 Cadbury Schweppes 0.079911 0.256194 6.10048 3.4 -2.70048 17 Bongrain 0.079911 0.256194 4.076544 3.3 -0.77654 18 Nutreco 0.079911 0.256194 2.00137 3 0.99863 19 Kerry Group 0.079911 0.256194 4.25588 3 -1.25588 20 Danisco 0.079911 0.256194 2.795572 2.8 0.004428 21 Pernod Ricard 0.079911 0.256194 3.256722 2.7 -0.55672 22 Ebro Puleva 0.079911 0.256194 1.642697 2 0.357303 To determine which company least fits the regression equation, the expected sales is calculated using the regression equation and assuming that sales depend on the number of employees. We substitute for the number of employees in the regression equation to get the sales figure for each of the company. This figure is compared to the actual sales figure for each company. It can be observed that the company that has the highest deviation from the predicted or expected sales is Nestle. The regression equation suggests that its sales should be 18.3 billion. However, the actual sales figure turns out to be 22.7billion representing a difference of 4.43billion. This company therefore has the largest residual and thus fits the regression line least well as compared to the other companies. Filling in the Missing Gaps company name headquarters year end employees (thousands) sales in billion Numico NL Dec06 7.6 2.026989 InBev sa BE Dec06 21.2 5.5 The missing figures are calculated as follows: Sales = 0.079911 + 0.256194 x 7.6 = 2.02billion Number of Employees = (Sales - Alpha)/Beta = (5.5 - 0.079911)/0.256194 = 21.2 thousand employees Part Two: Scatter Plot ANALYSIS of the SCATTER PLOT DIAGRAM The scatter graph above shows the growth of the value of retail sales from food stores over time. It covers the periods from the first quarter of 2003 to the fourth quarter of 2009. The graph clearly shows a pattern in the growth of index. The index increases as the year reaches its second quarter, declines on its third and rises again on the fourth. This is exhibited in all the seven years. The increase from the third quarter to the fourth quarter is the higher than the increase in the second quarter. This is also true in all the seven years. Practically this reveals that retail sales is highest as the year is in its fourth quarter. Sales is also booming in the first to second quarter but it gloomy in the second to third quarter. Average Annual Increase Date Index Per Quarter Annual Index Annual Increase 2003 Q1 88.3 2003 Q2 92.8 2003 Q3 92 2003 Q4 99.5 372.6 2004 Q1 92.1 2004 Q2 96.6 2004 Q3 95.4 2004 Q4 103.5 387.6 2005 Q1 95.3 15 2005 Q2 99.3 2005 Q3 98 2005 Q4 107.4 400 2006 Q1 96.6 12.4 2006 Q2 102.8 2006 Q3 102.7 2006 Q4 112.4 414.5 2007 Q1 101.7 14.5 2007 Q2 107.4 2007 Q3 106.2 2007 Q4 116.6 431.9 2008 Q1 107.7 17.4 2008 Q2 113.8 2008 Q3 112.9 2008 Q4 123 457.4 2009 Q1 114.4 25.5 2009 Q2 121.2 2009 Q3 118.6 AVERAGE ANNUAL INCREASE 16.96 Getting the average annual increase necessitates the computation of the annual index and the growth per year. The annual growth for the years 2004, 2005, 2006, 2007 and 2008 are then computed to find the average. The annual indices are 372.6 for 2003, 387.6 for 2004, 400 for 2005, 414.5 for 2006, 431.9 for 2007 and 457.4 for 2008. For 2009, the figure cannot be computed since our data is only until the third quarter. From these numbers we can compute the annual increase which are 15 for 2004, 12.4 for 2005, 14.5 for 2006, 17.4 for 2007 and 25.5 for 2008. The average annual increase is 16.96. Analysis of the seasonal variations Value of retail sales from food stores index with 2005 = 100 Index linear trend detrended series 1 2003 Q1 88.3 89.93571 -1.635714286 2 2003 Q2 92.8 91.04664 1.753357753 3 2003 Q3 92 92.15757 -0.157570208 4 2003 Q4 99.5 93.2685 6.231501832 88.82478632 5 2004 Q1 92.1 94.37943 -2.279426129 1.110927961 6 2004 Q2 96.6 95.49035 1.10964591 7 2004 Q3 95.4 96.60128 -1.201282051 Y=88.8247863 + 1.11092796X 8 2004 Q4 103.5 97.71221 5.787789988 9 2005 Q1 95.3 98.82314 -3.523137973 10 2005 Q2 99.3 99.93407 -0.634065934 11 2005 Q3 98 101.045 -3.044993895 12 2005 Q4 107.4 102.1559 5.244078144 13 2006 Q1 96.6 103.2668 -6.666849817 14 2006 Q2 102.8 104.3778 -1.577777778 15 2006 Q3 102.7 105.4887 -2.788705739 16 2006 Q4 112.4 106.5996 5.8003663 17 2007 Q1 101.7 107.7106 -6.010561661 18 2007 Q2 107.4 108.8215 -1.421489621 19 2007 Q3 106.2 109.9324 -3.732417582 20 2007 Q4 116.6 111.0433 5.556654457 21 2008 Q1 107.7 112.1543 -4.454273504 22 2008 Q2 113.8 113.2652 0.534798535 23 2008 Q3 112.9 114.3761 -1.476129426 24 2008 Q4 123 115.4871 7.512942613 25 2009 Q1 114.4 116.598 -2.197985348 26 2009 Q2 121.2 117.7089 3.491086691 27 2009 Q3 118.6 118.8198 -0.21984127 Quarter 1 Quarter 2 Quarter 3 Quarter 4 2003 -1.635714286 1.753358 -0.157570208 6.231501832 2004 -2.279426129 1.109646 -1.201282051 5.787789988 2005 -3.523137973 -0.63407 -3.044993895 5.244078144 2006 -6.666849817 -1.57778 -2.788705739 5.8003663 2007 -6.010561661 -1.42149 -3.732417582 5.556654457 2008 -4.454273504 0.534799 -1.476129426 7.512942613 2009 -2.197985348 3.491087 -0.21984127 average -3.823992674 0.465079 -1.802991453 6.022222222 Above is the estimate of the average seasonal variations: -3.823992674 for 1st quarter, 0.465079 for the second quarter, 1.802991453 for the third quarter and 6.022222222 for the fourth quarter. The process of getting the seasonal variation necessitated the derivation of the intercept and the slope. The linear equation is: Y=88.8247863 + 1.11092796X The fourth column is computed through this formula " C2: =I$1+I$2*A2" and then copied until the last row (TIMEWEB). The figures for the detrended series was computed as the difference between the third and the fourth column. This is where we got the figures per quarter and we just computed the average per quarter. This gives us the seasonal variations. The trend can easily be noticed by looking at the scatter diagram. Retail sales shows an increase in the second quarter. This is true for all the periods in focus. The second quarter is known to be a vacation time. This is known to be the "summer quarter" (TIMEWEB). Since it is summer, sales of cold products which are sold in retail such as ice cream will increase. This increases the total consumer spending which results to this figure. Sales decline in the third quarter approaches and it reaches its peak during the fourth quarter. The fourth quarter surge of retail sales can be very well explained by the Christmas season. In almost all countries, retail sales can be assumed to show the same trend. During Christmas season, sales of food and other materials such as gift items increase. Forecast Value of retail sales from food stores index with 2005 = 100 Index linear trend detrended series 1 2003 Q1 88.3 89.93571 -1.635714286 2 2003 Q2 92.8 91.04664 1.753357753 3 2003 Q3 92 92.15757 -0.157570208 4 2003 Q4 99.5 93.2685 6.231501832 88.82478632 5 2004 Q1 92.1 94.37943 -2.279426129 1.110927961 6 2004 Q2 96.6 95.49035 1.10964591 7 2004 Q3 95.4 96.60128 -1.201282051 Y=88.8247863 + 1.11092796X 8 2004 Q4 103.5 97.71221 5.787789988 9 2005 Q1 95.3 98.82314 -3.523137973 10 2005 Q2 99.3 99.93407 -0.634065934 11 2005 Q3 98 101.045 -3.044993895 12 2005 Q4 107.4 102.1559 5.244078144 13 2006 Q1 96.6 103.2668 -6.666849817 14 2006 Q2 102.8 104.3778 -1.577777778 15 2006 Q3 102.7 105.4887 -2.788705739 16 2006 Q4 112.4 106.5996 5.8003663 17 2007 Q1 101.7 107.7106 -6.010561661 18 2007 Q2 107.4 108.8215 -1.421489621 19 2007 Q3 106.2 109.9324 -3.732417582 20 2007 Q4 116.6 111.0433 5.556654457 21 2008 Q1 107.7 112.1543 -4.454273504 22 2008 Q2 113.8 113.2652 0.534798535 23 2008 Q3 112.9 114.3761 -1.476129426 24 2008 Q4 123 115.4871 7.512942613 25 2009 Q1 114.4 116.598 -2.197985348 26 2009 Q2 121.2 117.7089 3.491086691 27 2009 Q3 118.6 118.8198 -0.21984127 28 119.9308 29 121.0417 30 122.1526 31 123.2636 The table above shows the forecasted figures for the 28th to 31st quarters. The computation is based from the trended series using simple linear regression. On the fourth quarter of 2009, the retail sales is expected to record at 119.9308. In 2010, figures were anticipated to be 121.0417 for the first quarter, 122.1526 for the second quarter and 123.2636 for the third quarter. These figures represent the computed values of y as given by the regression model so that the forecast values for the fourth quarter of 2008 is Y = 88.8247863 + 1.11092796(28) = 119.9308. The prediction will always record a positive growth since the intercept and the slope are positive. This might not always be the case. However, statistically, the predictions through linear regression are deemed to be sound and acceptable. Works Cited TIMEWEB. http://www.bized.co.uk. 13 January 2010 . Read More