Step 1: Enter the data You must enter grades and dates into different Microsoft Excel cells in thi
a. Open Microsoft Excel.
b. In the A1 cell, type Date.
c. In the B1 cell, type Grade.
d. In the C1 cell, type Forecast.
e. In the D1 cell, type Lower Confidence Boundary.
f. In the E1 cell, type Upper Confidence Boundary.
g. Starting with the A2 cell, type the following dates in the A2 through A11 cells:
1/8/2018, 1/15/2018, 1/22/2018, 1/29/2018, 2/5/2018, 2/12/2018, 2/19/2018, 2/26/2018, 3/5/2018, 3/12/2018,
3/19/2018, 3/26/2018, 4/2/2018, 4/9/2018
h. Starting with the B2 cell, type the following grades in the B2 through B11 cells:
100, 90, 75, 80, 50, 95, 85, 100, 80, 75
Step 2: Execute a Data Forecast In this section, Excel will be used to forecast what your grades will be for the weeks that remain. Note this forecast is based on what the qualifications you've already accomplished.
a. Use the forecast sheet function. Click and hold to select cells A1 through B11 (all of the data where there is data in both the A and B columns).
Note: If you do not have the 2016 version of Excel (Windows only), skip to Step 2g (Mac users go here).
b. Select the Data menu option > Forecast Sheet.
Note: If you do not have the Forecast icon available in the Data menu option, but have the 2016 version of Excel, select the File menu option -> Options -> Excel Add-Ins -> Go -> enable the checkbox beside Analysis ToolPak > OK. If you return to the File > Options > Add-Ins window, you should see the Analysis ToolPak in the top section where the active add-ins list.
c. In the Forecast End calendar window, select 4/9/2018 as the end date.
d. Expand the Options area by clicking once on the arrow beside the word "Options." Notice that you can adjust the confidence interval (the upper and lower limits of what Excel predicts you will score in the next few weeks).
e. Click the Create button. The window and graph shown should be similar to what is shown. Note that the graph was moved below the data just to capture the screen.
f. Notice that Excel predicts that you will make 80.39 on March 19th, but is 95% confident that it really will be a score somewhere between 47.54 and 113.23.
What score is predicted for April 2nd?
Within what range of scores is Excel 95% confident that you will make on April 9th?
g. If you do not have the 2016 version of Excel or if you just like using a formula instead of the using the menu, you can input a formula and get the same numbers.
In C12 enter the following formula:
In D12, enter the following formula:
In E12, enter the following formula:
To create the similar graph, simply copy the formulas down to the rest of the rows and insert a line graph.
Part 2: Predicting the Super Bowl.
Use the above technique (include a chart!) for predicting the Superbowl in this segment. Place two new sheets in the same excellent workbook, one for the 49ers and one for the Chiefs
For each team in the Superbowl, you need to use the regular season scores and dates (don't use preseason or playoffs). There are a total of 16 weeks (do not use the bye week).
Be sure to update your ranges and target dates within either the wizard or your formulas (depending which you used).
Answer the following questions within your spreadsheet:
How accurate do you believe this forecast was to the actual Super Bowl score?
What information do the confidence intervals provide?