Answered step by step
Verified Expert Solution
Question
...
1 Approved Answer
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
Floyds 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. Floyds 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 Floyds 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 Floyds 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: MINarray The MIN function returns the smallest value in a set of numbers. For example, if the range A:A contains the values and then the formula MINA:A returns the number because it is the smallest of the three numbers: MATCHlookupvalue, lookuparray, 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 lookupvalue is the value to match, the lookuparray is the range of search, and match type indicates the type of match use for an exact match For example, if the range A:A contains the values and then the formula MATCHA:A returns the number because is the second item in the range. INDEXarray columnnum The INDEX function returns the value of an element in a position of an array. For example, if the range A:A contains the values and then the formula INDEXA:A because is the value in the second position of the array A:AHint: 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 twodimensional array: INDEXarray rownum, columnnum where array is a matrix, rownum is the row numbers and columnnum 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 $ and the fuel surcharge is $ 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. $
Floyds 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. Floyds 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 Floyds 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 Floyds 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:
MINarray
The MIN function returns the smallest value in a set of numbers. For example, if the range A:A contains the values and then the formula MINA:A returns the number because it is the smallest of the three numbers:
MATCHlookupvalue, lookuparray, 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 lookupvalue is the value to match, the lookuparray is the range of search, and match type indicates the type of match use for an exact match
For example, if the range A:A contains the values and then the formula MATCHA:A returns the number because is the second item in the range.
INDEXarray columnnum
The INDEX function returns the value of an element in a position of an array. For example, if the range A:A contains the values and then the formula INDEXA:A because is the value in the second position of the array A:AHint: 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 twodimensional array: INDEXarray rownum, columnnum where array is a matrix, rownum is the row numbers and columnnum 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 $ and the fuel surcharge is $ 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.
$
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started