This is economics and statistics Microsoftexcel homework
PROBLEM SET 3: Excel Functions and Formulas[footnoteRef:1] [1: This problem set is authored by Professor Minyoung Ku in the Department of Public Management at the John Jay College of Criminal Justice, City University of New York.] This exercise is a follow-up to the lab session for “Data Analysis and Visualization in Excel III: Excel Functions and Formulas”. Problem Set Rules: · Each student should submit an individual problem set in Excel in Blackboard through the link, “Problem Set 3 Submission” in the Assignments tab. · Discussing problem sets with other students is permitted. Copying from another person is not permitted. · This assignment is due on the date indicated in the syllabus and on blackboard. · Late assignments will not be accepted. No exceptions. Download the “Citywide HRA-Administered Medicaid Enrollees” data from the New York City Open Data Portal at https://data.cityofnewyork.us/Social-Services/Citywide-HRA-Administered-Medicaid-Enrollees/33db-aeds. Don’t forget to convert the CSV file to an Excel file, and name the new file “Problem Set 3-(Your Initial)”. What you’ve just downloaded is a Comma Separated Values (CSV) file, no matter whether you chose “CVS” or “CVS for Excel”. Before working with the data, open the CSV file in Excel and save it as EXCEL WORKBOOK (.xls or .xlsx). Otherwise you will lose the changes that you make in the file in Excel, such as formulas, tables and charts, when closing Excel. In this problem set, you will be asked to manipulate and analyze data on Medicaid Enrollment in New York City from January 2007 through December 2016. The goal of this assignment is not only to practice Excel functions that you learned in class but also to better understand how one of the two primary U.S. government-run health programs, Medicaid has been rolled out in NYC. This problem set is also intended to provide you with an opportunity to build a portfolio that can show your data analysis skills to your future employers, even if you are not interested in positions in the field of public health or in the public sector. The more you know, the more you can do with data. Without domain understanding, data analytics could extract only poor or wrong information from data. (For more information, see https://www.forbes.com/sites/kalevleetaru/2016/06/12/why-we-need-more-domain-experts-in-the-data-sciences/#c0a8e0b3b508). So, we need to learn what the data are about and to understand its context before working with the data, at least What is Medicaid? and What services does the city government deliver under the program? For students who are not familiar with the program, a short description of the health program is provided below. Medicaid is a joint federal and state program that helps with medical costs for some low-income, elderly, or disabled people who meet the eligibility requirements for the program. The details of Medicaid program vary from state to state. In NYC, four benefits are offered to Medicaid enrollees: Cash Assistance, Supplemental Security Income, Nursing Home Care, and Medical Assistance. Some enrollees receive only Medical Assistance, while others need multiple types of assistances. Cash Assistance is for people who do not earn enough money to support them or their family, or who cannot work because of a disability. Supplemental Security Income is a federal program that provides monthly payments to low-income people who are at least age 65, or disabled or blinded. Nursing Home Care is provided to people who require skilled nursing care but have few assets. Through Medical Assistance, Medicaid provides medical care coverage to low income people. For more information about the program, visit the websites below: https://www.health.ny.gov/health_care/medicaid/ https://www.nytimes.com/2017/06/23/health/medicaid-basic-facts.html Do you feel more familiar with Medicaid now? If so, let’s start! 1. Create a back-up dataset in a separate worksheet (within the same file) and name it “Back-up Data”. Go back to the original dataset and answer Q2 through Q5. 2. Calculate the AVERAGE number of individuals enrolled in each of the four NYC Medicaid services (i.e., Cash Assistance–Medical Assistance, Social Security Income–Medical Assistance, Nursing Home-Medical Assistance, and Medical Assistance Only) for each year from 2007–2016. Also, calculate the AVERAGE number of “Total HRA-Enrolled Medical Assistance Individuals” for each year during the same period. You should use an appropriate function in Excel. 3. Calculate the MAXIMUM number of individuals enrolled in each of the four NYC Medicaid services (i.e., Cash Assistance–Medical Assistance, Social Security Income–Medical Assistance, Nursing Home-Medical Assistance, and Medical Assistance Only) for each year from 2007–2016. Also, calculate the MXIMUM number of “Total HRA-Enrolled Medical Assistance Individuals” for each year during the same period. You should use an appropriate function in Excel. 4. Calculate the MINIMUM number of individuals enrolled in each of the four NYC Medicaid services (i.e., Cash Assistance–Medical Assistance, Social Security Income–Medical Assistance, Nursing Home-Medical Assistance, and Medical Assistance Only) for each year from 2007–2016. Also, calculate the MINIMUM number of “Total HRA-Enrolled Medical Assistance Individuals” for each year during the same period. You should use an appropriate function in Excel. 5. Calculate the RANGE of individuals enrolled in each of the four NYC Medicaid services (i.e., Cash Assistance–Medical Assistance, Social Security Income–Medical Assistance, Nursing Home-Medical Assistance, and Medical Assistance Only) for each year from 2007–2016. Also, calculate the RANGE of “Total HRA-Enrolled Medical Assistance Individuals” for each year during the same period. You should build a formula in Excel. Note: In statistics, RANGE is defined as the difference between the maximum and minimum values. To answer Q6 through Q8, add a textbox to your worksheet. 6. In which year was the enrollment in “Cash Assistance-Medical Assistance” highest? Explain how you got your answer. 7. In which year was the enrollment in “Medical Assistance Only” lowest? Explain how you got your answer. 8. Mary James, NYC Medicaid Director argues that the average number of “Total HRA-Enrolled Medical Assistance Individuals” decreased continuously between 2007 and 2016. Is the statement TRUE or FALSE? Explain how you got your answer. 9. In a separate worksheet (within the same file), using the statistical data that you’ve just calculated in Q2-5, create a TABLE that displays a summary of AVERAGE NYC Medicaid Services Enrollment information, which contains the average number of individuals enrolled in each of the four NYC Medicaid services (i.e., Cash Assistance–Medical Assistance, Social Security Income–Medical Assistance, Nursing Home-Medical Assistance, and Medical Assistance Only) and the average number of “Total HRA-Enrolled Medical Assistance Individuals” for each year from 2007–2016. A template of this summary table is shown on page 4. 10. Save your Excel file and submit it in Blackboard. 3 2 Figure 1. Template of the Summary Table in Q 9 PROBLEM SET 3: Excel Functions and Formulas[footnoteRef:1] [1: This problem set is authored by Professor Minyoung Ku in the Department of Public Management at the John Jay College of Criminal Justice, City University of New York.] This exercise is a follow-up to the lab session for “Data Analysis and Visualization in Excel III: Excel Functions and Formulas”. Problem Set Rules: · Each student should submit an individual problem set in Excel in Blackboard through the link, “Problem Set 3 Submission” in the Assignments tab. · Discussing problem sets with other students is permitted. Copying from another person is not permitted. · This assignment is due on the date indicated in the syllabus and on blackboard. · Late assignments will not be accepted. No exceptions. Download the “Citywide HRA-Administered Medicaid Enrollees” data from the New York City Open Data Portal at https://data.cityofnewyork.us/Social-Services/Citywide-HRA-Administered-Medicaid-Enrollees/33db-aeds. Don’t forget to convert the CSV file to an Excel file, and name the new file “Problem Set 3-(Your Initial)”. What you’ve just downloaded is a Comma Separated Values (CSV) file, no matter whether you chose “CVS” or “CVS for Excel”. Before working with the data, open the CSV file in Excel and save it as EXCEL WORKBOOK (.xls or .xlsx). Otherwise you will lose the changes that you make in the file in Excel, such as formulas, tables and charts, when closing Excel. In this problem set, you will be asked to manipulate and analyze data on Medicaid Enrollment in New York City from January 2007 through December 2016. The goal of this assignment is not only to practice Excel functions that you learned in class but also to better understand how one of the two primary U.S. government-run health programs, Medicaid has been rolled out in NYC. This problem set is also intended to provide you with an opportunity to build a portfolio that can show your data analysis skills to your future employers, even if you are not interested in positions in the field of public health or in the public sector. The more you know, the more you can do with data. Without domain understanding, data analytics could extract only poor or wrong information from data. (For more information, see https://www.forbes.com/sites/kalevleetaru/2016/06/12/why-we-need-more-domain-experts-in-the-data-sciences/#c0a8e0b3b508). So, we need to learn what the data are about and to understand its context before working with the data, at least What is Medicaid? and What services does the city government deliver under the program? For students who are not familiar with the program, a short description of the health program is provided below. Medicaid is a joint federal and state program that helps with medical costs for some low-income, elderly, or disabled people who meet the eligibility requirements for the program. The details of Medicaid program vary from state to state. In NYC, four benefits are offered to Medicaid enrollees: Cash Assistance, Supplemental Security Income, Nursing Home Care, and Medical Assistance. Some enrollees receive only Medical Assistance, while others need multiple types of assistances. Cash Assistance is for people who do not earn enough money to support them or their family, or who cannot work because of a disability. Supplemental Security Income is a federal program that provides monthly payments to low-income people who are at least age 65, or disabled or blinded. Nursing Home Care is provided to people who require skilled nursing care but have few assets. Through Medical Assistance, Medicaid provides medical care coverage to low income people. For more information about the program, visit the websites below: https://www.health.ny.gov/health_care/medicaid/ https://www.nytimes.com/2017/06/23/health/medicaid-basic-facts.html Do you feel more familiar with Medicaid now? If so, let’s start! 1. Create a back-up dataset in a separate worksheet (within the same file) and name it “Back-up Data”. Go back to the original dataset and answer Q2 through Q5. 2. Calculate the AVERAGE number of individuals enrolled in each of the four NYC Medicaid services (i.e., Cash Assistance–Medical Assistance, Social Security Income–Medical Assistance,