[footnoteRef:1] [1: http://www.thegrocer.co.uk/channels/supermarkets/tesco-team-leader-cull-to-be-rolled-out-nationwide/511789.article ]

[footnoteRef:2] [2: http://www.marketoracle.co.uk/Article46578.html]

Predicting Tescos Revenue

Does Tesco PLC operational revenue follow a specific pattern over time?

Candidate name: Jono LongSubject choice: Maths/BusinessTOK Teacher: Dr LalehExtended Essay Supervisor: Ms Nicola Burns

Word Count: 2940[footnoteRef:3] [3: http://www.hl.co.uk/news/articles/profit-warnings-catching-the-falling-knife ]

Contents:

Abstract..2

Introduction.2

Body, data table3

Body, Linear.4

Body, Logarithmic6

Body, Polynomial.8

Moving Average10

All Graphs12

Conclusion..13

Bibliography.14

Abstract: This essay contains the methods which were used to try to obtain an equation for Tescos operational revenue over a period of 16 years from 1997 up to and including 2012. This essay shows 4 different curves that have been tried to match one graph. For each graph, Linear, Logarithmic and polynomial, I have calculated the equation of the line and the deviation from the line of the points. Some of the points are explained, why they were higher or lower than expected. The lines were then predicted a further three years to up to and including 2015 where the curves were then measured against the original data to test their accuracy. These curves were then evaluated, there strengths and weaknesses and then summed up to judge the accuracy and reliability of the specific curve. The equation of each of the lines and the R2 values were found. The last sets of graphs show the moving average of the values taking 2, 3 and 4 points to measure an average, this is done to show the general trend line and the accuracy of the graph. To finish the essay I concluded with the thought that it was impossible to predict future revenue but there were some graphs that showed an almost accurate correlation for the data that was given, which could therefore mean an equation could be formed.Abstract word count: 228

Introduction:

In this extended essay I will be looking at Tescos operational revenue (Operational Revenue is the income from sale from a company over that period of time) from 1997 to 2015, over an 18 year period. My research question is; Does Tescos operational revenue follow a specific pattern over time, I will be working out a formula which Tesco could use to predict their future revenue from 2016 onwards. I will be researching data so that I can graph the revenue against time and try to find and equation for that line. I will try to look at different lines of best fit for each graph to see which prediction is the most reliable and accurate, this means that I have to use the date from 2005 to 2012 and see if the next 3 years of data match the graph. I could also find anomalies in the graph and see whether they can also be predicted so that businesses can be ready for future falls in revenue. This question, if proved successful can be extremely useful for future predictions of revenue for large companys which could also point out anomalies in the graph that could then be predicted in the future. This could be a huge advantage to a business and prove very competitive in that market because of these predictions. This could also be helpful to businessmen and women when choosing whether to invest in a company, knowing its predicted future revenue. I feel that creating a few different curves could be more reliable for Tesco and feel that they could be more accurate when predicting future patterns. After creating the graph I will evaluate the strengths and weaknesses of each one, then make an overall conclusion and possible factors I could do differently if I were to repeat the essay. Body:Results for Tescos operational revenue by time from 1997-2015:(See Appendix for list of website where this data was found)Time (Years)Operational revenue ( Millions)

199714,984

199817,779

199918,546

200020,358

200122,773

200225,654

200328,613

200433,557

200537,070

200643,137

200746,611

200858,588

200959,426

201056,910

201160,455

201263,916

201363,406

201463,557

201562,992

Operational revenue (Millions) This is the original graph for operational Revenue against time from 1997-2015. This shows a rather straight correlation with some points a little higher than the others, but there Is a general increase in revenue as time increases.

Time (Years)

Operational revenue (Millions) This graph is from 1997-2012, I will be adding different trend lines to this graph to see whether I can find the most accurate curve and find a suitable equation from it.

Time (Years)

Linear Graph:This is a graph of Tescos Operational revenue of a time period of 16 years from 1997 to 2012. This graph shows a linear line of best fit, with the regression equation and the R squared value (R2 ) which is a statistical measure of how close the data are to the fitted regression line. The equation will show me a specific formula that I could then prove by using it to match the following 3 years and also predict revenue for the future. Below is a graph with a linear line:Operational revenue (Millions) Time (Years)

The regression equation for this line is: Y=3624.9x 7E+06. An equation for a line is Y=MX+C the M is the gradient, for this linear line the gradient is 3624.9. This means that every 1 year across, 3624.9 million of revenue is gained. The Y intercept is at 7*10^6 which is 7,000,000 this value looks slightly unrealistic as it is saying that when time is 0, revenue is at 7,000,000 which is impossible. This shows that there are problems with excel. If I had more time then to improve this problem I would have to find data from when Tesco first opened and find all incomes from there. With an R2 value of 0.9632. This is a very accurate measure for Tescos operational revenue, when R2=1.00 that means that the points are completely accurate and are in line with all the others showing a perfect correlation however some of these points are a little out of line for example 2008 and 2009 both those years had a significantly higher revenue than that of the previous and future years. After studying the graph and realising there is an increase in revenue in 2008 and 2009 I have found out that in 2008 the retail giant took its conquest of the UK one step further by buying up some rival Somerfield stores on remote islands in Scotland, giving Tesco a presence in every single postcode area in the country. As it stands there is only one postcode in the UK - in Harrogate in North Yorkshire - which does not have a Tesco. In 2009 Tesco Bank was launched as a joint venture with Royal Bank of Scotland. Which would have increased revenue for those 2 years, therefore showing why Tesco dont have perfect correlation and why revenue may have been higher. The graph below shows the predicted next 3 years of Tescos revenue and states that by 2015 Tesco show be at a sales of 76,000 (million), however from the revenue between 2012 and 2015 the second graph shows that the linear line does not match up with the data, and the R2 value is decreased from 0.9632 to 0.9487 and the regression equation changes to Y=3226.6x - 6E+06. This is an accurate value of data however the prediction that was from the first graph was close (only 0.0145 out) but not entirely accurate. Operational revenue (Millions) Time (Years)

Operational revenue (Millions) Time (Years)

The reasons for why the values did not match up is because in 2013-14 Tesco had a bad year, they were failing to make profits in china, which meant shops were forced to closed and Tesco lost 1.5bn from there attempt to expand into china. However that wasnt there only problem, after attempting to expand into china they then tried America which proved unsuccessful after having to close 199 stores and losing out on 1.2bn. Then in 2015 Tesco had a very bad year, there was an inspection of its meats and was found to contain a large amount of horse meat in there beef burger. This lead to a very bad Public relations for the company and a lack of trust. Nearly 300 (million) has been wiped off the value of Tesco after a number of its burgers were found to contain horse meat. After this crisis there was a second major problem involving the suspension of the head of its UK business and called in independent accountants to investigate after discovering that its guidance to the city overstated expected first half profits by about 250 (million). The supermarket group led by the CEO Dave Lewis accountancy firms and legal advisors to scrutinise the overstatement at its troubled UK food business. Tescos shares fell by over 8% wiping 1.5bn off the retailers market value and more than 6bn had wiped off share value. These factors may prove why Tesco had such a bed 3 year from 2012 to 2015 and could show why the trend line wasnt so accurate. Strengths of this type of graphing is that it is very easier to work out, for this type of curve the line has to be straight, this means that there would always be a positive coloration between Tescos revenue which from previous data looks very accurate. The points have a very small amount of deviation from the line, this increases the accuracy of the line and improves the possibility of this graph and equation being the best match to predict Tescos operational revenue. Weaknesses of this graph is that because it is a straight line it means the revenue can never be negative and is always increasing. This could be a problem for Tesco as the business could lose out, in 2015 the business fell short of their target and its sales were lower than in 2014 (63,557) than in 2015 (62,992). This graph may not be as accurate as first expected because of this positive constant line.

Logarithmic Graph:A Logarithmic scale is a nonlinear scale used when there is a large range of quantities, common uses include earthquake magnitude (Richter scale), sound loudness and PH of solutions. I want to see whether it could use this type of curve to see whether it will match or give better results for my R2 value compared to my linear graph. As I feel that a logarithmic curve could be more suited to Tescos operational revenue. I think this because a logarithmic curve starts to narrow out as it increases and revenue becomes more constant. I feel this may be the case with Tesco as their revenue of the last few years has deviated negatively from the linear line in the graphs above. Operational revenue (Millions) Time (Years)

The Equation for this line is: Y= 7E+06ln(x) - 6E+07. The R2 value for this logarithmic curve is 0.9631, this is only 0.0001 less than that of the linear equation. Like the Linear line it is very straight, however the predicted line for 3 years in the future (as shown by the graph below) is more accurate than the linear line. This could mean that although this graph is less accurate, as there is greater deviation, it could be more accurate for predictions in the future. Below is the predicted graph and the real values of the graph. Operational revenue (Millions) Time (Years)

Operational revenue (Millions) Time (Years)

The equation of this line is Y=6E=06ln(x) 5E=07

The difference In R2 values is from 0.9631-0.9489 is 0.0142. This value is more accurate than the linear line by 0.0003 which could prove on a larger scale and over a longer period of time to be more accurate. However the problems with this way of graphing is that it Is hard to graph a logarithmic graph without using a computer, and a computer rounds the values that it has been given so this could be a problem with my results as all the values have been rounded. Strengths of the method is that the curve doesnt have to be straight, this can then take into account small deviations as shown in 2008 and 2009, it can also adjust itself to rapid changes in the revenue, if in future, with more information I could plot a more accurate graph this curve could match the data to be more reliable.

Polynomial order 4:A polynomial trend line is a curved line that is used when data fluctuates. It is useful, for example, for analysing gains and losses over a large data set. The order of the polynomial can be determined by the number of fluctuations in the data or by how many bends (hills and valleys) appear in the curve. An Order 2 polynomial trend line generally has only one hill or valley. Order 3 generally has one or two hills or valleys Order 4 generally has up to three. This graph below has been set to be order 4 and fits quite nicely onto the graph. Operational revenue (Millions) Time (Years)

The equation of this line is Y= - 2.7822x4 + 22279x3 - 7E+07x2 + 9E+10x - 4E+13The R value for this curve is 0.987 this is 0.0239 more accurate than the logarithmic curve and 0.0238 more accurate that the Linear line. This line is significantly more accurate than that shown by the previous curves. However the problem with this curve is that it has only 3 hills/valleys which cant comprehend for change in the future, as the curve finds hills/valleys in the data that has already been given. Below shows the predicted line from polynomial order 4, 3 years predicted. Operational revenue (Millions) Time (Years)

In fact, this graph predicts Tescos operational Revenue starts to rapidly decline until 2015 where the company is just making 40,000 (million). The R value for this graph is 0.9881 this is the closest trend line from that graphs that have already been looked at, however, this graph may be good for measuring Tescos Operational revenue but isnt very good at predicting in the future. Operational revenue (Millions) Time (Years)

Other problems with this graph is its equation is very hard to work out by hand and the numbers are rounded up/down so there could be some inaccuracies on the graph. Its not a very easy equation to work out. The curve is accurate but not realistic which is must be taken into account when trying to predict future values for revenue.

Moving Average step 3:A moving average trend line smooths out the fluctuations in data to show a pattern or trend more clearly. A moving average uses a specific number of data, in this case I will be showing the moving average of step 2 step 3 and step 4, averages the number of points used and uses the average values as a point to plot the line in the line. So for this graph, when the period is set to 2, the average of the first 2 data points is used as the first point in the moving average trend line. The average of the second and third data points is used as the second point in the trend line, and so on. The graph below show a moving average using 2 points of data.

Operational revenue (Millions) Time (Years)

The next graph shows the moving average of step 3, I am comparing these 3 graphs to see which one has the least deviation from the line that has enough data to be reliable and accurate.

Operational revenue (Millions) Time (Years)

As we can see this graph is that the trend line is more constant it doesnt show as much deviation in 2008 and 2009 compared to the first moving average graph with step 2. The graph below shows the moving average with step order 4.Operational revenue (Millions) Time (Years)

For this graph the point are even more averaged. The increase in revenue in 2008-2009 is to match to the original values. I feel that overall, the moving average step 3 is the best graph to use as it is most accurate with the data given. However the problem with moving average graph is that you cant predict the future of the graph as it is formed by data that has already been given. Below shows how I Calculated to the moving average for step 3, and I used excel to work out the values for Step 2&420358 + 22,773 + 25,654 = 68,78568,785/3 = 22,928Fourth point = 22,928/2001

22,773 + 25,654 + 28,613 = 77,040 77,040/3 = 25,680Fifth number = 25,680/2002

25,654 + 28,613 + 33,557 = 87,82487,824/3 = 29,275Sixth point = 29,275/2003 28,613 + 33,557+ 37,070 = 9924099240/3 = 33080 Seventh point = 33080/2004

2005 = 37,9212006 = 422722007 = 494452008 = 54875 2009 = 58,3082010 = 58,9302011 = 604272012 = 625922013 = 636262014 = 6331814,984 + 17,779 + 18,546 = 5130951309/3 = 17103First point = 17103/1998

17,779 + 18,546 + 20358 = 56,68356,683/3 = 18,894.3Second point = 18,894.3/1999

18,546 + 20358 + 22,773 = 61,67761,677/3 = 20,559Third point = 20,559/2000

Conclusion To conclude on this essay after studying whether there is a specific equation that can measure Tescos operational revenue over a period of time I have realised that it is impossible to measure and to predict the future accurately. The most realistic graph, for me, was the Moving average step 3 graph, this graph is useful to study trend lines for data that has been given and averages it so that it can show more of a general trend. However if I were to have more time I would have done a Weighted moving average and used more data from previous years. I may have drawn my graphs by hand and found whether that would have been more accurate. The closest equation that I have found that relates to the Tescos operational revenue is the polynomial order 4 graph. This graph had a R2 value of .987 for the 2012 graph and a more accurate value .9881 for the 2015 graph which proves that it becomes more accurate to the line which could suggest a better fit to Tescos revenue. The equation for polynomial order 4 is Y = 0.0092x4 - 99.617x3 + 377733x2 - 6E+08x + 4E+11. Problems with this curve is that when predicted further, because of the limited number of peaks and troughs the next values would be decreasing and never increases. So this therefore states that in 10 years time Tesco will have a negative Revenue which I feel is highly unlikely. So overall although its almost impossible to predict the future it can mapped and assumptions can be made. The most accurate graph I feel is the linear graph, it may not be an exact fit but taking in all other factors, it seems to be the most accurate graph.

Bibliography: http://www.telegraph.co.uk/finance/markets/2788089/A-history-of-Tesco-The-rise-of-Britains-biggest-supermarket.html http://www.theguardian.com/business/2014/sep/22/tesco-investigators-overstating-profit-250m http://www.theguardian.com/business/marketforceslive/2013/jan/16/horse-meat-tesco-market-value-shares https://www.google.co.uk http://www.intel.co.uk/content/dam/www/program/education/us/en/documents/project-design/track/track-spreadsheet-trendlines.pdf Data from first table: http://www.tesco.com/investorInformation/report98/report.pdf http://www.tescoplc.com/files/pdf/reports/annual_report_2000.pdf https://www.tescoplc.com/files/pdf/reports/annual_report_2002.pdf https://www.tescoplc.com/files/pdf/reports/annual_report_2004.pdf https://www.tescoplc.com/files/pdf/reports/annual_report_2006.pdf https://www.tescoplc.com/files/pdf/reports/annual_report_2008.pdf https://www.tescoplc.com/files/pdf/reports/annual_report_2010.pdf http://www.londonstockexchange.com/exchange/prices-and-markets/stocks/summary/company-summary/GB0008847096GBGBXSET0.html 14