Excel Test – Revenue Cycle Data

Analytics                                                                                                                                                                             

Data analytics is a crucial part of the future of healthcare and our next analyst must have a strong foundation of technical skills in addition to a basic understanding of healthcare finance. This workbook will test your technical abilities as well as your abilities to identify trends and describe them to an end user which is a practical example of this job’s requirements. You will have a limited amount of time from the distribution of this workbook to complete this test and return it to Alvin He at alvin.he@chubb.com for review and scoring (date/time will be specified in your email). Good Luck!                                                          

Step 1: Data Manipulation & Validation                                                                                                                                                                                                                                                                                 

1. Clean-up / Reorganize the “MOCK_DATA_1_RAW” data set so that columns are arranged in a logical order                                                                                                                                                                                                                                                                                       

2. Combine missing information from “MOCK_DATA_2_RAW” into “MOCK_DATA_1_RAW” data set to create one comprehensive set of information (Mock 1 is your source of truth)                                                                                                                                                                                                                                                                           

2a. Briefly describe how you combined the information and validated that the information brought over was placed in the correct corresponding row (duplicate rows should not exist)                                                                                                                                

3. Create a calculated field to show any remaining balances after payments/adjustments                                                               

4. Create a calculated field using an “IF” statement to identify any $0 insurance payments as a “Denial” or an insurance payment as a “Payment”                                                                                                                                                                                                                                                                                         

5. Create a calculated field to show any negative balances as a “Credit”                                                                                                                                                                                                                                                                                  

Step 2: Data Processing & Reporting                                                                                                                                                                                                                                                                                       

1. On a new tab, create a table showing the Total Charges, Payments, Adjustments, and Remaining Balances by Insurance                                                                                                                                                                           

2. On the same tab, create a second table to show the count of denials and amount denied by payer                                                                                                                                                                                                                                                                                        

3. On a new tab, create a table to show the standard A/R aging buckets (0-30, 31-60, 61-90, 91-120, 121-180, 180+) days AS OF 9/12/2023 with the total outstanding amount in each bucket by insurance                                                                                                                                                                                                                    

4. Create a graph that visualizes the aging buckets                                                                                                                                                                                                                                                                                                                           

Step 3: Data Analysis & Description                                                                                                                                                                                                                                                                                         

1. Describe any trends that you see in any of the field combinations                                                                                                                                                                                                         

2. Describe the aging buckets and any questions or concerns that these buckets might present