When fitting a trend model on excel one would choose model for a quadratic trend.
|
The data in Strategic represent the amount of oil, in billions of barrels, held in the U.S. strategic oil reserve, from 1981 through 2008. Show
A. Plot the dataStep 1: Copy the data and paste it into the Excel sheet. Step 2: To create a scatter plot of the data, go to Insert -> Scatter -> Scatter with only markers. The scatter plot will instantly display on the screen.  Assign a variable t depicting the years. Calculate the values of t squared corresponding to the t series. Considering oil reserves as y, compute log y. Go to Formulas -> Math and Trigo -> LOG10 In cell E2, enter the number as B2 under the LOG10 dialog box. Auto-fill this formula till cell E29. B. Compute a linear trend forecasting equation and plot the trend line.Step 1: To calculate the linear trend, go to Data -> Data Analysis. Select the tool of Regression from the Data Analysis dialog box. Step 2: In the Regression dialog box, enter the following: Input y range: B1 to B29 Input x range: C1 to C29 Select the tick boxes: label, line fit plots. Step 3: click on ok. The regression results are displayed: Step 4: From the regression coefficients given in the summary output, we get the following linear forecasting equation: Y= 396.19 + 11.03 x C. Compute a quadratic trend forecasting equation and plot the resultsStep 1: To calculate the quadratic trend, go to Data -> Data Analysis. Step 2: In the Regression dialog box, enter the following: Input y range: B1 to B29 Input x range: C1 to C29 Select the tick boxes: label, line fit plots. Step 3: click on ok. The regression results are displayed: Step 4: From the regression coefficients given in the summary output, we get the following linear forecasting
equation: D. Compute an exponential trend forecasting equation and plot the resultsStep 1: To calculate the exponential trend, go to Data -> Data Analysis. Step 2: In the Regression dialog box, enter the following: Input y range: E1 to C29 Step 3: click on ok. The regression results are displayed: Step 4: compute the figure of 10^intercept. The intercept comes out to be 388.59 Now, we get the following exponential forecasting equation: Y = 388.59*10^0.0098*t Step 5: The plot of the trend line is: E. Which model is the most appropriate?The coefficients of determination for the three time series models we developed are: Linear model_R2 = 68.24% Quadratic model_R2 = 75.25% Exponential_R2 = 55.7% Since the coefficient of determination is the highest for the quadratic trend, therefore, the quadratic model seems the most appropriate F. Using the most appropriate model, forecast the number of barrels, in billions, in 2009. Check how accurate your forecast is by locating the true value for 2009 on the Internet or in your library
Excel Econometrics TutorialsMultiple Regression Analysis
Time Series Analysis
Data Analysis
What is quadratic trend in time series?Generally, a quadratic trendline is a second-order polynomial which attempts to best fit a set of data. The equation will look something like this: In our application, the x-value will be a measure of time like {1, 2, …, n}, and the y-value will be our KPI (sessions, leads, organic traffic, etc).
Which method should be used when your time series has both trend and seasonality?Winters' method It is appropriate for a series with both trend and seasonal variation.
What does a linear trend model imply?The linear trend model tries to find the slope and intercept that give the best average fit to all the past data, and unfortunately its deviation from the data is often greatest at the very end of the time series (the “business end” as I like to call it), where the forecasting action is!
What is a linear Forecast trendline?A linear trendline is a best-fit straight line that is used with simple linear data sets. Your data is linear if the pattern in its data points resembles a line. A linear trendline usually shows that something is increasing or decreasing at a steady rate.
|
