Weeks 5 and 6: Analyzing Spreadsheet Data

 

For this project you will be analyzing data utilizing a spreadsheet software package. The purpose of this project is to enhance and then highlight your skills at analyzing a database utilizing a spreadsheet program. This week you are supplied with one Excel Workbook that has three different databases for you to analyze (see attachments below) To get started, download this workbook and save it on your computer. These tasks require you to reorganize data, build charts and tables, and then explain what the data is telling you. If your spreadsheet skills are at all rusty, it may be beneficial to go into the Content Section and review some of the PowerPoint Presentations, videos, and GCFLearnFree materials prior to getting started.

Start by downloading and saving the databases from the hyperlink below to your computer. Database 1 will be used for Jobs 1 through 4, Database 2 for Jobs 5 and 6, and Database 3 for Job 7. There are complete instructions inside the Workbook you will download, however, here is a short description of each Job you will be completing over the next two weeks:

• Job 1: Reorganize data by month, analyze, and report on your findings. The first worksheet tab is labeled “Database 1 & Instructions” Average Temperatures for Washington DC 1990 – 2001. Use the data in this first tab to complete Jobs 1 – 4. You will first sort the data by month and create a table and a line chart of the average temperatures by month. Complete instructions for this project are located in the Workbook under the Raw Database 1 tab. There is a Help Video video included in the Content Section (Readings and Resources) section for weeks 5 & 6 (Video 1) that explains how to sort data and use a formula to obtain averages.
• Job 2: Analyze the same data you used in Job 1, but reorganize the data by year. In this Job, you will create a table and line chart using the annual data. Complete instructions are on the first tab marked “Database 1 & Instructions”
• Job 3: Create a histogram of the data in Database 1. Use the same Database; there are instructions for this project on that first worksheet tab. If you have not created a histogram before, there is a short video in the Content section that can help you get started.
• Job 4: Create a Pivot Table. Use the same database 1 to create a Pivot Table of the maximum monthly data by year. The Pivot table is an excellent way to give a visual representation of summarized data. Finally, you are asked to explain any trends you see in your table. There is a Training video included in the Content section for weeks 5 & 6 labeled Video 2
• Jobs 5 & 6: Create Pivot Tables. For these two projects you will use data found on the worksheet tab marked “Raw Database 2 & Instructions.” Here you will create two different pivot tables that analyse first by counts and then by the average price by region.
• Job 7: Create a Pareto Analysis. In this final project, you will use the data on the Worksheet tab marked “Database 3 & Instructions.” The database on this page contains the suppliers, products, and order size for the Month of September. You will be creating and then answering questions regarding two Pareto charts that you will create. There are instructions in the tab Marked “Database 3” For how to create a Pareto chart. There is a YouTube video included in the content section under weeks 5 & 6 to help you through creating a Pareto analysis. There are also detailed instructions for how to create a Pareto Analysis on the Database 3 page.

Remember to save your work often. By Sunday midnight of week 6 submit one Excel Workbook that includes all seven jobs. Use the naming convention: JSmith_Weeks 5&6_Project7.

Please submit your Excel file into the classroom for grading. Additionally, put load your Excel file in your Cloud folder for your potential future use.

2

>

Salary Data Salary Data

,

0

,

0

0

Beginning Salary Current Salary

0

,000

8 8

05

5

0

,000

8 12

0

,9

,100

0 8 15

,002

,

0

8 16

1

8 17

,620

8 18

,490

4

8 19

,183

8

8

0

8

$12,000

12

0 12

$13,500

12

$13,500

12

0

24 12

$15,000 75 12

$15,000

12

0

18 12

$12,000

12

11 12

$12,000 11 12

0 12

11 12

$10,950 9 12

$12,000 11 12

$11,250 13 12

$25,500

9 12

12

$19,650 $11,250 5 12

$11,250 5 12

$11,250 18 12

$10,950 8 12

$11,250 4 12

$11,250 8 12

$23,400 $11,250 0 12

$10,950 0 12

12

12

72 12

$16,500 7 12

$23,100 $12,000 228 12

$15,750

12

$13,500 32 12

$15,750

12

$11,250

12

$11,250 2 12

$21,300 $11,250 3 12
$24,300 $15,000

12

$19,650

12

$15,000

12

$15,750

12

$15,750 80 12

$11,250 0 12

$13,500 26 12

$24,450 $15,750

12

$10,200 163 12

15

15

$13,500

15

$18,750

15

$27,900 $12,750

15

$27,750 $14,250 34 15

15

$14,250

15

$13,500

15

$26,550 $14,250

15

$15,000 54 15

$26,700 $15,000 56 15

$15,000 25 15

$16,500 24 15

$12,750 19 15

$13,950 22 15

$15,750 55 15

$16,500 80 15

$15,000 64 15

$13,500

15

$16,500 84 15

$20,550

15

$18,750 36 16

$30,300 $16,500 143 16

70 16

$26,250

16

$12,750

16

150 16

20 16

60 16

3 16

150 16

15 16

55 16

16

$21,750 19 16

$12,750

17

$60,000

17 17

$68,125 $32,490 29 18

19

19

9 19

$36,000 $19,500 21 19

	Current Salary		Beginning Salary	Previous Experience (months)	Education (years)
$	21			9						0	$	1		3	20								19														8	Years of Education
$28,3		5	$																																															12		26	$13,	4	$23,591
$30,		7	$																								15	451	$13,	36	$	25	84
$				17		$10,200	$															16	$33,384
	$21,300	$				11		55			24	$27,525	$57,	54
	$21,750	$12,450	3				18	$	22	$44,550
