EX16_XL_CH03_GRADER_CAP_HW – Airline Arrivals Analysis 1.7
Project Description:
You are an analyst for the airline industry. You created a workbook that lists overall airline arrival statistics for several years. In particular, you listed the percentage and number of on-time arrivals, late arrivals, canceled flights, and diverted flights based on information provided by the Bureau of Transportation Statistics. You want to create charts and insert sparklines that show the trends to discuss with airline and airport managers.
Steps to Perform:
| Step | Instructions | Points Possible |
| 1 |
Start Excel. Download and open the file named e03_grader_h1.xlsx. Rename the file as LastNameFirst_Assignment6__ AirlineAnalysis.xlsx |
0 |
| 2 | Insert Line sparklines in the range G4:G7, using the data for the five years in the range B4:F7.
Hint: -Insert sparklines in range G4:G7 |
10 |
| 3 | Display the high and low points for the Sparklines.
Hint: -High points for Sparkline displayed |
4 |
| 4 | For the range containing Sparklines, change the high point marker color to Green (under Standard Colors).
Hint: -Sparklines high point marker color changed to Green |
4 |
| 5 | Select the range A4:A7 and the range F4:F7. Create a pie chart and move it to a chart sheet named Pie Chart.
Hint: -Range A4:A7 selected and used |
10 |
| 6 | Type 2014 Flight Arrivals as the chart title.
Hint: |
3 |
| 7 | Apply the Style 12 chart style. Format the chart title with Blue font color (under Standard Colors).
Hint: -Style 12 chart style applied correctly to chart |
6 |
| 8 | Change the Legend Position to Top to position the legend between the chart title and the plot area.
Hint: -Legend position to Top |
4 |
| 9 | Add data labels to the Best Fit position.
Hint: -Data labels added |
4 |
| 10 | Apply 12-pt size and bold the data labels.
Hint: -12-pt size applied to data labels |
4 |
| 11 | Format the Canceled data point with Dark Red fill color. Format the Late Arrival data point in Green. Explode the Late Arrival data point by 5%.
Hint: -Canceled data point formatted with Dark Red fill color |
5 |
| 12 | Select the range A10:F15 in the Arrivals worksheet and create a clustered column chart.
Hint: -Arrivals worksheet used |
10 |
| 13 | Type On-Time and Late Flight Arrivals for the chart title.
Hint: -Chart has a title |
3 |
| 14 | Position the clustered column chart so that the top-left corner is in cell A20. Change the chart width to 5.75 inches and the height to 3.5 inches.
Hint: -Make sure Excel units are in inches |
6 |
| 15 | Apply chart filters to remove the canceled, diverted, and total operations data.
Hint: -Chart filters applied to remove |
6 |
| 16 | Select the value axis, set 500000 for the Major unit, display the axis units in Millions, select category Number format with 1 decimal place.
Hint: -Value axis selected |
8 |
| 17 | Add a primary vertical axis title Number of Flights.
Hint: -Primary vertical axis added |
5 |
| 18 | Apply the Color 2 chart color to the chart.
Note, depending on the version of Office used, the color name may be Colorful Palette 2. Hint: -Color applied to chart |
4 |
| 19 | Apply the Light Gradient – Accent 3 fill to the chart area.
Hint: -Applied Gradient to chart area |
4 |
| 20 | Close the workbook, and then exit Excel. Submit the workbook as directed. | 0 |
| Total Points | 100 |
