need assistance with excel

1. Classify patients into 4 tiers based on number of claims. (6 points)

a. Tier 1 (High-Risk) (75-100 percentile); Tier 2 (Rising Risk) (50-75percentile), Tier 3 (Med Risk)(25-50%) Tier 4 (Low Risk) (0-25%)

b. Add a new column to the Beneficiary table that indicates the tier of the patient

c. Add a new column to the beneficiary table that calculates patient age in 2019 (2019 – Year of birth)

d. For each tier identify ( using Pivot table )

i. Average and Median Claim count and claim amount

ii. Percentage of the population with each type of chronic condition

iii. Percentage of population in different age ranges (65 -74, 75-85, 85+)

e. Write a narrative description of each tier and Health IT recommendations for providing services that can reduce costs and improving quality of care (about 150 words)

File too large to upload – can I email it to you?