Microsoft Excel Work
This page includes three examples of calculators I built while I was a short term disability analyst at The Hartford. I shared these tools throughout the department.
Also included is an example of an analysis I did of costumer service data. It demonstrates my use of pivot tables, sorts and filters, and charts.
VBA Examples
The link below links to an excel file that demonstrates Macros I coded using VBA while at Milliman Intelliscript to automate some of my work. Please note that there is no sensitive information, this is a file I created to imitate the file I worked with at Intelliscript, and there is no proprietary code or information.
Calculator 1
When a claimant returns to work partially, they are eligible for partial short term disability benefits. The benefits are calculated based on how many hours they work. The hours they worked are multiplied by the claimant's hourly wage to determine their weekly earnings.
A claimant receives a portion of their disability benefits proportional to the amount of their week that they partially worked. So if they worked 25% of their work week, they receive 75% of their benefits for example.
The calculator below was designed to be able to quickly enter information about the number of hours a claimant worked during their partial work week and then have excel calculate their dollars earned.
https://1drv.ms/x/s!Ah2Ig4Yd9FbBgTxZBEsotIW7AIDi?e=LPBcp6
Calculator 2
Below is a series of calculators I designed to perform routine claims calculations to save time.
The most complex one is the calculator that calculates how much a claimant has been paid over a period of time. It involved using data validation to switch between functions that calculate the number of calendar days or Monday through Friday business days between two dates.
This was a valuable calculator when claimants where disputing their payment amount. I could easily calculate what the claimant should have been paid, and then create a table summarizing what the claimant was paid. If the two were equal, it quickly resolved the claimants concern.
https://1drv.ms/x/s!Ah2Ig4Yd9FbBgTd4lld1MEakLbgW?e=LJDynU
Calculator 3
Below is a workbook that I used to manage my daily tasks at the Hartford. One worksheet shows the unformatted data I got from the Hartford's claims software. This is all fake names/companies. I generated the IDs using excel, and I created the companies and claimant names based of firms I was familiar with and mixing musicians first and last names.
The workbook also allowed me to manage other aspects of my work such as voicemails and emails. It was often useful to be able to see what voicemails or emails corresponded with tasks on my task worksheet. If a claimant left a voicemail without a reason for the need for call back, I could find it on the task worksheet and take care of the issue and call out with it already resolved. This was typically faster than having the claimant explain the issue.
https://1drv.ms/x/s!Ah2Ig4Yd9FbBgTsFm3XmnmFTtnb0?e=qXoipi
Personal Finance Manager
This is a workbook I designed to manage my personal finance. It uses VLook ups as a means of calculating mortgage costs, workbook protection so that calculation cells can’t be changed, and it has a budget and a spending tracking sheet.
https://1drv.ms/x/s!Ah2Ig4Yd9FbBgT36UUmrC46blCpM?e=K0wPjq
Pivot Table, Sort and Filter, Graph
While at the Hartford I did several projects using data tools and below is an example of one of these data analysis projects. It gives examples of my use of excel tools such as pivot tables, sorts and filters, and graphs.
In a prior project I presented evidence that speed of service is one of the key drivers of positive costumer service scores. This argument was based on two key pieces of evidence:
A review of costumer service scores in a prior project indicated that when claimants provided positive feedback on their costumer service survey, the speed with which their claim was approved and/or the speed at which they received their benefits was the most common reason for them giving a positive score.
Of the four main metrics which short term disability claims analysts are evaluated on at the Hartford, two out of four of these metrics emphasize the speed at which analysts process claims (3rd to Decision and End to End). When we consider claims processing speed to be a driver of the costumer service score, speed becomes the majority of how an analyst’s performance is measured. The claims quality score is the only performance metric that does not place heavy emphasis on speed of execution. It should be noted however that the speed with which the customer is served is measured in the claims quality reviews.
3rd to Decision
End to End
Costumer Service
Claims Quality
The pivot table below summarizes my current costumer service scores as of 4/23/2018.
To make this data more meaningful, I think it prudent to take out two outlier scores. As of 4/23/2018, I have only received one survey that gave me a score of 1, and one survey that gave me a score of 6. All other score columns have at least 5 surveys. These two columns thus represent outliers. Removing them from the table allows us to see a better overall picture of my interim CLI scores over the last four months.
With the outliers removed, the following information can be gleaned:
My interim costumer service score average increases to 69.89%
The next most common score after 10 and 9 is 8. While a score of 8 is not considered a positive score, it is noteworthy that 78.49% of my costumer service scores are 8 or above and 83.87% of my CLI scores are at or above a 7. This indicates that I have an opportunity to increase my score by providing some extra costumer service to about 14% of my claims scores that already have given a review that is close to 9 or 10.
Below is a graph created in excel of that summarizes the data in the pivot table just discussed:
As can be seen in the graph above, my costumer service surveys with a score of 10 have increased at a rapid pace from the beginning of the year through March. Surveys with a score of 9 have been fairly steady. The lines for scores less than 9 are lumped together at the bottom of the chart and each line has stayed fairly consistent over the last four months with no significant spike in any of the scores except a slight spike when my number of 8 interim scores increased in the month of March.
While my scores of ten have declined recently, a look back at the pivot table that this chart is based on shows that I have received less total scores in the current month than February or March. This makes sense given that the month of April is still not over. The graph above indicates suggest that my costumer service scores have been consistent year to date.
Given that my speed and costumer service metrics are above benchmark and appear to be consistent, I can now focus on my claims quality metric and ensure that the way I handle claims is above benchmark.
Microsoft Access Work
One project I did at the Hartford used Microsoft Access (and SQL, although I lost the SQL example) and was intended to achieve two goals:
We had interim claim costumer service surveys that occurred while the claim was ongoing and post claims surveys that occurred after the claim was over. Management's goal was to ensure that the final post claim survey was a score of 9 or 10 out of 10 (thus indicating what the claimant's whole claim experience was and that it was positive).
My goal was to use Access to merge the data we had on interim claim costumer service surveys and claims that were scheduled to be closed to create an excel spreadsheet that had claimants whose claims were both ready to be closed and who already had indicated a score or 9 or 10 on the interim claims costumer service survey.
Once I had a list of claimants who I knew already had indicated a 9 or 10, I followed up with that list of claimants to ensure their claim should indeed be closed, address any issues they may still have, and then asked them to take the post claim survey.
I had for two years the second highest costumer service score in the department.
Below are some images showing how this database was created
