Here are annual revenue data in billions of dollars for the 10-year period 2008–2017 for two well-known retailers:
Amazon: 19.1; 24.5; 32.4; 48.1; 61.1; 74.5; 89.0; 107.9; 136.0; 177.9.
Walmart: 402.2; 406.1; 420.0; 445.0; 467.2; 474.5; 483.5; 480.0; 482.2; 496.8.
For each company, use linear extrapolation in Excel to forecast revenue in the year 2021. (Hint: Use either the Trendline option or the Regression tool.)
1.) Using the regression tool in Excel (with a constant term), in 2021, Amazon's stock price is forecasted to be ______
and Walmart's is forecasted to be ______(Enter your responses rounded to two decimal places.)
2) Based on these extrapolations, when would Amazon be predicted to equal Walmart in revenue?
Amazon's stock price would be predicted to equal Walmart's stock price in approximately the year _____ (Enter your response using an integer.)
3) Why has Amazon been growing faster than Walmart? Is the slow growth of Walmart related to the rapid growth of Amazon?
Amazon may be growing faster than Walmart because
A. Amazon does not experiment with prices.
B. Walmart is the same size as Amazon.
C. maintaining retail stores is relatively cheap.
D. consumer preferences are shifting toward online shopping.
4) The growth of Amazon has likely _______ the growth of Walmart.
Regression option for the Data Analysis Tool pack is used where a regression output is made using the data.
Both summary outputs of Amazon and Walmart are given as:
| AMAZON | ||||||||
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.968638 | |||||||
| R Square | 0.938259 | |||||||
| Adjusted R Square | 0.930542 | |||||||
| Standard Error | 13.59622 | |||||||
| Observations | 10 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 1 | 22473.83 | 22473.83 | 121.5739 | 4.08E-06 | |||
| Residual | 8 | 1478.858 | 184.8573 | |||||
| Total | 9 | 23952.69 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | -33139 | 3012.504 | -11.0005 | 4.15E-06 | -40085.8 | -26192.1 | -40085.8 | -26192.1 |
| Year | 16.50485 | 1.496895 | 11.02606 | 4.08E-06 | 13.053 | 19.95669 | 13.053 | 19.95669 |
| WALMART | ||||||||
| SUMMARY OUTPUT | ||||||||
| Regression Statistics | ||||||||
| Multiple R | 0.950459 | |||||||
| R Square | 0.903373 | |||||||
| Adjusted R Square | 0.891294 | |||||||
| Standard Error | 11.50134 | |||||||
| Observations | 10 | |||||||
| ANOVA | ||||||||
| df | SS | MS | F | Significance F | ||||
| Regression | 1 | 9893.599 | 9893.599 | 74.79242 | 2.48E-05 | |||
| Residual | 8 | 1058.246 | 132.2808 | |||||
| Total | 9 | 10951.85 | ||||||
| Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
| Intercept | -21583 | 2548.342 | -8.46941 | 2.89E-05 | -27459.4 | -15706.5 | -27459.4 | -15706.5 |
| Year | 10.95091 | 1.266256 | 8.648261 | 2.48E-05 | 8.030918 | 13.8709 | 8.030918 | 13.8709 |
Question 1
Amazon equation
Average Revenue of Amazon = 16.50485*Year -33139
Projection of year 2021
Average Revenue of Amazon = 16.50485*2021 - 33139 = 217.34
Walmart equation
Average Revenue of Amazon = 10.95091*Year -21583
Projection of year 2021
Average Revenue of Amazon = 10.95091*2021 - 21583 = 548.8327
Question 2
With extrapolation, 20 years data is extracted
| Year | Amazon | Walmart |
| 2008 | 19.1 | 402.2 |
| 2009 | 24.5 | 406.1 |
| 2010 | 32.4 | 420.0 |
| 2011 | 48.1 | 445.0 |
| 2012 | 61.1 | 467.2 |
| 2013 | 74.5 | 474.5 |
| 2014 | 89 | 483.5 |
| 2015 | 107.9 | 480.0 |
| 2016 | 136 | 482.2 |
| 2017 | 177.9 | 496.8 |
| 2018 | 167.83 | 515.98 |
| 2019 | 184.33 | 526.9309 |
| 2020 | 200.84 | 537.8818 |
| 2021 | 217.34 | 548.8327 |
| 2022 | 233.85 | 559.7836 |
| 2023 | 250.35 | 570.7345 |
| 2024 | 266.86 | 581.6855 |
| 2025 | 283.36 | 592.6364 |
| 2026 | 299.87 | 603.5873 |
| 2027 | 316.37 | 614.5382 |
| 2028 | 332.88 | 625.4891 |
| 2029 | 349.38 | 636.44 |
| 2030 | 365.88 | 647.3909 |
| 2031 | 382.39 | 658.3418 |
| 2032 | 398.89 | 669.2927 |
| 2033 | 415.4 | 680.2436 |
| 2034 | 431.9 | 691.1945 |
| 2035 | 448.41 | 702.1455 |
| 2036 | 464.91 | 713.0964 |
| 2037 | 481.42 | 724.0473 |
by estimation, around the year 2033 Amazon's stock
price would be somewhat equivalent to Walmart's
price.
Question 3
Point D)
Amazon may be growing faster because the customer preferences are shifting to online shopping. This is also because Walmart has a huge physical footprint which gets difficult for customers to select items and then go to cashier for its payment.
Hence, eCommerce is a growing business and Amazon's growth is majorly because of its online presence.
Question 4
Growth table
| Year | Amazon | Walmart | Growth of Amazon | Growth of Walmart |
| 2008 | 19.1 | 402.2 | ||
| 2009 | 24.5 | 406.1 | 28.272% | 0.970% |
| 2010 | 32.4 | 420.0 | 32.245% | 3.423% |
| 2011 | 48.1 | 445.0 | 48.457% | 5.952% |
| 2012 | 61.1 | 467.2 | 27.027% | 4.989% |
| 2013 | 74.5 | 474.5 | 21.931% | 1.563% |
| 2014 | 89 | 483.5 | 19.463% | 1.897% |
| 2015 | 107.9 | 480.0 | 21.236% | -0.724% |
| 2016 | 136 | 482.2 | 26.043% | 0.458% |
| 2017 | 177.9 | 496.8 | 30.809% | 3.028% |
| 2018 | 167.83 | 515.98 | -5.662% | 3.861% |
| 2019 | 184.33 | 526.9309 | 9.834% | 2.122% |
| 2020 | 200.84 | 537.8818 | 8.954% | 2.078% |
| 2021 | 217.34 | 548.8327 | 8.218% | 2.036% |
Amazon's growth started from 28% and in 2021 from extrapolation is 8.218% which is succeeding higher than the Walmart's growth from 1% (2008) to 2% in 2021.
The growth of Amazon has likely succeeded the growth of Walmart
Here are annual revenue data in billions of dollars for the 10-year period 2008–2017 for two...