com sci simnet.com excel project

A Skills Approach: Excel 2016 Chapter 5: Adding Charts and
Analyzing Data

Fix It 5.6

Step 1
Download
start file

The staff members of a private college preparatory school have been trying to analyze student data and create
what-if scenarios regarding investment decisions. Unfortunately, they’ve made a mess of this workbook. Can you
help them fix it?

Skills needed to complete this project:

• Sorting Data
• Converting Data into Tables
• Filtering Table Data with Slicers
• Removing Duplicate Rows from Tables
• Adding Total Rows to Tables
• Creating PivotTables Using Recommended PivotTables
• Inserting a Pie Chart
• Changing the Chart Type
• Analyzing Data with Data Tables

1. Open the start file EX2016-FixIt-5-6. The file will be renamed automatically to include your name.

Change the project file name if directed to do so by your instructor, and save it.

2. If the workbook opens in Protected View, click the Enable Editing button in the Message Bar at the top of the

workbook so you can modify the workbook.

3. The data in the Student Test Data worksheet has a couple of problems to fix:

a. Format the data so the school faculty can use slicers to filter data. Use any style you want.

b. The data should be organized alphabetically by student.

c. Display slicers so data can be filtered by month, by subject, or by student or by any combination of the

three. Go ahead and display the slicers, but you don’t need to apply any filtering.

d. Find and delete the duplicate records. There should be 90 unique records.

e. The data should include a Total row to calculate the average score.

4. The PivotTable on the Student Pivot sheet should summarize the average scores for each subject for each

student by month. However, the Score field data have been added to the wrong section of the PivotTable.

a. Move the Score field to the correct section of the PivotTable.

b. Modify the calculation to display the average score instead of the total score.

5. The PivotChart on the Faculty Pivot sheet should show the size of each department as a proportional part of

a whole. A line chart was the wrong choice.

a. Change the chart type to a better choice.

b. Display Data Labels using the Data Callout format.

1 | Page Fix It 5.6 Last Updated 11/20/17

A Skills Approach: Excel 2016 Chapter 5: Adding Charts and
Analyzing Data

6. The school finance committee has determined that a $28,000 facilities upgrade will be needed in 12 years.

An alumnus has donated $20,000. If invested, will it grow to be enough? That depends on the rate of

return. On the Data Table worksheet, an attempt has been made to use Excel’s data table feature to

determine the value of the investment after ten years at different rates of return. The formula in cell D6 is

correct, but something went wrong with the data table. Delete the zeros in cells D7:D30 and try again.

Even if you’re not familiar with the FV formula used in cell D6, you should be able to figure this out.

7. Save and close the workbook

Step 2
Upload &
Save

Step 3
Grade my
Project

2 | Page Fix It 5.6 Last Updated 11/20/17

8. Upload and save your project file.

9. Submit project for grading.

Fix It 5.6

Faculty Data

,000

Assoc $60,000

Instr

,000

Assoc $60,000

Assoc $60,000

Math

,000

Chemistry Prof $90,000

Art Prof

History Instr $44,000

Math Instr

,000

Chemistry Assoc $60,000

Art Instr $54,000

History Prof $90,000

French Assoc $60,000

Literature Prof $80,000

Literature Prof

,000

Math Assoc $60,000

Chemistry Prof $90,000

First Name

Last Name

Dept

Rank

Salary

Damon

Iheduru

Math

Assoc

$

60

Maria

Cesario

Chemistry

Sarah

Paul

Art

Instr

$44,000

Jerome

Moody

History

$

54

R. Douglas

Kraay

French

Angelita

Usman

Literature

Igor

Berkovich

Prof

$

90

Royal

Singletary

J. Tim

Scheb

$80,000

John

MacMillan

Achi

Ronen

$

64

Rosalie

Saulnier

Lester

Janovicek

David

Rosencraft

Walter

Rose III

Uriel

Brandt

Warren

Fernandez

$

85

Catherine

Fitzgerald-Cramer

Patricia

Presley-Dixon

Faculty Pivot

French

Art

,000

Chemistry

French

0,000

History

Literature

Math

,000

