Description

Forecasting Assignment – Due May 4

In this assignment, you will perform the role of analyst for Walmart and create a revenue forecast that includes descriptive and predictive analytics.

1.       Download the file forecasting_assignment.xlsx. The first worksheet contains quarterly Walmart U.S. revenue going back to 2007, in billions of dollars. The second column contains the number of stores in the U.S.

2.       Generate a line chart showing revenue through time. Is there a long-term trend? Is there a seasonal pattern?

3.       Create a smoothed revenue time series using a 3-quarter moving average (MA-3). Using this series, predict the revenue for the first quarter of 2020.

4.       Create a smoothed revenue time series using exponential smoothing, setting the smoothing coefficient (W) equal to .3. Using this series, predict revenue for the first quarter of 2020.

5.       We’re missing store number data for 2007-2011. One possibility for interpolating these numbers is to find a general trend of store numbers year-by-year. Perform a regression, predicting store number by year. Report the R-squared and the fitted equation. (Note that this is not perfect, since we’re using the fit equation to predict values outside the testing data, but this is the best we can do for now). Using the regression model, predict the number of stories for the years 2007 to 2011. Add them to the data set.

6.       Generate univariate descriptive statistics for all three variables. Report any outliers.

7.       Create a table showing pairwise correlations of all three variables. Our goal is to predict revenue using a regression, but we should not add two independent variables that are highly correlated. Which variable should we not include?

8.       Create dummy variables for Q1, Q2, andQ3 to incorporate the quarterly seasonality into your time series regression forecast.

9.       We need to interpolate what 2020’s US population will be. Use the same regression method you used to estimate store number in 5, using the Population Data worksheet.

10.   Fit the following regression. Report the values all the beta coefficients, the Adjusted R-squared, and the p-values for all t-tests on the independent variables.

 

11.   Using this equation, predict the revenue for the first quarter of 2020. Why is lower than the previous value?