European Agri-Business and Regression Equation - Essay Example

TABLE OF CONTENTS Part European Agri-Business 3 Introduction 3 Correlation Coefficient (r) and Correlation of Determination (R2) 4 Regression Equation 6 Predictions 8 Part 2 Retail Sales by Food Stores 9 Introduction 10 Time Line 10 Seasonal Variations 11 Predictions 11 Bibliography 13 Glossary 13 Appendices 14 PART I EUROPEAN AGRI-BUSINESS Introduction The data under study contains the end-of-year figures (employees and sales) for some of the main European agri-business companies. These 22 companies mainly sell food and drink. The figures given are for business activity within Europe only. The sales of these 22 companies consists of 471,000 employees is 122 billion Euros. The descriptive statistics, shown in Table 1, provides the information that the minimum number of employees in a company is 5000 and the maximum is 71000. There are 21.4 thousand employees in a company on average at a standard deviation of 15.575. The least sales of a company is 2 billion Euros while the highest sales accounts to 23 billion Euros. The average sale of a company is 5.56 billion Euros at a standard deviation of 4.32. Table 1 Statistics employees (thousands) sales in Euros billion N Valid 22 22 Missing 0 0 Mean 21.40 5.56 Std. Deviation 15.575 4.325 Range 66 21 Minimum 5 2 Maximum 71 23 Sum 471 122 The analysis of the sales and employees data suggested that Tate & Lyle in UK has a contribution to sales per employee at 73% and the lowest contribution of 14% by Cadbury Schweppes at UK. 19% of the total sales are attributed to Nestle at Zurich while 14% of the sales have happened in UK. Moreover, total sales of were registered in December 2006. Further analysis of the data, as shown in Figure 1, indicates that increase in sales is not always due to increase in employees and vice versa. For example, even though Tate and Lyle have 5000 employees, but have sales of 4 billion Euros. On the other hand, Eoro Puleva has a sale of 2 billion Euros (half of Tate and Lyle) with 6000 employees. The reason behind this is that sales are not only dependent on the number of employees; rather there are several factors like price of the product, geography, consumer behavior, etc. Figure 1 Scatter Plot Correlation Coefficient (r) and Correlation of Determination (R2) The present study considers two factors ; sales and employees. Therefore, in order to analyze the relationship between the two variables, Correlation is the best statistical tool. Table 1 shows the correlation matrix generated using SPSS. Table 2 Correlation Matrix employees (thousands) sales in Euros billion employees (thousands) Pearson Correlation 1 .923** Sig. (2-tailed) .000 N 22 22 sales in Euros billion Pearson Correlation .923** 1 Sig. (2-tailed) .000 N 22 22 **. Correlation is significant at the 0.01 level (2-tailed). The correlation coefficient of 0.923 at the significant level of 0.01 suggests that there is a strong relationship between the number of employees and the amount of sales a company has. This means that there is a strong relationship as indicated by the significant value of 0.000. This means that the size of the company is directly proportional to the sales of the company. Thus if the number of employees in a company is increased the sales will also increase. Figure 2 Relationship between number of employees and sales The above figure shows that there is a positive relationship between the number of employees and sales of a company. Regression Equation After finding a positive relation between the two variables, the amount of influence the number of employees has on the sales of the company has to be measured. The coefficient of determination, R2 = 0.851 in Table 3 shows that 85% of the total variation in sales can be explained by the linear relationship between sales and number of employees (as described by the regression equation). But, the remaining 15% suggests that there are other factors that influence sales. Thus, Table 3 also shows that there is a significant change in the amount of sales when there is an increase in the number of employees. Figure 3 Regression Equation From Figure 3, the regression equation is: Sales = 0.08 + 0.26 (no. of employees) Outliners The point that is furthest away from the regression line is marked in blue. This blue point indicates that ‘Nestle’ has 71 thousand employees and has sales of 22.7 billion Euros and does not fit under the regression equation. Table 3 Regression Analysis Model Summary Model R R Square Adjusted R Square Std. Error of the Estimate Change Statistics R Square Change F Change df1 df2 Sig. F Change 1 .923a .851 .844 6.158 .851 114.347 1 20 .000 a. Predictors: (Constant), sales in Euros billion Table 4 provides the coefficients of regression which suggests that the regression equation is given as Sales = 0.080 + 0.256 (number of employees). This means that for one unit increase in the number of employees, the sales increases by 0.256 billion Euros. Similarly, when there is a 0 increase in the number of employees, the sales increase by 0.08 billion Euros. Table 4 Coefficients of Regression Coefficientsa Model Unstandardized Coefficients Standardized Coefficients t Sig. B Std. Error Beta 1 (Constant) .080 .629 .127 .900 employees (thousands) .256 .024 .923 10.693 .000 a. Dependent Variable: sales in Euros billion Predictions Table 5 Predictions Company Name Headquarters Year End Employees (thousands) Sales (€ billion) Numico NL Dec06 7.6 2.02 InBev sa BE Dec06 21.17 5.5 Based on the above equation, it was found that 7.6 thousand employees belong to Numico, whose sales are predicted as 2.02 billion Euros. Similarly, when sales of InBev sa are 5.5 billion Euros, the number of employees required is 21.17 thousand. The accuracy of the predictions is given as 6.158 (standard error of estimate). This means that the predictions provided are accurate by 68% and are quite helpful as they lie within the original data range. Thus, the sales are predicted to vary between -4.1 billion Euros and 8.1 billion Euros. PART II RETAIL SALES BY FOOD STORES Introduction The data was obtained to represent the value of retail sales from food stores on a quarterly basis. The formal description of the data is given by “Retail sales index: Predominantly food stores (val nsa): All Business Index, not seasonally adjusted, base year 2005 = 100”. The sales data is provided for the years 2003 to Q3 2009. The sales are expected to be forecasted for the next four quarters (Q4, 2009 to 2010). As the data is related to time, therefore, the existence of the time series components such as seasonality and trend are studied. From the provided data, a time series graph for the value of retail sales from food stores. Figure 1 indicates that the sales of each of the 4 quarters in a year are following a regular pattern. This regularity in the pattern is known as seasonality. This is an additional component of the time series that plays a major role in controlling the movement of the data. The data further shows a steady upward movement denoting an increase in sales over time. This means that the sales of Q1 2004 follow the same trend of Q1 2003 but the amount of sales has increased considerably. Thus, in order to get a better picture of the actual flow of sales, the seasonality component should be removed. Figure 1 Time Series Graph for the Value of Retail Sales from Food Stores Having noticed distinct trend and regular patterns of variation about the trend, the average annual increase of sales can also be obtained. It is the difference in sales between two consecutive years (latest – previous). Since the data is given on quarterly basis, the yearly sales value is obtained by taking the average. The annual increase can also be obtained as percentage increase or growth rate (col E). This information helps us in understanding the significance of the upward movement of the data every year. Trend Line In order to remove the seasonal variations, several methods of decomposition are available. The present study makes use of the Holts-Winter method which involves the moving averages concept. The moving averages (col. G) (here, 4-point moving average is used as it is a quarterly data) remove the seasonality. The centered moving averages (col. H) then gives us a trend line (Figure 2). The trend equation and the R2 value are also obtained as given below. The R2 = 0.08339 means that 83% of the variation in y is explained by x. The regression equation is trend value = 1.110 (quarter no.) + 88.82, where the gradient is 1.110 and the intercept is 88.82. Thus, from the trend equation, the trend prediction (col. K) for the next 4 quarters is obtained. Figure 2 Moving Averages Concept Seasonal Variations The seasonal variation of each quarter (col. I) is calculated using the actual sales data and the centered moving average value of the corresponding quarter. The seasonal variation is obtained for each quarter from 2003 to 2009. Hence to obtain one seasonal variation per quarter, the average of each of the 4 quarters is taken. The total of these average seasonal variations (col. J) should be almost equal to the number of the quarters involved in the calculations. Predictions Having found the average seasonal variations and trend prediction, the forecasts (col. L) can be made as their product. The predicted sales values are as follows: Table 1 Predictions Year Quarter Quarter No. Prediction 2009 Q4 28 98.69 2010 Q1 29 86.43 2010 Q2 30 102.65 2010 Q3 31 111.76 A standard error of estimate of 4.013 provides the accuracy of prediction. That is, with 68% accuracy, the sales are predicted to vary between 94.677 and 102.703 in Q4 2009. Figure 3 Values of Retail Sales from Food Stores with Predictions The above figure shows the values of retail sales from food stores with predictions. The graph shows a dip in the level of sales in the 2009 Q4. A further dip is seen in 2010 Q1, while there is an increase in 2010 Q2 and 2010 Q3. These predictions are based on the assumption that the pattern of retail sales from food stores will continue in the following year. These predictions might prove inaccurate if any external factors influence consumer behavior. BIBLIOGRAPHY London Metropolitan Business School Module book of BA 1015 London Metropolitan Business School Web learn GLOSSARY Correlation coefficient = r Correlation of determination = R² APPENDICES PART I Company Name Headquarters Year End Employees (thousands) Sales (€ billion) Nestle CH Dec-06 71 22.7 Heineken NL Dec-06 38.9 8.8 Groupe Danone FR Dec-06 35 8.6 Unilever NL/UK Dec-06 44 8.6 Danish Crown Amba DK Oct-06 26.9 6.5 Groupe Lactalis FR Dec-06 25.7 6.4 Associated British Food UK Sep-06 28.3 5.7 Sudzucker DE Feb-07 19.6 5.8 Carlsberg DK Dec-06 25.7 5.2 Scottish & Newcastle UK Dec-06 15 4.9 Royal Friesland Foods NL Dec-06 15.3 4.7 Campina NL Dec-06 6.3 3.6 Oetker Group DE Dec-06 15.4 3.6 Barilla IT May-07 7 3.6 Tate & Lyle UK Dec-06 4.8 3.5 Cadbury Schweppes UK Dec-06 23.5 3.4 Bongrain FR Dec-06 15.6 3.3 Nutreco NL Dec-06 7.5 3 Kerry Group IR Dec-06 16.3 3 Danisco DK Apr-07 10.6 2.8 Pernod Ricard FR Jun-07 12.4 2.7 Ebro Puleva ES Dec-06 6.1 2 PART II Value of retail sales from food stores index with 2005 = 100 Quarter No. Years  Index 1 2003 Q1 88.3 2 2003 Q2 92.8 3 2003 Q3 92 4 2003 Q4 99.5 5 2004 Q1 92.1 6 2004 Q2 96.6 7 2004 Q3 95.4 8 2004 Q4 103.5 9 2005 Q1 95.3 10 2005 Q2 99.3 11 2005 Q3 98 12 2005 Q4 107.4 13 2006 Q1 96.6 14 2006 Q2 102.8 15 2006 Q3 102.7 16 2006 Q4 112.4 17 2007 Q1 101.7 18 2007 Q2 107.4 19 2007 Q3 106.2 20 2007 Q4 116.6 21 2008 Q1 107.7 22 2008 Q2 113.8 23 2008 Q3 112.9 24 2008 Q4 123 25 2009 Q1 114.4 26 2009 Q2 121.2 27 2009 Q3 118.6 28 2009 Q4 98.7       29 2010 Q1 86.4       30 2010 Q2 102.7       31 2010 Q3 111.8       Read More