$21,		150						$12,000	159	$	32		$68,125
$25,200			$18,750		34	$	29	$52,813
	$26,250	$16,050	2	64
$24,150					$12,750	96
$21,	60								$13,500		228
$21,450	381
$21,900	$9,750
$24,000	244
$27,300	66
$40,		80									$15,000
$27,600
$25,800		143
	$26,	70	$12,900
$20,850		163
	$26,550	$13,050
	$27,750
$24,900												$11,250
		$19,650				$10,950
$22,050
	$25,500
	$23,100
$11,400
$30,600							$15,750	460
$22,350
	$23,400
	$24,300
$28,500
$19,950
$18,150
$59,400	$33,750	2	72
	$24,450				$14,250	117
$35,700						$16,500
$22,200
$37,650	132
	$27,900
$31,200	155
	$20,550	154
$20,700
121
	$13,950	133
$30,750		56
$33,540	47
$30,270
$20,400
$29,700
87
$16,350
$57,000	$27,000	144
$45,000	$21,000	138
$32,100	67
	$36,000	114
115
$35,100	$16,800	137
$46,000	48
$23,700	359
61
$35,250
$30,900
$29,400
$28,200
$22,950
	$30,300
$40,350
$32,400
$23,850	122
$28,050
$49,000	86
$40,200
$103,750	$27,510
$42,300	126
$25,050	123
$66,000	$47,490
$52,650		$19,500
$45,625	$23,250
$34,500	$17,250
$103,500		$60,000
$56,750	$30,000
$34,950	$20,250
$100,000	$44,100	128
$70,000
$29,100	375
	$32,490
$52,125	$27,480	221
$61,875	$36,750	199
$61,250	$33,000

2

>

Cereal Data Cereal Data

0

.50

10

General Mills

0

0

2

8

Cheerios General Mills

0

2

1

General Mills

0

9

13

General Mills 110

0

2 13 7

14

General Mills 110

0 12 13

18

General Mills 110

0 12 13

General Mills

2 11 10

General Mills 110

0

0

9

General Mills 110

0

1.50

10

General Mills 110

0

0 21 3

General Mills 110 180 0 12 12

General Mills 100

2 15 6

General Mills

1.50

10

General Mills 110

0 21 3

General Mills 140

4 15 14

General Mills 100 200 3

3

General Mills 110 140 0 13 12

General Mills 100 200 3 17 3

General Mills 110 200 1 18 8

2

70 2

9 7 5

3

Kellogg 50 140 14 8 0

6

Kellogg 110

1 11 14

16 Corn Flakes Kellogg 100

0

1 21 2

17

Kellogg 110 90 1 13 12

Kellogg 110 140 4 10 7

21

Kellogg 110 220 1 21 3

Kellogg 110 125 1 11 13

25

Kellogg 110 200 1 14 11

26

Kellogg 100 0 3 14 7

28

Kellogg

5 14 12

Kellogg 110

1 17 6

Kellogg 140 170 2 20 9

Kellogg

3 17 13

Kellogg 120

0 15 9

Kellogg 100

0

1 20 3

Raisin Bran Kellogg 120 210 5 14 12

Kellogg 90 0 2 15 6

Kellogg 110 290 0 22 3

Kellogg 110 70 1 9 15

Kellogg 110

0

1 16 3

1

70 130 10 5 6

20

Nabisco 100 80 1 21 0

Nabisco 90 0 3 20 0

Nabisco 80 0 3 16 0

Nabisco 90 0 4 19 0

60

Nabisco 90 15 3 15 5

9

90 210 5 13 5

Post 120 160 5 12 10

29

Post 110

0 13 12

Post 100 140 3 15 5

32

Post 110 170 3 17 3

Post 120

3 13 4

Post 110 180 0 14 11

Post 120 200 6 11 14

10

120 220 0 12 12

35

Quaker 120 220 1 12 11

40

Quaker 100 150 2 12 6

Quaker 50 0 0 13 0

Quaker 50 0 1 10 0

50

Quaker 100

2 14 6

4

110 200 1 14 8

8

Ralston Purina 90 200 4 15 6

15

Ralston Purina 110

0 22 3

23

Ralston Purina 100 190 1 18 5

Ralston Purina 110 240 0 23 2

Ralston Purina 100

3 17 3

Product	Cereal Name	Manufacturer	Calories	Sodium	Fiber	Carbs	Sugars
										5	Apple Cinn	Cheerios																				General Mills																			1	1																														0		1					8			1.				50									10
				7	Basic						4						1																							3								21			18
							11																											110	2							9							17
														12	Cinnamon Toast Crunch		1				20		210								13
Clusters													14
Cocoa Puffs			1		80
Count Chocula	180
		22	Crispy Wheat & Raisins													100							1	40
30	Golden Grahams		28											15
3									6	Honey Nut Cheerios		25	11.50
39	Kix			26
41	Lucky Charms
43	Multi-Grain Cheerios				220
45	Oatmeal Raisin Crisp		130	1			70	13.50
61	Total	Corn Flakes								200
62	Total	Raisin Bran	1							90
63	Total Whole Grain				16
64	Trix
66	Wheaties
67	Wheaties Honey Gold
AlI-Bran																					Kellogg	60
All-Bran w/Extra Fiber
Apple Jacks		125
		29
Corn Pops
	19	Cracklin’ Oat Bran
