Data File needed for this Case Problem: Athey.xlsx

Athey Department Store Athey Department Store in Fort Dodge, Iowa, has always accepted returns ; of any product purchased in its store. Mitchell Athey wants to develop a system for handling the returns. A routing slip will allow him to monitor who handled the return at each step. He will also be able to collect the returns data in a worksheet for count of returns in each category. Mitchell has started developing the routing slip, and he wants you to finish creating it. Complete the following:

1. Open the Athey workbook located in the Excel8 > Case4 folder included with your Data Files, and then save the workbook as Athey Routing Slip in the location specified by your instructor.

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

3. Create a defined name for the table on the Return Data worksheet to help you when you create formulas with VLOOKUP functions. (Hint: Use a name other than ReturnTbl because this name is already used as the defined name for the table on the Return Data worksheet.)

4. In the Return Routing worksheet, do the following:

a. In cell B9, use a date function to display the current date.

b. In cell B11, use an input message to inform the user to enter a product description in this cell.

c. In cell B14, use a list validation for the Department located on the Return Data worksheet.

d. In cell B16, use a list validation for the Resolution located on the Return Table worksheet (the range B1:E1).

e. In cell B20, use an input message to inform the person entering the information to enter their name, initials, or Employee ID in this area.

5. In cell B18, create a formula using nested IF functions and VLOOKUP functions to determine what to do with the returns. Use the lookup table in the Return Data worksheet. Refer to Figure 8-46 for some hints on how to create the formula.

Figure 8-46 Formula for return resolution

6. Test your routing slip by entering the following information:

7. Protect all cells in the Return Routing worksheet except those in which you enter data.

8. In the Return Analysis worksheet, complete the following analysis of the Return Data worksheet using COUNTIF:

a. Compute the total Returns by Department.

b. Compute the total Returns by Resolution.

9. Save the workbook, and then close it.

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.