StM2A8h1

1Your supervisor has now asked you to create a workbook to log information about patients who come into the clinic for services each day. You keep track of the patient data as well as patients’ insurance and payment information.
2ENTER AND FORMAT IDENTIFYING INFORMATION
3Previously, you created a basic workbook serving as a log containing patient and insurance information. Your supervisor wants you to expand the workbook log to include payment information. The supervisor added column labels to the existing worksheet so that you would know the type of account information needed. Before creating the formulas and completing the log for a day’s entries, you want to add a title, a subtitle, and the date at the top of the worksheet.
4a.	Start Excel and open e_md_log and save it as e_md_log_LastnameFirstname (yourname).
5b.	Rename the sheet Visit Log, and then change the sheet tab color to Green, Accent 6.
6c.	Freeze the panes so that the column labels on row 6 will not scroll off screen.
7d.	Change the font to Calibri for the entire worksheet.
8e.	Type Diabetes Management Clinic at Orland Medical Center in cell A1, and then:
9•	Merge and center the title in the range A1:O1.
10•	Apply bold and 22pt font size.
11•	Select the Blue, Accent 1 font color.
12•	Apply the White, Background 1, Darker 15% fill color.
13f.	Type Patient Log in cell A2, and then do the following:
14•	Merge and center the subtitle in the range A2:O2.
15•	Use the Format Painter to copy the formatting from cell A1, and then reduce the font size to 16pt.
16g.	Apply the Thick Box Border style around the range A1:O2.
17h.	Use the appropriate date function in cell A3 to display today’s date in your worksheet. Use the Short Date Format, apply bold, and set the font size to 14pt, and then resize the column to display the date.
18FORMAT COLUMN LABELS
19The column labels in the worksheet need to show organization and have a professional look. Currently, some of the labels in the cells overlap or break inappropriately, making the text difficult to read. To assist with readability, you want to create a Patient Information categorical label to span over columns A:G and an Account Information categorical label to span over columns H:O on row 5. In addition, you will format the column labels.
20a.	Type Patient Information in cell A5, and then do the following:
21•	Merge and center the label in the range A5:G5.
22•	Apply bold and 14pt font size.
23•	Apply the Blue, Accent 1, Lighter 60% fill color for the range A5:G6 to color code the two rows of column labels for Patient Information.
24b.	Type Account Information in cell H5, and then do the following:
25•	Merge and center the label in the range H5:O5.
26•	Apply bold and 14pt font size.
27•	Apply the Orange, Accent 2, Lighter 60% fill color for the range H5:O6 to color code the two rows of column labels for Account Information
28c.	Add these formatting features to the column headings in row 6.
29•	Apply bold.
30•	Apply the Thick Bottom Border to the range A6:O6.
31•	AutoFit columns as needed.
32 
33FORMAT COLUMNAR DATA
34The Num column contains the order in which the patient arrives at the clinic. Instead of entering the numbers in the rest of the rows, use AutoFill to complete the series. The data in the other columns need to be formatted appropriately based on the data type. The date of birth data needs to be formatted with a date format, and the numerical data needs to be formatted to display dollar signs.
35a.	Use AutoFill to complete the series in the range A7:A51, increasing the number by one each for each row. Then do the following:
36•	Select Align Text Right.
37•	Increase the indent four times to shift the data more in the center while keeping the values aligned on the right side.
38b.	Format the range C7:C51 using the Short Date format. Increase the column width as needed. 
39c.	Format the ranges I7:I51 and N7:O51 with Accounting Number Format with no decimal places.
40ENTER PATIENT INFORMATION
41You need to input the last two patients of the day in the Patient Log worksheet.
42a.	Enter the data for the second-to-the-last patient on row 50:
43•	Num: 44
44•	Patient Name: Olsen, Brad
45•	Date of Birth: February 23, 1949
46•	Gender: Male
47•	Insurance Company: Blue Cross
48•	Insurance Type: HMO
49•	Insurance Policy Number: XOF-6365167
50•	Visit Code: 9211
51•	Payment/Co-Pay: 30
52•	Insurance Billing: 60
53b.	Enter the data for the last patient on row 51:
54•	Num: 45
55•	Patient Name: Kizilsac, Yusuf
56•	Date of Birth: June 14, 1952
57•	Gender: Male
58•	Insurance Company: None
59•	Visit Code: 8111
60•	Payment/Co-Pay: 100
61•	Insurance Billing: 0
62c.	Apply Calibri font to the new records if Calibri is not automatically applied.
63CALCULATE THE INSURANCE ADJUSTMENT
64Your clinic participates in a managed fee-for-service plan with the insurance companies. The service plan sets an amount a patient pays (co-pay) and a maximum charge the insurance company pays. If a difference exists between the amount your clinic charges and the total of the patient co-pay and insurance payment, the difference must be adjusted off the books.
65a.	Create a formula in cell L7 that adds the payment/co-pay to the insurance billing and then subtracts the amount from the charge.
66b.	Copy the formula down the Insurance Adjustment column.
67 
68DETERMINE THE CASH DISCOUNT AND ADJUSTED TOTAL DUE
69Your clinic has a policy that if a patient does not have insurance and pays cash the day of the visit, a 10% discount is applied to the amount due to reduce what the patient has to pay. First, insert an IF function to clearly display in a column that “Yes” an adjustment is applicable or “No” an adjustment is not applicable. Second, use another IF function to determine the amount of the discount. Finally, you use an IF function to determine the total due for the visit after the insurance adjustment or cash discount are applied. In most cases, the amount remaining after the co-pay and insurance payment is written off by the medical billing agency.
70a.	Create an IF function in cell M7. If the entry in the Insurance Company cell is None, the function returns Yes; any other value returns No.
71b.	Copy the function down the Discount Applicable column.
72c.	Create an IF function in cell N7 that determines if a discount is applicable (based on Yes for the respective patient in column M). If the logical test is true, the function calculates a 10% discount based on the original charge in column I for the respective patient. IF the logical test is false, the function returns 0. Use an absolute cell reference to the “cash discount” below the lookup table.
73d.	Copy the function down the Cash Discount column.
74e.	Create an IF function in cell O7. If the Discount Applicable cell displays Yes, then subtract the Cash Discount from the Charge to determine the cash amount the patient owes; otherwise, subtract the insurance adjustment from the charge to determine the total amount owed between the patient and insurance company.
75f.	Copy the function down the Total Due After Adjustments column.
76CREATE AND FORMAT A TABLE
77You want to preserve the original worksheet, but you want to copy the worksheet so you can convert the data to a table and use Excel’s table features to continue working with the data. After you convert a copied data set to a table, you will format it with a table style.
78a.	Copy the Visit Log worksheet. Place the copy to the right of the original worksheet. Rename the copied sheet Visit Log Table. Change the tab color of the new sheet to Purple.
79b.	Select the range A6:O51, and then convert the data to a table.
80c.	Apply Table Style Light 14 table style.
81d.	Apply Black, Text 1, Lighter 5% font color to the labels on row 6. (After you converted the data to a table, the style converted the font color to white, making it hard to read some of the column labels.)
82e.	Reduce the widths of columns L, N, and O.
83f.	Display the Total Row, and then display totals for these columns: Charge, Payment/Co-Pay, Insurance Billing, Insurance Adjustment, Cash Discount, and Total Due After Adjustment.
84CREATE A CHART
85You want to create a pie chart that depicts the percentage of co-pays, insurance billing, insurance adjustment, and cash discount of the original total charge. You will need to adjust the payment/co-pay after deducting the cash discount for those who pay cash. Use the Visit Log Table worksheet.
86a.	Set up data to chart:
87•	Type =J6 in cell J55, =K6 in cell K55, =L6 in cell L55, and =N6 in cell M55. Apply bold and wrap text to these labels and then reduce the font size to 10pt.
88•	Click cell J56, and then enter a formula to subtract the Cash Discount total from the Payment/Co-Pay total. This is the net amount of cash payment and co-payments.
89•	Click cell K56, and then enter a formula to echo (or equal) the Insurance Billing total.
90•	Click cell L56, and then enter a formula to echo (or equal) the Insurance Adjustment total.
91•	Click cell M56, and then enter a formula to echo (or equal) the Cash Discount total. (The total of all these formulas should sum to the original Charge total.)
92•	Format the formulas on row 56 with Accounting Number Format with zero decimal places.
93•	Type Net in cell I56.
94•	Apply the Gold, Accent 4, Lighter 60% fill color to the range I56:M56.
95b.	Create a column Sparkline in cell N56 based on the range J56:M56. Do the following:
96•	Apply Sparkline Style Accent 6, Darker 25% style to the sparkline.
97•	Increase the height of row 56 to 48.
98c.	Create a pie chart using the labels and formulas in rows 55 and 56.
99d.	Move the chart to a new sheet named Pie Chart.
100e.	Select chart Layout 1 to display the percentages and category names by or within each slice and to remove the legend.
101f.	Apply chart Style 5.
102g.	Type the chart title: Distribution of Total Charges.
103h.	Apply 12pt font size to the data labels.
104i.	Explode the Payment/Co-Pay slice.
105j.	Move the Pie Chart sheet to the right of the Visit Log Table sheet. Change the tab color of the Pie Chart sheet to Green.
106SORT THE TABLE DATA
107A physician has requested the data in a different order. The physician wants to see the data sorted by insurance company and patient for one review. She then wants to see the visit code and total due after adjustments for a different perspective. You will create copies of the worksheets so that each sheet can display the data differently.
108a.	Copy the Visit Log Table sheet, and then place the duplicate to the right of the Pie Chart sheet. Rename the copied sheet Sort by Insurance. Change the tab color to Yellow
109b.	Sort the table in the Sort by Insurance worksheet by Insurance Company in alphabetical order and then by Patient Name in alphabetical order.
110c.	Copy the Sort by Insurance sheet, place the duplicate to the right of the Sort by Insurance sheet, and then rename the copied sheet Sort by Visit. Change the tab color to Red.
111d.	Sort the data by Visit Code from smallest to largest and by Total Due After Adjustments from largest to smallest.
112e.	Apply the Gradient Fill Orange Data Bar conditional format to the Total Due After Adjustments column in the range O7:051.
113FILTER THE TABLE DATA
114The physician has requested specific information regarding the number of patients with Blue Cross insurance. She also wants to know the number of patients without insurance. You need to filter the data to fulfill the physician’s request.
115a.	Copy the Sort by Visit sheet, and then place the duplicate to the right of the Sort by Visit sheet. Rename the copied sheet Filter Insurance. Change the tab color to Orange. Remove the conditional formatting on the Filter Insurance sheet, if needed.
116b.	Create a filter that displays only patients who have Blue Cross Insurance.
117c.	Duplicate the Filter Insurance sheet, and then place the duplicate to the right of the Filter Insurance sheet. Rename the copied sheet Filter No Insurance. Change the tab color to Dark Blue.
118d.	Remove the filter in the copied sheet, and then set a filter to display records for only patients who do not have insurance.
119e.	Create a footer with your name on the left side, the sheet tab code in the center, and file name code on the right side of each worksheet.
120f.	Save and close the workbook.