Reference no: EM132381529

Assignment -

The focus or purpose of the assignment- the reason for creating the Excel spreadsheet.

The trends that you see from the sparkline chart that you created. As a consultant to the Bureau of Transportation, what recommendations would you make based on the sparkline chart?

1. The focus or purpose of the assignment-the reason for creating the Excel spreadsheet.

2. The trends that you see from the sparkline chart that you created. As a consultant to the Bureau of Transportation, what recommendations would you make based on the sparkline chart?

You are an analyst for the airline industry. You created a work-book 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 sparklines will help show any trends in on-time arrivals compared to late arrivals, canceled flights, and diverted flights.

a. Open the e03c1Arrivals workbook and save it as e03c1Arrivals_LastFirst.

b. Insert Line sparklines in the range G4:G7, 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 A4:A7 and the range F4:F7.

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.

g. 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 (scheduled) 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 A10:F15 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 A20.

d. Change the width to 5.75" and the height to 3.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 area.

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 looter.

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

b. Save and close the tile. Based on your instructor's direction, submit e03c1Arrivals_LastFirst.

Attachment:- Assignment Files.rar