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.

Insert and Format Sparklines

The first dataset shows the percentages. You want to insert sparklines that show the trends in the five$-$year data. The spar$-$klines will help show any trends in on$-$time arrivals compared to late arrivals, canceled flights, and diverted flights.

a$.$ Open the e$03$c$1$ Arrivals workbook and save it as e$03$clArrivals LastFirst.

b$.$ Insert Line sparklines in the range G$4$:G$7,$ using the data for the five years.

c$.$ Display the high and low points for the sparklines.

d$.$ Change the high point marker color to Green.

Create a Pie Chart

You want to focus on the arrival percentages for $2014.$ Creating a pie chart will help people visualize the breakdown of all operations for that year. After you create the chart, you will move it to its own chart sheet and edit the chart title to reflect $2014$ flight arrivals.

a$.$ Select the range A$4$:$7$ and the range F$4$:$7.$

b$.$ Create a pie chart and move it to a chart sheet named

Pie Chart.

c$.$ Change the chart title to $2014$ Flight Arrivals.

Add and Format Chart Elements

You want to format the chart by applying a different chart style and positioning the legend above the plot area. Furthermore. you need to add data labels so that you will know the percentages for the arrival categories. Finally, you want to emphasize the canceled flights in Dark Red and explode the late arrival pie slice.

a$.$ Apply the Style $12$ chart style to the pie chart.

b$.$ Format the chart title with Blue font color.

c$.$ Position the legend between the chart title and the plot area.

d$.$ Add data labels to the Best Fit position and display.

e$.$ Apply bold to the data labels and change the font size to $12.$

f$.$ Format the Canceled data point with Dark Red fill color and format the Late Arrival data point in Green.

$8.$ Explode the Late Arrival data point by $5\%.$

Create and Size a Column Chart

To provide a different perspective, you will create a clustered column chart using the actual number of flights. The Total Operations row indicates the total number of reported $($sched$-$uled$)$ flights. After creating the chart, you will position and size the chart below the source rows.

a$.$ Create a clustered column chart using the range

A$10$:F$15$ in the Arrivals sheet.

b$.$ Edit the chart title: On$-$Time and Late Flight Arrivals.

c$.$ Position the clustered column chart so that the top$-$left corner is in cell A$20.$

d$.$ Change the width to $5\mathrm{.}75"$ and the height to $3\mathrm{.}5".$

Format the Column Chart

Now that you have created the column chart, you realize that some data seems irrelevant. You will filter out the unneeded data. format the value axis to remove digits, insert a vertical axis title, apply a color change, and format the chart area.

a$.$ Apply chart filters to remove the canceled, diverted, and total operations data.

b$.$ Select the value axis, set $500000$ for the Major unit. display the axis units in Millions, select category

Number format with $1$ decimal place.

c$.$ Add a primary vertical axis title Number of Flights.

d$.$ Apply the Color $2$ chart color to the chart.

e$.$ Apply the Light Gradient $-$ Accent $3$ fill to the chart

Finalizing the Workbook

You want to prepare the workbook in case someone wants to print the data and charts. The margins and scaling have already been set. You just need to insert a footer.

a$.$ Create a footer on each worksheet with your name, the sheet name code, and the file name code.

b$.$ Save and close the file. Based on your instructor's direction. submit e$03$c$1$ Arrivals LastFirst.