Marketing analytics I HOMEWORK EXERCISE 3

PART I:  THE BASS DIFFUSION MODEL
For Part I of the exercise you will examine the properties of the Bass Diffusion Model.  For this homework assume a market size of 25MM and a forecast period of 20 years (i.e., T=20).  Note that we also use the terms sales and adoption interchangeably.

HINT:  For graphing the sales and cumulative sales an XY Scatter graph with Smooth Lines is a good choice.  In Excel, click the Insert Tab then click the XY Scatter Box.  This will give you a menu of different scatter plots.  Choose the scatter with smooth lines and markers graph.  An example plot is shown below.









Q1.    Build a forecast of sales and the cumulative sales assuming p=0.03 and a q=0.4.  Plot both sales and cumulative sales on the same graph.  
Q2.    Now build a forecast of the sales and the cumulative sales assuming p=0.4 and a q=0.03. Create a second plot of both sales and cumulative sales on the same graph.  
Q3.    For each model in 1) and 2) compute the share of sales due to innovators and imitators.  

PART II:  Forecasting Apple Sales Revenues

Please answer the following questions using the Apple revenue data.

Q1.  Compute a 2 period (i.e. two quarter) moving average (MA) forecast (one quarter ahead) of the revenues for Amazon.  Using the 2 period MA, compute an exponentially smoothed forecast.  Report the optimal values for the smoothing parameter (alpha).  Provide a graph of the actual revenues and the one step forecasts from the MA and the exponentially smoothed model.

Q2.  Compute a one step ahead forecast of the Apple revenue data using a dynamic level, trend and seasonality model as we estimated in class (i.e. an additive Holt Winters Model).  The notes below offers some guidance on steps to follow.  Also refer to the slide deck and the instructor spreadsheet for additional information

    To estimate the model you will need initial estimates of the level, trend, and the seasonality quantities.  Use the first eight observations (2 years of data) to initialize the model (which implies your first forecasted value will correspond to the ninth observation in each series).  To capture seasonality use a dummy for each quarter and impose the constraint that the four dummies sum to zero.

    Once the initialization model is estimated, use the values for the quarterly dummies and the trend coefficient from the initialization model to initialize your forecast model.  You will also need to initialize the smoothing parameters alpha, beta, and gamma.  Set the initial values to 0.5 for each of these parameters.  The initial value for the level should be set to the observed value in that period adjusted by the appropriate seasonality factor (see class notes and instructor spreadsheet).  Once the initial values are all set use the updating equations to fill out the remaining values of level, trend and seasonality.  Based on these values, compute the step ahead forecast, the errors, the squared errors, and the sum of squared errors.  Using solver, find the values for alpha, beta, and gamma that minimize the SSE (subject to the constraint that each of these three parameters are less than or equal to 1 and greater than or equal to 0).

    Once the optimal values of alpha, beta and gamma are set, provide a graph of the actual revenues and the one step forecasts from the dynamic level, trend and seasonality model.

Q3.  Using your dynamic level, trend and seasonality model for each of four quarters that follow the last observation in the data (see the end of Session 15 slide deck for example).