Assignment Prompt & Instructions
Se Attached
**files (1)**
– ## Assignment5Templatet_New2.xlsx
**## Assignment5Templatet_New2.xlsx**
## Red Snapper Forecast
| | | | | | | | | | | | | | |
| — | — | — | — | — | — | — | — | — | — | — | — | — | — |
| Assignment Week 5: You have been asked to analyze the trends in Red Snapper taken out of the coastal waters of Louisiana. The data (in Tons) for the last four years is recorded in the table below. There are 4 Tasks you will need to complete in this Time-Series Data Analysis and forecasting. |
| Historical Quarterly data Red Snapper Take in tons 2015 – 2018 and Forecast for 2019 |
| | | | | | | | | | | |
| | | t | Year | Quarter | Tons (x 100) Yt | MA (Moving Average) | CMA | St & It Yt/CMA | Seasonal Trend St | DeSeasonalize Yt /St | Trend Component Tt (Uses the Regression Formula Created in Step 4) | Forecast St x Tt | |
| | | 1 | 2015 | 1 | 4.50 | | | | 1.21 | 3.73 |
| | | 2 | | 2 | 6.90 | | | | 1.58 | 4.36 |
| | | 3 | | 3 | 3.20 | 4.05 | 4.18 | 0.77 | 0.84 | 3.81 |
| | | 4 | | 4 | 1.60 | 4.30 | 4.41 | 0.36 | 0.398 | 4.02 |
| | | 5 | 2016 | 1 | 5.50 | 4.53 | 4.64 | 1.19 | 1.21 | 4.56 |
| | | 6 | | 2 | 7.80 | 4.75 | 4.78 | 1.63 | 1.58 | 4.93 |
| | | 7 | | 3 | 4.10 | 4.80 | 4.91 | 0.83 | 0.84 | 4.88 |
| | | 8 | | 4 | 1.80 | 5.03 | 5.20 | 0.35 | 0.40 | 4.52 |
| | | 9 | 2017 | 1 | 6.40 | 5.38 | 5.46 | 1.17 | 1.21 | 5.30 |
| | | 10 | | 2 | 9.20 | 5.55 | 5.60 | 1.64 | 1.58 | 5.81 |
| | | 11 | | 3 | 4.80 | 5.65 | 5.76 | 0.83 | 0.84 | 5.71 |
| | | 12 | | 4 | 2.20 | 5.88 | 5.80 | 0.38 | 0.40 | 5.53 |
| | | 13 | 2018 | 1 | 7.30 | 5.73 | 5.78 | 1.26 | 1.21 | 6.05 |
| | | 14 | | 2 | 8.60 | 5.83 | 5.84 | 1.47 | 1.58 | 5.43 |
| | | 15 | | 3 | 5.20 | 5.85 | 5.61 | 0.93 | 0.84 | 6.19 |
| | | 16 | | 4 | 2.30 | 5.37 | 4.56 | 0.50 | 0.40 | 5.78 |
| | | 17 | 2019 | 1 | | 3.75 | | | 1.21 |
| | | 18 | | 2 | | 2.30 | | | 1.58 |
| | | 19 | | 3 | | | | | 0.84 |
| | | 20 | | 4 | | | | | 0.40 |
| Task 1 | Create a Time series visualization of the historical take of Red Snapper by creating a line chart |
| | with markers, by year and quarter, and with an appropriate title. Do not use Column C for this plot, only D, e, and F |
| | Start by highlighting the column headings and then all data down through Quarter 4. Follow the directions for |
| | creating this chart given in the "Excel Time Series Part 1 of 3" in the classroom under Week 5 Lesson. |
| Information | Next you need a Moving Average of 4 months in Column G. This procedure is explained in the "Excel Time Series Part 1 of 3" |
| | at approximately the 11 minute point in the video. Column H has been completed for you showing a Centered Moving average (Time |
| | Stamp 12:30. |
| | This step has been completed for you, however it will help you to understand how this was done |
| Refer to the Lesson Video titled "Excel – Time Series Forecasting – Parts 2 of 3 |
| Information | In this step, we need to extract the seasonality (St) in the data. It has already been completed for you in the table below, however please view and follow the video instructions to see how this was done. The seasonality factors have all been inserted for you by quarter in the table above. Finally, this allows us to finish the "DeSeasonalizes" in column K |
| | This step has been completed for you, however it will help you to understand how this was done |
| | | | Quarter | St |
| | | | 1 | 1.21 |
| | | | 2 | 1.58 |
| | | | 3 | 0.84 |
| | | | 4 | 0.40 |
| Refer to the Lesson Video titled "Excel – Time Series Forecasting – Parts 3 of 3 to complete Task 4 |
| Task 2 | In this Task, you will be creating a Trend component in Column L. To calculate the "Trend Component" ( Tt ) you will need to Create a regression analysis using the "Deseasonalized Data" as the Y-variable (in Column K) and "t" (time period in Column C) as our X-variable. Make sure you pick up only the rows with data, not the 2019 Forecast rows (17 – 20). Use Data -> Data Analysis -> Regression. Follow the instructions on the Video (Part 3 of 3) to complete this part of the analysis. When asked where to insert the regression Analysis use B71. The constants you receive from the regression analysis will be used in Task 2 |
| Task 3 | Now that our Regression Analysis has given us our two constants, we can use these to complete the Trend Component Tt (in Column L) You need to create an Excel formula using Trend Component = intercept Constant + x-variable constant*t). You can now complete Column L for Rows 6 through 25 |
| Tasks 4 & 5 | You are now ready to complete the "Forecast Column" (Column M) for each of the 16 periods of existing data and the Forecast period for 2019 (Rows 22 – 25). The Forecast column is created by multiplying the Trend component (column L) by the Seasonal Trend (column J). Finally, in the space below, Create a final Line Chart Plot of the forecasted data (Column M) through all 5 years (2015 – 2019). Add a trend line to this Line Chart Plot. |
## image1.png
**## Assignment5Templatet_New2.xlsx**
## Red Snapper Forecast
| | | | | | | | | | | | | | |
| — | — | — | — | — | — | — | — | — | — | — | — | — | — |
| Assignment Week 5: You have been asked to analyze the trends in Red Snapper taken out of the coastal waters of Louisiana. The data (in Tons) for the last four years is recorded in the table below. There are 4 Tasks you will need to complete in this Time-Series Data Analysis and forecasting. |
| Historical Quarterly data Red Snapper Take in tons 2015 – 2018 and Forecast for 2019 |
| | | | | | | | | | | |
| | | t | Year | Quarter | Tons (x 100) Yt | MA (Moving Average) | CMA | St & It Yt/CMA | Seasonal Trend St | DeSeasonalize Yt /St | Trend Component Tt (Uses the Regression Formula Created in Step 4) | Forecast St x Tt | |
| | | 1 | 2015 | 1 | 4.50 | | | | 1.21 | 3.73 |
| | | 2 | | 2 | 6.90 | | | | 1.58 | 4.36 |
| | | 3 | | 3 | 3.20 | 4.05 | 4.18 | 0.77 | 0.84 | 3.81 |
| | | 4 | | 4 | 1.60 | 4.30 | 4.41 | 0.36 | 0.398 | 4.02 |
| | | 5 | 2016 | 1 | 5.50 | 4.53 | 4.64 | 1.19 | 1.21 | 4.56 |
| | | 6 | | 2 | 7.80 | 4.75 | 4.78 | 1.63 | 1.58 | 4.93 |
| | | 7 | | 3 | 4.10 | 4.80 | 4.91 | 0.83 | 0.84 | 4.88 |
| | | 8 | | 4 | 1.80 | 5.03 | 5.20 | 0.35 | 0.40 | 4.52 |
| | | 9 | 2017 | 1 | 6.40 | 5.38 | 5.46 | 1.17 | 1.21 | 5.30 |
| | | 10 | | 2 | 9.20 | 5.55 | 5.60 | 1.64 | 1.58 | 5.81 |
| | | 11 | | 3 | 4.80 | 5.65 | 5.76 | 0.83 | 0.84 | 5.71 |
| | | 12 | | 4 | 2.20 | 5.88 | 5.80 | 0.38 | 0.40 | 5.53 |
| | | 13 | 2018 | 1 | 7.30 | 5.73 | 5.78 | 1.26 | 1.21 | 6.05 |
| | | 14 | | 2 | 8.60 | 5.83 | 5.84 | 1.47 | 1.58 | 5.43 |
| | | 15 | | 3 | 5.20 | 5.85 | 5.61 | 0.93 | 0.84 | 6.19 |
| | | 16 | | 4 | 2.30 | 5.37 | 4.56 | 0.50 | 0.40 | 5.78 |
| | | 17 | 2019 | 1 | | 3.75 | | | 1.21 |
| | | 18 | | 2 | | 2.30 | | | 1.58 |
| | | 19 | | 3 | | | | | 0.84 |
| | | 20 | | 4 | | | | | 0.40 |
| Task 1 | Create a Time series visualization of the historical take of Red Snapper by creating a line chart |
| | with markers, by year and quarter, and with an appropriate title. Do not use Column C for this plot, only D, e, and F |
| | Start by highlighting the column headings and then all data down through Quarter 4. Follow the directions for |
| | creating this chart given in the "Excel Time Series Part 1 of 3" in the classroom under Week 5 Lesson. |
| Information | Next you need a Moving Average of 4 months in Column G. This procedure is explained in the "Excel Time Series Part 1 of 3" |
| | at approximately the 11 minute point in the video. Column H has been completed for you showing a Centered Moving average (Time |
| | Stamp 12:30. |
| | This step has been completed for you, however it will help you to understand how this was done |
| Refer to the Lesson Video titled "Excel – Time Series Forecasting – Parts 2 of 3 |
| Information | In this step, we need to extract the seasonality (St) in the data. It has already been completed for you in the table below, however please view and follow the video instructions to see how this was done. The seasonality factors have all been inserted for you by quarter in the table above. Finally, this allows us to finish the "DeSeasonalizes" in column K |
| | This step has been completed for you, however it will help you to understand how this was done |
| | | | Quarter | St |
| | | | 1 | 1.21 |
| | | | 2 | 1.58 |
| | | | 3 | 0.84 |
| | | | 4 | 0.40 |
| Refer to the Lesson Video titled "Excel – Time Series Forecasting – Parts 3 of 3 to complete Task 4 |
| Task 2 | In this Task, you will be creating a Trend component in Column L. To calculate the "Trend Component" ( Tt ) you will need to Create a regression analysis using the "Deseasonalized Data" as the Y-variable (in Column K) and "t" (time period in Column C) as our X-variable. Make sure you pick up only the rows with data, not the 2019 Forecast rows (17 – 20). Use Data -> Data Analysis -> Regression. Follow the instructions on the Video (Part 3 of 3) to complete this part of the analysis. When asked where to insert the regression Analysis use B71. The constants you receive from the regression analysis will be used in Task 2 |
| Task 3 | Now that our Regression Analysis has given us our two constants, we can use these to complete the Trend Component Tt (in Column L) You need to create an Excel formula using Trend Component = intercept Constant + x-variable constant*t). You can now complete Column L for Rows 6 through 25 |
| Tasks 4 & 5 | You are now ready to complete the "Forecast Column" (Column M) for each of the 16 periods of existing data and the Forecast period for 2019 (Rows 22 – 25). The Forecast column is created by multiplying the Trend component (column L) by the Seasonal Trend (column J). Finally, in the space below, Create a final Line Chart Plot of the forecasted data (Column M) through all 5 years (2015 – 2019). Add a trend line to this Line Chart Plot. |
## image1.png