Business Intelligence Assignment
UFO Data
Let’s have a little fun. Jan Harzan, Executive Director of Mutual UFO Network, Inc. (MUFON), needs someone to analyze the UFO report data her organization has collected. She wants to know where and when most of the UFOs are sighted and whether there are any other useful patterns in the data.
You work as an intern for MUFON. The local MUFON chapter decides to volunteer you for the project because of your recent college courses. Jan agrees and gives you the data in the form of a CSV file named UFO_Sightings.csv that has 80333 records. This file contains data about when and where and what kind of UFOs have been reported.
Jan reminds you to add the three recent sightings reported in the news. Navy pilots captured some video of a UFO in the fall of 2017, and two commercial aircraft pilots reporting seeing the same UFO in Arizona on Feb. 24, 2018. (You will then have 80336 records in the spreadsheet.)
Copy this data into your own Excel spreadsheet and save it with your name and an abbreviation of the assignment in the file name (Example: TroboyKim_BI_UFO.xlsx). This data has some missing values and may have some errors. Look for these problems and decide what action to take. If you can reasonably infer what the missing data is or calculate a reasonable replacement for bad data, do that. Be sure to document your decisions. If you keep the record and correct the problem or plan to ignore it, add a note in a column to the immediate right of the data (no blank columns). If you decide to remove a record, place it in a separate worksheet labeled Bad Data with a note as to what the problem is.
Create a Documentation worksheet using the examples in the Tegrity video posted in Blackboard or in the Travel Agency spreadsheet in the Excel folder in the folder for your course in the T: Drive. In the Results section, report the results of the analysis (facts found). Add a Recommendations section and indicate what course of action the company should take, if any. Provide clear evidence for your results and recommendations in terms of Pivot Charts.
Perform a Pivot Table analysis to answer Jan’s three questions. Be sure to create at least four Pivot Tables and associated Pivot Charts. You must have at least one one-dimensional Pivot Table and at least one three-dimensional Pivot Table. I encourage you to have at least one two-dimension Pivot Table. Your analysis should permit at least two types of charts (bar, line, pie, etc.). (Hints: In the Value Field Settings options, check out the ‘Show Values as’ tab. Percentages allow pie charts.) Make sure that all Pivot Charts are labeled professionally. In particular adjust the title and legend, where necessary (don’t use a legend if there is no need for one).
Carefully label your work. Worksheets should be named appropriately and in an informative way ( not ‘Sheet1’, etc.), including those worksheets containing Pivot Tables/Charts. Create a separate worksheet if you add any calculated columns; preserve the original data in a worksheet by itself. Because Pivot Charts are interactive and change when you change the Pivot Table, use a separate worksheet for each Pivot table and it’s chart. Use those charts to support your results and recommendations. You can also paste copies of a Pivot chart on the Documentation worksheet itself, although they should be much smaller in size when used there.
Save your file using an appropriate file name (example: TroboyKim_BI_UFO.xlsx). Submit your file at the link in the Excel assignment folder in the Assignments area of Blackboard by the deadline listed in the Course Schedule.
