Moving Average calculation for Close prices.

Posted on by Leakey

Instructions for Forecasting Project

PREAMBLE

  1. Your report should be done using Microsoft Excel.
  2. There are 6 analyses to be done; each should be distinctly identified and should start on a new worksheet.
  3. All your calculations should be done using MS Excel’s cell formulas, no manual calculations.
  4. Save the Excel workbook as “Course Number_Lastname_Firstname”
    DATA COLLECTION AND PREPARATION
  5. Google “Yahoo Finance”
  6. At the top of the screen enter the name of the company you wish to analyze and select it.
  7. In the left panel, click on Historical Prices.
  8. In the “Set Date Range” field select data for the past 3 years:
    a. Start Date: Jan 20xx
    b. End Date: Dec 20xx
    c. Click on “Weekly”
  9. Click on “Get Prices” (Do not use the volume data; use all others.)
  10. At the bottom of the data, click on “Download to Spreadsheet” (Your data will be downloaded to MS Excel as a CSV file.)
  11. Save your data as an Excel Workbook using the following format:
    “Course Number_Lastname_Firstname”
  12. If necessary, reorder your data from oldest to newest so they can be in chronological order.
  13. Store your data as weekly data

ANALYSIS

  1. On one worksheet in MS Excel (Rename the tab 4-Period MA):
    a. Perform a 4-week Moving Average calculation for Close prices.
    b. Forecast each for the next week.
    c. Construct a table and calculate the BIAS, MAD, MSE, MAPE, MPE, and Tracking Signal (TS).
    d. On the same worksheet as the table, graph each price and its Moving Average on the same graph.
    e. On the same worksheet, plot a second graph of the Tracking Signal (TS) for this data and indicate if it is within Control Limits of +/-3.
  2. On a second worksheet in the same MS Excel workbook (Rename the tab 4-Period WMA):
    a. Perform a 4-week Moving Average calculation for Close prices.
    b. Forecast each for the next week.
    c. Construct a table and calculate the BIAS, MAD, MSE, MAPE, MPE, and Tracking Signal (TS).
    d. On the same worksheet as the table, graph each price and its Moving Average on the same graph.
    e. On the same worksheet, plot a second graph of the Tracking Signal (TS) for this data and indicate if it is within Control Limits of +/-3.
  3. On a third worksheet in the same MS Excel workbook (Rename the tab Exp. Smthg.):
  4. Using Exponential Smoothing with  = 0.3, forecast the volume for the next week.
  5. Construct a table and calculate the BIAS, MAD, MSE, MAPE, MPE, and Tracking Signal (TS).
  6. On the same worksheet as the table, graph the each price, and the Exponentially Smoothed data on the same graph.
  7. On the same worksheet, plot a second graph of the Tracking Signal (TS) for this data and indicate if it is within Control Limits of +/-3.
  8. On a fourth worksheet in the same MS Excel workbook (Rename the tab Exp. Smthg.w_Trend):
  9. Using Exponential Smoothing with Trend; where  = 0.3,  = 0.2, Forecast each price for the next week.
  10. Assume T1 = 0. Calculate the BIAS, MAD, MSE, MAPE, MPE, and Tracking Signal (TS).
  11. On the same worksheet as the table, graph each price and the Exponentially Smoothed (with Trend) data on the same graph.
  12. On the same worksheet, plot a second graph of the Tracking Signal (TS) for this weekly data and indicate if it is within Control Limits of +/-3.
  13. On a fifth worksheet in the same MS Excel workbook (Rename the tab Regression):
    a. Develop a Regression Equation for the weekly data volume.
    b. Based on your regression, forecast each price for the next week.
    c. Construct a table and calculate the BIAS, MAD, MSE, MAPE, MPE, and Tracking Signal (TS).
    d. On the same worksheet as the table, graph each price and the Trend line data on the same graph.
    e. On the same worksheet, plot a second graph of the Tracking Signal (TS) for each price data and indicate if it is within Control Limits of +/-3.
  14. On a sixth worksheet in the same MS Excel workbook (Rename the tab Seasonal Indices):
    a. Develop a Seasonal Indices for the weekly data for each price.
    b. Adjust your trend data with the seasonal indices and forecast each price for the next week.
    c. Construct a table and calculate the BIAS, MAD, MSE, MAPE, MPE, and Tracking Signal (TS).
    d. On the same worksheet as the table, graph each price and the seasonally adjusted trend data on the same graph.
    e. On a separate graph on the same worksheet, plot the Tracking Signal (TS) for the seasonally adjusted trend data for each price and indicate if it is within Control Limits of +/-3.

NOTE:
The overall report of your analysis should compare the performance of each of the 6 methods developed and recommend the BEST forecasting method.

This question has been answered.

Get Answer