Detailed Notes on Time Series Analysis & Forecasting topic 8
Behavior of Trend Lines and Time Series
- Random and stationary are considered the same.
- Analyzing time series data to identify characteristics such as random, stationary, trend, seasonal, or cyclical behaviors.
Analyzing Time Series Data - Question 9.3a
- Creating line charts for time series data (e.g., IBM, Intel, Cisco, GE, Dow Jones index).
- Example: For IBM, highlight the price column and plot it as a line graph.
- Trend Identification: Observe an upward trend over time.
- Interpretation can be refined (e.g., initial downward trend followed by an overall upward trend).
- Analyzing Intel's stock prices over time.
- Similar to IBM, Intel exhibits an upward trend. There's a downward behavior early in the time series, but it trends upward overall.
- Analyzing Cisco's stock prices over time.
- Upward trend in the first half, stationary behavior in the second half.
- Overall, Cisco exhibits an upward trend.
Analyzing Time Series Data - Question 9.3b
- Analyzing rate percentages from 2009 to 2012.
- Highlight the relevant columns and insert a line graph.
- Modify Y-axis bounds (e.g., set minimum to 7.5%) for better visualization.
- Trend Identification: Upward trend in the first part, downward trend in the later part.
- Without further data, the behavior appears random.
- Specific Behavior: Upward and downward movements suggest seasonal behavior.
- Seasonal Behavior: Repetitive movements within a year.
- Cyclical Behavior: Repetitive movements over more than one year.
Analyzing Time Series Data - Question 9.3c
- Analyzing new car sales data over three years.
- Generate a time series line chart.
- Limit the Y-axis bounds for better visualization (e.g., minimum to 35,000).
- Clear ups and downs correspond to nearly one entire year, indicating a seasonal behavior.
- Upward trend in the first half of the year, downward trend in the second half.
- Overall, an upward trend can be observed.
Important Note:
- Analysis of time series behavior depends on the focus (overall vs. specific).
- Interpretation is subjective.
Forecasting Using Simple Linear Regression (Question 9.13)
- Forecasting stock exchange prices using simple linear regression.
- The dataset includes dates and stock exchange prices.
- Objective: Forecast stock exchange prices for the next three days.
- Initial data preparation involves addressing date format issues in Excel.
- Dates are crucial for time series analysis and forecasting.
- Change the date format to be readable.
Approach for Forecasting
- Generate a scatter plot or line chart of the historical data.
- Insert a line chart with dates on the x-axis and stock exchange prices on the y-axis.
- Add a linear trend line to the chart.
- Display the equation of the trend line and the R-squared value.
Calculating Slope and Intercept
- Three methods to calculate slope and intercept:
- Using the trend line equation on the chart.
- Using the SLOPE and INTERCEPT functions in Excel.
- Using the regression tool in the data analysis tool pack.
- Using the SLOPE function:
- Known y's: The stock exchange prices (y-variable).
- Known x's: The dates (x-variable).
- Using the INTERCEPT function:
- Ensure the calculated slope and intercept match the trend line equation.
- Format the trend line equation to display sufficient decimal places for accuracy.
Consideration: Numerical Representation of Dates
- Excel represents dates as numerical values, starting from 01/01/1900 as 1.
- Current date example: May 2, 2025, is represented as 45,779.
- Sometimes, Excel considers dates as observation numbers (1, 2, 3, …) instead of actual date values.
- If the slope in the trend line equation differs, Excel is likely treating dates as observation numbers.
Forecasting Stock Exchange Index
- Always use the slope and intercept from the SLOPE and INTERCEPT functions for forecasting.
- Forecast calculation:
- Lock the intercept and slope values in the formula for consistent calculations.
- Drag the formula down to forecast for subsequent days.
- If the result appears as a date, change the format back to a number.
- The slope represents the inclination of the trend line and remains constant.
- The intercept may change based on how Excel interprets the numerical value of the dates.
Question 9.14: Alternative approach to forecasting using regression tool
- Visualizing Time Series with a Line Chart to identify Trend
- Insert Line Chart.
- Add Trendline: Linear.
- Display Equation and R-squared on chart.
- Equation from trendline
Data Analysis Tool for Regression
- Access Data Analysis Toolpak (ensure it's enabled in Excel Add-ins).
- Regression Analysis: Data > Data Analysis > Regression.
- Input Y Range: Annual CPI data.
- Input X Range: Years data.
- Ensure Labels are checked if headers are selected.
- Choose an Output Range in the worksheet.
Interpreting Regression Output
- Compare Intercept Values: Notice discrepancy between chart equation intercept and regression output (due to how years are treated numerically).
- Chart intercept: +180
- Summary intercept: -9,807.
- Slope Consistency: Slope will always be nearly identical across methods.
- Rely on Regression Output for Accuracy: Primarily use results from regression tool or SLOPE/INTERCEPT functions for forecasting.
Forecasting with Regression Output
- Finding slope:
- Finding slope:
- Calculate Forecast: Y = Intercept + Slope * X.
Scenario Comparison
Two Scenarios:
- X is treated as continuous years.
- X as incremental values (like scenario analysis).
Formula Consistency: Use equation format consistently aligned with format of 'X' values.
Example: Forecasting
- Scenario 1: Forecast for 2013 (treated incrementally) = intercept + slope * 11.
- Scenario 2: Forecast for 2013 (year value) = intercept + slope * 2013.
Important Note:
- When X is annual values, forecast using results (intercept, slope) from Data Analysis Toolpak or functions.
Question 15:
- Forecasting Using Simple Linear Regression
- Data: Internet Users (file: Internet users.xlsx).
- Objective: Forecast for 2013 - 2015.
Data Preparation
- Isolate Relevant Data: Columns for years, US internet users, Japan internet users.
- Create Forecast Years: Create list for 2013, 2014, 2015.
Visual Exploration
- Plot Time Series Data: Highlight years, US, and Japan; generate a line chart.
- Chart Recommendation: Use Combo chart if feasible.
Adding Trendlines
- Add trend lines for US and Japan.
- Display equation and R-squared for each trend line to access function for forecasting purposes.
Regression Analysis Functions
- Finding slope:
- Finding slope:
Slope and Intercept Calculation
- Use Excel Formulas: SLOPE (known_ys, known_xs) and INTERCEPT (known_ys, known_xs).
Efficiency with Cell Referencing
- Utilize relative and absolute cell references.
Generating Forecasts
- Apply Regression Equation: Forecast = Intercept + Slope * Year.
- Apply Excel Formulas: Use computed intercept and slope for both countries.
- Extending Trend Lines: Forecast effectively extends historical trend.
- Final Projections: Last three data points represent 2013-2015 Internet users.
Incorporating Seasonality in Forecasting: Multiple Regression model
- Multiple regression model that incorporates seasonality in the forecast.
Understanding Seasonality and Dummy Variables
- Creating Dummy Variables: To include months (January, February, etc.) in the regression, create dummy variables.
- Each dummy variable represents a month.
- If the observation is in January, the January dummy variable is 1; otherwise, it's 0.
- If this cell here is equal to this cell, result is a 1. To make everything else a 0, the output is a 0.
- And then one again and zero and so on. Okay?
- Name it as January, February, March, up until December. This can be made easier if you want to automatically create all of these dummy variables automatically.
- Finding the month:
=IF(C4=$D$3,1,0)
Statistical Considerations and Model Selection
- Assessing Model Fit: Check R-squared and significance F values.
is the coefficient of determination.
Implementation Steps
- Multiple Regression Setup: Open Data Analysis, choose Regression.
- Data Input: Specify the Y Range (dependent variable) and the X Range (dummy variables).
Dummy Variable Adjustments
- When including multiple dummy variables inside a model, it can be problematic.
- Ex:
- You have a data set and you want instrument type inside your regression model. and the instrument type is A, B, C, and D. you have zeros and ones in all of the rows and that they are related to each of the types. You need you to include A, B, C and D as a binary variable. The output will be the same error. This is because it will result in an error.
- Omit One Dummy Variable: Remove one dummy variable and that dummy variable that you excluded would be the baseline. This will avoid multicollinearity.
- Multiple independent variables, need to select them side by side, what you can do is either deselect 1 through 11, or 2 through 12 because they are side by side
Regression Execution: Excluding MAY
- Specify the Y Range: Total number of visits from data.
- X-range will be excluding the variable you'd like.
- Rearrange so that you can have columns side by side to make the regression process easier.*
Recalculate regression due to variables being in different places for the sake of selection to exclude dummy variables.
- Y-range is still total number of visits.
- X-range exclude may.
Intercept Interpretation
- Intercept Value: Intercept corresponds to the average tourist arrival, the estimation of the tourist arrival considering the month that you excluded.
Coefficient Interpretation
- The coefficients or all of the other dummy variables will be a interpretation based on the variable that you excluded.
- If November has 12,920 as a coefficient means that in addition November has 12,920 more tourist arrivals then compared to MAY.
- Other Coefficients: They indicate how much higher or lower tourist arrivals are in each month compared to the excluded month.