Homework #1: Building Models in Excel

This homework assignment involves building several Excel models to analyze healthcare clinic costs and develop a cost-reduction plan. The first problem focuses on calculating advanced access performance metrics using Excel functions like SUM, COUNT, VLOOKUP, and IF statements. The second problem uses data simulation and analysis to determine optimal contract bidding strategies. The third problem, the most substantial, requires creating a comprehensive cost-reduction budget analysis for a healthcare system. This involves consolidating clinics, adjusting staffing levels based on wRVUs and other factors, and presenting findings in a PowerPoint presentation. The assignment emphasizes using Excel's capabilities for data analysis, visualization, and decision-making in a healthcare context. Students are expected to demonstrate proficiency in Excel functions, data interpretation, and creating compelling visual presentations.

Homework #1
Building Models in Excel
All directions are given in Excel 2013
There are 3 problems in this first homework, with an accompanying Excel file that must be completed. The third
problem requires you to also create Powerpoint slides that describe your decision and its justification.
Point allocations for the problems are: problem 1: 20 points; problem 2: 30 points; problem 3: 40 points, problem
3 write-up: 10 points. The Excel portion of the grading will be based on your arriving at the correct answers in
the cells indicated. Even if you arrive at the correct answers in the cells, full credit will not be given if you
hardwired in numbers into your formulas, rather than inserting the appropriate cell references.
Your Powerpoint write-up for problem 3 will be based on the extent to which it is effective in using charts,
graphs, and words to convey the message to convince key stakeholders of your recommendations. An approach
to convincing key stakeholders is to describe and show: 1) the current situation and what will happen if the status
quo continues (create the tension for change and set the vision for where the organization needs to be); 2) what
are the options to prevent the bad, and get us to the good in the future, and what are the pros and cons of those
options; 3) what is your recommended option and why; and 4) what this means for the affected stakeholders. Be
creative in thinking through how to put together a convincing story in Powerpoint. Lists of bullet points will be
the worst possible approach. A well-articulated story with graphs or charts and text to explain them, will garner
full points.
1