Crispix
24	Froot Loops
Frosted Flakes
Frosted Mini-Whests
Fruitful Bran							120		240
37	Just Right Crunchy Nugget			170
38	Just Right Fruit &Nut
42	Mueslix Crispy Blend		160		150
44	Nut & Honey Crunch		190
47	Product 19		32
51
52	Raisin Squares
54	Rice Krispies
58	Smacks
59	Special K			23
100% Bran						Nabisco
Cream of Wheat
55	Shrdded Wht Spn Size
56	Shredded Wheat
57	Shredded Wheat n Bran
Strawberry Fruit Wheels
Bran Flakes								Post
27	Fruit & Fibre
Fruity Pebbles	1	35
31	Grape Nuts Flakes
Grape-Nuts
33	Great Grains Pecan	75
34	Honey Comb
46	Post Nat. Raisin Bran
Cap’n’Crunch						Quaker
Honey Graham Ohs
Life
48	Puffed Rice
49	Puffed Wheat
Quaker Oat Squares	135
Almond Delight						Ralston Purina
Bran Chex
Corn Chex	280
Double Chex
53	Rice Chex
65	Wheat Chex	230

2

>Database

1

& Instructions Average Temperature

s by month For Washington D.C. between

1990

and

2001 Month Year

Temperature

Start the next four “Jobs” by looking over the instructions below. There are instructions that continue on down, so look for them all. January

1990

4

0.8 February

1990

42.2

Job 7.1

Use the sort function to reorder the data by month, then use a formula to calculate the average temperature by month between 1990 and 2001. Next create a table of the average temperatures by month. Finally create a scatter plot of the data March

1990

48.1

Job 7.2

Use the sort function to reorder the data by year, then use a formula to calculate the average temperature by year between 1990 and 2001. Next Create a table of the average temperature by year. Finally create a scatter plot of the data April

1990

5

4.

3

Job 7.3

Create a histogram of the temperatures creating 10 “bins” adding a title “Washington D.C. Temperatures” and change the color of the background and bars to your favorite College Football team colors May

1990

61.6

Job 7.4

Create a Pivot Table with the months as rows, the final column is a Grand Total Average by month and June

1990

72.0 The final row is a grand total average by year July

1990

76.9 August

1990

73.8 September

1990

66.1 Use the database to the left for all of Jobs 7.1, 7.2, 7.3, and 7.4 October

1990

58.7 November

1990

48.6 Step-by-step instructions for Job 7.1 December

1990

41.3

1

Use the tab marked Job 7.1 and start by copying the raw data to this worksheet. You may also want to copy these instructions and the table below. January

1991 34.0

2

Sort the data by month (Use “Sort & Filter), then create a table as you see below and use the “@average” formula and the reorganized data to get the average by month. February 1991

39.5

3

Then use the “Insert -> Line chart” to creat a line chart beside the table showing the trend graphicaly by month March 1991

46.1

4

Finally, explain what you see in the data regarding the average temperature by month in Washington D.C. April 1991

56.0 Monthly May 1991

69.3

Month Average
June 1991

72.3

January
July 1991

77.9

February
August 1991

77.0

March
September 1991

67.6

April
October 1991

56.6

May
November 1991

45.7

June
December 1991

38.5

July
January

1992 35.0

August
February 1992

38.0

September
March 1992

41.9

October
April 1992

53.1

November
May 1992

59.7

December
June 1992

68.6 July 1992

75.9 Step-by-step instructions for Job 7.2 August 1992

70.4

1

Use the tab marked Job 7. 2, copy the raw data to this tab. You may want to copy the instructions and the table below also. September 1992

66.0

2

In this worksheet you will use the data sorted by year, as it should already be, to create a table as below. Again, use the @average formula and the reorganized data to get the average by year. October 1992

52.2

3

Then use the “Insert” Line chart to creat a line chart beside the table showing the trend graphicaly by year November 1992 45.7 4

Finally, explain what you see in the data regarding the average temperature by year in Washington D.C. December 1992

36.8 January

1993

36.8
February 1993

31.7 March 1993

39.3 Yearly April 1993

52.6

Year Average
May 1993

64.5

1990
June 1993 72.0 1991
July 1993

79.7

1992
August 1993

78.0

1993
September 1993

68.7 1994 October 1993

54.2 1995 November 1993

45.3 1996 December 1993 34.0

1997 January 1994

26.1 1998 February 1994

33.3 1999 March 1994

42.7 2000 April 1994

60.1

2001
May 1994

59.9 June 1994

76.1 July 1994

79.5 August 1994 73.8

Step-by-step instructions for Job 7.3 September 1994

66.7

1

Use the tab marked Job 7. 3, copy the raw data to this tab. Also copy these instructions. October 1994

53.9

2

You do not need to sort the data for this job. In this worksheet you will create a histogram of all of the temperatures November 1994

49.1

3

Then, use the “Insert – Recommended charts – All Charts – Histogram” to create a histogram of all the data December 1994

40.3

4

Once the chart is completed and inserted in the space below, click on the bars and change the “binning” to 7 bins, and change the color of the bars to your College Football team colors (yes, for real) January 1995

37.2

5

Do a little research online regarding how to read a histogram and explain what you see in the chart February 1995

31.3 March 1995 46.1
April 1995 52.6
May 1995

62.7 Step-by-step instructions for Job 7.4 June 1995

71.5

1

Use the tab marked Job 7.4, copy the raw data to this tab. You may also want to copy these instructions July 1995

78.2

2

In this worksheet you will create a Pivot-table and pivot chart August 1995

77.6

3

Start in “Insert – Pivot Table & Chart” September 1995

67.1

4

Insert the month data for the rows and the year data for the columns, and put the “Temperature” in as the “∑ Value.” Change the “Value Field Setting” to the Max Temperature October 1995

