Data File needed for this Case Problem: NP_EX_6-5.xlsx Mercy Field Clinic Craig Manteo is the Quality of Care manager at Mercy Field Clinic located in Knoxville, Tennessee. Craig wants to use Excel to monitor daily clinic appointments, looking at how many patients a doctor sees per day and on how much time is spent with each patient. Craig is also interested in whether patients are experiencing long wait times within particular departments or with specific doctors. You've been given a worksheet containing the scheduled appointments from a typical day. Craig wants you to create a dashboard that can be used to summarize the appointments from that day. Complete the following. 1. Open the NP_EX_6-5.xlsx workbook located in the Excel6 > Case2 folder included with your Data Files. Save the workbook as NP_EX_6_Mercy in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. The Patient Log worksheet lists the entire day's appointments, from 8 am to 5 pm at four departments. Convert this data range into an Excel table using Appointments as the table name. Turn off the banded rows style. 4. In the Dept Lookup, Physician Lookup, and Patient Lookup worksheets, convert each data range to a table, naming them Dept_Lookup, Physician_Lookup, and Patient_Lookup, respectively. You'll use these tables to display names instead of IDs in the Appointments table. 5. In the Patient Log worksheet, use your mouse to enter formulas for the following calculated fields (resize the columns as needed to make the field values easy to read): a. Between the Dept ID and Physician ID fields, insert the Department field, and then create an exact match lookup with the VLOOKUP function using the value of the Dept ID field in the Appointments table to retrieve the department name from column 2 of the Dept_Lookup table. (Hint: Remember with an exact match lookup to set the range_lookup argument to FALSE.) b. Between the Physician ID and Patient ID fields, insert the Physician field, and then create an exact match lookup with the VLOOKUP function using the Physician ID field to retrieve the physician name from column 2 of the Physician_Lookup table. c. Between the Patient ID and Patient Check In fields, insert the Patient field, and then create an exact match lookup using the Patient ID field to retrieve the patient name from column 2 of the Patient_Lookup table. 6. Insert the Patient Wait field in column K to calculate whether a patient had to wait past the scheduled appointment time. Use your mouse to enter the IF function to test whether the value of the Exam Start field (cell 15) is greater than the Appt Time Field (cell A5); if it is, return the value 1; otherwise, return the value 0. Format the calculated values with the General cell format. Data File needed for this Case Problem: NP_EX_6-5.xlsx Mercy Field Clinic Craig Manteo is the Quality of Care manager at Mercy Field Clinic located in Knoxville, Tennessee. Craig wants to use Excel to monitor daily clinic appointments, looking at how many patients a doctor sees per day and on how much time is spent with each patient. Craig is also interested in whether patients are experiencing long wait times within particular departments or with specific doctors. You've been given a worksheet containing the scheduled appointments from a typical day. Craig wants you to create a dashboard that can be used to summarize the appointments from that day. Complete the following. 1. Open the NP_EX_6-5.xlsx workbook located in the Excel6 > Case2 folder included with your Data Files. Save the workbook as NP_EX_6_Mercy in the location specified by your instructor. 2. In the Documentation sheet, enter your name and the date. 3. The Patient Log worksheet lists the entire day's appointments, from 8 am to 5 pm at four departments. Convert this data range into an Excel table using Appointments as the table name. Turn off the banded rows style. 4. In the Dept Lookup, Physician Lookup, and Patient Lookup worksheets, convert each data range to a table, naming them Dept_Lookup, Physician_Lookup, and Patient_Lookup, respectively. You'll use these tables to display names instead of IDs in the Appointments table. 5. In the Patient Log worksheet, use your mouse to enter formulas for the following calculated fields (resize the columns as needed to make the field values easy to read): a. Between the Dept ID and Physician ID fields, insert the Department field, and then create an exact match lookup with the VLOOKUP function using the value of the Dept ID field in the Appointments table to retrieve the department name from column 2 of the Dept_Lookup table. (Hint: Remember with an exact match lookup to set the range_lookup argument to FALSE.) b. Between the Physician ID and Patient ID fields, insert the Physician field, and then create an exact match lookup with the VLOOKUP function using the Physician ID field to retrieve the physician name from column 2 of the Physician_Lookup table. c. Between the Patient ID and Patient Check In fields, insert the Patient field, and then create an exact match lookup using the Patient ID field to retrieve the patient name from column 2 of the Patient_Lookup table. 6. Insert the Patient Wait field in column K to calculate whether a patient had to wait past the scheduled appointment time. Use your mouse to enter the IF function to test whether the value of the Exam Start field (cell 15) is greater than the Appt Time Field (cell A5); if it is, return the value 1; otherwise, return the value 0. Format the calculated values with the General cell format
