project business analytics

Part 1) Bank Closings (Chapter 3- Data Visualization) U.S. bank branch closings have reached new high levels in recent years with 3324 closures in 2020. This is mainly due to the fact that costumers continued to migrate to online and mobile banking with high yield savings accounts. The data of bank closings in the United States between 2004 and 2012 is given in the file Closing.xls. Using PivotTables answer the questions below (copy and paste the PivotTables from Excel to your report): a) How many bank closings occurred in the U.S. after the financial crisis (total number of closings for 2009 through 2012)? Show the top 10 states for this period. Which state shows the fourth largest number of closings for the period after the financial crisis? How many closings in that state? Which cities in that state show the second largest number of bank closings? How many?

b) Show the top 5 cities in the U.S. with the largest number of bank closings for the years 2004 through 2012?

c) What percentage of total bank closings happened in 2009 (of the total closings for the years 2004 through 2012)? Use a line chart to the show the percentage of bank closings from 2004 through 2012 and discuss your findings.

d) Filter the PivotTable to show bank closings only in Illinois (IL), Nevada (NV), Colorado (CO), and Texas (TX) for the years 2004 through 2012. What is the total number of bank closings in these states for the years 2004 through 2012?

Part 2) Heart Attack (Chapter 4- Regression) A study performed by a team of scientists at Fremont University Medical School evaluates how body mass index(BMI), cholesterol level, family history of heart attacks, and regular use of tobacco relate to the risk of heart attack (risk of heart attack is probability (in percent) that a patient will have a heart attack over the next 15 year. For each categorical variable (family history of heart attack and regular use of tobacco) you need to define a dummy variable with 1 indicating the answer “Yes” and 0 indicating the answer “No”. The data of study in given in Excel file Heart_attack.xls.

a) Develop an estimated multiple regression equation that estimates risk of heart attack based on body mass index (BMI), cholesterol level, family history of heart attacks, and regular use of tobacco.

b) Use the significance F to determine whether the overall significant relationship is present. What is the conclusion at the 0.05 level of significance?

c) Determine the significance of each individual independent variable at the 0.05 level of significance using p-values from t-test. Which independent variables have significant relationship with risk of heart attack? Discuss.

d) What is the risk of heart attack over the next 15 years for a patient with BMI of 30, cholesterol level of 290, has a family history of heart attack and has been regularly using tobacco.

e) Mention at least two other variables that could be included in the model as independent variables.