Math excel HW

OPMT 5751 ONLINE Forecasting Project Kevin Wainwright

INSTRUCTIONS:

Part 1: Setting up

You will be replicating 3 examples found in the reading found on the course website.

http://faculty.bcitbusiness.org/kevinw/5751/Ch13_Time_Series_Forecasting-Holt-Winter-DummyVar.pdf

You will also need to download a ZIP file that contains Statpro and the three data files. The file is called 5751_Project. Once you

have downloaded and un-zipped the folder. Open it. You will see the following files:

IMPORTANT:

There are 3 data files: 5751_Hammer_Sales, 5751_HouseSales, 5751_Soft_Drink_Sales2 (See image) needed for the

assignment

There is an Excel addin file: StatPro (see image) You will need to use this to install StatPro into Excel. All other files are sub-

routines used by the StatPro program. Do not do anything with these files, but keep them in the same folder as StatPro

I strongly recommend you keep all your work in this folder.

INSTALLING StatPro

The easiest method is to go to the folder and double click on the stat pro file. This will start Excel and install StatPro. Note that

you will need to do this every time you want to do a session using StatPro.

Alternatively, you can permanently install StatPro using “Options” -> “Add-ins” within excel. If you are not familiar with this

process. Watch the video on the website.

OPMT 5751 ONLINE Forecasting Project Kevin Wainwright

Once you double click on StatPro and Excel begins, you may get this message. ENABLE MACROS

Then click on the ADD-INS Tab. You should see StatPro

Note that StatPro may behave differently with different versions of Windows and Office.

Second, you can do most of these problems without StatPro, but they will take longer and equire more

manual inputting of formula’s and graphs.

Finally, this will not work on Apple Computers. You need a PC. Older versions of Excel are fine.

OPMT 5751 ONLINE Forecasting Project Kevin Wainwright

Problem 1: Auto-regression: Forecasting with lagged dependent variables.

First, read the Example 13.5 from pages 741-745 from Chapter 13 found on the website. You have a file

entitled 5751_Hammer_Sales.xlsx, which has weekly sales for 42 weeks. You are going to build a

forecast model where the current period sales (Yt) are a function of previous (lagged) periods sales. In

particular, you will estimate the following three equations.

Yt = a + b1Yt-1 + e

Yt = a + b1Yt-1 + b2Yt-2 + e

Yt = a + b1Yt-1 + b2Yt-2 + b3Yt-3 + e

Exercise:

1. Generate a scatter plot of the data series. You will use this chart as a reference for your forecast.

2. Set up three columns of lagged data. You do this by copying the sales data and pasting into the

next column with the appropriate shift.

Note that to run the regressions (three) you will need to omit rows that have no values,

otherwise it will not regress properly. You must have equal number of rows for each and every

variable.

3. Now review the results of your regressions. Comment on the R square and p-values. Which of

the three models would you choose for your forecast equation?

4. Using the model you chose to create a column of forecasts next to the column of actuals

Be sure to use proper cell references such that the week you forecast is a function of previous

months Actual sales (not previous forecasts). Show both the actual sales and forecast in the

same chart. How good is your forecast?

Week Sales Lag1 Lag2 Lag3

1 115

2 125 115

3 116 125 115

4 130 116 125 115

5 82 130 116 125

6 81 82 130 116

7 69 81 82 130

Week Sales Forecast

1 115 115

2 125 Equation

3 116 Here

4 130

5 82

6 81

7 69

OPMT 5751 ONLINE Forecasting Project Kevin Wainwright

Problem 2: Moving Averages versus Holts Method.

This section requires the file 5751_HouseSales.xls. You should also watch the video on Holt-Winter’s

Method from the course website before beginning the assignment.

a) For the moving average approach, read and replicate Example 13.6 pages 747-751

b) For the Holt’s method, read and replicate Example 13.6 pages 758 to 761

Write up the results for both. Explain which method you would recommend and give an explanation as

to why that is your choice.

Problem 3: Seasonal Forecasting – Winter’s Method versus Dummy Variables

This section requires the file 5751_Soft_Drink_Sales2.xls. This file will have two sheets. One for Winter’s

Method and one for the Seasonal Dummy Variables approach.

For Winters method, read and replicate Example 13.7 pages 765 to 768.

For Seasonal Dummy Variable approach, read and replicate Example 13.7 pages 773 to 776. Note the

forecast equation on page 774. I would recommend against using it as typed. Instead, manually type the

equation using the constant and coefficient values of your regression.

=EXP($L$16+$L$17*C2+$L$18*D2+$L$19*E2*$L$20*F2)

This formula goes in Cell H2 of work sheet (see image)

OPMT 5751 ONLINE Forecasting Project Kevin Wainwright

ALSO, remember, that for your forecast, you need to add “Period” and each of the dummy variable for

the forecast:

Use the StatPro Chart option “Time Series” to plot the actual sales and the forecast

In your write-up, compare the two methods.

800

1600

2400

3200

4000

4800

5600

6400

Quarter

Time series plot of Units and Forecast

Units

Forecast