EX 5-9bIndependent Challenge 1
As 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 Code Procedure Date Amount Billed Payment Provider Patient ID
601Q Therap Proc 2 1/15/2021 $65 $30 Rubin 1189
e
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 Martin’s 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.[order_button_a]
Figure 5-22
January Procedures worksheet. The table is formatted with table style Blue-Gray, Table Style Medium 7; the field name row is dark blue with white text; the table is white with blue row borders, no banding. A cell in column F (Provider field) was clicked, displaying the in-cell dropdown list restricting entries to Axel, Martin, and Rubin. In Column J, database function information displays as follows: Lookup for Procedure Code in cell J2 (331E) is given in cell J4 as “Therapeutic Activity B”. Provider summary information for Rubin: Amount billed shown in cell J10 is $2,288. Number of procedures in cell J12 is 28.
Enlarge Image
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.[order_button_b]
Last Completed Projects
| topic title | academic level | Writer | delivered |
|---|