58.3

5

Explain what the pivot chart tells you and how it relates to the two tables and charts you have already created in Jobs 7.1 and 7.2 November 1995

39.7 December 1995

33.0 January 1996

30.0 February 1996

34.7 March 1996 39.7
April 1996

54.6 May 1996 59.7
June 1996

72.8 July 1996

72.9 August 1996

72.7 September 1996 67.1
October 1996

55.7 November 1996 39.3
December 1996

38.3 January 1997

32.5 February 1997

40.5 March 1997

44.6 April 1997

50.3 May 1997

58.8 June 1997

69.1 July 1997

75.8 August 1997

73.3 September 1997

65.1 October 1997

53.7 November 1997

43.6 December 1997 37.2
January 1998

40.1 February 1998

41.0 March 1998

45.5 April 1998

54.9 May 1998 66.0
June 1998 70.4
July 1998

75.3 August 1998

76.2 September 1998 72.7
October 1998

56.2 November 1998

45.6 December 1998

40.9 January 1999

34.9 February 1999

37.5 March 1999

41.4 April 1999

53.0 May 1999

62.8 June 1999

70.8 July 1999

78.5 August 1999

75.4 September 1999

67.0 October 1999 53.1
November 1999

50.1 December 1999

38.2 January 2000

32.7 February 2000 36.8
March 2000

48.2 April 2000

53.6 May 2000

64.8 June 2000 72.3
July 2000

72.1 August 2000

73.0 September 2000

65.2 October 2000

57.2 November 2000

42.9 December 2000

28.1 January 2001

32.3 February 2001 38.5
March 2001

36.2 April 2001

55.5 May 2001

60.8 June 2001

69.0 July 2001

72.2 August 2001

64.1 September 2001

65.3

Job 7.1

Job 7.2

Job 7.3

Job 7.4

Database 2 & Instructions

s and

s by region

Book

East Credit Web

Book

East Credit Web

Book

East Credit Web

.29

Book 1

East

Web

Book 2

East Credit Web

Book 3

East Paypal Web

Book 4

East Credit Web

Book 5

East Paypal Web

.35

Book

East Credit

Book

East Paypal Email

Book

East Paypal Web

Book

East Credit Email

Book

East Credit Web

6.37

Book

East Credit Email

Book

East Credit Web

Book

East Credit Web

Book

East Credit Web

Book

East Credit Email

Book

East Credit Email

Book

East Paypal Email

Book

East Credit Web $24.35 Book
East Credit Email

Book

East Credit Email

Book

East Paypal Email

Book

East Paypal Web

Book

East Credit Email

Book

East Credit Email

Book

East Paypal Web

Book

East Credit Email

Book

East Credit Email $24 Book

East Paypal Web

Book

East Credit Web

Book

East Credit Web

Book 1

East Credit Web

Book 2 Go to “Insert” and “PivotChart ” Use the option for table and chart

East Credit Web

Book 3 Insert regions in the rows and Products in the columns

East Paypal Email

Book 4

East Credit Web

Book 5

East Credit Email

Book

East Credit Web

Book

East Paypal Email

Book

East Paypal Web

Book

East Credit Web

Book

East Credit Web

Book

East Paypal Web

Book

East Credit Web

Book

East Paypal Web

Book

East Credit Web

Book

East Credit Web

Book

East Credit Web

Book

East Paypal Web

Book

East Credit Web

Book

East Credit Web

Book

East Credit Web

Book

East Credit Web

Book

East Credit Email

Book

East Paypal Web

DVD

East Paypal Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Email

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web $16.43 DVD
East Credit Web

DVD

East Credit Email $20.31 DVD
East Credit Web

DVD

East Credit Web $17.42 DVD
East Credit Email

DVD

East Credit Web

DVD

East Paypal Web

DVD

East Paypal Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Email

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web $21 DVD
East Credit Web

DVD

East Credit Email

DVD

East Credit Web

DVD

East Paypal Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Credit Web

DVD

East Paypal Email

DVD

East Paypal Web $16.15 DVD
East Credit Web

DVD

East Paypal Web

DVD

East Credit Web

DVD

East Credit Web

DVD

Paypal Email

Book

West Credit Web

Book

West Credit Email

Book

West Paypal Web

Book

West Credit Web

Book

West Paypal Email

Book

West Credit Web

Book

West Credit Web

Book

West Credit Web

Book

West Credit Web

Book

West Paypal Web

Book

West Paypal Web

Book

West Paypal Email

Book

West Credit Web

Book

West Paypal Email

Book

West Paypal Email

Book

West Credit Web

Book

West Credit Web

Book

West Credit Email

Book

West Paypal Web

Book

West Credit Web

Book

West Paypal Web

Book

West Paypal Web

Book

West Paypal Web

Book

West Paypal Email

Book

West Paypal Web

Book

West Paypal Web

Book

West Paypal Email

Book

West Credit Email

Book

West Credit Web

Book

West Paypal Email

Book

West Credit Web $23.51 Book
West Credit Web

Book

West Paypal Web

Book

West Paypal Web $19.65 Book
West Paypal Web

Book

West Paypal Web

Book

West Paypal Web

Book

West Credit Email

Book

West Credit Web

Book

West Credit Email

Book

West Credit Web

Book

West Credit Web

Book

West Paypal Web

Book

West Credit Web

Book

West Credit Web $16.06 Book

West Credit Web

Book

West Paypal Email

Book

West Credit Web

Book

West Paypal Web

Book

West Paypal Email

Book

West Paypal Web

