Floyd’s Bumpers has distribution centers in Lafayette, Indiana; Charlotte, North Carolina; Los Angeles, California; Dallas, Texas; and Pittsburgh, Pennsylvania. Each distribution center carries all products sold. Floyd’s customers are auto repair shops and larger auto parts retail stores. You are asked to perform an analysis of the customer assignments to determine which of Floyd’s customers should be assigned to each distribution center. The rule for assigning customers to distribution centers is simple: A customer should be assigned to the closest center. The worksheet Floyds in the provided datafile contains the distance from each of Floyd’s 1,029 customers to each of the five distribution centers. Your task is to build a list that tells which distribution center should serve each customer. The following functions will be helpful: =MIN(array). The MIN function returns the smallest value in a set of numbers. For example, if the range A1:A3 contains the values 6, 25, and 38, then the formula =MIN(A1:A3) returns the number 6, because it is the smallest of the three numbers: =MATCH(lookup_value, lookup_array, match type). The MATCH function searches for a specified item in a range of cells and returns the relative position of that item in the range. The lookup_value is the value to match, the lookup_array is the range of search, and match type indicates the type of match (use 0 for an exact match). For example, if the range A1:A3 contains the values 6, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range. =INDEX(array, column_num). The INDEX function returns the value of an element in a position of an array. For example, if the range A1:A3 contains the values 6, 25, and 38, then the formula =INDEX(A1:A3, 2) 5 25, because 25 is the value in the second position of the array A1:A3. (Hint: Create three new columns. In the first column, use the MIN function to calculate the minimum distance for the customer in that row. In the second column use the MATCH function to find the position of the minimum distance. In the third column, use the position in the previous column with the INDEX function referencing the row of distribution center names to find the name of the distribution center that should service that customer.) Click on the datafile logo to reference the data. (Hint: The INDEX function may be used with a two-dimensional array: =INDEX(array, row_num, column_num), where array is a matrix, row_num is the row numbers and column_num is the column position of the desired element of the matrix.) Floyd's Bumpers pays a transportation company to ship its product to its customers. Floyd's Bumpers ships full truckloads to its customers. Therefore, the cost for shipping is a function of the distance traveled and a fuel surcharge (also on a per mile basis). The cost per mile is $2.46 and the fuel surcharge is $.56 per mile. The worksheet May in the provided datafile contains data for shipments for the month of May (each record is simply the customer zip code for a given truckload shipment), as well as the distance table from the distribution centers to each customer. Use the VLOOKUP function to retrieve the distance traveled for each shipment from the exercise completed above, and calculate the charge for each shipment. What is the total amount that Floyd's Bumpers spends on these May shipments? If required, round your answers to two decimal places.

Floyd’s Bumpers has distribution centers in Lafayette, Indiana; Charlotte, North Carolina; Los Angeles, California; Dallas, Texas; and Pittsburgh, Pennsylvania. Each distribution center carries all products sold. Floyd’s customers are auto repair shops and larger auto parts retail stores. You are asked to perform an analysis of the customer assignments to determine which of Floyd’s customers should be assigned to each distribution center. The rule for assigning customers to distribution centers is simple: A customer should be assigned to the closest center. The worksheet Floyds in the provided datafile contains the distance from each of Floyd’s 1,029 customers to each of the five distribution centers. Your task is to build a list that tells which distribution center should serve each customer. The following functions will be helpful:

=MIN(array)

The MIN function returns the smallest value in a set of numbers. For example, if the range A1:A3 contains the values 6, 25, and 38, then the formula =MIN(A1:A3) returns the number 6, because it is the smallest of the three numbers:

=MATCH(lookup_value, lookup_array, match type)

The MATCH function searches for a specified item in a range of cells and returns the relative position of that item in the range. The lookup_value is the value to match, the lookup_array is the range of search, and match type indicates the type of match (use 0 for an exact match).

For example, if the range A1:A3 contains the values 6, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

=INDEX(array, column_num)

The INDEX function returns the value of an element in a position of an array. For example, if the range A1:A3 contains the values 6, 25, and 38, then the formula =INDEX(A1:A3, 2) 5 25, because 25 is the value in the second position of the array A1:A3. (Hint: Create three new columns. In the first column, use the MIN function to calculate the minimum distance for the customer in that row. In the second column use the MATCH function to find the position of the minimum distance. In the third column, use the position in the previous column with the INDEX function referencing the row of distribution center names to find the name of the distribution center that should service that customer.)

Click on the datafile logo to reference the data.

(Hint: The INDEX function may be used with a two-dimensional array: =INDEX(array, row_num, column_num), where array is a matrix, row_num is the row numbers and column_num is the column position of the desired element of the matrix.)

Floyd's Bumpers pays a transportation company to ship its product to its customers. Floyd's Bumpers ships full truckloads to its customers. Therefore, the cost for shipping is a function of the distance traveled and a fuel surcharge (also on a per mile basis). The cost per mile is $2.59 and the fuel surcharge is $.56 per mile. The worksheet May in the provided datafile contains data for shipments for the month of May (each record is simply the customer zip code for a given truckload shipment), as well as the distance table from the distribution centers to each customer. Use the VLOOKUP function to retrieve the distance traveled for each shipment from the exercise completed above, and calculate the charge for each shipment. What is the total amount that Floyd's Bumpers spends on these May shipments?

