Week 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Total Total Costs of Physician Visits 77,302 79,189 74,897 73,410 74,554 75,480 74,236 75,508 76,317 76,021 78,845 79,737 80,263 79,010 81,235 84,212 82,183 82,476 81,098 84,207 90,131 95,107 99,865 97,770 98,707 2,051,760 # Visits 397 439 388 419 393 425 410 458 449 455 445 467 471 478 491 494 519 467 467 493 475 511 512 512 535 11,570 # Employees 4,048 4,055 4,065 4,077 4,086 4,064 4,067 4,089 4,096 4,078 4,103 4,105 4,075 4,092 4,093 4,118 4,117 4,127 4,121 4,122 4,146 4,146 4,140 4,120 4,125 102,475 TRUE MASTER PLAN – QUESTIONS Use the guidelines for writing a report on the course web site and use the following questions as a guide: 1. Using only the information provided in Exhibit 1, explain why further analysis of physician visits may be needed. Compare the profitability of hospital and surgical services to physician services, using the allocation of revenue that was given. Show the breakdown of the $250 premium using a bar chart or pie chart. Does the allocation of the $250 per employee per month payment across the types of health care services seem reasonable, given the past two months’ utilization? 2. The weekly utilization data is provided in the Excel file on the class website. Create scatter plots to show the relationships between the following and explain the results: 2.1 The total cost of physician visits vs. # of employees 2.2 # of employees vs. # of visits 2.3 Total cost for physician visits vs. # of visits 3. For question # 2, calculate visits per employee and the cost per visit for each week. Calculate and interpret the mean, standard deviation, and coefficient of variation for these measures for the six-month period. Explain how these statistics are useful in understanding the trend in total outpatient physician costs per DigiTech Media employee. 4. Regression analysis can be useful to tease out the importance of various factors in explaining costs. 4.1 Evaluate the relationship between visits per week and week. Interpret your regression results by discussing the significance of the regression equation and the magnitude of the estimated coefficients. 4.2 Evaluate the relationship between cost per visit and week. Interpret your regression results by discussing the significance of the regression equation and the magnitude of the estimated coefficients. 4.3 Compare the results from the two regressions and explain how they can be used to help Mr. Glen in making his decision. 5. Mr. Gordon referred to a national study of copayment levels. The important results of this study are provided in Exhibit 3. 5.1 Calculate the price elasticity of demand for physician visits at each copayment level using the DigiTech method. What does the data tell you about the price elasticity of demand for physician visits? How does this information help Mr. Glen in making his decision? 5.2 Using the six months of data provided in the Excel file, simulate the profitability of the physician services department if copayments are increased to $20 per visit. 6. Using the six months of data provided in the Excel file, calculate the percentage reduction in physician payments that would be required to achieve the same level of profits as in Q.5.2. 7. In addition to the options suggested by his staff, Mr. Glen recently read an article about rationing health care services as a method of controlling costs. The general idea of rationing is that more expensive treatments are excluded so that basic health benefits can be provided to a wider population. Health plans can implement rationing by limiting the types of services they will cover. While they commonly exclude coverage for experimental treatments and cosmetic surgery, many are now considering adding physical therapy, mental health services, and therapies that treat fatal conditions to the list of excluded services. Would you recommend that Mr. Glen consider this approach? Discuss ethical considerations. True Master Plan: Coaching Notes 1 Question 2: Sample Statistics and Scatter Plot ◼ ◼ ◼ Create two new variables: visits/employee and cost/visit. How do these compare over time? Calculate means and standard deviations: consider confidence intervals for these variables. Scatter Plots: ◼ ◼ Instructions for making scatter plots with Excel are on the Materials section of BUS 302 web site. There are instructions for Excel 2003 and Excel 2007. Be sure to use the appropriate instructions. 2 Question 3: Regression Analysis ◼ Evaluate the time trends in visits per week and cost per visit. ◼ Use regression. ◼ ◼ Instructions are in the Materials section of the BUS 302 web site for both Excel 2003 and Excel 2007. Be sure to correctly indentify the X and Y variables. 3 Question 3: Regression Analysis ◼ Coefficient of Determination – (R2) ◼ ◼ Regression Coefficients ◼ ◼ The percent of variation in the dependent variable (Y) accounted for by variation in the independent variable (X). The closer to 1 the better. Coefficient on x-variable measures the slope of the trend Line. (Positive Sign - Positive Relationship, Negative Sign – Negative Relationship) t-statistic and p-value are used to indicate the observed level of significance. 4 Using Excel for Statistics Office 2010 and 2013 1 Concepts Covered ◼ Statistics - Descriptive Statistics - Histograms - Hypothesis Testing - Scatter Plots - Regression Analysis 2 To Set Up Statistical Package ◼ ◼ ◼ ◼ Click File Tab, and Then Click Options. Click Add-ins. In View and Manage Box, Select Analysis ToolPak. Click Go. In the Add-Ins Available Box, Select Analysis ToolPak Check Box and Click OK. (If ToolPak Is Not Listed, Click Browse to Locate It.) 3 Using Excel: Descriptive Statistics ◼ ◼ ◼ ◼ ◼ Click Data/Data Analysis (Far Right) /Descriptive Statistics & OK. Put Checkmarks on Summary Statistics, 95% or 99% Confidence Interval, & Labels in First Row Boxes. Move Cursor to Input Range Window, Highlight Data to Analyze including Labels, & Click OK. Your Data will Appear on New Worksheet. Widen Columns by Clicking Home/Format/AutoFit Column Width. 4 Using Excel: Constructing Histograms ◼ ◼ ◼ ◼ ◼ Click Data/Data Analysis/Histogram & OK. Put Checkmarks on Chart Output & New Worksheet Boxes. Move Cursor to Input Range Window, Highlight Data Going into Histogram. Move Cursor to Input Bin Range, Highlight Data Showing Upper Value of Each Bin & Click OK. Histogram will be on New Worksheet. You May Lengthen it by Clicking Blank Space in Window, Moving Cursor to Window Bottom Line & Holding Down Mouse Button as You Pull Down Window. 5 Using Excel: Hypothesis Testing ◼ ◼ ◼ ◼ ◼ ◼ Go to Sheet One. Click Data/Data Analysis/ and the Appropriate Statistical Test. Then Click OK. On New Window Check Labels Box and Put Cursor on Variable 1 Range. Highlight Variable 1 Data Including Label. Put Cursor on Variable 2 Range & Highlight Variable 2 Data (Including Label). Then Click OK. Click Home/Format/AutoFit/Column Width 6 Using Excel: Scatter Plots ◼ ◼ ◼ ◼ Go to Sheet One. Highlight Data (Be Sure X Values are in Left Column and Y Values are in Right Column). Click Insert/Scatter. Pull down menu and click Upper Left Icon. Click a Datum Point on Chart with Right Mouse Key, Add Trendline, & Click Linear. 7 Using Excel: Regression Analysis ◼ ◼ ◼ ◼ ◼ ◼ Go to Sheet One. Click Data/Data Analysis (On Far Right) /Regression & Click OK. On New Window Check Labels Box and Put Cursor on X Range. Highlight X Data Including Label. Put Cursor on Y Range & Highlight Y Data (Including Label), Then Click OK. Click Home/Format/AutoFit Column Width. 8 How to Do a Regression in Excel – Office 2003 A Tutorial for Gateway Students 1. Enter the data into a spreadsheet 2. Tools/DataAnalysis/Regression 3. Enter the dependent variable in the “y” column and the independent variable (or variables) in the “x” columns 4. Indicate where output should go (the 1st cell in the range works) 5. The basic regression is done. (You may need to widen columns)
Purchase answer to see full attachment