Exercise 3 An independent analyst used a second risk score with a similar rating scale (zero being the lowest score, twelve being the highest) to analyze how the different scores predicted patients' average health care costs over a selected period of time. Note the different trend for cost between the two risk scores. Count of Risk Score 3500 3000 3142 2455 2500 2000 1500 128624529 38889 47559 53605 42551 60000 50000 40000 30000 Average of Cost Exercise 4 Follow the instructions below to create a pivot table and a graph similar to the one shown in Exercises #1 and #2. NOTE: The instructions are specific to Microsoft Excel 2013. Create a Pivot Table Using the Provided Data Set 1. Open the file named Unit8_dataexample.xls in Excel. The spreadsheet contains the data shown below. The data are organized in columns, with headings that describe the information in each column. 2. Click on the Sheet2 tab, next to the Analytic_DataSet tab. This spreadsheet should be blank. 3. Click in the A1 cell in the spreadsheet. 4. Click on the Insert tab on the Excel ribbon. 5. Click the PivotTable button. The Create Pivot Table window opens. Count of Risk Score 1000 500 3500 3000 2500 2000 1500 1000 0 11287 18895 634 Risk Score 1 263 9332 35 4 0 4 5 6 7 3149 52162 51202 1324 1275 26407 24454 17280 13688 18046 30190 27333 36120 30387 34335 45960 20000 10000 60000 50000 40000 30000 20000 10000 728 563 500 323 256 141 67 33 26 8 6 0 0 3 4 5 6 7 8 9 10 11 12 Risk Score 2 1. How would you interpret these results? Health IT Workforce Curriculum Version 4.0 Public Average of Cost Health Care Data Analytics 4 Risk Adjustment and Predictive Modeling 0 1613.58 69 8000 0 1 2 8 0 U baseline 0 2874.24 69 8000 0 1 2 8 0 0 predictio 0 26087.67 50 8000 1 3 2 6 3 3 baseline 0 13131.66 50 8000 1 3 2 6 3 3 predictio 0 49389.84 ? X 3 Create PivotTable 2 baseline 0 15257.46 3 2 predictio Choose the data that you want to analyze 0 3365.37 0 0 baseline Select a table or range 7 49620.42 0 0 predictio 0 1314.9 Table/Range: Analytic_DataSet!$A$1:$N$7900 0 2 baseline Use an external data source 0 6229.14 0 2 predictio 0 3236.49 Choose Connection... 2 2 baseline 2 23792.49 Connection name: 2 2 predictio 0 4556.1 0 1554.39 Choose where you want the PivotTable report to be placed New Worksheet 1 1 baseline 1 1 predictio 0 625.14 Existing Worksheet 0 0 baseline 0 599.61 Location: 0 0 predictio 0 24042.93 3 2 baseline 0 39812.22 Choose whether you want to analyze multiple tables Add this data to the Data Model 3 2 predictio 0 0 0 0 baseline OK Cancel 0 39.18 0 0 predictio 0 358.89 0 0 baseline 00 0000 0 788.7 68 8000 0 1 2 6 0 0 predictio 0 4335.57 63 8000 1 1 2 8 3 2 baseline 0 21921.72 63 8000 1 1 2 8 3 2 predictio 0 8332.83 58 8000 1 1 2 8 0 2 baseline 0 16752.69 58 8000 1 1 2 8 0 2 predictio 0 4344.81 68 8000 1 1 2 8 1 0 baseline 6. In the Table/Range field, type: Analytic_DataSet!$A$1:$N$7900 Health IT Workforce Curriculum Version 4.0 Public Health Care Data Analytics Risk Adjustment and Predictive Modeling LQ 5