Case Problem 3 Data File needed for this Case Problem: Travel.xlsx Rocco Snowboards Anita Park

Fiocco Snowboards Anita Park is a sales rep for Fiocco Snowboards of Provo, Utah. She is planning a trip to Colorado to meet with stores that sell the company’s products. After landing in Denver, Anita will visit nine stores over a three-day period. She wants to reduce the time she spends on the road, so she has asked you to come up with a travel route that minimizes the distance traveled between the nine cities. In a worksheet, Anita identi­ed each store with an ID number ranging from 1 to 9 and entered the longitude and latitude of the cities she will visit, the travel distance between each city, and a map of the city locations. You will use this data to ­nd the best route for Anita’s trip. Complete the following:

1. Open the Travel workbook located in the Excel10 > Case3 folder included with your Data Files, and then save the workbook as Travel Route in the location speci­ed by your instructor.

2. In the Documentation worksheet, enter your name and the date.

3. In the Sales Map worksheet, in cell B6, enter 9 to specify Denver as the ­rst stop in her route.

4. In cell C6, use an exact match VLOOKUP function to retrieve the city name from the lookup table in the range J6:K14. In cells D6 and E6, also use VLOOKUP functions to retrieve the city’s longitude and latitude.

5. For your initial values, assume that Anita goes to Leadville from Denver. In cell F6, enter 2 for the store ID. In cell G6, use an exact match VLOOKUP function to retrieve the city name.

6. In cell H6, use an exact match VLOOKUP function to retrieve the distance between Denver and Leadville using the lookup table in the range J17:T26. (Hint: Use cell B6 as the lookup value, and add two to the value in cell F6 for the column index number.)

7. In cell B7, display the value of cell F6 because Anita will use the ending city from the previous stop as the starting point for the next leg of her trip.

8. Copy the formulas in the range C6:E6 into the range C7:E7 to display the city name and location for the start of the second leg of her trip.

9. In cell F7, enter 3 as the destination (Telluride) of the second leg of the trip. Copy the formulas in the range G6:H6 into the range G7:H7 to display the city name and travel distance for the end of the second leg. (There will not be a value because you haven’t speci­ed this location yet.)

10. Copy the formulas and values in the range B7:H7 into the range B8:H14.

11. In cell F14, enter 9 because Anita intends to return to Denver at the end of her trip. The values in the table represent the initial conditions for the route you will ­nd.

12. In cell H15, enter a formula to calculate the total driving distance in the range H6:H14.

13. Use Solver as follows to ­nd a route that will minimize the total travel distance:

a. Minimize the value in cell H15 by changing the values in the range F6:F13 containing each destination to ­nd the minimum driving distance. (You don’t change cell F14 because Anita ends her trip in Denver.)

b. Add a constraint to the model that all of the values in the range F6:F13 are different. This will have the effect inserting the integers 1 through 8 one time each into the selected cells.

c. Change the Solving method to Evolutionary, and then run the model. (Note that it may take a minute for Solver to ­nd the minimum travel distance.) When Solver is complete, the route map will show the complete path that Anita will take to visit the nine stores.

14. Save the Answer and Population report for Solver method so that Anita can study the method that Solver used to arrive at a solution. Move the worksheets to the end of the workbook.

15. Save the workbook, and then close it.