Question

3. Apple’s Worldwide Revenues from 2004 to 2019 is as follows:
Year Worldwide Revenue in Billions
2004 8.2
2005 13.9
2006 19.3
2007 24.6
2008 37.5
2009 42.9
2010 65.2
2011 108.2
2012 156.5
2013 170.9
2014 182.8
2015 233.72
2016 215.64
2017 229.23
2018 265.6
2019 260.17

a. Enter the data above into the tab labeled Apple. Graph the data in Excel and use your graph to determine what kind of time series pattern exist. Put your answer in your spreadsheet.
b. Make the following forecasts for 2020. For all of them, use Mean Squared Error to determine which of the forecasts is the best. Make sure your answers are clearly labeled.
i. Naïve forecast from one prior time period
ii. Calculate a 4-period moving average
iii. Calculate a 3-period moving average with the following weights for time t: time period t-1=0.8, t-2 = 0.15, t-3=.05
c. In the tab called Apple Smoothing, use the data from 3. to forecast 2020 using an alpha equal to 0.7, 0.8, and 0.9. Using MSE, which one offers the best estimate for 2020?
d. In the tab called Apple Regression, use the information from 3. and run a regression to determine your forecast for 2020
i. Put your regression output in F1 of the same workbook.
ii. Calculate what your forecast is for 2020 in F21.
iii. How does well does this regression equation predict revenue? Write your answer in F22. In addition, explain what your numerical answer means in words.

Answer 1

ANSWER::

3Q)

a).

The graph is shown below. It shows an increasing trend. The line is overall quite linear.

b).

The forecasts and the MSE values are shown below. The best forecast is Naïve forecast as it has the lowest MSE 651.34

c).

The smoothing forecasts are shown below. The best smoothing factor is 0.9 as it has the lowest MSE 716.66

d).

The regression output is shown below. The forecast of period 2020 is 296.13. The regression model shows a 97.69% correlation with the annual progress. The variation in the revenue due to annual progress can be explained to an extend of 95.44% (value or R-square).