First forecasting method we will use today is the Nai've Forecasting Method. 3. Copy the data set to a new sheet. The sales data for
First forecasting method we will use today is the Nai've Forecasting Method. 3. Copy the data set to a new sheet. The sales data for the 22 weeks are entered into worksheet rows 3 through 24 of column B. b. Name the worksheet properly. c. Develop the chart by adding four more columns {see the chart below} to collect and analyze the forecast values and forecast accuracy. d. Nai've Forecasting Method uses the most resent value of a time series variable as a forecast for the next period. In our case, we use the most resent week's sales as a forecast for the next wee k. So, we can start our forecast from week 2 and go down to week 23. Fill in the forecast column rows4 through 24 of column C, also fill in the forecast value for the 23'\"I week into the cell C25. e. Fill in three more columns as follows: o Absolute 1value of Forecast Errorjust enter in the corresponding cell of the row 4 'week 2' D4 the function abs {sales value forecast value} = abs [B4424], then draw it down to the row 'week 22'. Squared Forecast Error it is just a square of the results in the cells D4 to D24. Absolute 1value of Percentage Error calculate in the cell F4 = D4IB4"1IDD, it"s the ratio of the Absolute 1value of Forecast Error and the Sales value times IDD. Draw it down to the cell F24, which the row \"week 22' o In the cells D25, E25, and F25 find the values of MAE, MSE, and MAPE as the average of the corresponding columns. Absolute Percentage Error, 96 f. Construct a time series plot of the sales and the Naive forecast. Part 2: Moving Average Forecasting Method (25 points) The second forecasting method we will explore today is the Moving Average Forecasting Method. a. Copy the raw data set to a new sheet, and name the worksheet properly. b. Develop the same chart to collect and analyze the forecast values and forecast accuracy. C. Moving Average Forecasting Method we will use to produce three-week moving average (k = 3). Here are the steps: o Click the Data tab on the Ribbon. In the Analysis group, click Data Analysis o Choose Moving Average from the list of Analysis Tools o When the Moving Average dialog box appears: Enter B3:B 24 in the Input Range box - Enter 3 in the Interval box - Enter C4 in the Output Range box Click OK o The three-week moving averages will appear in column C of the worksheet. The forecast for week 4 appears next to the sales value for week 4, and so on including the forecast for week 23. You can delete #N/A signs in the cells above week 4. d. Perform the forecast accuracy analysis and find the values of MAE, MSE, and MAPE for this method. e. Construct a time series plot of the Sales and the Three-Week Moving Average forecast (k = 3).Gasoline Sales Time Series after Obtaining the Contract Sales, 1000s of Week Gallons 17 21 19 23 18 16 20 18 22 10 20 11 15 12 22 13 31 14 34 15 31 16 33 17 28 18 32 19 30 20 29 21 34 22 22
Step 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