Floyd's Bumpers has distribution centers in Lafayette, indiana; Chanlotte, North Carolina; Los Angeles, Calfomia; Dallos, Texas; and Pittsburgh, Pennivivania, Each distribubion center carries all products sold. Floyd's customers are auto repair shops and larger auto parts retas stores. You are asked to perform an analysis of the customer assignments to determine which of Fioydts 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 docest tenter. The workshoet Floyds in the provided datanie contains the distance from each of Flovd's 1,029 customers to each of the five distribution centers, Your task is to build a list that tels which distribution center should serve each customer, The following functions wall be helpful: =MIN(array) The MiN finction returns the smallest value in a set of numbers, For example, if the range At:AJ contains the values 6 , 25, and 38, then the formula Min(A1 :AJ) returns the number 6 . because it is the smailest of the three numbers: - MATCHChokip, value, lookup amay, motch thpel The March function searches for a specified item in a range of cels and rotums the relative gosition of that item in the range. The lookup. vakie is the value to match, the fookie. 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 retums the value of an element in a position of an array, For example, if the range AliA3 contains the values 6,25 , and 38 , then the formula =1 NDEX(A1:A3, 2 ) 525 , because 25 is the value in the second position of the array At:A3. (Hint: Create three new columns. In the first column, use the Min function to calculare the minimum distance for the customer in that row. In the second column ust 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. The INDEX function refurns the value of an element in a pesition of an array. For example, if the range A11 A3 contains the values 6 , 25, and 38 , then the formula =inDEXiA1:A3, 2) 525 , because 25 is the value in the second position af the array A1:A3. (Hint: Create three new columns. In the first column, use the Mil function to calculate the minimum ditance for the customer in that row. In the second column use the MATCA function to find the position of the minimum d stance. In the third column, use the postion in ine previous colum alth if Click on the datafile logo to reference the data. DATA the column position of the desired element of the matrix.). Fioyds Bumpers pays o transportation company to ship its product to its custemens. Fiopd's Bumpers ships full truckosds to its customers, Therelare, the coit for insping is a functisn of the distance traveled and a fuel surcharge (aso on a per mile basis). The cost per mie is $2.55 and the fuel surcharge is 5.56 per mile. The workareet May an the provided detafie containe data for shipments for the month of May (each record is simply the customer zip oode for a given trudbad sNipmenth, as well as the distance table tram the distribution centers to each customer. Use the viookup. function to retrieve the distance traveled for each shipment from the exerdse campicted above, and calculate the charge for each ahipment, What is the tolal amount that Floyd's Bumpers spends on these May shipments? If required, round your answers to two decimal piaces