Statistics
2. Go to Yahoo Finance, copy a dataset of about 50 prices of your choice of a company’s stock. It can be any company, for any year and time period (daily, weekly, or monthly stock prices). You need at least 50 prices minimum. If you have less, your work will not be accepted. For my video, I chose 1 year and weekly closing prices, but you can choose 5 years or 6 months, but you just need to adjust the frequency.
- Using Chapter 16 on forecasting methods,
Stock prices can be easily obtained from Yahoo Finance.
Yahoo! Finance:
http://finance.yahoo.com/
Below the “Home” menu, enter the name of a company and click on “Get Quotes”. On the next page opening, on the left side menu select “Historical Prices”.
3. You cannot have the same company or dates as your classmate. If I receive the same excel file for 2 students, I will not accept the work. Everyone should have their own company and their own date range.
4. Submit your answers in an Excel file on Moodle
5. Watch the following video on how to do the assignment
https://www.youtube.com/post_login?noapp=1
FORECASTING – TIME SERIES ANALYSIS IN EXCEL
(Chapter 16 in Excel)
EXERCISE #1: OBTAIN FORECASTED VALUES USING THE MOVING AVERAGE METHOD
Option 1: With Data Analysis Toolpak: Data ( Data Analysis ( Moving Average
In the dialog box: Enter the data range in “INPUT RANGE” and under “INTERVAL” enter the number for your choice of an MA order (e.g., for an MA(5), the number 5 was entered above).
Then under “OUTPUT RANGE”, highlight the cells in which you want to place the output but start with one cell after the first one with the real values. For example, from above, cell F3 was selected as the output range and the output will be as follows:
Option 2: Enter AVERAGE formulas in each cell
For example, to get the MA(5) forecasted value of $21.702 as shown in cell F7 the formula appears below:
EXERCISE #2: OBTAIN FORECASTED VALUES USING THE EXPONENTIAL SMOOTHING METHOD
Option 1: With Data Analysis Toolpak: Data ( Data Analysis ( Exponential Smoothing
In the dialog box: Enter the data range in “INPUT RANGE” and under “DAMPING FACTOR” enter the number for your choice of a weight (W) or smoothing coefficient between 0 and 1.
Excel uses this particular formula where W is the weight:
forecasted Yt = (1- W) Yt – 1 + W forecasted Yt – 1
Then under “OUTPUT RANGE”, highlight the cells in which you want to place the output but place it at the same row as the first observation with the real values. For example, as shown below, the output range was selected to be in cell F2.
The output will be:
Option 2: Enter the formulas in each cell
For example, with a smoothing factor of 0.50 in which the exponential smoothing equation is
forecasted Yt = (1- W) Yt – 1 + W forecasted Yt – 1
to get the exp. smoothing forecasted value of $22.87 in cell F4 the formula appears below:
EXERCISE #3: OBTAIN FORECASTED VALUES USING THE REGRESSION ANALYSIS METHOD
First, define your regression model; any model you wish but explain it using the equation form. For example, we can forecast using a First Degree Autoregressive Model with this equation:
Yt = a + b Yt -1
Note that in Excel you would need to create an extra column for the data at time Yt – 1 as shown below:
Option 1: With Data Analysis Toolpak: Data ( Data Analysis ( Regression
In the dialog box: enter under “Input Y Range” the
t
data. Under “Input X Range”, enter the
t-1
data. And, mark “Line Fit Plots” to get all the forecasted Yt
The output will be as shown below. Notice that by selecting “Line Fit Plots“, the regression output includes the values of each forecasted Yt under the column titled “Predicted Sara Lee at t” in the “Residual Output” section shown in the last section of the output.
From the regression output, using this data as an example, we can see that the intercept is equal to 3.611107174 and the slope is equal to 0.813321907. Thus, our regression equation is:
Yt = 3.611107174 + 0.813321907 Yt-1
Note: because the 95% confidence interval of the slope does not include 0, we can say that Yt is affected by its past values.
Option 2: Enter the formulas in each cell
To calculate the intercept and the slope, enter these formulas separately in a cell:
=INTERCEPT(data range of Yt, data range of Yt-1)
=SLOPE(data range of Yt, data range of Yt-1)
Below, you can see the formula for the intercept in cell I3
Below, you can see the formula for the slope in cell I4
Finally, to compute the forecasted values create one new column and use the regression equation with the values of the intercept and slope. For our sample data, this formula is
Yt = 3.611107174 + 0.813321907 Yt-1
and the Excel column will be as shown below:
EXERCISE #4: COMPARE THE FORECASTED VALUES FROM ALL THREE METHODS AND CHOOSE THE BEST ONE
In this case, we needed to calculate the forecast error under each method for each observation and use MAD = average absolute forecast error. Hence, the first part in Excel is to get the absolute value of each of the forecast errors under each method as follows (see formula in cell C7):
Then, we calculate the average of these forecast errors under each method to get the MAD as follows (see formula in cell C56):
As we can see the MAD is lower under the regression method, which is not surprising since we have an upward time trend in the data because these are stock prices that have been moving up over the years. In the case of an upward time trend it is better to use a regression model.
These are the forecasted values