DOWNLOAD THIS ANSWER
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.
