Question
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
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-text-image.webp)
Get Instant Access with AI-Powered Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Step: 3
![blur-text-image](https://dsd5zvtm8ll6.cloudfront.net/includes/images/document_product_info/blur-subtext-image.webp)
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started