If required, round your answers to two decimal places.

Floyd’s Bumpers has distribution centers in Lafayette, Indiana; Charlotte, North Carolina; Los Angeles, California; Dallas, Texas; and Pittsburgh, Pennsylvania. Each distribution center carries all products sold. Floyd’s customers are auto repair shops and larger auto parts retail stores. You are asked to perform an analysis of the customer assignments to determine which of Floyd’s customers should be assigned to each distribution center. The rule for assigning customers to distribution centers is simple: A customer should be assigned to the closest center. The worksheet Floyds in the provided datafile contains the distance from each of Floyd’s 1,029 customers to each of the five distribution centers. Your task is to build a list that tells which distribution center should serve each customer. The following functions will be helpful:

=MIN(array).

The MIN function returns the smallest value in a set of numbers. For example, if the range A1:A3 contains the values 6, 25, and 38, then the formula =MIN(A1:A3) returns the number 6, because it is the smallest of the three numbers:

=MATCH(lookup_value, lookup_array, match type).

The MATCH function searches for a specified item in a range of cells and returns the relative position of that item in the range. The lookup_value is the value to match, the lookup_array is the range of search, and match type indicates the type of match (use 0 for an exact match).

For example, if the range A1:A3 contains the values 6, 25, and 38, then the formula =MATCH(25,A1:A3,0) returns the number 2, because 25 is the second item in the range.

=INDEX(array, column_num).

The INDEX function returns the value of an element in a position of an array. For example, if the range A1:A3 contains the values 6, 25, and 38, then the formula =INDEX(A1:A3, 2) 5 25, because 25 is the value in the second position of the array A1:A3. (Hint: Create three new columns. In the first column, use the MIN function to calculate the minimum distance for the customer in that row. In the second column use the MATCH function to find the position of the minimum distance. In the third column, use the position in the previous column with the INDEX function referencing the row of distribution center names to find the name of the distribution center that should service that customer.)

Click on the datafile logo to reference the data.

(Hint: The INDEX function may be used with a two-dimensional array: =INDEX(array, row_num, column_num), where array is a matrix, row_num is the row numbers and column_num is the column position of the desired element of the matrix.)

Floyd's Bumpers pays a transportation company to ship its product to its customers. Floyd's Bumpers ships full truckloads to its customers. Therefore, the cost for shipping is a function of the distance traveled and a fuel surcharge (also on a per mile basis). The cost per mile is $2.54 and the fuel surcharge is $.56 per mile. The worksheet May in the provided datafile contains data for shipments for the month of May (each record is simply the customer zip code for a given truckload shipment), as well as the distance table from the distribution centers to each customer. Use the VLOOKUP function to retrieve the distance traveled for each shipment from the exercise completed above, and calculate the charge for each shipment. What is the total amount that Floyd's Bumpers spends on these May shipments?

If required, round your answers to two decimal places.

Match the following:

1. The Exel file Accounting Professionals.xls provides the results of a survey of 27 employees in a tax division of a Fortune 100 company. Use PivotTables to find a cross tabulation of the following. Copy and Paste your results and write 1-2 sentences summarizing your findings.
   1. The number of employees of each gender who have or do not have a CPA
   2. The number of employees in each age group who have or do not have a CPA

Employee        Gender  Years of Service.       Years Undergraduate Study       Graduate Degree?        CPA?    Age Group       
1       F       17      4       N       Y       5       
2       F       6       2       N       N       2       
3       M       8       4       Y       Y       3       
4       F       8       4       Y       N       3       
5       M       16      4       Y       Y       4       
6       F       21      1       N       Y       7       
7       M       27      4       N       N       7       
8       F       7       4       Y       Y       2       
9       M       8       4       N       N       3       
10      M       23      2       N       Y       5       
11      F       9       4       Y       Y       3       
12      F       8       2       N       N       2       
13      F       8       4       Y       N       2       
14      M       26      4       N       Y       6       
15      F       9       4       N       Y       2       
16      F       9       2       N       N       2       
17      M       19      2       Y       Y       4       
18      M       5       4       N       N       4       
19      M       19      4       Y       N       7       
20      M       20      4       N       N       6       
21      F       14      4       Y       Y       4       
22      M       31      4       N       N       7       
23      F       10      0       N       N       7       
24      F       10      4       N       Y       3       
25      M       26      4       Y       Y       6       
26      M       28      4       N       N       7       
27      F       5       4       N       Y       1       
                                                        
                                                Age Group       Age Range
                                                1       21-25
                                                2       26-30
                                                3       31-35
                                                4       36-40
                                                5       41-45
                                                6       46-50
                                                7       51-55
                                                8       56-60
                                                9       over 60
                                                        

