Question

In: Statistics and Probability

Floyd’s Bumpers has distribution centers in Lafayette, Indiana; Charlotte, North Carolina; Los Angeles, California; Dallas, Texas;...

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. datafile.png (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.42 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.

Solutions

Expert Solution

Create three new columns G, H, I in the Floyds's tab with headers Min, Match and Index

In the min column, of G4 cell reference, enter the formula MIN(B4:F4) and in the column of Match of cell reference H4 enter the formula MATCH(G4,B4:F4,0). Then under the index column of I4 cell reference enter the formula INDEX($B$3:$F$3,1,H4). This will return the closes distribution center of the that customer. Then drag and fill the three columns G,H and I.

Next in the May tab, create three columns named Cost per mile, Surcharge and Total charge in the columns C, D and E. In the column of distance traveled, in the cell B3 enter the formula VLOOKUP(A3,Floyds!A:G,7,0). This will give the distance traveled. Next under the column C, in the cell C3 enter the formula B3*2.64. This will give the cost for the miles. Then in the column D, in the cell D3 enter the formula B3*0.56 which will give the fuel surcharge for the miles traveled. In the column E, in the cell reference E3, enter the formula C3+D3. This will give the total cost for the transportation of the relevant zip code. Then select all the cells from B3:E3 and drag and fill the sheet. This will fill all the costs. The overall cost is $688,963.2 For the May shipments, Floyd's Bumper spends $688,963.2.

The completed excel file is uploaded in the link

https://1drv.ms/x/s!Au3NzHtAEb0skOVs7TpXuEjlINEB5A


Related Solutions

Floyd’s Bumpers has distribution centers in Lafayette, Indiana; Charlotte, North Carolina; Los Angeles, California; Dallas, Texas;...
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...
Floyd’s Bumpers has distribution centers in Lafayette, Indiana; Charlotte, North Carolina; Los Angeles, California; Dallas, Texas;...
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...
Floyd’s Bumpers has distribution centers in Lafayette, Indiana; Charlotte, North Carolina; Los Angeles, California; Dallas, Texas;...
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...
Floyd’s Bumpers has distribution centers in Lafayette, Indiana; Charlotte, North Carolina; Los Angeles, California; Dallas, Texas;...
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...
Dubois Corporation has four outlets across the country: Boston, Dallas, Los Angeles, and St. Paul. Plans...
Dubois Corporation has four outlets across the country: Boston, Dallas, Los Angeles, and St. Paul. Plans call for the following number of products (in 1000s) to be needed at each location. Boston             70 Dallas              80 Los Angeles    100 St. Paul            100             Dubois has two warehouses located in St. Louis and Pittsburgh. Dubois has three plants capable of producing the product. The plants and production capabilities are as follows:             Denver            100             Atlanta            100             Chicago           150 Transportation costs...
oltac Corporation (a U.S. company located in Charlotte, North Carolina) has the following import/export transactions denominated...
oltac Corporation (a U.S. company located in Charlotte, North Carolina) has the following import/export transactions denominated in Mexican pesos in 2017: March 1 Bought inventory costing 114,000 pesos on credit. May 1 Sold 70 percent of the inventory for 94,000 pesos on credit. August 1 Collected 77,000 pesos from customers. September 1 Paid 67,000 pesos to suppliers. Currency exchange rates for 1 peso for 2017 are as follows: March 1 $ 0.23 May 1 0.24 August 1 0.25 September 1...
Speculation Welles Fargo Bank, based in Los Angeles, California, has the ability to borrow 30 million...
Speculation Welles Fargo Bank, based in Los Angeles, California, has the ability to borrow 30 million U.S. dollars (USD) at 5% annualized. It can then invest in either the euro (EUR), the Australian dollar (AUD), or the Canadian dollar (CAD). For simplicity, suppose that the lending rate of all of the euro, the Australian dollar, and the Canadian dollar is 3% annualized over a 10-day time period. The spot rates and the expected rates of the euro, Australian dollar and...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT