Utilize quantitative forecasting methods with time series data
Prompt:
This week is dedicated to Time-Series Forecasting techniques.
Start in the Lessons section of the classroom by completing the reading and viewing assignments. Don't even think about trying to complete this homework assignment without understanding the concepts involved in Forecasting Time-Series data.
Once you have the concepts down in chapter 5, and have completed the Discussion forum, you are ready to start this assignment. Download the Excel Template attached to this assignment onto your computer, open it up, and read through the "Red Snapper Scenario" at the top of the page.
All of this week's assignments focuses on historical Red Snapper Catches for 2015 through 2018 (by Quarter) off the coast of Louisiana. The last four rows of the table are empty as 2019 is what you are going to forecast using the historical data for 2015 through 2018.
As you scroll down through the Spreadsheet, you will notice in the far left column, there are five tasks for you to complete this week. Every one of these tasks is mirrored in the three YouTube videos below. Although this project is about Red Snapper Catches (your faithful designer's favorite fish to catch and eat out of the southern bays of Louisiana) and the videos forecast Car sales, the process for forecasting the future year (in our case 2019) is identical.
You will want to watch these videos as you complete each of the five tasks. Where you want to transition from video 1 to video 2 and from video 2 to video 3 is very easy to see. One easy way to get started is to have a video open on one side of your screen and the excel template on the other.
Youtube Videos:
Instructions:
Download the template attached below for this assignment onto your computer. Label this file as: JJohnston_Assignment_5
It's important that you complete the reading assignments first before you tackle this assignment.
There are several "Tasks" in the workbook; scroll down and make sure you have completed all five of the tasks in this assignment before pushing the send button.
When you first open the Template, you may say "OMG, what have I gotten myself into?" Rightfully so, this is a big project, and, like eating an 18-inch pizza, it's best done one bite at a time.
So, look over the tasks and then access the first video and get going. It's probably a good idea to watch the first video all the way through to get a feel for where you are going before starting on the first task. If you get lost and can't quite figure out how to proceed, message your instructor, we are always glad to help.
This week's assignment is best completed while watching the videos above in order. It is recommended that you have the video open on one side (1/2) of your screen and the homework template on the second half of the screen. That way, you can watch the video and complete the assignment synchronously. Make sense?
You must use formulas or Excel functions to calculate every answer. No credit will be given if a number is simply inserted (or plugged). Here's a (home made) help video that shows how to complete the Excel Spreadsheet:
Grading: There is no grading rubric for this assignment, your instructor has a key that includes exactly what the tables and charts should look like.
Assignment 5 Templatet_New (1)
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.
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.
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
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
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