The Excel Portion of the Grading

Added on - Sep 2019

Trusted by 2+ million users,
1000+ happy students everyday
Showing pages 1 to 6 of 29 pages
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 willnotbe 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 HealthcareManagement.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 qualitymetrics 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 tosee their designated provider. (When a patient signs up for insurance, they have to indicate whichdoctor they wish to choose as their primary care physician. The goal is that each patient should be ableto 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’spatients as opposed to his/her own. (Thus, the physician is covering for other physicians in thepractice.) The goal is that for an established physician, their appointment slots should be filled as muchas 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 tobe minimal.However, for new physicians who are still building their practices, both PCPmatch and PCPcoverage will likely behigh (these physicians see all their own patients, but still have room in their schedules to take excessappointment demand for other physicians’ patients).If PCPmatch and PCPcoverage are both low for a physician, this likely indicates that the physician’s panel ofpatients is too large.Helpful Excel InformationThe Homework 1 Excel file contains appointment data for December 2010 in the worksheet called1.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 notnecessarily 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),andSpecialty.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, thenchoosing Review Tab, then choosing New Comment in the Comment section. (If a comment already exists inthat 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 anyanalyses by day of week:2
Weekday: Column B uses theWeekdayfunction 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 meansthat 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 havethe correct number format. To change the number format, highlight that column by putting the cursor on the Bat 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 ridof 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_wkcolumn of data, you will see on the worksheet calledweekdaythat a table has been created in cells C4 throughD10 that is called day_of_week. You can see the name show up in the upper left hand side name box byhighlighting 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 called1.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 theclosest 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 thePCP_Prov_ID matches the Appt_Prov_ID then it should return a 1, else 0. This should be done for CellsF2 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 thetotal number of visits equals the PCP match percentage. Calculating the percentage requires threesteps: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 beforeclicking 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 beforeclicking 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 relativereference. 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/F3432Make 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. Theclinic considers itself successful if PCPmatch is at least 80% for each physician. They also look for physicians inwhich PCP Match and PCP coverage are both low, as it can indicate that the physician’s panel of patients may betoo 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 anExcel Pivot Table.1.Highlight cells A1 through G3430 by clicking on cell A1 and then scroll down until you can see cell G3430,thenhold down the shift keywhile simultaneously clicking on cell G3430.2.Choose Insert, then Pivot Table. Because you highlighted your range already, the wizard pivot tableinformation 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 youwant to Create a Manual pivot table if it is asking you to choose either Automatic or Manual, and choose to putthe pivot table on a New Worksheet.)6
Desklib Logo
You are reading a preview
Upload your documents to download or

Become a Desklib member to get access