Use the Shoppers.xlsx file for these questions

Download the responses into Excel. Save the file to the Desktop. Name the file YourName_GBS221_Descriptive_TestPt1

Label the tab containing the original data Data . Copy and paste the data column needed for each question in a separate Excel Tab, then solve the problem. Label the tab with the problem number. #1, #2, #3, etc. Clearly type a label to identify each answer in the worksheet. You can highlight the answers in yellow to make them easy to find.

Calculate the statistics using either Excel or your calculator. Use Excel to create the charts. Make sure each chart has a title. Use the chart elements you feel are most appropriate.

Round all answers to one decimal position higher than the original data value.

5 pts For the Time variable calculate: the mean , median, mode, variance and the standard deviation. Round the numbers to two decimal places.

5 pts Using either a pivot table or the CountIf function create a frequency distribution, with percentages, for the Browser variable. Create a Column chart showing the proportion of each Browser usage of the respondents. Be sure to title the chart. Show the percentage values to one decimal place.

5 pts Using the Day variable Using either a pivot table or the CountIf function, create a frequency distribution showing the frequencies and percentages for each day of the week. Create a 2-D pie chart of the results. Show the percentage values to one decimal place.

5 pts Sort the data for the Amount Spent amount in ascending order. Using the Excel built-in functions, Calculate Q1, Q2, Q3. Calculate the Interquartile Range, the Lower limit and the Upper limit for the Amount Spent variable. Type the answers in the excel tab containing the data. Show the answers to two decimal places.

5 pts For Amount Spent variable, can any of the Amount Spent values be considered outliers; Using the definition of an outlier being any value with a Z-score greater than or equal to 3.0 or a Z-score less than or equal to -3 ? How many outliers are there in the data? If so, list the outliers. Show the answers to two decimal places. If there are no outliers, just type "NO OUTLIERS"

5 pts. Create a frequency distribution of the Amount Spent variable. Create classes to group the data starting at $0 and ending at $160 by 20. Use a class size of 20. Create a histogram of this data. Include a title, Amount Spent on the histogram. What is the frequency of the $40 -60 class?

2.5 pt : Calculate the Average Time for the three Browsers. Show the answer to one decimal place.

2.5 pt: Calculate the Standard Deviation of the Time for the three Browsers. Show the answer to one decimal place

5 pt: Calculate the Coefficient of Variation of the Time for the three Browsers. Give the answer to one decimal place.

5 pts: Which Browsers has the most variation in the Time spent online.

5 pts: Use the PanamaRailroad.xlsx file to calculate the Geometric mean of the return for the Panama Railroad Company Stock. Show Geometric mean of the growth factors to four decimal places.

5 pts: Create a cross-tabulation showing Browsers as the Row labels and Day as the column labels. The Values should be the Sum of the Amount Spent. What percentage of the total Amount Spent is spent by FireFox Users on Friday? Give the answer to one decimal place?

5 pts: Construct a scatter diagram with Page Views as the X- variable and Amount Spent as the Y-variable. Add the Trendline, Equation, and the R-Squared value. What percentage of the variation of the Y-variable can be accounted by the equation? Give your answer to one decimal place.

