These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Wednesday, April 13, 2016

Medicare Average Claims Report

1 Executive Summary

As an actuarial student, I have to analyze extensive and unorganized data from several health insurance providers, including data from Medicare and Medicaid. The data that I deal with usually comes with extra information that is superfluous and unnecessary. The purpose of this project is to create clean and user friendly data so that users can take a quick look at the total averages of payments that Medicare users made. From the several columns that the data has, users or actuaries are usually interested in two: Ambulatory Payment Classification (APC) and Providers State. Using these two columns, or more accurately variables, users or actuaries can calculate the Total Average Payments, which helps them focus on geographical areas and services provided. In turn, the can generate a deeper analysis.

By clicking the Filter Summary bottom the user can select from as many APCs and US states to filter the data, which came from the 2013 data released by Medicare and Medicaid on their website. Once the data is filtered, it is summarized in the Filter Summary sheet. The next step is achieved by clicking the Pivot Table Graph button. This button allows the user or actuary to create a pivot table based on the filtered data. Additionally, this step creates a bar chart that shows the distribution of the Total Average Payment by state and city


Blog Archive