MGNT 333 – Fall 2020
Forecasting Assignment – Due Nov 4
In this assignment, you will perform the role of analyst for Walmart and create a revenue forecast that includes descriptive and predictive analytics.
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
Generate a line chart showing revenue through
time. Is there a long-term trend? Is there a seasonal pattern?
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.
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.
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.
Generate univariate descriptive statistics for
all three variables. Report any outliers.
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?
Create dummy variables for Q1, Q2, andQ3 to
incorporate the quarterly seasonality into your time series regression
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.
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?