Question

Ch 04 HW 3 Forecasting problem Method: Linear Trend optimized with Solver - pick your own initial values for the y-axis intercept (a) and slope parameter (b) before optimizing the model The data for this problem is shown in the table below. Note: changes from the original problem data are highlighted in yellow. DO NOT USE the "brute force" method shown in Example 8 on pages 120-121. DO NOT USE the linear regression function from Excel's ToolPak or the LINEST Excel function. Set up a forecasting table and use Excel Solver to optimize it. Time (t) Actual A(t) 1 950 2 930 3 915 4 920 5 901 6 898 7 884 8 913 9 924 10 937 11 don't have yet What is the Forecast for Time Period 11 using this method?  

Answer 1

Answer:-

Given that:-

Linear Trend optimized with Solver - pick your own initial values for the y-axis intercept (a) and slope parameter (b) before optimizing the model The data for this problem is shown in the table below. Note: changes from the original problem data are highlighted in yellow. DO NOT USE the "brute force" method shown in Example 8 on pages 120-121. DO NOT USE the linear regression function from Excel's ToolPak or the LINEST Excel function. Set up a forecasting table and use Excel Solver to optimize it. Time (t) Actual A(t) 1 950 2 930 3 915 4 920 5 901 6 898 7 884 8 913 9 924 10 937 11 don't have yet

What is the Forecast for Time Period 11 using this method?  

Explanation: The Linear trend for the given data can be obtained using the Scatterplot chart of the MS Excel and adding a trendline to it. The scatterplot of the given data is as under:

The trendline for the given data is Y = -1.697X + 926.53

Forecast for the time period 11 = (-1.697 x 11) + 926.53 = 907.87