Data Project

Data Project: The attached Excel spreadsheet contains a list of hypothetical employees (column C) who – as of Q1 2019 - had been working with OLI for some time, had recently joined, or were expected to join that calendar year. The ‘Raw Historical Data’ tab also contains premiums contributions for both the employer and employee for Medical, Dental, and Vision insurance from 2016 – 2019. Some employees may have chosen not to participate in a specific plan for certain years. This model assumes that the coverage and type of plan are consistent with the employee’s choice throughout all years, for simplicity’s sake. Please use this file to demonstrate your Excel competency and analytical instinct by: Creating dynamic reporting of the recent and historic benefits information included Using any combination of Excel tools and formulae to present the information in a format of your choosing Raising any findings you discover through your analysis of the attached data Populating any missing 2018 Medical Employee Annual Premium (column I) for employees highlighted in yellow. (This information should come from the ‘Premiums’ tab.) Feeling free to run with this portion of the work sample as much as you wish Communication Sample: Referencing your data project, please craft an e-mail message (using appropriate business language, grammar/etiquette, tone, and creativity) to OLI’s Leadership Team that highlights your analysis/observations. Assume that this insight will inform decisions during future healthcare policy renewal periods.