Q.Explain the flaws in the following analysis or conclusion. (Note that this is not about chi-squared test per se, perhaps, it is about what a statistical analysis (hypothesis testing in this case) can tell us and what it cannot) Background - The Scholarship Committee comprises 3 faculty members, one of whom is Professor X. Professor X also wrote recommendation letter for 5 students who took his class earlier and four of them were awarded scholarships (out of a total of six scholarships awarded to graduate students). A student who applied but was not awarded a scholarship accused Professor X of favoritism, claiming that he/she was denied a scholarship despite having a very high GPA because he/she did not take class under Professor X and had declined him/her a recommendation letter. The student then did following statistical analysis as an evidence of favoritism . The student runs a Chisq-test and his result shows not independent between students who took class under Professor X and Students got scholarship. H0: students who get scholarship is independent with whether they have taken class under Professor X H1: not independent # Let total number of students who applied for scholarship is 70, and only 5 people get the scholarship, conservatively estimate only 3 out of 5 students took Class with ProfessorX, the other two selected did not take Class with ProfessorX # chisquare test shows the p-value <0.05, thus we reject H0 at .05 level and conclude students who got scholarship was affected by whether they have class under Professor X #student in addition tests , how about the total applicants were 60, or 80? and find out either case we reject H0, they are not independent.

Q.Explain the flaws in the following analysis or conclusion. (Note that this is not about chi-squared test per se, perhaps, it is about what a statistical analysis (hypothesis testing in this case) can tell us and what it cannot)

Background - The Scholarship Committee comprises 3 faculty members, one of whom is Professor X. Professor X also wrote recommendation letter for 5 students who took his class earlier and four of them were awarded scholarships (out of a total of six scholarships awarded to graduate students). A student who applied but was not awarded a scholarship accused Professor X of favoritism, claiming that he/she was denied a scholarship despite having a very high GPA because he/she did not take class under Professor X and had declined him/her a recommendation letter. The student then did following statistical analysis as an evidence of favoritism .

The student runs a Chisq-test and his result shows not independent between students who took class under Professor X and Students got scholarship.

H0: students who get scholarship is independent with whether they have taken class under Professor X

H1: not independent

# Let total number of students who applied for scholarship is 70, and only 5 people get the scholarship, conservatively estimate only
3 out of 5 students took Class with ProfessorX, the other two selected did not take Class with ProfessorX

# chisquare test shows the p-value <0.05, thus we reject H0 at .05 level and conclude students who got scholarship was affected by whether they have class under Professor X

#student in addition tests , how about the total applicants were 60, or 80?
and find out either case we reject H0, they are not independent.

Q.Explain the flaws in the following analysis or conclusion. (Note that this is not about chi-squared test per se, perhaps, it is about what a statistical analysis (hypothesis testing in this case) can tell us and what it cannot) Background - The Scholarship Committee comprises 3 faculty members, one of whom is Professor X. Professor X also wrote recommendation letter for 5 students who took his class earlier and four of them were awarded scholarships (out of a total of six scholarships awarded to graduate students). A student who applied but was not awarded a scholarship accused Professor X of favoritism, claiming that he/she was denied a scholarship despite having a very high GPA because he/she did not take class under Professor X and had declined him/her a recommendation letter. The student then did following statistical analysis as an evidence of favoritism . The student runs a Chisq-test and his result shows not independent between students who took class under Professor X and Students got scholarship. H0: students who get scholarship is independent with whether they have taken class under Professor X H1: not independent # Let total number of students who applied for scholarship is 70, and only 5 people get the scholarship, conservatively estimate only 3 out of 5 students took Class with ProfessorX, the other two selected did not take Class with ProfessorX # chisquare test shows the p-value <0.05, thus we reject H0 at .05 level and conclude students who got scholarship was affected by whether they have class under Professor X #student in addition tests , how about the total applicants were 60, or 80? and find out either case we reject H0, they are not independent.

Q.Explain the flaws in the following analysis or conclusion. (Note that this is not about chi-squared test per se, perhaps, it is about what a statistical analysis (hypothesis testing in this case) can tell us and what it cannot) Background - The Scholarship Committee comprises 3 faculty members, one of whom is Professor X. Professor X also wrote recommendation letter for 5 students who took his class earlier and four of them were awarded scholarships (out of a total of six scholarships awarded to graduate students). A student who applied but was not awarded a scholarship accused Professor X of favoritism, claiming that he/she was denied a scholarship despite having a very high GPA because he/she did not take class under Professor X and had declined him/her a recommendation letter. The student then did following statistical analysis as an evidence of favoritism . The student runs a Chisq-test and his result shows not independent between students who took class under Professor X and Students got scholarship. H0: students who get scholarship is independent with whether they have taken class under Professor X H1: not independent # Let total number of students who applied for scholarship is 70, and only 5 people get the scholarship, conservatively estimate only 3 out of 5 students took Class with ProfessorX, the other two selected did not take Class with ProfessorX # chisquare test shows the p-value <0.05, thus we reject H0 at .05 level and conclude students who got scholarship was affected by whether they have class under Professor X #student in addition tests , how about the total applicants were 60, or 80? and find out either case we reject H0, they are not independent.