Objective

Illustrate some analytical review techniques available using just Excel and the Web based analysis tools. For these examples we will use the data from the State of Pennsylvania regarding their excluded medicaid providers. This data is available at both the Pennsylvania web-site and also at the EZ-R Stats site. This data was selected because it contains many of the common types of data elements which can be reviewed, and also because it is relatively small (approximately 1,500 rows on an Excel sheet).

If you wish to follow along with this tutorial, open the example workbook in Excel, which contains the list of Medicaid providers excluded by Pennsylvania as of 2-3-2007.

The analytical tests we will perform use the tools available at no cost on the Web Analytics page.

The data consists of the following elements: last name, first name, middle name, provider name, license number, beginning exclusion date, ending exclusion date and name of county.

The first test we will perform is to classify the ending exclusion dates (as text). To do this, we first open up the web analytics page for data classification, then we select and copy the data from Excel (in this case the range G2:G1585) and then "paste" the data into the web analytics form using the keyboard command Control-V.

We then specify the test to be performed by clicking the radio button "text only", and then run the test by clicking the "run" button. If all went well you should see a results page like that shown below:

This chart gives us the values, sorted in descending order. Note that the most common value is "indefinitely", at 94%, as would be expected. However, note the value of 12/31/2299, which occurs twice. This value means the providers have been excluded for 293 years, which is longer than the length of time most people live.

Just for illustration purposes, let's check how many exclusion dates fall on a Federal holiday. To do this, we open the web analytics page for date checking. We then select and copy the date range for the beginning and ending dates, i.e. F2:G1585, and paste this data into the form using the keyboard command Control-V. (Make sure it is formatted as a date, using the first format option). We then click on the "run" button. If all went well, you should see results like that shown below:

As can be seen, some of the providers' exclusion dates begin on a Federal holiday. Examples of situations where such dates may raise questions, would include journal or general ledger entries, invoices or purchase orders, etc.

There are many additional analytical tests which can be performed through the internet, and these are described at the web analytics page. Note that there is no software to install or maintain, nor is there any additional cost (assuming that you already have an internet connection and Excel).