Floyd’s Bumpers has distribution centers in Lafayette, Indiana;Charlotte, North Carolina; Los Angeles, California; Dallas, Texas;and Pittsburgh, Pennsylvania. Each distribution center carries allproducts sold. Floyd’s customers are auto repair shops and largerauto parts retail stores. You are asked to perform an analysis ofthe customer assignments to determine which of Floyd’s customersshould be assigned to each distribution center. The rule forassigning customers to distribution centers is simple: A customershould be assigned to the closest center. The worksheet Floyds inthe provided datafile contains the distance from each of Floyd’s1,029 customers to each of the five distribution centers. Your taskis to build a list that tells which distribution center shouldserve 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 isthe smallest of the three numbers:
=MATCH(lookup_value, lookup_array, matchtype).
The MATCH function searches for a specified item in a range ofcells and returns the relative position of that item in the range.The lookup_value is the value to match, thelookup_array is the range of search, and matchtype indicates the type of match (use 0 for an exactmatch).
For example, if the range A1:A3 contains the values 6, 25, and38, 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 positionof 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 isthe value in the second position of the array A1:A3. (Hint: Createthree new columns. In the first column, use the MIN function tocalculate the minimum distance for the customer in that row. In thesecond column use the MATCH function to find the position of theminimum distance. In the third column, use the position in theprevious column with the INDEX function referencing the row ofdistribution center names to find the name of the distributioncenter that should service that customer.)
Click on the datafile logo to reference the data.
(Hint: The INDEX function may be used with atwo-dimensional array: =INDEX(array, row_num, column_num),where array is a matrix, row_num is the rownumbers and column_num is the column position of thedesired element of the matrix.)
Floyd's Bumpers pays a transportation company to ship itsproduct to its customers. Floyd's Bumpers ships full truckloads toits customers. Therefore, the cost for shipping is a function ofthe distance traveled and a fuel surcharge (also on a per milebasis). The cost per mile is $2.54 and the fuel surcharge is $.56per mile. The worksheet May in the provided datafile contains datafor shipments for the month of May (each record is simply thecustomer zip code for a given truckload shipment), as well as thedistance table from the distribution centers to each customer. Usethe VLOOKUP function to retrieve the distance traveled for eachshipment from the exercise completed above, and calculate thecharge for each shipment. What is the total amount that Floyd'sBumpers spends on these May shipments?
If required, round your answers to two decimal places.
