Independent Challenge 1As the assistant to the clinic director at Riverwalk Medical Clinic, you have been asked to organize the billing information for the physical therapy department. Using data in an Excel worksheet, you will create a table and analyze the procedure data to help with the January billing.a. Start Excel, open IL_EX_5-3.xlsx from the location where you store your Data Files, then save it as IL_EX_5_RiverwalkPT.b. Using the data in the range A1:G64, create a table and format the table with the table style Blue-Gray, Table Style Medium 7. Widen the columns as necessary to fully display the field names.c. Remove the banding of the table rows. (Hint: Use the Table Style Options group on the Table Tools Design tab.)d. Add the record shown below to the end of the table:Procedure CodeProcedureDateAmount BilledPaymentProviderPatient ID601QTherap Proc 21/15/2021$65$30Rubin1189e. Delete the record in row 8 for procedure code 251D.f. Remove any duplicate records by checking for matching procedure codes.g. Add a new column to the table by entering a field named Balance in cell H1. Calculate the balance for each procedure using structured references to subtract the payment from the amount billed. Format the balance amounts in column H in Accounting format with the $ symbol and no decimal places.h. Sort the table by Balance in ascending order. i. Use a custom sort to sort the table first by the procedure in descending order, and then within each procedure by the provider in ascending order.j. Filter the table to show only Martins procedures, then copy the filtered records to the Martin Procedures worksheet. Do not copy the field names. Clear the filter from the table on the January Procedures worksheet.k. Filter the table to show only records where the amount billed is greater than or equal to $80 and less than or equal to $200. Copy the filtered records to the >= 80 <=200 worksheet. Do not copy the field names. Remove the filter from the table on the January Procedures worksheet.l. On the January Procedures worksheet, enter 467B in cell J2. Enter a VLOOKUP function in cell J4 to retrieve the procedure based on the procedure code entered in cell J2. Make sure you have an exact match with the procedure code. Test the function by changing the procedure code in cell J2 to 331E.m. Use the database function DSUM in cell J10 to total the amount billed for Rubin using the criteria in J7:J8. Format cell J10 in the Accounting format with the $ symbol and no decimal places.n. Using the criteria in J7:J8, enter a database function in cell J12 to count the number of procedures performed by Rubin. (Hint: Use the DCOUNTA function.) o. Use the Data Validation dialog box to add an in-cell dropdown list to the cells in the provider column that restricts entries to Axel, Martin, and Rubin. Check the list by clicking any cell in column F. Compare your table to FIGURE 5-22.p. Add print titles to repeat the first row at the top of each printed page, enter your name in the center section of the worksheet footer, enter January Procedures in the center section of the header, then activate cell A1.q. Save the workbook, preview it, then submit the workbook to your instructor.r. Close the workbook, then close Excel.

Patient ID 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 Procedure Code Procedure 124A Therap Proc 2 325B Therap Proc 2 311A Therap Proc 5 431V Therapeutic Activity B 762N Therap Proc 5 215C Therap Proc 6 251D Therap Proc 4 966W Therap Proc 2 6535 Therapeutic Activity Ter 245M Therap Proc 4 3340 Therap Proc3 452R Therap Proc 1 331E Therapeutic Activity B 855R Manual Therapy Tec 7540 Therapeutic Activity D 543Y Therap Proc 2 556J Therap Proc 3 675Y Therap Proc 3 544T Therap Proc 1 778W Therap Proc 3 446R Therap Proc 3 677Y Therap Proc 4 433 Therap Proc 5 5500 Therap Proc 6 335P Manual Therapy Tec 661Y Therapeutic Activity A 422R Manual Therapy Tec 325B Therap Proc 2 749L Therapeutic Activity Ter 467B Therap Proc 4 739) Manual Therapy Tec 558B Manual Therapy Tec 831P Manual Therapy Tec 13 Therapeutic Activity A 674T Therapeutic Activity Ter 867M Therap Proc 3 670G Therapeutic Activity Te 622V Manual Therapy Tec 590X Manual Therapy Tec Amount Date Billed Payment 1/21/21 $ 65.00 $ 40.00 1/12/21 $ 65.00 $ 10.00 1/21/21 $ 65.00 $ 8.00 1/21/21 $ 65.00 $ 10.00 1/21/21 $ 65.00 $ 30.00 1/14/21 $ 65.00 $ 24.14 1/20/21 $ 50.00 $ 24.14 1/3/21 $ 65.00 $ 24.14 1/6/21 $ 65.00 $ 30.07 1/7/21 $ 50.00 $ 30.07 1/4/21 $ 75.00 $ 20.00 1/21/21 $ 50.00 $ 8.00 1/21/21 $ 65.00 $ 15.00 1/31/21 $ 65.00 $ 32.00 1/5/21 $ 82.00 $ 32.00 1/5/21 $ 65.00 $ 1/14/21 $ 75.00 $ 1/21/21 $ 75.00 $ 1/20/21 $ 50.00 $ 24.14 1/17/21 $ 75.00 $ 24.14 1/19/21 $ 75.00 24.14 1/19/21 $ 50.00 $ 24.14 1/13/21 $ 65.00 $ 24.14 1/14/21 $ 65.00 $ 24.14 1/5/21 $ 65.00 $ 30.07 1/10/21 $ 78.00 $ 30.07 1/21/21 $ 65.00 $ 1/12/21 $ 65.00 $ 10.00 1/20/21 $ 65.00 $ 24.14 1/18/21 $ 50.00 $ 24.14 1/21/21 $ 65.00 $ 1/14/21 $ 65.00 $ 1/13/21 $ 65.00 $ 50.00 1/13/21 $ 78.00 $ 50.00 1/13/21 $ 65.00 $ 40.00 1/26/21 $ 75.00 $ 1/26/21 $ 65.00 $ 1/24/21 $ 65.00 $ 13.75 1/25/21 $ 65.00 $ 13.75 Provider Rubin Martin Rubin Axel Axel Rubin Martin Martin Axel Rubin Axel Martin Rubin Axel Rubin Martin Axel Rubin Axel Rubin Axel Martin Martin Rubin Martin Rubin Martin Martin Rubin Martin Rubin Axel Rubin Axel Rubin Martin Rubin Rubin Rubin 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1126 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162