Computer Science Excel and Pdf assignment

Assignment 4

CIS110 – Spring Term 2017

Point Value: 100 points

Assignment Due Date: 11:59 pm Wednesday February 22, 2017

Learning Objectives:

 To create various types of charts: column chart, bar chart, line chart, and pie chart

 To present data with visual representations

 To add chart elements

 Format chart elements

 Create and customize sparklines.

Part I: multiple choices (50 points, 5 points for each question)

Please select the best answer:

1. Which type of chart is the least appropriate for depicting yearly rainfall totals for five

cities for four years:

(a) Pie chart

(b) Line chart

(c) Column chart

(d) Bar chart

2. What is the typical sequence for creating a chart?

(a) Select the chart type, select the data source, and then size and position the chart.

(b) Select the data source, size the chart, select the chart type, and then position the chart.

(c) Select the data source, select the chart type, and then size and position the chart.

(d) Click the cell to contain the chart, select the chart type, and then select the data

source.

3. Which of the following applies to sparkline

(a) Chart title

(b) Single-cell chart

(c) Legend

(d) Multiple data series

4. If you want to show exact values for a data series in a bar chart, which chart element

should you display

(a) Chart title

(b) Legend

(c) Value axis title

(d) Data labels

5. The value axis currently shows increments such as 50,000 and 100,000. What do you

select to display increments of 50 and 100:

(a) More Primary Vertical Axis Title options

(b) Show Axis in Thousands

(c) Show Axis in Millions

(d) Show Right to Left Axis

6. You want to create a single chart that shows each of five divisions’ proportion of yearly

sales for each year for five years. Which type of chart can accommodate your needs

(a) Pie chart

(b) Surface chart

(c) Clustered bar chart

(d) 100% stacked column chart

7. Currently, a column chart shows values on the value axis, years on the category axis, and

state names in the legend. What should you do if you want to organize data with the

states on the category axis and the years shown in the legend:

(a) Change the chart type to a clustered column chart

(b) Click Switch Row/Column in the Data group on the Design tab

(c) Click Layout 2 in the Chart Layouts group on the Design tab and apply a different

chart style

(d) Click Legend in the Labels group on the Layout tab and select Show legend at bottom

8. Which tab contains commands to apply a predefined chart layout that controls which

elements are included, where, and their color scheme?

(a) Design

(b) Layout

(c) Format

(d) Page Layout

9. Which icon does not display to the right of a selected chart:

(a) Chart elements

(b) Chart Styles

(c) Chart Filters

(d) Chart Quick Analysis

10. What type of charts is best to show a company’s profit and loss:

(a) Line Sparkline

(b) Pie chart

(c) Win/Loss Sparkline

(d) Bar chart

Part II: project (50 points)

Please follow the instructions below to make a Microsoft Excel workbook:

1. Please download e03m1Airports.xlsx from Blackboard and save it on your computer

2. Open e03m1Airports.xlsx and save it as e03m1Airports_Lastname_Firstname.xlsx

3. Create a clustered column chart for the range A4:E9. Position the chart to fit in the range

A15:F34.

4. Customize the chart style by doing the following:

a. Apply Style 8 chart style

b. Select Color 6 in the Monochromatic section of the Change Colors gallery

c. Change the fill color of the 2011 data series to White, Background 1.

5. Enter Passengers by Top U.S. Airports as the chart title.

6. Adjust the value axis by doing the following:

a. Change the display units to Millions for the value axis.

b. Edit the axis title to display MILLIONS OF PASSENGERS

7. Display data labels above the columns for the 2011 data series only.

8. Insert Line sparklines in the range F5:F9 to illustrate the data in the range B5:E9. This

should insert a sparkline to represent yearly data for each airport.

9. Customize the sparklines by doing the following:

a. Show the high and low points in each sparkline

b. Apply Black, Text 1 color to the high point marker in each sparkline.

c. Apply Dark Red color to the low point marker in each sparkline.

10. Merge cells in the range A36:F41, wrap text, and then apply Top Align and Align Left

alignments.

11. Compose a paragraph that analyzes the trends depicted by the airport sparklines. Notice

the overall trends in decreased and increased number of passengers and any unusual

activity for an airport. Spell check the worksheet and correct any errors.

12. Please create a new worksheet named MultipleChoice and write the 10 multiple

questions’ answers on Column A. For example, A1 should be the answer to question 1,

A2 should be the answer to question 2, A3 should be the answer to question 3, . . . , A10

should be the answer to question 10.

13. Save and close the file.

14. Submit the file e03m1Airports_Lastname_Firstname.xlsx on Blackboard.