PowerPivot Help

[jgedwardsv]

Hi All,

I am trying to create a model to evaluate participant data and then create a series of charts and tables to correlate that data output to a specific target date mutual fund series.

I have the logic thought out, but am having trouble executing this with powerpivot.

I have 3 tables and a helper age table built. One table contains all the raw data on each target date series with values of equity exposure as the columns (i.e. 50 years to date x%, 45 years to date y% etc etc). A second table contains the participant data calculated in excel (I calculate future account balance and estimate social security payments to derive a total benefit at retirement and then calculate a replacement ratio based on current income and then based on inflated income in the future). My final table contains an array of replacement ratios, grades for each, and corresponding equity allocation.

I am having trouble calculating a participant's age group (in columns in the equity allocation) and corresponding that to a participant's income replacement ratio (the rows in the same table). How can I pull the age group and replacement ratio to find the equity allocation associated with it? I believe this would be an index/match function in excel, but given that this analysis is intended to be run repeatedly with varying sets of participant data (i have linked the table from an external excel workbook to prevent someone from messing with the data model).

Thanks for your help, I'm really stuck here and believe I am close to figuring out how to model this correctly.

John

 

[Matt Allington]

Hi John, I would like to help an der I see you have thought this through. This is not my industry and I don't really understand the scenario. Any chance you can post a sampl eworkbook that illustrates the problem?

 

[jgedwardsv]

Matt,

Thanks for the offer and sorry for the delay (I was away this weekend with the wife and didn't get back until Monday night).

What is the best way to get the files to you? The way I have it set up now is with the data split between multiple linked workbooks, all linked to the analysis workbook. Would I need to send them all? My intent was to have one workbook that contained only the data from the participants so anyone in the firm can update the data and not have the chance of messing with the data inputs for the funds we are analyzing or the age table, etc. I want to prevent editing of things that shouldn't be edited as much as possible.

John

 

[jgedwardsv]

We also have a sharepoint system set up and my end goal was to have participant information stored in each company site in a sharepoint list that I can then pull from for the analysis and build as many calculations into the powerpivot as possible.

 

[Matt Allington]

We should do this via the forum. The best way to get help is to provide a sample workbook that has a small amount of test data that demonstrates the problem, then post it a link to it stored on Dropbox or similar

 

[jgedwardsv]

Matt,

Please see below for the raw data I am looking to manipulate:
https://www.dropbox.com/sh/z8nx1cayf63qxn9/AAB4Ndu7BMP65ZgK0tweSozia?dl=0

I copied the tables from the existing workbooks and put them all into one file so it would be more concise and you can hopefully see the logic.

I have data for participants where I have calculated out future balances and withdrawal amounts to get to the end goal of an income replacement ratio.

What I want to do is measure each participant based on their age group and the income replacement ratio to determine what percentage of equity exposure each one should have, separate them into their respective 5 year increments and aggregate the data to compare to the equity allocations in the TDFData chart. I organized the chart based on years to retirement so the information (in theory) shouldn't need to be updated frequently unless the equity allocations change for an individual target date fund series.

The end goal is to determine which series most closely matches the equity allocations that are most appropriate for the overall participant population (up to 5 "closest" matches would be great, allowing for additional comparison using our existing tools to measure performance, holdings, costs, etc.).

My only issue is that this is not a one time thing - this is something that I want to be able to repeat based on the participant data inputs changing, both for this existing suite of participants and other groups - otherwise I could manually do all these calculations in excel, creating tables for each individual age group. In my original iteration I used many different tables and the spreadsheet became so large that it was unwieldy.

Thanks again for your help and please let me know if I didn't explain something properly.

 

[Matt Allington]

Unfortunately I don't understand the data or the industry, so that makes it hard. Self Service BI is great because you know the industry and the tools, but of course that doesn't help when you are learning. To try to move this forward, let me ask some questions and ask for more info

What I want to do is measure each participant based on their age group and the income replacement ratio to determine what percentage of equity exposure each one should have

What does "measure" mean in this instance?

separate them into their respective 5 year increments and aggregate the data to compare to the equity allocations in the TDFData chart.

Again, I don't know what this means "separate into their respective 5 year increments"

Maybe a worked through example using Excel would help

 

[jgedwardsv]

Matt,

Sorry for the confusion - I'm probably not describing this correctly.

For the first question, what I am looking to do (and have done through powerpivot) is use the age table to create groups of employees based on their age - calculate out each employee's age and then drop them into a group based on years to retirement (starting with 50, declining by 5 year increments to -20 years to retirement). This is because the target date funds come in 5 year incremental steps (i.e. 50 years to retirement is the 2060 fund).

Would it make sense to make this a calculated column in powerpivot or is there a way to write a formula in DAX that would do this automatically when dropped into the pivot table?

The reason I want to separate the employees into 5 year increments is to match each employee up to the respective fund that they should be in based on their age, then aggregate the replacement ratios for each of those employees. For instance if I have 3 employees that would fall in the 50 years to retirement, find the average replacement ratio, tie it to the appropriate equity allocation to determine the best fit fund in the target date data workbook.

What I am trying to achieve is an overall best fit target date fund series for the total employee group, based on the lowest differential in total equity for each 5 year block of employees. It would ideally be visualized by a trend line that most closely corresponds to the declining equity allocations of each suite of target date funds.

 

[Matt Allington]

For the first question, what I am looking to do (and have done through powerpivot) is use the age table to create groups of employees based on their age - calculate out each employee's age and then drop them into a group based on years to retirement (starting with 50, declining by 5 year increments to -20 years to retirement). This is because the target date funds come in 5 year incremental steps (i.e. 50 years to retirement is the 2060 fund).

OK, got it. Read my article about age banding here Banding in DAX - Excelerator BI

..then aggregate the replacement ratios for each of those employees. For instance if I have 3 employees that would fall in the 50 years to retirement, find the average replacement ratio, tie it to the appropriate equity allocation to determine the best fit fund in the target date data workbook.

Lost me again. But with the bands above, does that help you?

 

[jgedwardsv]

I think I'm going to have to put together several different bands to make this work... This will be my project for the very slow Christmas holiday week.

On another note - I just ordered your book from Amazon, I'm going to guess that I will find some information I need in there as this project progresses. Thanks so much for your help so far, I'm sure I'll be back on here looking for more help once I solve the banding issue.