Problem 1: Advanced Access Performance Metrics
Your clinic has implemented advanced access, an outpatient scheduling technique designed to provide same-day
appointment access. (For more background information on Advanced Access, see Gupta, D., Potthoff, S.,
Blowers, D. and Corlett, J. (2006). Performance Metrics for Advanced Access. Journal of Healthcare
Management. 51(4):246-259. It is available via the link on the Moodle site just below Homework #1).
As part of the performance monitoring feedback mechanisms the clinic has implemented, two key quality
metrics are being tracked to monitor advanced access performance in your clinic:
 PCPmatch (PCP=Primary Care Physician): the proportion of patients who came to the clinic who got to
see their designated provider. (When a patient signs up for insurance, they have to indicate which
doctor they wish to choose as their primary care physician. The goal is that each patient should be able
to get in to see his/her own PCP when s/he comes in for an appointment.)
 PCPcoverage: the proportion of a physician’s appointments in which s/he saw other physician’s
patients as opposed to his/her own. (Thus, the physician is covering for other physicians in the
practice.) The goal is that for an established physician, their appointment slots should be filled as much
as possible with that physician’s patients and not other physicians’ patients.
Generally, for an established physician, one wants PCPmatch to be as close to 1 as possible, and PCPcoverage to
be minimal.
However, for new physicians who are still building their practices, both PCPmatch and PCPcoverage will likely be
high (these physicians see all their own patients, but still have room in their schedules to take excess
appointment demand for other physicians’ patients).
If PCPmatch and PCPcoverage are both low for a physician, this likely indicates that the physician’s panel of
patients is too large.
Helpful Excel Information
The Homework 1 Excel file contains appointment data for December 2010 in the worksheet called
1.appt_Dec2010. The original variables provided were:
 Date,
 PCP_Prov_ID (this is the identification number of the primary care provider of the patient, but not
necessarily the physician the patient saw for this appointment),
 Appt_Prov_ID (this is the identification number of the physician the patient saw for this appointment),
and
 Specialty.
The red triangle in the corner of cell G1 (where Specialty header is) means that there is a comment for that cell.
If you move your cursor over that cell, the comment will appear.
EXCEL TIP: Comments are created by placing the cursor in the cell to which you want to add a comment, then
choosing Review Tab, then choosing New Comment in the Comment section. (If a comment already exists in
that cell, the option that will appear in the Comment section is Edit Comment.)
You can see that two additional variables were created in the event that in the future you need to conduct any
analyses by day of week:
2

Weekday: Column B uses the Weekday function to calculate the day of week of the Date shown in Column A.
This excel function determines the day of week for any date, and return a number that ranges from 1 through 7.
=WEEKDAY(A2,2) means:
 what is the day of the week for the date in cell A2,
 the 2 after the comma starts the numbering so that 1=Monday. A 1 instead of a 2 after the comma means
that 1=Sunday.
EXCEL TIP: If the result isn’t a 1 through 7 after hitting the enter key, it usually means that the cell does not have
the correct number format. To change the number format, highlight that column by putting the cursor on the B
at the very top of the column and it will turn to a black down arrow. Left click to highlight the whole column.
Then choose Home Tab, and on the Number group, click on the little down arrow to choose Number. To get rid
of any numbers to the right of the decimal place, click on the button indicated.
Day_of_wk: A 1 through 7 isn’t very user friendly to indicate a day of the week. To create the Day_of_wk
column of data, you will see on the worksheet called weekday that a table has been created in cells C4 through
D10 that is called day_of_week. You can see the name show up in the upper left hand side name box by
highlighting cells C4 through D10. (Yes, groups of cells can be named just as individual cells can be named.) This
is a LOOKUP table that will be used for column C of the appt_Dec2010 worksheet.
When you go back to cell C2 of the worksheet called 1.appt_Dec2010, the =VLOOKUP(B2,day_of_week,2,FALSE)
means (the V stands for Vertical; there is also a related function called HLOOKUP for Horizontal):
 the value to be looked up in the first column of the table array is in cell B2,
 the day_of_week is the table array to look in,
 look in column 2 of the table for the value to be returned,
 false means the value to be looked up in column 1 must be an exact match (true means look for the
closest match in column 1 and return the corresponding value in column 2).
3

Your Analysis
Your 2 minute analysis
Your boss asked you for a quick overall PCPmatch percentage for December, the meeting is in 2 minutes and she
is breathing over your shoulder asking for the number. Here’s what you need to do to make that calculation.
1. In the PCPmatch column (column F), create an indicator (0,1) variable using an if statement. If the
PCP_Prov_ID matches the Appt_Prov_ID then it should return a 1, else 0. This should be done for Cells
F2 through to F3430. =if(D2=E2,1,0), and fill to cell F3430.
2. You want to use these 0-1 indicators to calculate your percentage. The sum of the 1’s divided by the
total number of visits equals the PCP match percentage. Calculating the percentage requires three
steps:
Step 1: Sum the cells in Column F. In cell F3431, sum the numbers. =sum(F2:F3430).
 Make sure your cursor is in cell F3431 and type =sum(
 Then scroll up and click on cell F2, and then scroll down, and then hold down the shift key before
clicking on F3430,
 Then type ) and then hit the enter key.
Step 2: Count the number of visits represented in Column F. In cell F3432 count the visits.
=count(F2:F3430).
 Make sure your cursor is in cell F3432 and type =count(
 Then scroll up and click on cell F2, and then scroll down, and then hold down the shift key before
clicking on F3430,
 Then type ) and then hit the enter key.
 This counts the total number of entries in your column, which in this case represents the count of all
appointments in December.
 [A quicker way is to put a $ in front of the 2 and the 3450 in the formula in cell F3431; e.g.
=sum(F$2:F$3430). This will make the row number an absolute reference, rather than a relative
reference. Then copy the formula in cell F3431 to cell F3432 and change the word ‘sum’ to the word
‘count’.]
Step 3: In cell F3433, divide the sum in cell F3431 by the count in cell F3432.
= F3431/F3432
 Make sure your cursor is in cell F3433 and type =
 Then click you cursor on cell F3431, then type in the division sign / and then click on cell F3432
Your complete analysis
For tomorrow’s meeting, you have been asked to present PCPmatch and PCPcoverage data by physician. The
clinic considers itself successful if PCPmatch is at least 80% for each physician. They also look for physicians in
which PCP Match and PCP coverage are both low, as it can indicate that the physician’s panel of patients may be
too large if this persists on an ongoing basis.
4

Your goal is to create a table in Excel that looks like this:
Advanced Access Performance Metrics
Physician PCP Match PCP Coverage
100 68% 10%
200 73% 17%
300 86% 2%
400 88% 54%
500 80% 98%
600 63% 5%
700 92% 6%
800 85% 4%
900 78% 15%
1000 86% 12%
1100 71% 11%
1200 74% 13%
1300 89% 13%
1400 83% 54%
5

To do this, you will need to create an Excel Pivot Table.
1. Highlight cells A1 through G3430 by clicking on cell A1 and then scroll down until you can see cell G3430,
then hold down the shift key while simultaneously clicking on cell G3430.
2. Choose Insert, then Pivot Table. Because you highlighted your range already, the wizard pivot table
information should look as below. Make sure New Worksheet is selected and hit OK.
(For Mac Users using Mac Excel, the Pivot Table icon shows up when you click on the Data Tab, choose that you
want to Create a Manual pivot table if it is asking you to choose either Automatic or Manual, and choose to put
the pivot table on a New Worksheet.)
6

3. You will get a new worksheet that looks like below.
4. Double click on the worksheet name tab and change its name to pcp_pivot.
Excel Hint: If the Pivot Table Fields window on the right disappears from your screen, clicking in the Pivot
Table area will make it reappear.
7

5. Highlight PCP_Prov_ID on the pivot table field list and DRAG it to the Row Labels by holding down the
left mouse as you drag it.
6. Highlight Appt_Prov_ID on the pivot table field list and DRAG it to the Column Labels.
7. Highlight PCP_Prov_ID on the pivot table field list and DRAG it to the Values. However, we don’t want
the Sum of Values because this is summing the numbers in the PCP_Prov_ID cells. This doesn’t make
sense because the sum of the ID numbers is meaningless. We want the count because this will record
the number of times each PCP_Prov_ID showed up with each Appt_Prov_ID.
8. To get the Count, click on the down arrow in the Values box and choose the Value Field Settings option.
This will bring up a box as shown below. Choose Count and then hit OK.
8

9. If you want to make it easier to see all the columns without having to scroll to the right, you can reduce
column size by moving the cursor up to the part of column that has the column letter in it. As the cursor
moves over the lines dividing the columns, it will change from a white cross to a black cross. Double
click when it does this and it should make the column only as wide as it needs to be to still show the
data in the column.
10. Each row represents all of a physician’s patients that came in, regardless of which physician that patient
saw. For example, 310 of physician #100’s patients came in, and 211 of them saw physician #100. Each
column represents the patients a physician saw, regardless of which PCP that patient “belonged to”. So,
physician #100 saw 235 patients, of which 211 were his/her own patients.
Calculate PCP Match
So, to calculate PCP match, one needs to calculate the row percentages. Click on the down arrow on
Values, choose Value Fields Settings, and then when the box pops up, choose the Show Values As tab,
and then choose % of Row Total. Before you click the OK button, click the Number Format button, and
change the format to Percentage and change the decimal places to 0. Hit OK to get back to the Show
values as, and then hit that OK to present the data as row percentages.
9

11. To create the summary data table you need, create a table somewhere below your pivot table that looks
like below by typing the header “Advanced Access Performance Metrics” in one row, all in one column,
although it will look like it spills over into adjacent columns. To center this header under the 3 columns
that will be below it, choose the 3 cells that the header spans, then under Alignment option, click on
Merge and Center. Now in the next row, type “Physician” in the first column, “PCP Match” in the
second column, and “PCP Coverage” in the third column. Click the centered lines under Alignment to
center. Now go up to your pivot table, copy the physician ID numbers in the PCP_Prov_ID column, and
paste them into your table under the Physician header.
Advanced Access Performance Metrics
Physician PCP Match PCP Coverage
12. For the PCP Match cell for Physician 100, in your summary table, go to the appropriate cell in the PCP
Match column of your summary table, type an = sign, and then scroll up to the pivot table and click in
the cell where the PCP_Prov_ID and Appt_Prov_ID for Physician 100 intersect ( the 68% number). Then
hit return. Repeat this for all physicians.
13. When completed, the PCP Match column should be filled. However, the formulas are still referencing
the pivot table, so if you choose a different view of the pivot table to look at new data in your pivot
table, the numbers in PCP Match in your summary table would change also, which you don’t want. To
fix this, highlight all the PCP Match cells in your summary data table, and do a Ctrl + C to copy them.
Then choose the down arrow below the Paste button in the Clipboard section, and under the Paste
options choose Values. This will cut the link to the pivot table reference location.
10

Calculate PCP Coverage
14. To calculate PCP Coverage, one needs the column percentages. Remember to click anywhere in the
Pivot Table to bring back the Pivot Table Fields window on the right. Click on the down arrow on Values,
choose Value Fields Settings, and then when the box pops up, choose Show values as, and then choose
% of column. Before you click the OK button, choose the Number Format button and make sure the
Percentage format is selected and the decimal places are 0. Hit OK to get back to the Show values as,
and then hit that OK to present the data as column percentages. This should change the numbers in
each cell of the pivot table to the column percentage. However, in this case, the percentage at the
intersection of matching PCP Prov_ID and Appt_ID represents the proportion of that physician’s patients
that s/he saw. So, PCP Coverage will be 1 minus that percentage. So, the PCP Coverage for Physician 1
is 1-.9, etc.
15. Complete your summary table for the PCP Coverage column by getting the appropriate data from the
pivot table. So, for Physician 100, the cell entry would be =1-B5, etc. Then do a copy, paste special,
values for the PCP Coverage column to break the relative reference links to the pivot table.
11

16. Note that if you wanted to see the data in the pivot table for different days of the week, you can do the
following (not required for the homework assignment.) Drag the Day_of_wk into the Report Filter.
You will see “Day_of_wk appear in cell A1, and (All) in cell B1. Choose the down arrow in cell B1 to
choose different days of the week. The pivot table will filter the data to show only the day of the week
that you specify.
12