Book

West Paypal Email

Book

West Credit Web

Book

West Credit Web

Book

West Credit Web

Book

West Paypal Email

Book

West Credit Web

Book

West Paypal Web

Book

West Paypal Email

Book

West Paypal Web

Book

West Credit Web

Book

West Credit Web

Book

West Credit Email $24.71 Book
West Paypal Web

Book

West Paypal Web

Book

West Credit Web

Book

West Credit Web

Book

West Paypal Web

Book

West Credit Email

Book

West Paypal Email

Book

West Paypal Web

Book

West Credit Email

Book

West Paypal Web

Book

West Paypal Web

Book

West Credit Email

Book

West Paypal Email

Book

West Paypal Web

Book

West Paypal Web

Book

West Credit Web

Book

West Credit Web

Book

West Paypal Web $20.16 Book

West Credit Web

Book

West Credit Web

Book

West Credit Email

Book

West Credit Web

Book

West Credit Web

Book

West Credit Web

Book

West Credit Email

Book

West Credit Web

Book

West Paypal Web

Book

West Paypal Email

Book

West Credit Web

Book

West Paypal Web

Book

West Credit Email

Book

West Credit Web

Book

West Credit Web

Book

West Credit Web

Book

West Credit Email

Book

West Credit Web

Book

West Credit Web

DVD

West Paypal Email

DVD

West Paypal Web

DVD

West Credit Web $21.01 DVD
West Paypal Web

DVD

West Credit Web

DVD

West Credit Web

DVD

West Credit Web

DVD

West Paypal Email

DVD

West Credit Email $22.79 DVD
West Credit Web

DVD

West Credit Web $21.39 DVD
West Credit Web

DVD

West Credit Web

DVD

West Paypal Email

DVD

West Credit Web

DVD

West Paypal Web

DVD

West Paypal Email

DVD

West Credit Web

DVD

West Credit Email

DVD

West Credit Email

DVD

West Paypal Web

DVD

West Paypal Web

DVD

West Paypal Web $20.82 DVD
West Credit Web

DVD

West Credit Web $21.10 DVD
West Credit Web

DVD

West Credit Email

DVD

West Credit Web

DVD

West Paypal Web

DVD

West Credit Web

DVD

West Credit Web $21.49 DVD
West Credit Web

DVD

West Credit Web

DVD

West Credit Web

DVD

West Credit Web

DVD

West Paypal Web $24.65 DVD
West Credit Web $21.12 DVD
West Credit Web

DVD

West Credit Email

DVD

West Paypal Web

DVD

West Credit Web $16.99 DVD
West Paypal Web $24.80 DVD
West Credit Web

DVD

West Paypal Web

DVD

West Paypal Web

DVD

West Credit Web

DVD

West Credit Email

DVD

West Credit Web

DVD

West Credit Web

DVD

West Credit Email

DVD

West Credit Web $23.75 DVD
West Credit Email

DVD

West Paypal Email

DVD

West Paypal Web

DVD

West Paypal Web

DVD

West Credit Web

DVD

West Credit Email

DVD

West Credit Email $16.15 DVD
West Paypal Web

DVD

West Credit Email

DVD

West Credit Email

DVD

West Credit Web $22.57 DVD
West Credit Web

DVD

West Credit Email

DVD

West Paypal Web $22.92 DVD
West Paypal Email

DVD

West Paypal Web

DVD

West Credit Email $17.34 DVD
West Credit Web

DVD

West Paypal Web

DVD

West Credit Web

DVD

West Credit Web

DVD

West Credit Web $15.59 DVD
West Credit Web

DVD

West Credit Web

DVD

West Paypal Web

DVD

West Paypal Web

DVD

West Credit Web $21.64 DVD
West Credit Web

DVD

West Credit Web

DVD

West Credit Web

DVD

West Paypal Web

DVD

West Credit Web

DVD

West Paypal Web

DVD

West Credit Web

DVD

West Credit Web

DVD

West Paypal Web

DVD

West Credit Email $15.18 DVD
West Paypal Web

DVD

Paypal Email

Book

North Paypal Web

Book

North Paypal Email $16.09 Book
North Paypal Web

Book

North Paypal Email $21.64 Book
North Credit Web

Book

North Credit Email $16.83 Book
North Paypal Web

Book

North Paypal Web $22.26 Book
North Credit Web

Book

North Paypal Web

Book

North Paypal Web $24.97 Book
North Paypal Web

Book

North Paypal Web $23.74 Book
North Credit Web

Book

North Paypal Web

Book

North Credit Web

Book

North Credit Email

Book

North Paypal Web

Book

North Paypal Web

Book

North Credit Web

Book

North Paypal Web

Book

North Credit Web

Book

North Paypal Email

Book

North Credit Web $17.19 Book
North Credit Email

Book

North Credit Web

Book

North Credit Web

Book

North Credit Web $20.16 Book
North Credit Email

Book

North Paypal Email

Book

North Credit Web

Book

North Paypal Web

Book

North Credit Web

Book

North Credit Web

Book

North Credit Email $21.78 Book
North Paypal Web

Book

North Credit Email

Book

North Credit Web

Book

North Credit Web

Book

North Paypal Web

Book

North Paypal Web

Book

North Paypal Email

Book

North Credit Web

DVD

North Paypal Web

DVD

North Paypal Email

DVD

North Credit Web

DVD

North Credit Email

DVD

North Credit Email

DVD

North Credit Email

DVD

North Credit Web

DVD

North Credit Web

DVD

North Paypal Web $21.85 DVD
North Paypal Web

