Case Problem 2 Data Files needed for this Case Problem: NP_EX_10-4.xlsx, Support_EX_10_Turkeys.csv, Support_EX_10_Migration.csv Department of Ornithology Karen Hatch is a professor in the Department of Ornithology at the University of West Hilton in Florida. Professor Hatch is examining the migratory habits of turkey vultures and has compiled tracking data on seven birds. Professor Hatch wants you to complete a workbook that displays the turkeys migratory pattern and analyzes the relationship between each birds physical characteristics and the total distance they traveled. Complete the following: 1. Open the NP_EX_10-4.xlsx workbook located in the Excel10 > Case2 folder included with your Data Files. Save the workbook as NP_EX_10_Ornithology in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. Use Power Query to access the Support_EX_10_Turkeys.csv file, opening the file in the Power Query Editor, and then doing the following: a. Rename the query as Turkeys. b. Remove the summary text in the first three rows of the file. c. Use the text in the new first row as column headers. d. Remove the Species, Tracking Type, and Tracker columns from the data. e. Close and load the query to establish a connection to the Turkeys query and load it into the Data Model. Do not load the data into an Excel table, PivotTable, or PivotChart. 4. Use Power Query to access the Support_EX_10_Migration.csv file, and then edit the query as follows: a. Rename the query as Migration. b. Remove the first three rows from the data and use the new first row as column headers. c. Select the DateTime column; on the Add Column tab, in the From Date & Time group, click the Date button; and then click the Date Only command to create a new column named Date that contains only the date from the DateTime column. d. Remove the DataTime column from the query. e. Select the Date column and create a new column showing the End of Month date. Rename the column as YearMonth. f. Close and load the query to establish a connection to the Migration query and load it into the Data Model. Do not load the data into an Excel table, PivotTable, or PivotChart. 5. Use Power Pivot to create a relation between the Turkeys and Migration tables through the Tag field. 6. Karen wants to track the monthly distance traveled by each turkey vulture throughout the years. In the Migration Pivot worksheet, in cell B4, insert a PivotTable to track the monthly distance and average speed by each turkey vulture throughout the years. a. Rename the PivotTable as Migration Pivot. b. Move the Distance field from the Migration table to the Values area to calculate the sum of the Distance field. Rename the value field Miles Traveled. c. Move the YearMonth field to the Rows area. Remove the YearMonth (Year), YearMonth (Quarter), and YearMonth (Month) fields generated by Excel, leaving only the YearMonth field. 7. Karen wants a scatter chart of the data in the PivotTable. Copy the data in the range B4:C16 and paste a link to those copied cells in the range E4:F16 to set up the data for a scatter chart. Format the data in the range E5:E16 using the Short Date format. Format the Miles Traveled data in the range F5:F16 using the Number format with a thousands separator and no decimal places. Complete the scatter chart as follows: a. Move the chart to cover the range B12:H27 on the Migration Dashboard worksheet. b. Change the chart title to Turkey Vulture Monthly Migration. c. Add the axis title Miles Traveled to the vertical axis and Date to the horizontal axis. d. Select the data labels on the horizontal axis and use the Orientation button in the Alignment group on the Home tab to angle the date text counterclockwise to fit within the chart area space. 9. In the range B6:H10, add a slicer for the Tag field from the Turkeys table. Lay out the slicer in 4 columns and connect it to the Migration Pivot PivotTable. Filter the chart to show the migration results from tag B45664. 10. Karen wants to investigate whether a relationship exists between migration distance and the birds weight. In the Weight Analysis worksheet, in cell B4 insert a PivotTable with the Weight field from the Turkeys table in the Rows area and the sum of the Distance field from the Migration table in the Values area. Youll use this to investigate whether a relationship exists between migration distance and the birds weight. 11. Copy the data in the range B4:C12 and paste a link in the range E4:F12. Display the values in the range F5:F12 as numbers with a thousands separator and no decimal places. Create a scatter chart of the data in the range E4:F12. Add a linear trendline to the chart and change the chart title to Miles Traveled vs. Weight (oz). Move the chart to the range J4:O11 in the Migration Dashboard worksheet. 12. Karen wants to see whether length is related to migration distance. In the Length Analysis worksheet, repeat Step 10 comparing the migration distance to the birds length. Place the chart in the range J12:O19 of the Migration Dashboard worksheet and change the chart title to Miles Traveled vs. Length (in). 13. In the Wingspan Analysis worksheet, repeat Step 10 to compare migration distance to the birds wingspan. Place the chart in the range J20:O27 of the Migration Dashboard worksheet with the chart title Miles Traveled vs. Wingspan (in). 14. Karen wants to display the route that the eight turkey vultures followed. Insert a 3D Map, and then do the following: a. Add map labels to the 3D globe. b. Drag the Latitude and Longitude fields from the Migration table to the Location area to show the path of the migration. c. Drag the Speed field from the Migration table to the Height area so that the height of the data markers is proportional to the birds speed. d. Drag the Tag field from the Turkeys table to the Category area to identify each birds migration route. e. Resize the Layer 1 box so that you can see all of the legend entries. 15. Do the following to see the turkey vulture migration in action: a. Drag the Date field from the Migration table to the Time area. b. Above and to the right of the Time area box, click the Clock button and click Data shows for an instant to have each marker replaced by the subsequent location of the bird in its migration. c. Click the Play button below the map to view the migration of the eight birds in action. 16. Close the 3D Map window, save the workbook, and then close it.