University of Louisville Excel Analysis Worksheet

Create Excel pivot table with a one page executive summary.

See attached documents for instruction on assignment.

Microsoft (MS) Excel is one of the many tools often used by practitioners to analyze large sets

of data. Once data has been entered and the different variables have been placed into unique

columns, pivot tables are often created to look at the data from different perspectives to identify

unique trends and patterns.

Context: This data comes from a physician office with primary care providers (PCP – Physicians)

and nurse practitioners (NPs). They are open M-F, 8am to 5pm. In interviews with the staff,

PCPs, and NPs, you were told the workload is approximately 75% visits with the PCP and 25%

of visits with the NP throughout the week and year. You would expect this type of ratio, since

there are four PCPs, who are paid considerably more and provide a wider range of expertise

and services than the two NPs. You have received the provided MS Excel spreadsheet with the

intention of validating the information received during the interviews. Additionally, review the

data carefully in terms of additional variances, which would need to be presented to the

staff/stakeholders for further explanation (there are several)!

From the spreadsheet, create a pivot table and examine the data by:

1. PCP and NP in general (percentage of visits completed by each).

2. PCP and NP by the type of visit (Initial Consultation/Subsequent Visit) completed over

the 12 months.

3. PCP and NP by the time of the visit (AM/PM) completed over the 12 months.

4. PCP and NP by the type of visit (Initial Consultation/Subsequent Visit) completed over

the 5 days.

5. PCP and NP by the time of the visit (AM/PM) completed over the 5 days.

6. PCP and NP by the status of the visits, specifically the types of cancelations.

Helpful Hints:

oints 1-5 require you to move the pptStatus4o the Filter box and select ONLY

ïmpleted6isits. You would not want to include visits not actually kept.

oints 2-3 require you to move onth4o the Column Box.

oints 4-5 require you to remove the onth!nd move the !y4o the Column Box.

ou may work on this assignment alone, work with others in the class

Deliverables:

ou may work on this assignment alone, with others in or outside of the class, and may

use any external resources to aid in your analysis. If you work in a group, each person

in the group must submit the assignment individually.

ubmit the MS Excel Spreadsheet file with the pivot table.

reate and submit a one-page executive summary, based on the data in the pivot

tables, as to whether the data supports or disproves the information received in the

interview. This one-page summary will be handed to the CEO of the practice directly, as

the CEO wanted an independent analysis.

hile graphs may be used, they are not required.

ables may be included in the summary; however, only one table may be used and the

final submission must remain at one page.