Business Decision Making - Assignment Example

Business BUSINESS DECISION MAKING Contents Task 3 XY Scatter Graph 3 Histogram 9 Task 2. 14 Trend Line 14 Task 4. 19 Mean 19 Median 19 Variance 19Standard Deviation 21 Range 23 Correlation coefficient 23 Percentile 24 Conclusion 25 References 26 Task 1. XY Scatter Graph From the XY scatter chart showing the sales of Sainsbury during the 25 years from 1980 to 2012 we can find that Sainsbury showed a positive trend from 1985 to almost 2000 and from 2000 to 2005 it showed fairly a static trend where the sales figures were almost close to each other. Although there was a drop in sales from 2004 to 2005 and then from 2005 we can again notice a highly positive trend in the sales which is increasing constantly year wise. XY scatter chart showing operating profit of Sainsbury during 25 years from 1980 to 2012 depicts that from 1985 to 1994 there has been a gradual increase in the operating profit although we find a sudden drop in the operating profit during the 2005 which may be due to the sales which has been seen to reduce tremendously during 2005. We notice a real hike in the operating profit from 1995 to 1996 after which there has been a gradual decrease noticed over a period 1997-1998 again a rise is noticed in 1999 and 2000. Again a drop in operating profit during 2001 and it remained the same in 2002, leading to a little rise during 2003 and 2004 and a tremendous dip in 2005 and then again gradual rise from 2005 to 2012 where the operating profit is the highest. The same trend is noticed in line graphs also. Line graphs depicting sales and operating profit of Sainsbury sales given below. XY sales chart of Tesco shows that the sales of Tesco are gradually increasing. The only highlighting portion is that during 2006 the sales remained constant as 2005. Again from 2005 we find that the sales are increasing gradually. XY chart of operating profit of Tesco shows that the operating profit too is perfectly reflecting the sales of Tesco showing that the increase or decrease in sales has reflected itself on the operating profit of Tesco. The same trend can be noticed also in other charts of Tesco such as the Line chart. The line chart of Sales and Operating profit of Tesco too shows that the sales and operating profit has been increasing gradually for over 25 years. Histogram Sainsbury’s Sales Bin Frequency 10000 5 15000 5 20000 13 25000 2 From above graph, it can be seen that highest frequency of Sainsbury’s sale is 13 within bin of 20,000. Operating Profit Bin Frequency 200 1 600 10 1000 14 From above graph, it can be seen that highest frequency of Sainsbury’s operating profit is 14 within bin of 1000. Tesco Sale Bin Frequency 10000 2 20000 4 30000 3 40000 2 50000 2 60000 2 From above graph, it can be seen that highest frequency of Tesco’s sale is 4 within bin of 20,000. Operating Profit Bin Frequency 1000 3 1500 4 2000 2 2500 1 3000 2 3500 2 4000 2 From above graph it can be seen that highest frequency of Tesco’s operating profit is 4 within bin of 1500. Task 2. Trend Line The below given scatter graph shows the trend line for Sales of Sainsbury for the past 25 years. The trend line for the past 25 years for sales is y=612.8x- 1000000 and corresponding R2 = 0.859. The trend line indicates that the sales value will increase in 2013. The forecasted sales of the year 2013 will be more than 23000. It can be forecasted because the trend line is moving upwards. The below given scatter graph shows the trend line of operating profit of Sainsbury. The trend line for the past 25 years is y= 3.808x - 7025 and its R2 is 0.014. The forecasted operating profit of Sainsbury will be among 600 to 800. The sales trend line for Tesco is y= 3400x -7000000 and its corresponding R2 is 0.976. The trend line is moving upwards thus it can be forecasted that the sales will be more than 65000. The operating profit trend line for Tesco is y= 22.9 x -45799 and its R2 is .964. The trend line is moving upwards and it can be forecasted that the operating profit will be more than 4500. The reliability factor indicates that the reliability is really high in this case. Reliability is actually the goodness of fit it shows that how properly the fit has actually taken place. It can take any values between 0 and 1. If the value is closer to 1 it indicates greater proportion has taken place as in variance. Task 4. To, The Manager, Here we will explain the process of calculation of the 25 years data of the two banks. We have calculated the mean, median, standard deviation, variance, range, correlation coefficient and percentile. Mean The mean is also known the average. It can be calculated by adding up all data given and dividing by the number of data entries. The mean of Sainsbury’s total sales is 14759.81 and the mean of the operating profit is 590.59. The mean of Tesco’s total sales is 36312 and the mean of the operating profit is 2091.31. Median The median is the middle of the total data set when data is arranged in either ascending or descending order. When the total data set in the list is odd, the median is found by arranging them in an ascending order. But when the data set is even then the median can be calculated by using the formula of median in excel. For Example =MEDIAN (B3:B27). The median or the mid-value of total sales of Sainsbury is 16061 and the median of operating profit of Sainsbury is 656. The median or the mid-value of total sales of Tesco is 33711.50 and the median of operating profit of Tesco is 1843.50. Variance Variance can be defined as the measurement of spread between the numbers in a set of data. Variance is used to measure that difference between the mean of the data set from each number in the data set. Variance can be calculated by using the “VAR” formula in excel. For example =VAR (B3:B27). Sainsbury sales variance Sainsbury operating profit variance 23679753.40 53123.10 20396597.16 51970.59 16540904.81 49873.88 13433204.07 49042.23 11230701.64 51218.60 9302131.93 53543.46 7697039.83 54753.56 6349284.65 55777.01 5197679.00 56059.32 4556467.12 57983.87 4116555.55 61377.75 4009781.55 62791.72 4285545.10 62415.83 4588256.73 67094.43 4955202.22 72567.19 5401280.30 78888.84 5938371.40 85545.39 6588811.30 93877.90 6375557.08 37701.12 6524765.43 17762.82 7260451.65 16113.18 8537100.94 12766.02 10670209.37 15122.29 13696938.47 19874.46 0.00 0.00 From the above table we can see the sales variance and operating profit variance of Sainsbury for the last 24 years. Tesco sales variance Tesco operating profit Variance 268487571.60 1237929.83 253007458.41 1194139.12 223073662.83 1054428.20 194159192.75 936757.85 177360458.40 870659.39 161062631.58 802720.42 146705978.98 735891.20 133576722.11 670494.00 130412396.58 627528.07 123088181.99 602916.63 127931696.96 625444.16 140369279.99 707701.21 166130510.24 835206.00 205092792.75 1041475.77 255719931.90 1322553.89 0.00 0.00 From the above table we can see the sales variance and operating profit variance of Tesco from the year 1997 as data before 1997 of Tesco is not available. Standard Deviation Standard deviation is the measurement of the dispersion of the data set from the mean of the data. It shows the deviation of the data from its average. Standard deviation states that if the spread among the data is higher then the standard deviation of the data set will be higher. Standard deviation can be calculated by applying square root to the variance. It can also be calculated by using the “STDEV” formula in excel. Standard deviation is a statistical tool which helps to determine the historical volatility of the company’s stock value, sales and operating profit. The higher the volatile the stock, the higher will be the standard deviation. Higher standard deviation states that how much amount of return of the company is deviating from the expected and normal returns. For Example =STDEV (B3:B27). Sainsbury sales standard deviation Sainsbury operating profit standard deviation 4866.18 230.48 4516.26 227.97 4067.05 223.32 3665.13 221.45 3351.22 226.32 3049.94 231.39 2774.35 233.99 2519.78 236.17 2279.84 236.77 2134.59 240.80 2028.93 247.75 2002.44 250.58 2070.16 249.83 2142.02 259.03 2226.03 269.38 2324.07 280.87 2436.88 292.48 2566.87 306.40 2524.99 194.17 2554.36 133.28 2694.52 126.94 2921.83 112.99 3266.53 122.97 3700.94 140.98 0.00 0.00 Tesco sales standard deviation Tesco operating profit standard deviation 16385.59 1112.62 15906.21 1092.77 14935.65 1026.85 13934.10 967.86 13317.67 933.09 12691.05 895.95 12112.22 857.84 11557.54 818.84 11419.82 792.17 11094.51 776.48 11310.69 790.85 11847.75 841.25 12889.16 913.90 14321.06 1020.53 15991.25 1150.02 0.00 0.00 From the above table we can see the standard deviation of sales and operating profit of Sainsbury and Tesco. Here we can see that the standard deviation of sales and operating profit of Tesco is higher than the standard deviation of Sainsbury. It indicates that sales and operating profit of Tesco is highly volatile than Sainsbury. It indicates that the sales and operating profit of Sainsbury is stable over the years. Range Range can be defined as the difference between the highest and lowest value of the data set. It is measured in the same units as the units of the data. It determines the statistical dispersion of the data set. Sainsbury sales range Sainsbury operating profit range 17292.5 1025 Tesco sales range Tesco operating range 49555 3211 From the above table it can be seen that the range of sales of Sainsbury is 17292.5 and the range of operating profit is 1025. The range of sales of Tesco is 49555 and the range of operating profit is 3211. Thus it can be seen that the difference between highest and lowest value of Tesco is higher than the difference between maximum and minimum value of Sainsbury. It indicates that the range of Tesco is higher than the range of Sainsbury. Correlation coefficient Correlation coefficient is a term which used in statistics to analyze the relationship between two random variables or sets of data. In case of Sainsbury’s, the two random variables are sales figure and operating profit from the year 1988 to 2012. With the help of correlation coefficient method in Ms Excel, we have analyzed the relationship between these two random variables. The output of this analysis is 0.316051898. This figure is stating that sales and operating profit of Sainsbury’s is positive, which means the company has earned enough profit up to year 2012. In case of case of Tesco, the two random variables are sales amount and operating profit from the year 1997 to 2012. With the help of correlation coefficient in Ms Excel, we have analyzed the relationship between these two random variables. The output of this analysis is 0.996651904. This figure is stating that sales and operating profit of Tesco is positive, which means the Tesco has done well in sales of their products. It showing the profit margin of Tesco is huge. By comparing the results of these two figure, correlation coefficient of Sainsbury’s and Tesco. It can be seen that Tesco has done much better than Sainsbury in terms of sales and has earned more profit. Percentile A percentile (or a percentile) is a measure utilized within statistics demonstrating the worth beneath which a given percentage of perceptions in a gathering of perceptions fall. The 25th percentile is otherwise called the first quartile (Q1), the 50th percentile as the median or second quartile (Q2), and the 75th percentile as the third quartile (Q3). In case of Sainsbury’s: For Sales 25th percentile is 11223.8 50th percentile is 16061 75th percentile is 18206 For Operating Profit 25th percentile is 520 50th percentile is 656 75th percentile is 756 In case of Tesco For sales: 25th percentile is 22169.25 50th percentile is 33711.5 75th percentile is 48948 For operating profit: 25th percentile is 1132 50th percentile is 1843.5 75th percentile is 2885.5 By comparing the both percentile and quartile of both Sainsbury’s and Tesco, it can be seen that Tesco’s overall performance is much better than Sainsbury’s in every aspects of sales performance. Tesco has done well from the year 1997 to 2012, whereas the sales figure of Sainsbury’s is very poor in compare with the figures of Tesco. Conclusion From the above study it can be seen that the sales of Sainsbury has shown a positive trend line over the years. It can be expected that the sales amount of the company will increase over the years. The operating profit of Sainsbury was high during 1985 to 1994 but it has reduced during 1994-95. After that it has increased till the year 2003 but decreased drastically during 2004 to 2006. It indicates that the operating profit of the company was quite volatile. From the sales trend of Tesco it can be seen that the sales graph ahs increased over the years. It indicates that the sales revenue of the company has increased over the years. The operating profit of the company has also increased over the years. Unlike Sainsbury, the operating profit of Tesco has constantly increased over the years. It was stable thus it can be seen that the sales and operating profit of Tesco has shown a constant increasing trend over the years than Sainsbury. From the statistical analysis of both the company it can be seen that the variance in sales and operating profit of Tesco is higher than the variance in Sainsbury which indicates that the sales and operating profit of Tesco is more different from its mean. The standard deviation of TESCO IS HIGHER THAN Sainsbury which indicates sales and operating profit of Tesco is more volatile than Sainsbury. The range of Tesco is also higher than Sainsbury. But from the correlation coefficient and percentile, it can be seen that Tesco has perform better than Sainsbury in terms earning sales revenue and operating profit. Thus from the above study it can be concluded that Tesco has performed better than Sainsbury over the years. References Abyss.uoregon.edu, n.d. Regression and correlation analysis. [online] Available at: http://abyss.uoregon.edu/~js/glossary/correlation.html [Accessed 9 Jul. 2014]. Graham, A., 2003. Statistics. 1st ed. [Blacklick, Ohio: McGraw-Hill. Harper, W., 1991. Statistics. 1st ed. London: Financial Times/Pitman Pub. Laerd Statistics, 2013. Linear Regression Analysis in SPSS - Procedure, assumptions and reporting the output.. [online] Statistics.laerd.com. Available at: https://statistics.laerd.com/spss-tutorials/linear-regression-using-spss-statistics.php [Accessed 9 Jul. 2014]. SLOCUM, S., n.d. equations; graphs and their application to statistical data; and the technical applications of percentage, such as banking and investment. The book closes with a few extended problems which follow some special activity involving mathematical calculations. The University of British Columbia, 2000. Mean and Variance of Binomial Random Varibles. [pdf] The University of British Columbia. Available at: http://www.math.ubc.ca/~feldman/m302/binomial.pdf [Accessed 9 Jul. 2014]. University of Dayton, 2014. Multiple Regression Example. [pdf] University of Dayton. Available at: http://campus.udayton.edu/~jrs/tools/download/interpeting%20SPSS%20regression%20output.pdf [Accessed 9 Jul. 2014]. Read More