Ask a question from expert
The Excel Portion of the Grading
29 Pages10463 Words360 Views
Added on 2019-09-13
The Excel Portion of the Grading
Added on 2019-09-13
BookmarkShareRelated Documents
Homework #1Building Models in ExcelAll directions are given in Excel 2013There are 3 problems in this first homework, with an accompanying Excel file that must be completed. The thirdproblem 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, problem3 write-up: 10 points. The Excel portion of the grading will be based on your arriving at the correct answers inthe cells indicated. Even if you arrive at the correct answers in the cells, full credit will not be given if youhardwired 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 approachto convincing key stakeholders is to describe and show: 1) the current situation and what will happen if the statusquo continues (create the tension for change and set the vision for where the organization needs to be); 2) whatare the options to prevent the bad, and get us to the good in the future, and what are the pros and cons of thoseoptions; 3) what is your recommended option and why; and 4) what this means for the affected stakeholders. Becreative in thinking through how to put together a convincing story in Powerpoint. Lists of bullet points will bethe worst possible approach. A well-articulated story with graphs or charts and text to explain them, will garnerfull points. 1
Problem 1: Advanced Access Performance MetricsYour clinic has implemented advanced access, an outpatient scheduling technique designed to provide same-dayappointment 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 InformationThe 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.) Thisis 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 AnalysisYour 2 minute analysisYour boss asked you for a quick overall PCPmatch percentage for December, the meeting is in 2 minutes and sheis 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 allappointments 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 F3432Your complete analysisFor 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 MetricsPhysicianPCP MatchPCP Coverage10068%10%20073%17%30086%2%40088%54%50080%98%60063%5%70092%6%80085%4%90078%15%100086%12%110071%11%120074%13%130089%13%140083%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
End of preview
Want to access all the pages? Upload your documents or become a member.