Sum of Salary
$ 1		78
$ 300,000
$	12
$ 188,000
$ 225,000
$ 2			74
		Grand Total	$ 1,285,000

Salary Total by Department

Total Art Chemistry French History Literature Math 178000 300000 120000 188000 22

50

00 274000

Student

Test Data

Student

Art

February Art

February Art

85

February Art John

February Art

February Literature Mary

February Literature Lisa

February Literature Mark

February Literature John

February Literature James 85
February French Lisa

February French Mary

February French Mark 59
February French John 51
February French James

February History Lisa

February History Mary

February History Mark 86
February History John 78
February History James

February Math Lisa

February Math Mary 54
February Math Mark 74
February Math John

February Math James 75
February Chemistry Lisa

February Chemistry Mary

February Chemistry Mark 89
February Chemistry John 77
February Chemistry James 86

Art Lisa 89

January Art Mary 61
January Art Mark

January Art John 83
January Art James

January

Mary 87

January Literature Lisa 87
January Literature Mark 89
January Literature John

January Literature James 90
January French Lisa

January French Mary 92
January French Mark 64
January French John 56
January French James

January History Lisa 81
January History Mary

January History Mark 83
January History John 75
January History James 84
January Math Lisa 65
January Math Mary 53
January Math Mark

January Math John 59
January Math James 68
January Chemistry Lisa 58
January Chemistry Mary

January Chemistry Mark 60
January Chemistry John 59
January Chemistry James 68

Art Lisa 92

March Art Mary

March Art Mark

March Art John 86
March Art James

January History John 75
January History James 84
January Math Lisa 65
January Math Mary 53
January Math Mark 67
January Math John 59
January Math James 68
January Chemistry Lisa 58
January Chemistry Mary 35
January Chemistry Mark 60
January Chemistry John 59
January Chemistry James 68
March Art Lisa 92
March Art Mary 48
March Art Mark 94
March Art John 86
March Art James 95

March Engish Mary

March Literature Lisa 41
March Literature Mark 45
March Literature John 57
March Literature James 86
March French Lisa 56
March French Mary 90
March French Mark 58
March French John 50
March French James

March History Lisa 91
March History Mary 67
March History Mark 93
March History John 85
March History James 94
March Math Lisa

March Math Mary 35
March Math Mark

March Math Lisa 71
March Math Mary 35
March Math Mark 73

March Math John 65
March Math James 74
March Chemistry Lisa 41
March Chemistry Mary 41
March Chemistry Mark

March Chemistry John

March Chemistry James 84

Month	Subject	Score
																																		February																							Lisa					83
																						Mary										59
																						Mark
			77
																						James										86
							68
			51
				53
		45
			57
			93
						75
					84
			61
					87
	72
	66
						89
				56
																																														January
			91
					92
		Engish
			81
	62
					65
						58
				67
					35
																																										March
		48
				94
		95
						41
	79
		71
		73
	43
	55

Student Pivot

Art

Chemistry

Engish

French

History

Literature

Math

Grand Total

48 59 61 77 83 85 86 89 91 92 94 95 35 41 43 55 56 58 59 60 68 77 84 86 89 41 87 50 51 56 57 58 59 62 64 65 75 79 90 92 93 58 61 67 75 78 81 83 84 85 86 87 91 93 94 41 45 51 53 57 68 81 85 86 87 89 90 35 53 54 59 65 66 67 68 71 72 73 74 75

James
January
February
March
John

January
February
March

Lisa

January
February
March

Mark

January
February
March

Mary

January
February
March
Grand Total

Column Labels
Art Total	Chemistry Total	Engish Total	French Total	History Total	Literature Total	Math Total
Row Labels

Data Table

12

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

$0

Amount Donated/Invested	$20,000.00
Number of Years
APR	6%
Rate	Value
$40,244
0.50%																								$0
1.00%
1.50%
2.00%
2.50%
3.00%
3.50%
4.00%
4.50%
5.00%
5.50%
6.00%
6.50%
7.00%
7.50%
8.00%
8.50%
9.00%
9.50%
10.00%
10.50%
11.00%
11.50%
12.00%