Assignment Questions You must analyze and report on data for all the airline$$s flights that depart from the North Region $($i$.$e$.$ airports serving the cities of Detroit, Philadelphia, and Seattle$)$ and from the South Region $($i$.$e$.$ airports serving the cities of Dallas, Fort Lauderdale, and Phoenix$).$ These filtered data have been provided to you in worksheet titled $$Regions Data$.$ NB: Traditional formulae and$/$or manipulating the data set in some way or another $($e$.$g$.,$ adding rows, columns, etc.$)$ is not accepted for this assignment simply because you are only being assessed on your skills with Macros and mainly Pivot Tables for data analysis. While in reality making changes to the data in some cases can help produce cleaner results, you are asked not to do so in this assignment. Assume that you only have "read$-$only" access to the Regions Data and can only use Pivot Tables and the analysis tools within Pivot Tables $($e$.$g$.,$ Calculated Fields$).$ Management is interested in the flight routes that are most problematic for the airline in terms of departure delays and the reasons for the delays. Management would also like to know which routes tend to depart early and contrast them with the problematic routes. You know that using Macros and Pivot Tables can help you manage the large volume of data you have been given, and you have been asked to answer the following specific questions. Question $1$ Management has asked you to automate a system that can pull only the necessary columns from the large volume of data received, manipulate and analyze the data, and setup an automated data collection process. On a separate worksheet called $$Q$1,$ create a single macro that will: a$)$ Copy columns $$FL$\_$NUM$,$ORIGIN$,$DEST$,$DEP$\_$DELAY$,$AIR$\_$TIME$,$CARRIER$\_$DELAY$,$ and $$WEATHER$\_$DELAY$$ from worksheet $$Regions Data$$ and paste the values into the worksheet $$Q$1$ b$)$ Format the columns to insure they are wide enough to display all their data and titles. Sort the data by $$DEP$\_$DELAY$$ from highest to lowest. c$)$ Attach your macro to a button, image, or smartshape on the worksheet and assign it to a hotkey of your choice. Include the hotkey description as text on your button $($e$.$g$.$ CTRL$+$SHIFT$+$Q$).$

Question $2$ For questions $2-4,$ please build the appropriate Pivot Table$($s$)$ and include the answer to the question in the memo to management. Please create one $($or optionally more$)$ NEW PIVOT TABLE for each question $($avoid copying sheets to avoid errors$).$ The airline is interested in having categorized information about the average of different types of delay in each of the regions $($i$.$e$.,$ north and south$).$ Therefore, you are asked to provide the management with a PivotTable and a column PivotChart that include the average delay of each type for flights in each region. Provide your work on a worksheet labelled $$Q$2.$ Include a synopsis of your findings and the trend you see in the data in your memo to management.

Question $3$ The airline assesses its delays based on the extent of the departure delay, and flags flights as $$Early$($i$.$e$.,=0$ minutes late but $<60$ minutes late$),$Delayed$($i$.$e$.,>=60$ minutes late but $<120$ minutes late$),$Very Delayed$($i$.$e$.,>=120$ minutes late but $<=180$ minutes late$),$ and $$Extremely Delayed$($i$.$e$.,>180$ minutes late$).$ So$,$ in your PivotTables, group the flights based on their departure delay $($i$.$e$.,$ DEP$\_$DELAY$)$ using the above guidelines and then: Identify which $3$ routes $($i$.$e$.,$ ORIGIN $->$ DEST$)$ are: a$)$ The worst in each region in terms of the average departure delay $($i$.$e$.,$ DEP$\_$DELAY$)$ for flights categorized as $$Extremely Delayed$.$ Provide your work on a worksheet labelled $$Q$3$a$.$ b$)$ The best in each region in terms of the total count of $$Early$$ flights $($i$.$e$.$ largest number of $$Early$$ flights$).$ Provide your work on the worksheet labelled $$Q$3$b$.$ Please note that you need to create one PivotTable to answer each of the above questions. Include a synopsis of your findings in your memo to management.

Question $4$ The flight data includes details for flights which are delayed. Specifically, the data in columns J to N provide the total minutes attributable to carrier delay, weather delay, National Air System delay, security delay and late aircraft delay. You realize that you can provide management with a pivot table that shows how much of the delay that is attributable to the airline $($i$.$e$.,$ carrier delay$)$ as a percentage of total delay. Using this pivot table, determine which $3$ routes in each region $($i$.$e$.,$ ORIGIN $->$ DEST$)$ have the highest percentage of the total delay that is attributable to the airline. Provide all required information in your memo to management. Be sure to label your worksheet $$Q$4.$ Note: In order to answer the question, you need to create a new field $($i$.$e$.,$ percentage of carrier delay in the total delay$).$ Make sure that you create it in your PivotTable and not in the original dataset $($i$.$e$.,$ Regions Data worksheet. How do I answer the Question $3$ and $4$ using pivot table.