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
> , 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 9 12 12 $11,250 5 12 $11,250 18 12 $10,950 8 12 $11,250 4 12 $11,250 8 12 $10,950 0 12 12 12 72 12 $16,500 7 12 $15,750 12 $13,500 32 12 $15,750 12 $11,250 12 $11,250 2 12 12 12 $15,000 12 $15,750 12 $15,750 80 12 $11,250 0 12 $13,500 26 12 12 $10,200 163 12 15 15 $13,500 15 $18,750 15 15 15 $14,250 15 $13,500 15 15 $15,000 54 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 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 17 17 19 19 9 19 >Database & Instructions
s by month For Washington D.C. between and Temperature 1990 0.8
1990 1990 1990 4. 1990 1990 1990 1990 1990 1990 1990 1990 1 2 3 4 Month Average January February March April May June July August September October November December 1 2 3 36.8 Year Average 1990 1992 1993 2001 1 2 3 4 5 1 2 3 4 5 Job 7.1 Job 7.2 Job 7.3 Job 7.4 s and s by region
Book Book Book .29
Book 1 Web Book 2 Book 3 Book 4 Book 5 .35
Book Book Book Book Book 6.37
Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book 1 Book 2 Go to “Insert” and “PivotChart ” Use the option for table and chart Book 3 Insert regions in the rows and Products in the columns Book 4 Book 5 Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD Paypal Email Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book West Credit Web $16.06 Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book West Paypal Web $20.16 Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD Paypal Email Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD Credit Web $15.33 Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book Book DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD DVD and Item description for September, 2016
1 2 O-Ring 3 O-Ring Electrical Connector 4 Spacetime Technologies Bolt-nut package 14910 Bolt-nut package 1 1 2 2 3 3 4 4 5 5 Fast-Tie Aerospace Shielded Cable/ft. 23625 > 0
.50
10 General Mills 0
0
2 8 Cheerios General Mills 0
2 1 General Mills 0 9 General Mills 110 0
2 13 7 General Mills 110 0 12 13 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 70 2 9 7 5 Kellogg 50 140 14 8 0 Kellogg 110 1 11 14 0
1 21 2 Kellogg 110 90 1 13 12 Kellogg 110 140 4 10 7 Kellogg 110 220 1 21 3 Kellogg 110 125 1 11 13 Kellogg 110 200 1 14 11 Kellogg 100 0 3 14 7 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 70 130 10 5 6 Nabisco 100 80 1 21 0 Nabisco 90 0 3 20 0 Nabisco 80 0 3 16 0 Nabisco 90 0 4 19 0 Nabisco 90 15 3 15 5 90 210 5 13 5 Post 120 160 5 12 10 Post 110 0 13 12 Post 100 140 3 15 5 Post 110 170 3 17 3 Post 120 3 13 4 Post 110 180 0 14 11 Post 120 200 6 11 14 120 220 0 12 12 Quaker 120 220 1 12 11 Quaker 100 150 2 12 6 Quaker 50 0 0 13 0 Quaker 50 0 1 10 0 Quaker 100 2 14 6 110 200 1 14 8 Ralston Purina 90 200 4 15 6 Ralston Purina 110 0 22 3 Ralston Purina 100 190 1 18 5 Ralston Purina 110 240 0 23 2 Ralston Purina 100 3 17 3Salary Data
Salary Data
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
$25,500
$11,400
$30,600
$15,750
460
$19,650 $11,250 5 12
$22,350
$23,400
$24,300
$28,500
$19,950
$23,400 $11,250 0 12
$18,150
$59,400
$33,750
2
72
$24,450
$14,250
117
$35,700
$16,500
$22,200
$23,100 $12,000 228 12
$37,650
132
$27,900
$31,200
155
$20,550
154
$20,700
$21,300 $11,250 3 12
$24,300 $15,000
121
$19,650
$13,950
133
$30,750
56
$33,540
47
$30,270
$20,400
$29,700
$24,450 $15,750
87
$16,350
$57,000
$27,000
144
$45,000
$21,000
138
$32,100
67
$36,000
114
$27,900 $12,750
115
$27,750 $14,250 34 15
$35,100
$16,800
137
$46,000
48
$23,700
359
$26,550 $14,250
61
$35,250
$26,700 $15,000 56 15
$30,900
$29,400
$28,200
$22,950
$30,300
$40,350
$32,400
$23,850
122
$28,050
$49,000
86
$40,200
$30,300 $16,500 143 16
$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
$60,000
$32,490
$68,125 $32,490 29 18
$52,125
$27,480
221
$61,875
$36,750
199
$61,250
$33,000
$36,000 $19,500 21 19
2
1
Average
Temperature
1990
2001
Month
Year
Start the next four “Jobs” by looking over the instructions below. There are instructions that continue on down, so look for them all.
January
4
February
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
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
5
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
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
72.0
The final row is a grand total average by year
July
76.9
August
73.8
September
66.1
Use the database to the left for all of Jobs 7.1, 7.2, 7.3, and 7.4
October
58.7
November
48.6
Step-by-step instructions for Job 7.1
December
41.3
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
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
Then use the “Insert -> Line chart” to creat a line chart beside the table showing the trend graphicaly by month
March 1991
46.1
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
June 1991
72.3
July 1991
77.9
August 1991
77.0
September 1991
67.6
October 1991
56.6
November 1991
45.7
December 1991
38.5
January
1992
35.0
February 1992
38.0
March 1992
41.9
April 1992
53.1
May 1992
59.7
June 1992
68.6
July 1992
75.9
Step-by-step instructions for Job 7.2
August 1992
70.4
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
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
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
February 1993
31.7
March 1993
39.3
Yearly
April 1993
52.6
May 1993
64.5
June 1993 72.0 1991
July 1993
79.7
August 1993
78.0
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
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
Use the tab marked Job 7. 3, copy the raw data to this tab. Also copy these instructions.
October 1994
53.9
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
Then, use the “Insert – Recommended charts – All Charts – Histogram” to create a histogram of all the data
December 1994
40.3
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
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
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
In this worksheet you will create a Pivot-table and pivot chart
August 1995
77.6
Start in “Insert – Pivot Table & Chart”
September 1995
67.1
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
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
Database 2 & Instructions
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
East Credit Web
$20.39
You will analyze jobs 5 and 6 togeather
East Credit Web
$151.67
East Credit Web
$18
Highlight the entire database (Five columns on the left).
East
Paypal
$209.51
Go to “Insert” and “PivotChart ” Use the option for table and chart
East Credit Web
$15.96
Insert regions in the rows and Products in the columns
East Paypal Web
$18.12
In the ∑ Values enter “Amount” and then change the option to “count”
East Credit Web
$18.32
If there isn’t enough room below, move Job 6 down to allow room for both the table and the chart
East Paypal Web
$24
East Credit
Email
$19.40
East Paypal Email
$23.49
East Paypal Web
$20.31
East Credit Email
$23.62
East Credit Web
$21
East Credit Email
$17.31
East Credit Web
$19.35
East Credit Web
$22.83
East Credit Web
$174.18
East Credit Email
$16.43
East Credit Email
$15.71
East Paypal Email
$17.49
East Credit Web $24.35 Book
East Credit Email
$177.32
East Credit Email
$24.40
East Paypal Email
$16.13
East Paypal Web
$17.10
East Credit Email
$21.01
East Credit Email
$16.34
East Paypal Web
$243.70
East Credit Email
$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),
East Credit Email $24 Book
This time change the “∑ Values” to Average Amount
East Paypal Web
$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
East Credit Web
$21.64
East Credit Web
$15.32
Again, highlight the entire database (Five columns on the left).
East Credit Web
$20.97
East Credit Web
$23.08
East Paypal Email
$24.61
For this chart, insert Amount into the “∑ Values” and change this to “Average Amount”
East Credit Web
$24.97
Now, compare the two charts and tables and draw some conclusions about what is going on in the four retions
East Credit Email
$244.75
East Credit Web
$162.74
East Paypal Email
$19.83
East Paypal Web
$19.65
East Credit Web
$22.41
East Credit Web
$21.32
East Paypal Web
$150.86
East Credit Web
$199.18
East Paypal Web
$24.80
East Credit Web
$17.38
East Credit Web
$22.99
East Credit Web
$24.11
East Paypal Web
$22.64
East Credit Web
$169.79
East Credit Web
$17.42
East Credit Web
$18.77
East Credit Web
$19.66
East Credit Email
$23.89
East Paypal Web
$20.19
East Paypal Web
$18.36
East Credit Web
$16.30
East Credit Web
$16.15
East Credit Web
$18.37
East Credit Web
$18.22
East Credit Email
$16.10
East Credit Web
$15.95
East Credit Web
$21.70
East Credit Web $16.43 DVD
East Credit Web
$17.47
East Credit Email $20.31 DVD
East Credit Web
$18.25
East Credit Web $17.42 DVD
East Credit Email
$23.73
East Credit Web
$20.75
East Paypal Web
$22.20
East Paypal Web
$21.15
East Credit Web
$15.16
East Credit Web
$18.94
East Credit Web
$15.59
East Credit Web
$17.20
East Credit Web
$15.81
East Credit Web
$24.71
East Credit Web
$21.49
East Credit Email
$21.85
East Credit Web
$22.05
East Credit Web
$22.80
East Credit Web $21 DVD
East Credit Web
$17.34
East Credit Email
$17.22
East Credit Web
$15.45
East Paypal Web
$23.40
East Credit Web
$16.86
East Credit Web
$24.58
East Credit Web
$15.55
East Paypal Email
$22.51
East Paypal Web $16.15 DVD
East Credit Web
$23.87
East Paypal Web
$17.67
East Credit Web
$17.28
East Credit Web
$22.57
West
$23.51
West Credit Web
$21.76
West Credit Email
$19.51
West Paypal Web
$20.16
West Credit Web
$205.58
West Paypal Email
$206.80
West Credit Web
$18.82
West Credit Web
$16.35
West Credit Web
$16.03
West Credit Web
$16.06
West Paypal Web
$21.75
West Paypal Web
$20.51
West Paypal Email
$157.76
West Credit Web
$23.94
West Paypal Email
$174.25
West Paypal Email
$18.06
West Credit Web
$22.19
West Credit Web
$23.90
West Credit Email
$209.37
West Paypal Web
$17.91
West Credit Web
$22.90
West Paypal Web
$18.54
West Paypal Web
$236.49
West Paypal Web
$19.96
West Paypal Email
$155.91
West Paypal Web
$15.19
West Paypal Web
$24.65
West Paypal Email
$24.88
West Credit Email
$21.39
West Credit Web
$15.72
West Paypal Email
$16.09
West Credit Web $23.51 Book
West Credit Web
$190.81
West Paypal Web
$21.12
West Paypal Web $19.65 Book
West Paypal Web
$19.88
West Paypal Web
$15.08
West Paypal Web
$17.52
West Credit Email
$15.66
West Credit Web
$22.37
West Credit Email
$192.41
West Credit Web
$22.39
West Credit Web
$226.15
West Paypal Web
$20.67
West Credit Web
$19.19
West Credit Web
$23.59
West Paypal Email
$20.42
West Credit Web
$161.46
West Paypal Web
$210.38
West Paypal Email
$161.50
West Paypal Web
$21.67
West Paypal Email
$22.04
West Credit Web
$17.81
West Credit Web
$22.55
West Credit Web
$241.65
West Paypal Email
$242.40
West Credit Web
$18.73
West Paypal Web
$17.24
West Paypal Email
$20.28
West Paypal Web
$21.18
West Credit Web
$15.86
West Credit Web
$24.42
West Credit Email $24.71 Book
West Paypal Web
$15.40
West Paypal Web
$19.95
West Credit Web
$231.23
West Credit Web
$21.83
West Paypal Web
$18.35
West Credit Email
$22.92
West Paypal Email
$19.09
West Paypal Web
$20.08
West Credit Email
$24.54
West Paypal Web
$24.81
West Paypal Web
$17.68
West Credit Email
$177.30
West Paypal Email
$15.54
West Paypal Web
$17.39
West Paypal Web
$18.27
West Credit Web
$23.75
West Credit Web
$20.32
West Credit Web
$16.79
West Credit Web
$22.79
West Credit Email
$18.74
West Credit Web
$24.66
West Credit Web
$18.19
West Credit Web
$24.84
West Credit Email
$16.65
West Credit Web
$21.10
West Paypal Web
$16.99
West Paypal Email
$23.96
West Credit Web
$17.76
West Paypal Web
$18.87
West Credit Email
$21.47
West Credit Web
$218.60
West Credit Web
$17.01
West Credit Web
$247.14
West Credit Email
$168.10
West Credit Web
$16.83
West Credit Web
$17.85
West Paypal Email
$17.30
West Paypal Web
$15.92
West Credit Web $21.01 DVD
West Paypal Web
$22.91
West Credit Web
$17.53
West Credit Web
$17.74
West Credit Web
$20.04
West Paypal Email
$23.21
West Credit Email $22.79 DVD
West Credit Web
$16.91
West Credit Web $21.39 DVD
West Credit Web
$16.60
West Credit Web
$20.82
West Paypal Email
$21.02
West Credit Web
$23.13
West Paypal Web
$15.17
West Paypal Email
$16.49
West Credit Web
$23.77
West Credit Email
$21.58
West Credit Email
$18.20
West Paypal Web
$20.26
West Paypal Web
$19.15
West Paypal Web $20.82 DVD
West Credit Web
$20.61
West Credit Web $21.10 DVD
West Credit Web
$22.03
West Credit Email
$24.03
West Credit Web
$24.59
West Paypal Web
$21.35
West Credit Web
$19.47
West Credit Web $21.49 DVD
West Credit Web
$19.71
West Credit Web
$15.56
West Credit Web
$21.78
West Credit Web
$16.54
West Paypal Web $24.65 DVD
West Credit Web $21.12 DVD
West Credit Web
$18.10
West Credit Email
$19.17
West Paypal Web
$15.18
West Credit Web $16.99 DVD
West Paypal Web $24.80 DVD
West Credit Web
$23.03
West Paypal Web
$21.03
West Paypal Web
$22.26
West Credit Web
$21.72
West Credit Email
$19.21
West Credit Web
$15.33
West Credit Web
$23.58
West Credit Email
$20.49
West Credit Web $23.75 DVD
West Credit Email
$23.74
West Paypal Email
$24.77
West Paypal Web
$23.01
West Paypal Web
$18.17
West Credit Web
$15.25
West Credit Email
$21.92
West Credit Email $16.15 DVD
West Paypal Web
$15.61
West Credit Email
$23.06
West Credit Email
$22.84
West Credit Web $22.57 DVD
West Credit Web
$24.79
West Credit Email
$18.84
West Paypal Web $22.92 DVD
West Paypal Email
$15.94
West Paypal Web
$19.20
West Credit Email $17.34 DVD
West Credit Web
$18.41
West Paypal Web
$17.08
West Credit Web
$17.50
West Credit Web
$19.27
West Credit Web $15.59 DVD
West Credit Web
$20.88
West Credit Web
$16.47
West Paypal Web
$18.11
West Paypal Web
$20.73
West Credit Web $21.64 DVD
West Credit Web
$17.70
West Credit Web
$24.24
West Credit Web
$16.01
West Paypal Web
$18.88
West Credit Web
$19.76
West Paypal Web
$21.29
West Credit Web
$24.44
West Credit Web
$17.87
West Paypal Web
$24.16
West Credit Email $15.18 DVD
West Paypal Web
$15.62
North
$17.16
North Paypal Web
$19.29
North Paypal Email $16.09 Book
North Paypal Web
$22.88
North Paypal Email $21.64 Book
North Credit Web
$18.93
North Credit Email $16.83 Book
North Paypal Web
$15.22
North Paypal Web $22.26 Book
North Credit Web
$22.86
North Paypal Web
$234.63
North Paypal Web $24.97 Book
North Paypal Web
$22.59
North Paypal Web $23.74 Book
North Credit Web
$19.44
North Paypal Web
$23.63
North Credit Web
$20.40
North Credit Email
$15.34
North Paypal Web
$216.20
North Paypal Web
$20.44
North Credit Web
$19.90
North Paypal Web
$191.43
North Credit Web
$22.65
North Paypal Email
$17.51
North Credit Web $17.19 Book
North Credit Email
$22.11
North Credit Web
$20.18
North Credit Web
$21.53
North Credit Web $20.16 Book
North Credit Email
$22.53
North Paypal Email
$23.54
North Credit Web
$153.83
North Paypal Web
$21.43
North Credit Web
$23.47
North Credit Web
$16.66
North Credit Email $21.78 Book
North Paypal Web
$160.78
North Credit Email
$18.92
North Credit Web
$152.27
North Credit Web
$20.83
North Paypal Web
$163.37
North Paypal Web
$24.78
North Paypal Email
$17.88
North Credit Web
$23.98
North Paypal Web
$19.54
North Paypal Email
$17.95
North Credit Web
$18.78
North Credit Email
$16.17
North Credit Email
$20.77
North Credit Email
$16.98
North Credit Web
$19.30
North Credit Web
$16.14
North Paypal Web $21.85 DVD
North Paypal Web
$21.99
North Credit Web $18.73 DVD
North Credit Web
$23.70
North Credit Web $18 DVD
North Credit Email
$19.58
North Credit Web
$19.74
North Credit Web
$24.60
North Paypal Web
$21.22
North Paypal Web
$19.43
North Credit Email $21.43 DVD
North Paypal Web $17.87 DVD
North Paypal Email
$17.83
North Paypal Web $15.96 DVD
North Credit Email
$17.41
North Credit Web
$16.97
North Paypal Web $24.71 DVD
North Credit Email
$19.52
North Credit Email
$18.81
North Paypal Web
$18.29
North Paypal Web
$20.63
North Credit Web $21.75 DVD
North Credit Web
$19.64
North Credit Email
$18.44
North Credit Web $16.86 DVD
North Credit Web
$19.97
North Credit Web $24.80 DVD
North Credit Web
$23.97
North Credit Web $19.96 DVD
North Credit Email
$23.64
North Paypal Email
$23.88
North Credit Web
$18.60
North Credit Web
$20.58
South
South Paypal Email
$24.45
South Credit Email $18.12 Book
South Credit Web
$20.22
South Paypal Web
$18.55
South Paypal Web
$217
South Credit Web
$150.99
South Paypal Web
$23.81
South Credit Web
$15.87
South Credit Web $21.15 Book
South Credit Web
$15.58
South Credit Email
$229.73
South Credit Email
$17.77
South Credit Web
$21.55
South Credit Email $15.33 Book
South Credit Web
$19.04
South Paypal Web $15.71 Book
South Credit Web
$17.33
South Paypal Web $15.18 Book
South Credit Web
$22.46
South Credit Web
$21.50
South Credit Web
$19.37
South Credit Email
$241.77
South Credit Web
$21.88
South Credit Email
$24.86
South Credit Web $19.76 Book
South Credit Email
$242.52
South Paypal Web $24.16 Book
South Paypal Web
$23.60
South Credit Web
$23.20
South Credit Web
$157.86
South Credit Web
$22.12
South Credit Web $24.77 Book
South Credit Web
$16.90
South Paypal Web $16.47 Book
South Paypal Web
$222.38
South Paypal Web
$188.85
South Credit Web
$18.57
South Credit Email
$19.13
South Credit Web
$23.31
South Credit Email
$188.16
South Credit Web
$246.67
South Credit Web $20.97 Book
South Credit Email
$16.52
South Credit Email
$22.31
South Credit Web
$17.35
South Credit Email
$18.14
South Credit Email $18.25 Book
South Credit Web
$203.72
South Paypal Web
$19.99
South Credit Web
$197.43
South Paypal Email
$21.68
South Paypal Email
$15.27
South Paypal Email
$19.79
South Credit Email
$21.54
South Paypal Email
$16.32
South Paypal Web
$18.53
South Credit Web
$18.40
South Credit Email $23.06 Book
South Credit Web $231.23 Book
South Paypal Web
$20.68
South Credit Web
$19.10
South Paypal Web
$23.39
South Paypal Web $19.66 DVD
South Credit Email
$20.13
South Credit Web
$21.94
South Credit Web
$22.21
South Paypal Web
$22.06
South Paypal Web
$20.60
South Paypal Email $19.30 DVD
South Paypal Web $22.37 DVD
South Credit Email
$22.17
South Credit Web $17.70 DVD
South Paypal Web $16.32 DVD
South Credit Web
$17.27
South Credit Web
$18.75
South Paypal Web
$20.87
South Paypal Web
$19.69
South Credit Email $21.92 DVD
South Paypal Web
$24.52
South Credit Web
$15.35
South Credit Web
$23.91
South Paypal Email
$20.33
South Paypal Web
$21.36
South Credit Email $21.58 DVD
South Paypal Web
$23.29
South Credit Web
$19.81
South Paypal Email
$23.45
South Paypal Web
$15.77
South Paypal Web $15.71 DVD
South Paypal Email $16.34 DVD
South Credit Web
$16.82
South Credit Web
$15.20
South Credit Web
$21.81
South Paypal Email $18.25 DVD
South Paypal Web
$21.20
South Paypal Web
$19.02
South Credit Web
$16.73
South Credit Email $22.05 DVD
Database 3 & Instructions
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
Supplier
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
Manley Valve Door Decal
82.5
Hulkey Fasteners
Hatch Decal
375
Step-by-step instructions for Job 7.7
Manley Valve Hatch Decal
467.5
The first pareto analysis will look at the “Cost per order” by “supplier”
Manley Valve
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
Fast-Tie Aerospace O-Ring
3062.5
and highlight the cost per order column from top to bottom
Spacetime Technologies O-Ring
3150
With the two columns highlighted, go to the banner above and
Fast-Tie Aerospace O-Ring
3185
go to “insert” -> “Recommended charts” -> “All Charts” -> and you will see two options, select the ‘Pareto” option
Spacetime Technologies O-Ring
3300
Insert and then expand the chart right below this step
Manley Valve O-Ring
3562.5
Fast-Tie Aerospace O-Ring
3675
Manley Valve O-Ring
3705
Manley Valve O-Ring
3847.5
Pylon Accessories
4425
Durrable Products
Gasket
4562.5
Durrable Products Gasket
5292.5
Durrable Products Gasket
5365.5
Spacetime Technologies Gasket
6075
Fast-Tie Aerospace O-Ring
6125
Spacetime Technologies Gasket
6277.5
Pylon Accessories Gasket
6562.5
Pylon Accessories Gasket
6750
Hulkey Fasteners
Electrical Connector
6781.25
Hulkey Fasteners Electrical Connector
6875
Pylon Accessories Gasket
6937.5
Hulkey Fasteners Electrical Connector
7000
Fast-Tie Aerospace Electrical Connector
7062.5
Hulkey Fasteners Electrical Connector 7062.5
Durrable Products Gasket
7245.25
Steelpin Inc.
7425
For this next analysis highlight “Item Description” and Cost Per order
Pylon Accessories Gasket 7425
and insert the same type of chart (Pareto Analysis) below this step
Steelpin Inc. Electrical Connector
7837.5
Steelpin Inc. Electrical Connector
8002.5
Durrable Products
Pressure Gauge
9000
Manley Valve Pressure Gauge
9045
Manley Valve Pressure Gauge
9547.5
Fast-Tie Aerospace Pressure Gauge
9975
Manley Valve Pressure Gauge
10050
Fast-Tie Aerospace Pressure Gauge
10450
Durrable Products Pressure Gauge
10800
Durrable Products
Bolt-nut package
13650
Spacetime Technologies Bolt-nut package
14910
Spacetime Technologies Bolt-nut package
15087.5
Steelpin Inc. Bolt-nut package
15562.5
Steelpin Inc. Bolt-nut package
15750
Steelpin Inc. Bolt-nut package
15937.5
Spacetime Technologies Bolt-nut package
16276.75
Spacetime Technologies Bolt-nut package
16330
Hulkey Fasteners
Shielded Cable/ft.
16625
Spacetime Technologies Bolt-nut package
17040
Spacetime Technologies Shielded Cable/ft.
17250
Step 5
Answer the following questions:
Answers
Alum Sheeting
17775
What suppliers do approximately 80% of all orders come from?
Steelpin Inc. Shielded Cable/ft.
18150
What products make up 80% of all orders?
Steelpin Inc. Shielded Cable/ft.
19250
Which suppliers supply less than 10% of all orders?
Steelpin Inc. Shielded Cable/ft.
19910
Which products amount to less than 10% of all orders
Steelpin Inc.
Side Panel
21450
How could this analysis be used in your job?
Fast-Tie Aerospace Shielded Cable/ft.
22575
Steelpin Inc. Side Panel
23400
Fast-Tie Aerospace Shielded Cable/ft.
23625
Hulkey Fasteners Shielded Cable/ft.
23750
Fast-Tie Aerospace Shielded Cable/ft.
24150
Durrable Products Shielded Cable/ft.
25000
Steelpin Inc. Side Panel
25350
Alum Sheeting Side Panel
25900
Fast-Tie Aerospace Side Panel
26250
Fast-Tie Aerospace Side Panel
27125
Alum Sheeting Side Panel
27750
Fast-Tie Aerospace Side Panel
30625
Alum Sheeting
Airframe fasteners
38250
Durrable Products Airframe fasteners
42000
Alum Sheeting Airframe fasteners
42500
Alum Sheeting Airframe fasteners
44625
Hulkey Fasteners Airframe fasteners
53125
Durrable Products Airframe fasteners
58800
Hulkey Fasteners Airframe fasteners
61625
Durrable Products Airframe fasteners
63000
Hulkey Fasteners Airframe fasteners
63750
Steelpin Inc.
Machined Valve
64500
Hulkey Fasteners Airframe fasteners
65875
Hulkey Fasteners Airframe fasteners
72250
Hulkey Fasteners Airframe fasteners
74375
Hulkey Fasteners Airframe fasteners
76500
Steelpin Inc. Machined Valve
77400
Manley Valve Machined Valve
81937.5
Hulkey Fasteners Airframe fasteners
82875
Steelpin Inc. Machined Valve
96750
Alum Sheeting
Control Panel
103530
Durrable Products Control Panel
110000
Durrable Products Control Panel
121000
Alum Sheeting Control Panel
127500
2
Cereal Data
Cereal Data
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
13
Clusters
14
14
Cocoa Puffs
1
80
18
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
2
AlI-Bran
Kellogg
60
3
All-Bran w/Extra Fiber
6
Apple Jacks
125
16 Corn Flakes Kellogg 100
29
17
Corn Pops
19
Cracklin’ Oat Bran
21
Crispix
24
Froot Loops
25
Frosted Flakes
26
Frosted Mini-Whests
28
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
1
100% Bran
Nabisco
20
Cream of Wheat
55
Shrdded Wht Spn Size
56
Shredded Wheat
57
Shredded Wheat n Bran
60
Strawberry Fruit Wheels
9
Bran Flakes
Post
27
Fruit & Fibre
29
Fruity Pebbles
1
35
31
Grape Nuts Flakes
32
Grape-Nuts
33
Great Grains Pecan
75
34
Honey Comb
46
Post Nat. Raisin Bran
10
Cap’n’Crunch
Quaker
35
Honey Graham Ohs
40
Life
48
Puffed Rice
49
Puffed Wheat
50
Quaker Oat Squares
135
4
Almond Delight
Ralston Purina
8
Bran Chex
15
Corn Chex
280
23
Double Chex
53
Rice Chex
65
Wheat Chex
230