A New Tool in the Actuarial World: Power Pivot

Written by Lawrence Hii: DSC_0407

I became a full-time actuary in January 2013. If you have read a couple blogs in our “Real Life” section, you might realize that most actuaries deal with data every day in their work.

During the first week of my internship at SALT Solutions, Coach Kester sent me a file. When I opened it, all I saw was a big massive table with a couple columns, but endless amount of rows. All of them were filled with numbers, mostly zeros. When I looked down, it said “Page 1 of 10,000”.

Coach Kester said this is what we call “data”.

So, what is data? Raw data is a collection of information. Most of the time, it’s in numbers. Sometimes, it’s in words and we call them strings. But, raw data does not provide useful information, which is why most actuaries use Excel or Access as a tool to slice-and-dice the raw data.

One quick illustration: We have raw data containing daily sale transactions of a company. To find the total sale of the company, we use Excel’s summation function. But the downside is that there is row limitation in an Excel spreadsheet. Excel cannot handle more than 70,000 rows in one spreadsheet. This can cause a huge problem for actuaries to keep their work within that limit.

Now, we have a tool that not only overcomes the limitation issue, but also gives you more calculation functions.  Let me introduce you to it – PowerPivot!

PowerPivot is a free add-on for Excel 2010 and 2013. A lot of people overlook this. PowerPivot is a business intelligence tool, or as I call it, a hybrid tool of Microsoft Access and Excel. It merges both tools’ functions in one excel spreadsheet.

With PowerPivot, you can create tables and draw relationships between tables. You can also perform calculations across tables in one Excel spreadsheet. The best part of PowerPivot is that all the imported data is in read-only format, which means any changes to the data in PowerPivot will not affect your real data or raw data. It works exactly like Pivot Table, but comes with more functions.

Personally, I think PowerPivot is an awesome tool to learn. As a novice student-actuary, I learned PowerPivot from on a book called DAX Formulas for PowerPivot by Rob Collie. I also did some online research.

You can check out more PowerPivot information here: www.powerpivotpro.com. For college students, I believe PowerPivot will be a game changer in front of actuaries and recruiters during job interviews. Why not take advantage of it this summer and learn something new?