Weeks 5 and 6: Analyzing Spreadsheet Data
ttn41092
DatabaseforExcelJobs1thru72.xlsxDatabase 1 & Instructions
| Average Temperatures 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 | 40.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 | 54.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
| Online Sales of Books and DVDs by region | ||||||||||
| 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 | Book | Change the ∑ Values to "count" This tells you how many of each product was sold in each region | |||||
| East | Credit | Web | $20.39 | Book | You will analyze jobs 5 and 6 togeather | |||||
| East | Credit | Web | $151.67 | Book | ||||||
| East | Credit | Web | $18.29 | Book | 1 | Highlight the entire database (Five columns on the left). | ||||
| East | Paypal | Web | $209.51 | Book | 2 | Go to "Insert" and "PivotChart " Use the option for table and chart | ||||
| East | Credit | Web | $15.96 | Book | 3 | Insert regions in the rows and Products in the columns | ||||
| East | Paypal | Web | $18.12 | Book | 4 | In the ∑ Values enter "Amount" and then change the option to "count" | ||||
| East | Credit | Web | $18.32 | Book | 5 | 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.35 | Book | ||||||
| East | Credit | $19.40 | Book | |||||||
| East | Paypal | $23.49 | Book | |||||||
| East | Paypal | Web | $20.31 | Book | ||||||
| East | Credit | $23.62 | Book | |||||||
| East | Credit | Web | $216.37 | Book | ||||||
| East | Credit | $17.31 | Book | |||||||
| East | Credit | Web | $19.35 | Book | ||||||
| East | Credit | Web | $22.83 | Book | ||||||
| East | Credit | Web | $174.18 | Book | ||||||
| East | Credit | $16.43 | Book | |||||||
| East | Credit | $15.71 | Book | |||||||
| East | Paypal | $17.49 | Book | |||||||
| East | Credit | Web | $24.35 | Book | ||||||
| East | Credit | $177.32 | Book | |||||||
| East | Credit | $24.40 | Book | |||||||
| East | Paypal | $16.13 | Book | |||||||
| East | Paypal | Web | $17.10 | Book | ||||||
| East | Credit | $21.01 | Book | |||||||
| East | Credit | $16.34 | Book | |||||||
| East | Paypal | Web | $243.70 | Book | ||||||
| East | Credit | $209.20 | Book | 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 | $24 | Book | This time change the "∑ Values" to Average Amount | ||||||
| East | Paypal | Web | $17.19 | Book | 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 | Book | ||||||
| East | Credit | Web | $15.32 | Book | 1 | Again, highlight the entire database (Five columns on the left). | ||||
| East | Credit | Web | $20.97 | Book | 2 | Go to "Insert" and "PivotChart " Use the option for table and chart | ||||
| East | Credit | Web | $23.08 | Book | 3 | Insert regions in the rows and Products in the columns | ||||
| East | Paypal | $24.61 | Book | 4 | For this chart, insert Amount into the "∑ Values" and change this to "Average Amount" | |||||
| East | Credit | Web | $24.97 | Book | 5 | Now, compare the two charts and tables and draw some conclusions about what is going on in the four retions | ||||
| East | Credit | $244.75 | Book | |||||||
| East | Credit | Web | $162.74 | Book | ||||||
| East | Paypal | $19.83 | Book | |||||||
| East | Paypal | Web | $19.65 | Book | ||||||
| East | Credit | Web | $22.41 | Book | ||||||
| East | Credit | Web | $21.32 | Book | ||||||
| East | Paypal | Web | $150.86 | Book | ||||||
| East | Credit | Web | $199.18 | Book | ||||||
| East | Paypal | Web | $24.80 | Book | ||||||
| East | Credit | Web | $17.38 | Book | ||||||
| East | Credit | Web | $22.99 | Book | ||||||
| East | Credit | Web | $24.11 | Book | ||||||
| East | Paypal | Web | $22.64 | Book | ||||||
| East | Credit | Web | $169.79 | Book | ||||||
| East | Credit | Web | $17.42 | Book | ||||||
| East | Credit | Web | $18.77 | Book | ||||||
| East | Credit | Web | $19.66 | Book | ||||||
| East | Credit | $23.89 | Book | |||||||
| East | Paypal | Web | $20.19 | DVD | ||||||
| East | Paypal | Web | $18.36 | DVD | ||||||
| East | Credit | Web | $16.30 | DVD | ||||||
| East | Credit | Web | $16.15 | DVD | ||||||
| East | Credit | Web | $18.37 | DVD | ||||||
| East | Credit | Web | $18.22 | DVD | ||||||
| East | Credit | $16.10 | DVD | |||||||
| East | Credit | Web | $15.95 | DVD | ||||||
| East | Credit | Web | $21.70 | DVD | ||||||
| East | Credit | Web | $16.43 | DVD | ||||||
| East | Credit | Web | $17.47 | DVD | ||||||
| East | Credit | $20.31 | DVD | |||||||
| East | Credit | Web | $18.25 | DVD | ||||||
| East | Credit | Web | $17.42 | DVD | ||||||
| East | Credit | $23.73 | DVD | |||||||
| East | Credit | Web | $20.75 | DVD | ||||||
| East | Paypal | Web | $22.20 | DVD | ||||||
| East | Paypal | Web | $21.15 | DVD | ||||||
| East | Credit | Web | $15.16 | DVD | ||||||
| East | Credit | Web | $18.94 | DVD | ||||||
| East | Credit | Web | $15.59 | DVD | ||||||
| East | Credit | Web | $17.20 | DVD | ||||||
| East | Credit | Web | $15.81 | DVD | ||||||
| East | Credit | Web | $24.71 | DVD | ||||||
| East | Credit | Web | $21.49 | DVD | ||||||
| East | Credit | $21.85 | DVD | |||||||
| East | Credit | Web | $22.05 | DVD | ||||||
| East | Credit | Web | $22.80 | DVD | ||||||
| East | Credit | Web | $21 | DVD | ||||||
| East | Credit | Web | $17.34 | DVD | ||||||
| East | Credit | $17.22 | DVD | |||||||
| East | Credit | Web | $15.45 | DVD | ||||||
| East | Paypal | Web | $23.40 | DVD | ||||||
| East | Credit | Web | $16.86 | DVD | ||||||
| East | Credit | Web | $24.58 | DVD | ||||||
| East | Credit | Web | $15.55 | DVD | ||||||
| East | Paypal | $22.51 | DVD | |||||||
| East | Paypal | Web | $16.15 | DVD | ||||||
| East | Credit | Web | $23.87 | DVD | ||||||
| East | Paypal | Web | $17.67 | DVD | ||||||
| East | Credit | Web | $17.28 | DVD | ||||||
| East | Credit | Web | $22.57 | DVD | ||||||
| West | Paypal | $23.51 | Book | |||||||
| West | Credit | Web | $21.76 | Book | ||||||
| West | Credit | $19.51 | Book | |||||||
| West | Paypal | Web | $20.16 | Book | ||||||
| West | Credit | Web | $205.58 | Book | ||||||
| West | Paypal | $206.80 | Book | |||||||
| West | Credit | Web | $18.82 | Book | ||||||
| West | Credit | Web | $16.35 | Book | ||||||
| West | Credit | Web | $16.03 | Book | ||||||
| West | Credit | Web | $16.06 | Book | ||||||
| West | Paypal | Web | $21.75 | Book | ||||||
| West | Paypal | Web | $20.51 | Book | ||||||
| West | Paypal | $157.76 | Book | |||||||
| West | Credit | Web | $23.94 | Book | ||||||
| West | Paypal | $174.25 | Book | |||||||
| West | Paypal | $18.06 | Book | |||||||
| West | Credit | Web | $22.19 | Book | ||||||
| West | Credit | Web | $23.90 | Book | ||||||
| West | Credit | $209.37 | Book | |||||||
| West | Paypal | Web | $17.91 | Book | ||||||
| West | Credit | Web | $22.90 | Book | ||||||
| West | Paypal | Web | $18.54 | Book | ||||||
| West | Paypal | Web | $236.49 | Book | ||||||
| West | Paypal | Web | $19.96 | Book | ||||||
| West | Paypal | $155.91 | Book | |||||||
| West | Paypal | Web | $15.19 | Book | ||||||
| West | Paypal | Web | $24.65 | Book | ||||||
| West | Paypal | $24.88 | Book | |||||||
| West | Credit | $21.39 | Book | |||||||
| West | Credit | Web | $15.72 | Book | ||||||
| West | Paypal | $16.09 | Book | |||||||
| West | Credit | Web | $23.51 | Book | ||||||
| West | Credit | Web | $190.81 | Book | ||||||
| West | Paypal | Web | $21.12 | Book | ||||||
| West | Paypal | Web | $19.65 | Book | ||||||
| West | Paypal | Web | $19.88 | Book | ||||||
| West | Paypal | Web | $15.08 | Book | ||||||
| West | Paypal | Web | $17.52 | Book | ||||||
| West | Credit | $15.66 | Book | |||||||
| West | Credit | Web | $22.37 | Book | ||||||
| West | Credit | $192.41 | Book | |||||||
| West | Credit | Web | $22.39 | Book | ||||||
| West | Credit | Web | $226.15 | Book | ||||||
| West | Paypal | Web | $20.67 | Book | ||||||
| West | Credit | Web | $19.19 | Book | ||||||
| West | Credit | Web | $16.06 | Book | ||||||
| West | Credit | Web | $23.59 | Book | ||||||
| West | Paypal | $20.42 | Book | |||||||
| West | Credit | Web | $161.46 | Book | ||||||
| West | Paypal | Web | $210.38 | Book | ||||||
| West | Paypal | $161.50 | Book | |||||||
| West | Paypal | Web | $21.67 | Book | ||||||
| West | Paypal | $22.04 | Book | |||||||
| West | Credit | Web | $17.81 | Book | ||||||
| West | Credit | Web | $22.55 | Book | ||||||
| West | Credit | Web | $241.65 | Book | ||||||
| West | Paypal | $242.40 | Book | |||||||
| West | Credit | Web | $18.73 | Book | ||||||
| West | Paypal | Web | $17.24 | Book | ||||||
| West | Paypal | $20.28 | Book | |||||||
| West | Paypal | Web | $21.18 | Book | ||||||
| West | Credit | Web | $15.86 | Book | ||||||
| West | Credit | Web | $24.42 | Book | ||||||
| West | Credit | $24.71 | Book | |||||||
| West | Paypal | Web | $15.40 | Book | ||||||
| West | Paypal | Web | $19.95 | Book | ||||||
| West | Credit | Web | $231.23 | Book | ||||||
| West | Credit | Web | $21.83 | Book | ||||||
| West | Paypal | Web | $18.35 | Book | ||||||
| West | Credit | $22.92 | Book | |||||||
| West | Paypal | $19.09 | Book | |||||||
| West | Paypal | Web | $20.08 | Book | ||||||
| West | Credit | $24.54 | Book | |||||||
| West | Paypal | Web | $24.81 | Book | ||||||
| West | Paypal | Web | $17.68 | Book | ||||||
| West | Credit | $177.30 | Book | |||||||
| West | Paypal | $15.54 | Book | |||||||
| West | Paypal | Web | $17.39 | Book | ||||||
| West | Paypal | Web | $18.27 | Book | ||||||
| West | Credit | Web | $23.75 | Book | ||||||
| West | Credit | Web | $20.32 | Book | ||||||
| West | Paypal | Web | $20.16 | Book | ||||||
| West | Credit | Web | $16.79 | Book | ||||||
| West | Credit | Web | $22.79 | Book | ||||||
| West | Credit | $18.74 | Book | |||||||
| West | Credit | Web | $24.66 | Book | ||||||
| West | Credit | Web | $18.19 | Book | ||||||
| West | Credit | Web | $24.84 | Book | ||||||
| West | Credit | $16.65 | Book | |||||||
| West | Credit | Web | $21.10 | Book | ||||||
| West | Paypal | Web | $16.99 | Book | ||||||
| West | Paypal | $23.96 | Book | |||||||
| West | Credit | Web | $17.76 | Book | ||||||
| West | Paypal | Web | $18.87 | Book | ||||||
| West | Credit | $21.47 | Book | |||||||
| West | Credit | Web | $218.60 | Book | ||||||
| West | Credit | Web | $17.01 | Book | ||||||
| West | Credit | Web | $247.14 | Book | ||||||
| West | Credit | $168.10 | Book | |||||||
| West | Credit | Web | $16.83 | Book | ||||||
| West | Credit | Web | $17.85 | DVD | ||||||
| West | Paypal | $17.30 | DVD | |||||||
| West | Paypal | Web | $15.92 | DVD | ||||||
| West | Credit | Web | $21.01 | DVD | ||||||
| West | Paypal | Web | $22.91 | DVD | ||||||
| West | Credit | Web | $17.53 | DVD | ||||||
| West | Credit | Web | $17.74 | DVD | ||||||
| West | Credit | Web | $20.04 | DVD | ||||||
| West | Paypal | $23.21 | DVD | |||||||
| West | Credit | $22.79 | DVD | |||||||
| West | Credit | Web | $16.91 | DVD | ||||||
| West | Credit | Web | $21.39 | DVD | ||||||
| West | Credit | Web | $16.60 | DVD | ||||||
| West | Credit | Web | $20.82 | DVD | ||||||
| West | Paypal | $21.02 | DVD | |||||||
| West | Credit | Web | $23.13 | DVD | ||||||
| West | Paypal | Web | $15.17 | DVD | ||||||
| West | Paypal | $16.49 | DVD | |||||||
| West | Credit | Web | $23.77 | DVD | ||||||
| West | Credit | $21.58 | DVD | |||||||
| West | Credit | $18.20 | DVD | |||||||
| West | Paypal | Web | $20.26 | DVD | ||||||
| West | Paypal | Web | $19.15 | DVD | ||||||
| West | Paypal | Web | $20.82 | DVD | ||||||
| West | Credit | Web | $20.61 | DVD | ||||||
| West | Credit | Web | $21.10 | DVD | ||||||
| West | Credit | Web | $22.03 | DVD | ||||||
| West | Credit | $24.03 | DVD | |||||||
| West | Credit | Web | $24.59 | DVD | ||||||
| West | Paypal | Web | $21.35 | DVD | ||||||
| West | Credit | Web | $19.47 | DVD | ||||||
| West | Credit | Web | $21.49 | DVD | ||||||
| West | Credit | Web | $19.71 | DVD | ||||||
| West | Credit | Web | $15.56 | DVD | ||||||
| West | Credit | Web | $21.78 | DVD | ||||||
| West | Credit | Web | $16.54 | DVD | ||||||
| West | Paypal | Web | $24.65 | DVD | ||||||
| West | Credit | Web | $21.12 | DVD | ||||||
| West | Credit | Web | $18.10 | DVD | ||||||
| West | Credit | $19.17 | DVD | |||||||
| West | Paypal | Web | $15.18 | DVD | ||||||
| West | Credit | Web | $16.99 | DVD | ||||||
| West | Paypal | Web | $24.80 | DVD | ||||||
| West | Credit | Web | $23.03 | DVD | ||||||
| West | Paypal | Web | $21.03 | DVD | ||||||
| West | Paypal | Web | $22.26 | DVD | ||||||
| West | Credit | Web | $21.72 | DVD | ||||||
| West | Credit | $19.21 | DVD | |||||||
| West | Credit | Web | $15.33 | DVD | ||||||
| West | Credit | Web | $23.58 | DVD | ||||||
| West | Credit | $20.49 | DVD | |||||||
| West | Credit | Web | $23.75 | DVD | ||||||
| West | Credit | $23.74 | DVD | |||||||
| West | Paypal | $24.77 | DVD | |||||||
| West | Paypal | Web | $23.01 | DVD | ||||||
| West | Paypal | Web | $18.17 | DVD | ||||||
| West | Credit | Web | $15.25 | DVD | ||||||
| West | Credit | $21.92 | DVD | |||||||
| West | Credit | $16.15 | DVD | |||||||
| West | Paypal | Web | $15.61 | DVD | ||||||
| West | Credit | $23.06 | DVD | |||||||
| West | Credit | $22.84 | DVD | |||||||
| West | Credit | Web | $22.57 | DVD | ||||||
| West | Credit | Web | $24.79 | DVD | ||||||
| West | Credit | $18.84 | DVD | |||||||
| West | Paypal | Web | $22.92 | DVD | ||||||
| West | Paypal | $15.94 | DVD | |||||||
| West | Paypal | Web | $19.20 | DVD | ||||||
| West | Credit | $17.34 | DVD | |||||||
| West | Credit | Web | $18.41 | DVD | ||||||
| West | Paypal | Web | $17.08 | DVD | ||||||
| West | Credit | Web | $17.50 | DVD | ||||||
| West | Credit | Web | $19.27 | DVD | ||||||
| West | Credit | Web | $15.59 | DVD | ||||||
| West | Credit | Web | $20.88 | DVD | ||||||
| West | Credit | Web | $16.47 | DVD | ||||||
| West | Paypal | Web | $18.11 | DVD | ||||||
| West | Paypal | Web | $20.73 | DVD | ||||||
| West | Credit | Web | $21.64 | DVD | ||||||
| West | Credit | Web | $17.70 | DVD | ||||||
| West | Credit | Web | $24.24 | DVD | ||||||
| West | Credit | Web | $16.01 | DVD | ||||||
| West | Paypal | Web | $18.88 | DVD | ||||||
| West | Credit | Web | $19.76 | DVD | ||||||
| West | Paypal | Web | $21.29 | DVD | ||||||
| West | Credit | Web | $24.44 | DVD | ||||||
| West | Credit | Web | $17.87 | DVD | ||||||
| West | Paypal | Web | $24.16 | DVD | ||||||
| West | Credit | $15.18 | DVD | |||||||
| West | Paypal | Web | $15.62 | DVD | ||||||
| North | Paypal | $17.16 | Book | |||||||
| North | Paypal | Web | $19.29 | Book | ||||||
| North | Paypal | $16.09 | Book | |||||||
| North | Paypal | Web | $22.88 | Book | ||||||
| North | Paypal | $21.64 | Book | |||||||
| North | Credit | Web | $18.93 | Book | ||||||
| North | Credit | $16.83 | Book | |||||||
| North | Paypal | Web | $15.22 | Book | ||||||
| North | Paypal | Web | $22.26 | Book | ||||||
| North | Credit | Web | $22.86 | Book | ||||||
| North | Paypal | Web | $234.63 | Book | ||||||
| North | Paypal | Web | $24.97 | Book | ||||||
| North | Paypal | Web | $22.59 | Book | ||||||
| North | Paypal | Web | $23.74 | Book | ||||||
| North | Credit | Web | $19.44 | Book | ||||||
| North | Paypal | Web | $23.63 | Book | ||||||
| North | Credit | Web | $20.40 | Book | ||||||
| North | Credit | $15.34 | Book | |||||||
| North | Paypal | Web | $216.20 | Book | ||||||
| North | Paypal | Web | $20.44 | Book | ||||||
| North | Credit | Web | $19.90 | Book | ||||||
| North | Paypal | Web | $191.43 | Book | ||||||
| North | Credit | Web | $22.65 | Book | ||||||
| North | Paypal | $17.51 | Book | |||||||
| North | Credit | Web | $17.19 | Book | ||||||
| North | Credit | $22.11 | Book | |||||||
| North | Credit | Web | $20.18 | Book | ||||||
| North | Credit | Web | $21.53 | Book | ||||||
| North | Credit | Web | $20.16 | Book | ||||||
| North | Credit | $22.53 | Book | |||||||
| North | Paypal | $23.54 | Book | |||||||
| North | Credit | Web | $153.83 | Book | ||||||
| North | Paypal | Web | $21.43 | Book | ||||||
| North | Credit | Web | $23.47 | Book | ||||||
| North | Credit | Web | $16.66 | Book | ||||||
| North | Credit | $21.78 | Book | |||||||
| North | Paypal | Web | $160.78 | Book | ||||||
| North | Credit | $18.92 | Book | |||||||
| North | Credit | Web | $152.27 | Book | ||||||
| North | Credit | Web | $20.83 | Book | ||||||
| North | Paypal | Web | $163.37 | Book | ||||||
| North | Paypal | Web | $24.78 | Book | ||||||
| North | Paypal | $17.88 | Book | |||||||
| North | Credit | Web | $23.98 | DVD | ||||||
| North | Paypal | Web | $19.54 | DVD | ||||||
| North | Paypal | $17.95 | DVD | |||||||
| North | Credit | Web | $18.78 | DVD | ||||||
| North | Credit | $16.17 | DVD | |||||||
| North | Credit | $20.77 | DVD | |||||||
| North | Credit | $16.98 | DVD | |||||||
| North | Credit | Web | $19.30 | DVD | ||||||
| North | Credit | Web | $16.14 | DVD | ||||||
| North | Paypal | Web | $21.85 | DVD | ||||||
| North | Paypal | Web | $21.99 | DVD | ||||||
| North | Credit | Web | $18.73 | DVD | ||||||
| North | Credit | Web | $23.70 | DVD | ||||||
| North | Credit | Web | $18 | DVD | ||||||
| North | Credit | $19.58 | DVD | |||||||
| North | Credit | Web | $19.74 | DVD | ||||||
| North | Credit | Web | $24.60 | DVD | ||||||
| North | Paypal | Web | $21.22 | DVD | ||||||
| North | Paypal | Web | $19.43 | DVD | ||||||
| North | Credit | $21.43 | DVD | |||||||
| North | Paypal | Web | $17.87 | DVD | ||||||
| North | Paypal | $17.83 | DVD | |||||||
| North | Paypal | Web | $15.96 | DVD | ||||||
| North | Credit | $17.41 | DVD | |||||||
| North | Credit | Web | $16.97 | DVD | ||||||
| North | Paypal | Web | $24.71 | DVD | ||||||
| North | Credit | $19.52 | DVD | |||||||
| North | Credit | $18.81 | DVD | |||||||
| North | Paypal | Web | $18.29 | DVD | ||||||
| North | Paypal | Web | $20.63 | DVD | ||||||
| North | Credit | Web | $21.75 | DVD | ||||||
| North | Credit | Web | $19.64 | DVD | ||||||
| North | Credit | $18.44 | DVD | |||||||
| North | Credit | Web | $16.86 | DVD | ||||||
| North | Credit | Web | $19.97 | DVD | ||||||
| North | Credit | Web | $24.80 | DVD | ||||||
| North | Credit | Web | $23.97 | DVD | ||||||
| North | Credit | Web | $19.96 | DVD | ||||||
| North | Credit | $23.64 | DVD | |||||||
| North | Paypal | $23.88 | DVD | |||||||
| North | Credit | Web | $18.60 | DVD | ||||||
| North | Credit | Web | $20.58 | DVD | ||||||
| South | Credit | Web | $15.33 | Book | ||||||
| South | Paypal | $24.45 | Book | |||||||
| South | Credit | $18.12 | Book | |||||||
| South | Credit | Web | $20.22 | Book | ||||||
| South | Paypal | Web | $18.55 | Book | ||||||
| South | Paypal | Web | $217 | Book | ||||||
| South | Credit | Web | $150.99 | Book | ||||||
| South | Paypal | Web | $23.81 | Book | ||||||
| South | Credit | Web | $15.87 | Book | ||||||
| South | Credit | Web | $21.15 | Book | ||||||
| South | Credit | Web | $15.58 | Book | ||||||
| South | Credit | $229.73 | Book | |||||||
| South | Credit | $17.77 | Book | |||||||
| South | Credit | Web | $21.55 | Book | ||||||
| South | Credit | $15.33 | Book | |||||||
| South | Credit | Web | $19.04 | Book | ||||||
| South | Paypal | Web | $15.71 | Book | ||||||
| South | Credit | Web | $17.33 | Book | ||||||
| South | Paypal | Web | $15.18 | Book | ||||||
| South | Credit | Web | $22.46 | Book | ||||||
| South | Credit | Web | $21.50 | Book | ||||||
| South | Credit | Web | $19.37 | Book | ||||||
| South | Credit | $241.77 | Book | |||||||
| South | Credit | Web | $21.88 | Book | ||||||
| South | Credit | $24.86 | Book | |||||||
| South | Credit | Web | $19.76 | Book | ||||||
| South | Credit | $242.52 | Book | |||||||
| South | Paypal | Web | $24.16 | Book | ||||||
| South | Paypal | Web | $23.60 | Book | ||||||
| South | Credit | Web | $23.20 | Book | ||||||
| South | Credit | Web | $157.86 | Book | ||||||
| South | Credit | Web | $22.12 | Book | ||||||
| South | Credit | Web | $24.77 | Book | ||||||
| South | Credit | Web | $16.90 | Book | ||||||
| South | Paypal | Web | $16.47 | Book | ||||||
| South | Paypal | Web | $222.38 | Book | ||||||
| South | Paypal | Web | $188.85 | Book | ||||||
| South | Credit | Web | $18.57 | Book | ||||||
| South | Credit | $19.13 | Book | |||||||
| South | Credit | Web | $23.31 | Book | ||||||
| South | Credit | $188.16 | Book | |||||||
| South | Credit | Web | $246.67 | Book | ||||||
| South | Credit | Web | $20.97 | Book | ||||||
| South | Credit | $16.52 | Book | |||||||
| South | Credit | $22.31 | Book | |||||||
| South | Credit | Web | $17.35 | Book | ||||||
| South | Credit | $18.14 | Book | |||||||
| South | Credit | $18.25 | Book | |||||||
| South | Credit | Web | $203.72 | Book | ||||||
| South | Paypal | Web | $19.99 | Book | ||||||
| South | Credit | Web | $197.43 | Book | ||||||
| South | Paypal | $21.68 | Book | |||||||
| South | Paypal | $15.27 | Book | |||||||
| South | Paypal | $19.79 | Book | |||||||
| South | Credit | $21.54 | Book | |||||||
| South | Paypal | $16.32 | Book | |||||||
| South | Paypal | Web | $18.53 | Book | ||||||
| South | Credit | Web | $18.40 | Book | ||||||
| South | Credit | $23.06 | Book | |||||||
| South | Credit | Web | $231.23 | Book | ||||||
| South | Paypal | Web | $20.68 | Book | ||||||
| South | Credit | Web | $19.10 | Book | ||||||
| South | Paypal | Web | $23.39 | DVD | ||||||
| South | Paypal | Web | $19.66 | DVD | ||||||
| South | Credit | $20.13 | DVD | |||||||
| South | Credit | Web | $21.94 | DVD | ||||||
| South | Credit | Web | $22.21 | DVD | ||||||
| South | Paypal | Web | $22.06 | DVD | ||||||
| South | Paypal | Web | $20.60 | DVD | ||||||
| South | Paypal | $19.30 | DVD | |||||||
| South | Paypal | Web | $22.37 | DVD | ||||||
| South | Credit | $22.17 | DVD | |||||||
| South | Credit | Web | $17.70 | DVD | ||||||
| South | Paypal | Web | $16.32 | DVD | ||||||
| South | Credit | Web | $17.27 | DVD | ||||||
| South | Credit | Web | $18.75 | DVD | ||||||
| South | Paypal | Web | $20.87 | DVD | ||||||
| South | Paypal | Web | $19.69 | DVD | ||||||
| South | Credit | $21.92 | DVD | |||||||
| South | Paypal | Web | $24.52 | DVD | ||||||
| South | Credit | Web | $15.35 | DVD | ||||||
| South | Credit | Web | $23.91 | DVD | ||||||
| South | Paypal | $20.33 | DVD | |||||||
| South | Paypal | Web | $21.36 | DVD | ||||||
| South | Credit | $21.58 | DVD | |||||||
| South | Paypal | Web | $23.29 | DVD | ||||||
| South | Credit | Web | $19.81 | DVD | ||||||
| South | Paypal | $23.45 | DVD | |||||||
| South | Paypal | Web | $15.77 | DVD | ||||||
| South | Paypal | Web | $15.71 | DVD | ||||||
| South | Paypal | $16.34 | DVD | |||||||
| South | Credit | Web | $16.82 | DVD | ||||||
| South | Credit | Web | $15.20 | DVD | ||||||
| South | Credit | Web | $21.81 | DVD | ||||||
| South | Paypal | $18.25 | DVD | |||||||
| South | Paypal | Web | $21.20 | DVD | ||||||
| South | Paypal | Web | $19.02 | DVD | ||||||
| South | Credit | Web | $16.73 | DVD | ||||||
| South | Credit | $22.05 | DVD |
Database 3 & Instructions
| Purchase Orders by Supplier and Item description for September, 2016 | 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 | 1 | The first pareto analysis will look at the "Cost per order" by "supplier" | |||||||||||
| Manley Valve | Panel Decal | 525 | 2 | 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 | O-Ring | 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 | 3 | 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 | O-Ring | 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. | Electrical Connector | 7425 | 4 | 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 | 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 | Bolt-nut package | 17775 | 1 | What suppliers do approximately 80% of all orders come from? | 1 | ||||||||||
| Steelpin Inc. | Shielded Cable/ft. | 18150 | 2 | What products make up 80% of all orders? | 2 | ||||||||||
| Steelpin Inc. | Shielded Cable/ft. | 19250 | 3 | Which suppliers supply less than 10% of all orders? | 3 | ||||||||||
| Steelpin Inc. | Shielded Cable/ft. | 19910 | 4 | Which products amount to less than 10% of all orders | 4 | ||||||||||
| Steelpin Inc. | Side Panel | 21450 | 5 | How could this analysis be used in your job? | 5 | ||||||||||
| Fast-Tie Aerospace | Shielded Cable/ft. | 22575 | |||||||||||||
| Steelpin Inc. | Side Panel | 23400 | |||||||||||||
| Fast-Tie Aerospace | Shielded Cable/ft. | 23625 | |||||||||||||
| 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 |
