HR & Benefit Managers: Do you utilize Excel to analyze data? What types of analyses do you do?
Do you use PivotTables and VLookups? If so, can you give some examples of how you use these tools to analyze your database and get the results you need?
Good Answers (2)
Charles L
Benefits Manager at Stanford University
Best Answers in: Compensation and Benefits (2), Staffing and Recruiting (2)
JoAnn:
There are days when I believe my organization would come to a screeching halt were it not for Excel and all it's formulas and tables.
"Ah, Excel, how many ways do I love thee?"
Medical plan benefit comparisons
** Side-by-side, easy-to-manipulate comparisons of the benefit attributes and copayments. The table is also the foundation for the comparison charts we provide to employees at our benefits website.
Financial reporting
** When would one NOT use Excel to create and evaluate the financial performance of anything . . . ?
Employee contribution modeling
** One of my most-favorite things to do using Excel. It helps me model our contribution strategy and see how a minor change impacts the company's cost, and the employee's contribution amount.
Benefit plan census data
** We have to look at so many different cuts of the census. We can take a census of more than 13,000 people and, using commands like Data>Sort or Data>Filter, we can find out just about anything.
** Pivot Tables -- a fantastic little feature that let's you take a huge amount of data and generate statistics in a few minutes. Medical plan enrollment, for example.
** Lookup -- depending on the type of analysis you need, this command can tell help you determine premium due for life, AD&D, and disability. It can help you analyze different plan designs for disability benefits.
I could go on and on, but I'd hit the 4,000 character limit in this answer field. :-)
In your role, and given the company you work for, I'd like to suggest getting in touch with one of your consultants, such as your actuary or the person that supports him/her. Have a casual conversation -- off the record -- about Excel.
Consider talking to your manager and asking if your consultants could come visit you and show how to leverage Excel to help you do your job. Sometimes consultants are glad to help their clients develop their skills without charging their client for the time. The more you know, the better you become, and the easier the job is for everyone else.
Yes, I use Excel extensively for a variety of analyses and end user applications. Certainly PivotTables and Vlookups are part of that, but there's also -
-Goal Seek or Solver: trying to solve for an optimal budget scenario given certain constraints, for example.
-Statistical Analysis: Histograms and distribution analysis for salary compression or EEO analysis, for example (requires an add-in).
-Regression analysis: Either single or multiple for determining the impact of one variable on a predicted result (eg. wellness factors and their impact on health utilization). Trend analysis could also be done here.
-End user applications: In Excel, you can create user-friendly calculators and estimators (e.g., a tool that will estimate an employee's bonus or expected out of pocket health costs). There's also management tools such as succession analysis, merit increase calculators, and the like.
As I've used Excel more and more, I've found that branching out into Visual Basic (macros, custom functions, loops, etc.) yields even more utility.
I hope this answers your question. If you have something more specific in mind, let me know.
Thanks,
Paul
