Information systems lab

HCA 447 - Healthcare Informatics

Spring 2019 (1 credit hour course)

Instructions for Advanced Microsoft Excel Project

Build a Pivot Table and Pivot Chart using Microsoft Excel data provided that meets the criteria below.

The Pivot Table should contain the following

1. The Monthly Summary tab SUMs the Patient Account Balance by month.

2. The Patient Summary tab SUMs the Patient Account Balance by Patient.

3. The Practice Summary tab SUMs the Volume by Practice by Month.

The Pivot Chart should contain the following

1. Bar Chart of the Monthly Summary tab.

2. Bar Chart of the Patient Summary tab. 3. Bar Chart of the Practice Summary. The VLookup function should be used to crosswalk the Practice Name on the Patient Accounts tab to the Tax Identification Number (TIN#) on the Practice tab. Upload the Microsoft Excel file via Blackboard along with a short description of

the Pivot Tables, Pivot Charts, and use of VLookup.

Microsoft has support pages explaining these tools.

Pivot Table

Pivot Chart

VLookup

Evaluation Criteria

Evaluation for grade

1. Correct use of Pivot Table to summarize data.

2. Correct use of Pivot Chart to create a visual of the data in the Pivot Table.

3. All tabs populated as listed above.

4. Correct use of VLookup function to crosswalk one value to add another to the table.