DVD

North Credit Web $18.73 DVD
North Credit Web

DVD

North Credit Web $18 DVD
North Credit Email

DVD

North Credit Web

DVD

North Credit Web

DVD

North Paypal Web

DVD

North Paypal Web

DVD

North Credit Email $21.43 DVD
North Paypal Web $17.87 DVD
North Paypal Email

DVD

North Paypal Web $15.96 DVD
North Credit Email

DVD

North Credit Web

DVD

North Paypal Web $24.71 DVD
North Credit Email

DVD

North Credit Email

DVD

North Paypal Web

DVD

North Paypal Web

DVD

North Credit Web $21.75 DVD
North Credit Web

DVD

North Credit Email

DVD

North Credit Web $16.86 DVD
North Credit Web

DVD

North Credit Web $24.80 DVD
North Credit Web

DVD

North Credit Web $19.96 DVD
North Credit Email

DVD

North Paypal Email

DVD

North Credit Web

DVD

North Credit Web

DVD

Credit Web $15.33 Book

South Paypal Email

Book

South Credit Email $18.12 Book
South Credit Web

Book

South Paypal Web

Book

South Paypal Web

Book

South Credit Web

Book

South Paypal Web

Book

South Credit Web

Book

South Credit Web $21.15 Book
South Credit Web

Book

South Credit Email

Book

South Credit Email

Book

South Credit Web

Book

South Credit Email $15.33 Book
South Credit Web

Book

South Paypal Web $15.71 Book
South Credit Web

Book

South Paypal Web $15.18 Book
South Credit Web

Book

South Credit Web

Book

South Credit Web

Book

South Credit Email

Book

South Credit Web

Book

South Credit Email

Book

South Credit Web $19.76 Book
South Credit Email

Book

South Paypal Web $24.16 Book
South Paypal Web

Book

South Credit Web

Book

South Credit Web

Book

South Credit Web

Book

South Credit Web $24.77 Book
South Credit Web

Book

South Paypal Web $16.47 Book
South Paypal Web

Book

South Paypal Web

Book

South Credit Web

Book

South Credit Email

Book

South Credit Web

Book

South Credit Email

Book

South Credit Web

Book

South Credit Web $20.97 Book
South Credit Email

Book

South Credit Email

Book

South Credit Web

Book

South Credit Email

Book

South Credit Email $18.25 Book
South Credit Web

Book

South Paypal Web

Book

South Credit Web

Book

South Paypal Email

Book

South Paypal Email

Book

South Paypal Email

Book

South Credit Email

Book

South Paypal Email

Book

South Paypal Web

Book

South Credit Web

Book

South Credit Email $23.06 Book
South Credit Web $231.23 Book
South Paypal Web

Book

South Credit Web

Book

South Paypal Web

DVD

South Paypal Web $19.66 DVD
South Credit Email

DVD

South Credit Web

DVD

South Credit Web

DVD

South Paypal Web

DVD

South Paypal Web

DVD

South Paypal Email $19.30 DVD
South Paypal Web $22.37 DVD
South Credit Email

DVD

South Credit Web $17.70 DVD
South Paypal Web $16.32 DVD
South Credit Web

DVD

South Credit Web

DVD

South Paypal Web

DVD

South Paypal Web

DVD

South Credit Email $21.92 DVD
South Paypal Web

DVD

South Credit Web

DVD

South Credit Web

DVD

South Paypal Email

DVD

South Paypal Web

DVD

South Credit Email $21.58 DVD
South Paypal Web

DVD

South Credit Web

DVD

South Paypal Email

DVD

South Paypal Web

DVD

South Paypal Web $15.71 DVD
South Paypal Email $16.34 DVD
South Credit Web

DVD

South Credit Web

DVD

South Credit Web

DVD

South Paypal Email $18.25 DVD
South Paypal Web

DVD

South Paypal Web

DVD

South Credit Web

DVD

South Credit Email $22.05 DVD

Online Sales of																																																																																																																																																																																																																																																																					Book																																																																																																																																																																																																																			DVD
Start the next two jobs by looking over the instructions below.
Step-by-Step Instructions for Jobs 5 & 6
Region	Payment	Source	Amount	Product	Job 7.5	In the space directly below, create a Pivot Table and chart of the Sales by region (region is the row) and by product (DVD and Book are the two columns)
																																																																																																	East																																																																																																																																																																																																																																																																																																											Credit																																																																																																																																																																																																																																																																																																																																																							Web	$177.72	Change the ∑ Values to “count” This tells you how many of each product was sold in each region
$20.39	You will analyze jobs 5 and 6 togeather
$151.67
	$18	Highlight the entire database (Five columns on the left).
																																																																																																																																																																												Paypal	$209.51		Go to “Insert” and “PivotChart ” Use the option for table and chart
	$15.96		Insert regions in the rows and Products in the columns
	$18.12	In the ∑ Values enter “Amount” and then change the option to “count”
$18.32	If there isn’t enough room below, move Job 6 down to allow room for both the table and the chart
		$24
																																																																																																																																Email	$19.40
$23.49
	$20.31
$23.62
	$21
$17.31
$19.35
$22.83
$174.18
	$16.43
		$15.71
$17.49
$177.32
$24.40
$16.13
$17.10
	$21.01
	$16.34
$243.70
$209.20	Job 7.6	In the space directly below, create another pivot table and chart of the region (rows) and products (DVD and Book are the columns),
This time change the “∑ Values” to Average Amount
	$17.19	Compare the data you obtained in Job 5 with that you found in Job 6 and explain what you see off to the side of the pivot table and graph
		$21.64