A1 1 fo Customer 1 Customer 2 1 3 2 4 3 5 4 6 5 7 6 8 7 9 8 10 9 11 10 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 21 20 22 21 23 22 24 23 25 24 26 25 27 26 28 27 29 28 30 29 31 30 32 31 33 32 34 33 35 34 B Day Browser Mon Internet Explorer Wed Other Mon Internet Explorer Tue Firefox Wed Internet Explorer Sat Firefox Sun Internet Explorer Fri Firefox Wed Firefox Tue Internet Explorer Sat Other Sun Firefox Sun Other Mon Firefox Fri Internet Explorer Thu Internet Explorer Thus Internet Explorer Mon Internet Explorer Fri Other Wed Internet Explorer Sat Firefox Tue Internet Explorer Sun Internet Explorer Tue Firefox Fri Firefox Wed Internet Explorer Internet Explorer Sat Firefox Fri Firefox Mon Other Thu Internet Explorer FH Firefox Sun Internet Explorer Fri Internet Explorer Sheet2 Sheet D E F Time (min) Pages Viewed Amount Spent ($) 12.0 4 54.52 19.5 6 94.90 8.5 4 26.68 11.4 2 44.73 11.3 4 66.27 10.5 6 67.80 11.4 2 36.04 4.3 6 55.96 12.7 3 70.94 24.7 7 68.73 133 6 54.04 14.3 5 48.05 11.7 7 64.16 24.4 10 158.51 8.4 3 84.12 9.6 4 59 20 23.3 7 91.62 14.0 7 126.40 5.6 68.45 15.1 32.69 16.3 78.58 10.2 6 74.43 8.0 3 32.73 8.0 2 48.66 9.6 3 54.66 11.0 2 40.54 16.9 34.69 6.0 27.91 329 10 155.30 11.8 9 120.25 7.1 2 41.20 18.0 8 134.40 11.8 4 37.17 9.1 3 4 Sat nomnom Data 52.09 de Type here to search a B C X Customer A 21 22 23 24 25 26 27 28 29 30 31 32 2 3 2 5 +oad 00 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 B Sat Firefox Tue Internet Explorer Sun Internet Explorer Tue Firefox Fri Firefox Wed Internet Explorer Sat Internet Explorer Sat Firefox Fri Firefox Mon Other Thu Internet Explorer Fri Firefox Sun Internet Explorer Fri Internet Explorer Tue Internet Explorer Mon Firefox Thu Firefox Sat Internet Explorer Tue Internet Explorer Wed Internet Explorer Other Sat Internet Explorer Fri Firefox Mon Internet Explorer Mon Internet Explorer Fri Firefox Thu Internet Explorer Fri Internet Explorer Mon Other Fri Internet Explorer D 16.3 10.2 8.0 8.0 9.6 11.0 16.9 60 32.9 11.8 7.1 18.0 11.8 9.1 7.8 16.5 6.2 11.3 10.6 5.0 13.9 18.1 10.8 13.3 30.1 13.7 8.1 9.7 73 13.4 F 78.58 74.43 32.73 48.66 54.66 40.54 34.69 27.91 155.30 120.25 41.20 134.40 37.17 52.09 71.81 59.99 84.17 55.58 39.06 36.48 67.44 60.14 70.38 110.65 104.23 68.17 17.84 103.15 52.13 98.75 3 5 5 4 4 2 5 36 37 38 39 40 41 42 43 44 45 Tue 4 7 6 wa UNA Data Sheet2 Sheets Fedy + Type here to search 3 A1 1 fo Customer 1 Customer 2 1 3 2 4 3 5 4 6 5 7 6 8 7 9 8 10 9 11 10 12 11 13 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 21 20 22 21 23 22 24 23 25 24 26 25 27 26 28 27 29 28 30 29 31 30 32 31 33 32 34 33 35 34 B Day Browser Mon Internet Explorer Wed Other Mon Internet Explorer Tue Firefox Wed Internet Explorer Sat Firefox Sun Internet Explorer Fri Firefox Wed Firefox Tue Internet Explorer Sat Other Sun Firefox Sun Other Mon Firefox Fri Internet Explorer Thu Internet Explorer Thus Internet Explorer Mon Internet Explorer Fri Other Wed Internet Explorer Sat Firefox Tue Internet Explorer Sun Internet Explorer Tue Firefox Fri Firefox Wed Internet Explorer Internet Explorer Sat Firefox Fri Firefox Mon Other Thu Internet Explorer FH Firefox Sun Internet Explorer Fri Internet Explorer Sheet2 Sheet D E F Time (min) Pages Viewed Amount Spent ($) 12.0 4 54.52 19.5 6 94.90 8.5 4 26.68 11.4 2 44.73 11.3 4 66.27 10.5 6 67.80 11.4 2 36.04 4.3 6 55.96 12.7 3 70.94 24.7 7 68.73 133 6 54.04 14.3 5 48.05 11.7 7 64.16 24.4 10 158.51 8.4 3 84.12 9.6 4 59 20 23.3 7 91.62 14.0 7 126.40 5.6 68.45 15.1 32.69 16.3 78.58 10.2 6 74.43 8.0 3 32.73 8.0 2 48.66 9.6 3 54.66 11.0 2 40.54 16.9 34.69 6.0 27.91 329 10 155.30 11.8 9 120.25 7.1 2 41.20 18.0 8 134.40 11.8 4 37.17 9.1 3 4 Sat nomnom Data 52.09 de Type here to search a B C X Customer A 21 22 23 24 25 26 27 28 29 30 31 32 2 3 2 5 +oad 00 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 B Sat Firefox Tue Internet Explorer Sun Internet Explorer Tue Firefox Fri Firefox Wed Internet Explorer Sat Internet Explorer Sat Firefox Fri Firefox Mon Other Thu Internet Explorer Fri Firefox Sun Internet Explorer Fri Internet Explorer Tue Internet Explorer Mon Firefox Thu Firefox Sat Internet Explorer Tue Internet Explorer Wed Internet Explorer Other Sat Internet Explorer Fri Firefox Mon Internet Explorer Mon Internet Explorer Fri Firefox Thu Internet Explorer Fri Internet Explorer Mon Other Fri Internet Explorer D 16.3 10.2 8.0 8.0 9.6 11.0 16.9 60 32.9 11.8 7.1 18.0 11.8 9.1 7.8 16.5 6.2 11.3 10.6 5.0 13.9 18.1 10.8 13.3 30.1 13.7 8.1 9.7 73 13.4 F 78.58 74.43 32.73 48.66 54.66 40.54 34.69 27.91 155.30 120.25 41.20 134.40 37.17 52.09 71.81 59.99 84.17 55.58 39.06 36.48 67.44 60.14 70.38 110.65 104.23 68.17 17.84 103.15 52.13 98.75 3 5 5 4 4 2 5 36 37 38 39 40 41 42 43 44 45 Tue 4 7 6 wa UNA Data Sheet2 Sheets Fedy + Type here to search 3