Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Please use excel to solve. Thanks! Assignment 6-Time Series Analysis Note this is not a timed assignment. For each question on this assignment you must
Please use excel to solve.
Thanks!
Assignment 6-Time Series Analysis Note this is not a timed assignment. For each question on this assignment you must create a separate worksheet in Excel. You will submit only one Excel file. Questions 45 through 49 refer to the data in the file that accompanies this book named MortgageRates.xlsx in chapter 11 data resources. It contains average monthly 30-year mortgage rates over 82 consecutive months. 45. a. Compute the two-period and four-period moving average predictions for the data set. b. Compute the MSE for each of the two moving averages. Which appears to provide the best fit for this data set? C. Compute forecasts for the next two months using the two-period and four-period moving average techniques. 46. Use Solver to determine the weights for a four-period weighted moving average on the data set that minimizes the MSE. a What are the optimal values for the weights? c. What are the forecasts for the next two months using this technique? 47 Create an exponential smoothing model that minimizes the MSE for the data set. Use Solver to estimate the optimal value of alpha. a. What is the optimal value of alpha? c. What are the forecasts for the next two months using this technique? 48. Create a Double Moving Average model (with k-4) for the data set. b. What are the forecasts for the next two months using this technique? 49. Use Holt's method to create a model that minimizes the MSE for the data set. Use Solver to estimate the optimal values of alpha and bet. a. What are the optimal values of alpha and beta? C. What are the forecasts for the next two months using this technique? 3 8 237250 32202504 4908 3 9 10 9 4 3 4 405221749 R-7, 7 7 7 8 8 8 8 8 77777787777 7. 7 7 7 6 7 7 7 7 7 6 6 6 6 6 6 6 123456789 10 1 2 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 11 12 1 112 3 4 5 6 7 8 9 10 y t 5226709 8 7 92321 666767 88888887777 77677776 66677 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 0 1 12 1 2 3 4 7 90123456 10 6.62 6.66 7.07 7.00 6.89 7.01 6.99 6.81 6.65 6.49 6.29 6.09 6.11 71 72 73 74 75 76 12 78 79 81 82 10 Assignment 6-Time Series Analysis Note this is not a timed assignment. For each question on this assignment you must create a separate worksheet in Excel. You will submit only one Excel file. Questions 45 through 49 refer to the data in the file that accompanies this book named MortgageRates.xlsx in chapter 11 data resources. It contains average monthly 30-year mortgage rates over 82 consecutive months. 45. a. Compute the two-period and four-period moving average predictions for the data set. b. Compute the MSE for each of the two moving averages. Which appears to provide the best fit for this data set? C. Compute forecasts for the next two months using the two-period and four-period moving average techniques. 46. Use Solver to determine the weights for a four-period weighted moving average on the data set that minimizes the MSE. a What are the optimal values for the weights? c. What are the forecasts for the next two months using this technique? 47 Create an exponential smoothing model that minimizes the MSE for the data set. Use Solver to estimate the optimal value of alpha. a. What is the optimal value of alpha? c. What are the forecasts for the next two months using this technique? 48. Create a Double Moving Average model (with k-4) for the data set. b. What are the forecasts for the next two months using this technique? 49. Use Holt's method to create a model that minimizes the MSE for the data set. Use Solver to estimate the optimal values of alpha and bet. a. What are the optimal values of alpha and beta? C. What are the forecasts for the next two months using this technique? 3 8 237250 32202504 4908 3 9 10 9 4 3 4 405221749 R-7, 7 7 7 8 8 8 8 8 77777787777 7. 7 7 7 6 7 7 7 7 7 6 6 6 6 6 6 6 123456789 10 1 2 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 11 12 1 112 3 4 5 6 7 8 9 10 y t 5226709 8 7 92321 666767 88888887777 77677776 66677 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 10 11 12 1 2 3 4 5 6 7 8 9 0 1 12 1 2 3 4 7 90123456 10 6.62 6.66 7.07 7.00 6.89 7.01 6.99 6.81 6.65 6.49 6.29 6.09 6.11 71 72 73 74 75 76 12 78 79 81 82 10Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started