$15.32	Again, highlight the entire database (Five columns on the left).
	$20.97
$23.08
$24.61	For this chart, insert Amount into the “∑ Values” and change this to “Average Amount”
	$24.97	Now, compare the two charts and tables and draw some conclusions about what is going on in the four retions
$244.75
$162.74
$19.83
	$19.65
$22.41
$21.32
$150.86
$199.18
		$24.80
$17.38
$22.99
$24.11
$22.64
$169.79
	$17.42
$18.77
	$19.66
$23.89
$20.19
$18.36
$16.30
		$16.15
$18.37
$18.22
$16.10
$15.95
$21.70
$17.47
		$18.25
$23.73
$20.75
$22.20
	$21.15
$15.16
$18.94
	$15.59
$17.20
$15.81
		$24.71
	$21.49
	$21.85
	$22.05
$22.80
	$17.34
$17.22
$15.45
$23.40
	$16.86
$24.58
$15.55
$22.51
$23.87
$17.67
$17.28
	$22.57
																																																																																																																																																																																													West		$23.51
$21.76
$19.51
		$20.16
$205.58
$206.80
$18.82
$16.35
$16.03
	$16.06
	$21.75
$20.51
$157.76
$23.94
$174.25
$18.06
$22.19
$23.90
$209.37
$17.91
$22.90
$18.54
$236.49
	$19.96
$155.91
$15.19
	$24.65
$24.88
	$21.39
$15.72
	$16.09
$190.81
	$21.12
$19.88
$15.08
$17.52
$15.66
	$22.37
$192.41
$22.39
$226.15
$20.67
$19.19
$23.59
$20.42
$161.46
$210.38
$161.50
$21.67
$22.04
$17.81
$22.55
$241.65
$242.40
	$18.73
$17.24
$20.28
$21.18
$15.86
$24.42
$15.40
$19.95
	$231.23
$21.83
$18.35
	$22.92
$19.09
$20.08
$24.54
$24.81
$17.68
$177.30
$15.54
$17.39
$18.27
	$23.75
$20.32
$16.79
	$22.79
$18.74
$24.66
$18.19
$24.84
$16.65
	$21.10
	$16.99
$23.96
$17.76
$18.87
$21.47
$218.60
$17.01
$247.14
$168.10
	$16.83
$17.85
$17.30
$15.92
$22.91
$17.53
$17.74
$20.04
$23.21
$16.91
$16.60
	$20.82
$21.02
$23.13
$15.17
$16.49
$23.77
	$21.58
$18.20
$20.26
$19.15
$20.61
$22.03
$24.03
$24.59
$21.35
$19.47
$19.71
$15.56
	$21.78
$16.54
$18.10
$19.17
		$15.18
$23.03
$21.03
	$22.26
$21.72
$19.21
		$15.33
$23.58
$20.49
	$23.74
	$24.77
$23.01
$18.17
$15.25
	$21.92
$15.61
	$23.06
$22.84
$24.79
$18.84
$15.94
$19.20
$18.41
$17.08
$17.50
$19.27
$20.88
	$16.47
$18.11
$20.73
	$17.70
$24.24
$16.01
$18.88
	$19.76
$21.29
$24.44
	$17.87
	$24.16
$15.62
																																																																																				North	$17.16
$19.29
$22.88
$18.93
$15.22
$22.86
$234.63
$22.59
$19.44
$23.63
$20.40
$15.34
$216.20
$20.44
$19.90
$191.43
$22.65
$17.51
$22.11
$20.18
$21.53
$22.53
$23.54
$153.83
	$21.43
$23.47
$16.66
$160.78
$18.92
$152.27
$20.83
$163.37
$24.78
$17.88
$23.98
$19.54
$17.95
$18.78
$16.17
$20.77
$16.98
	$19.30
$16.14
$21.99
$23.70
$19.58
$19.74
$24.60
$21.22
$19.43
$17.83
$17.41
$16.97
$19.52
$18.81
$18.29
$20.63
$19.64
$18.44
$19.97
$23.97
$23.64
$23.88
$18.60
$20.58
																																																																																																		South
$24.45
$20.22
$18.55
$217
$150.99
$23.81
$15.87
$15.58
$229.73
$17.77
$21.55
$19.04
$17.33
$22.46
$21.50
$19.37
$241.77
$21.88
$24.86
$242.52
$23.60
$23.20
$157.86
$22.12
$16.90
$222.38
$188.85
$18.57
$19.13
$23.31
$188.16
$246.67
$16.52
$22.31
$17.35
$18.14
$203.72
$19.99
$197.43
$21.68
$15.27
$19.79
$21.54
	$16.32
$18.53
$18.40
$20.68
$19.10
$23.39
$20.13
$21.94
$22.21
$22.06
$20.60
$22.17
$17.27
$18.75
$20.87
$19.69
$24.52
$15.35
$23.91
$20.33
$21.36
$23.29
$19.81
$23.45
$15.77
$16.82
$15.20
$21.81
$21.20
$19.02
$16.73

Database 3 & Instructions

and Item description for September, 2016

Supplier

Manley Valve Door Decal

Manley Valve Hatch Decal

1

Manley Valve

2

O-Ring

Fast-Tie Aerospace O-Ring

Spacetime Technologies O-Ring

3

Fast-Tie Aerospace O-Ring

Spacetime Technologies O-Ring

Manley Valve O-Ring

Fast-Tie Aerospace O-Ring

Manley Valve O-Ring

Manley Valve O-Ring

O-Ring

Durrable Products Gasket

Durrable Products Gasket

Spacetime Technologies Gasket

Fast-Tie Aerospace O-Ring

Spacetime Technologies Gasket

Pylon Accessories Gasket

Pylon Accessories Gasket

Hulkey Fasteners

Hulkey Fasteners Electrical Connector

Pylon Accessories Gasket

Hulkey Fasteners Electrical Connector

Fast-Tie Aerospace Electrical Connector

Hulkey Fasteners Electrical Connector 7062.5
Durrable Products Gasket

Electrical Connector

4

Pylon Accessories Gasket 7425

Steelpin Inc. Electrical Connector

Steelpin Inc. Electrical Connector

Durrable Products

Manley Valve Pressure Gauge

Manley Valve Pressure Gauge

Fast-Tie Aerospace Pressure Gauge

Manley Valve Pressure Gauge

Fast-Tie Aerospace Pressure Gauge

Durrable Products Pressure Gauge

Durrable Products

Spacetime Technologies Bolt-nut package

Spacetime Technologies Bolt-nut package 14910

Spacetime Technologies Bolt-nut package

Steelpin Inc. Bolt-nut package

Steelpin Inc. Bolt-nut package

Steelpin Inc. Bolt-nut package

Spacetime Technologies Bolt-nut package

Spacetime Technologies Bolt-nut package

Hulkey Fasteners

Spacetime Technologies Bolt-nut package

Spacetime Technologies Shielded Cable/ft.

Bolt-nut package

1

1

Steelpin Inc. Shielded Cable/ft.

2

2

Steelpin Inc. Shielded Cable/ft.

3

3

Steelpin Inc. Shielded Cable/ft.

4

4

Steelpin Inc.

5

5

Fast-Tie Aerospace Shielded Cable/ft.

Steelpin Inc. Side Panel

Fast-Tie Aerospace Shielded Cable/ft.

Fast-Tie Aerospace Shielded Cable/ft. 23625

Hulkey Fasteners Shielded Cable/ft.

Fast-Tie Aerospace Shielded Cable/ft.

Durrable Products Shielded Cable/ft.

Steelpin Inc. Side Panel

Alum Sheeting Side Panel

Fast-Tie Aerospace Side Panel

Fast-Tie Aerospace Side Panel

Alum Sheeting Side Panel

Fast-Tie Aerospace Side Panel

Alum Sheeting

Durrable Products Airframe fasteners

Alum Sheeting Airframe fasteners

Alum Sheeting Airframe fasteners

Hulkey Fasteners Airframe fasteners

Durrable Products Airframe fasteners

Hulkey Fasteners Airframe fasteners

Durrable Products Airframe fasteners

Hulkey Fasteners Airframe fasteners

Steelpin Inc.

Hulkey Fasteners Airframe fasteners

Hulkey Fasteners Airframe fasteners

Hulkey Fasteners Airframe fasteners

Hulkey Fasteners Airframe fasteners

Steelpin Inc. Machined Valve

Manley Valve Machined Valve

Hulkey Fasteners Airframe fasteners

Steelpin Inc. Machined Valve

Alum Sheeting

Durrable Products Control Panel

Durrable Products Control Panel

Alum Sheeting Control Panel

Purchase Orders by	Supplier	Job 7.7	For this analysis, you will create two Pareto charts and then compare them for your analysis
Use the database to the left for this analysis
Item Description	Cost per order	This database has all of the purchases from a variety of suppliers for 8 different products
										Manley Valve		Door Decal	68.75
82.5
														Hulkey Fasteners		Hatch Decal	375	Step-by-step instructions for Job 7.7
467.5	The first pareto analysis will look at the “Cost per order” by “supplier”
Panel Decal	525	Start by highlighting only the “Supplier” and “Cost per order” columns Note: also highlight the headings
											Spacetime Technologies												O-Ring	2700	First highlight the supplier column from top to bottom, then
														Fast-Tie Aerospace	2940	move your curser back to the top, hold the control key down on you pc
3062.5	and highlight the cost per order column from top to bottom
3150	With the two columns highlighted, go to the banner above and
3185	go to “insert” -> “Recommended charts” -> “All Charts” -> and you will see two options, select the ‘Pareto” option
3300	Insert and then expand the chart right below this step
3562.5
3675
3705
3847.5
				Pylon Accessories	4425
												Durrable Products										Gasket	4562.5
5292.5
5365.5
6075
6125
6277.5
6562.5
6750
							Electrical Connector	6781.25
6875
6937.5
7000
	7062.5
7245.25
														Steelpin Inc.		7425	For this next analysis highlight “Item Description” and Cost Per order
and insert the same type of chart (Pareto Analysis) below this step
7837.5
8002.5
						Pressure Gauge	9000
9045
9547.5
9975
10050
10450
10800
										Bolt-nut package	13650
	14910
15087.5
15562.5
15750
15937.5
16276.75
16330
										Shielded Cable/ft.	16625
17040
17250	Step 5	Answer the following questions:	Answers
							Alum Sheeting	17775	What suppliers do approximately 80% of all orders come from?
18150	What products make up 80% of all orders?
19250	Which suppliers supply less than 10% of all orders?
19910	Which products amount to less than 10% of all orders
							Side Panel	21450	How could this analysis be used in your job?
22575
23400
	23625
23750
24150
25000
25350
25900
26250
27125
27750
30625
													Airframe fasteners	38250
42000
42500
44625
53125
58800
61625
63000
63750
			Machined Valve	64500
65875
72250
74375
76500
77400
81937.5
82875
96750
			Control Panel	103530
110000
121000
127500