Bank Balance Excel Challenge

In this assignment, you will build and execute linear regression analyses on a dataset that contains regional median bank account balances as well as other variables such as income and age. In order to prepare for this assignment, you should complete the reading and watch the following videos available on Module 5:

• Introduction to Regression and Excel Example
• Multiple Regression Model and Example

Introduction and Data

The Excel file available here Download Excel file available herecontains data collected on bank account holders at a national bank. Each row in the data represents the median values for a particular region in which the bank operates. This data includes the median account balance for each region along with other median variables such as income and age. The director of accounts would like you to develop a multiple regression model that predicts the median bank account balance for a new prospective region based on a number of other factors about customers in each region. Specifically, these factors include the following for customers in each region:

• Education: The median number of years of education
• Income: The median income in $
• Age: The median age of account holders
• Home Value: The median home value in $
• Wealth: The median gross wealth in $
• Balance: The median bank account balance in $

Assignment

Perform a regression analysis in Excel with Balance as the dependent variable, and all other variables included as independent variables. In the regression parameter window, select "Output Range" as the output option. Then select Cell H1 as the output range. Examine the results of the regression analysis and then answer the following questions in the Text Box presented in the Excel file:

QUESTION 1: Which independent variables affect account balance with at least 90% confidence level? How do you know?

QUESTION 2: Consider the variable "Education". By how much (in dollars) is the account balance predicted to change with each additional year of education? How do you know?

QUESTION 3: What is the equation for this regression model? Include all variables in the equation regardless of the p value (use 3 decimal places for all coefficients).

QUESTION 4: Using this regression model, what would be the predicted account balance of a region with the following median values?

• Age: 37
• Education: 13.8
• Income: 85,000
• Home Value: 135,200
• Wealth: 115,500

QUESTION 5: What is the R² value (R square) for this regression model? What does this value mean? What does it tell us in terms of evaluating this model?

QUESTION 6: Which variable has the strongest effect on account balance? How do you know? To answer this questions you may need to create z-scores and re-run the regression analysis. In this case, create a new sheet and perform any data transformation and analysis there.

Show Screenshots and provide answer in excel formula format please .

QUESTION 1: Which independent variables affect account balance with at least 90% confidence level? How do you know? ANSWER 1: QUESTION 2: Consider the variable "Education". By how much (in dollars) is the account balance predicted to change with each additional year of education? How do you know? ANSWER 2: QUESTION 3: What is the equation for this regression model? Include all variables in the equation regardless of the p value (use 3 decimal places for all coefficients). ANSWER 3: QUESTION 4: Using this regression model, what would be the predicted account balance of a region with the following median values? Age: 37 Education: 13.8 Income: 85,000 Home Value: 135,200 Wealth: 115,500 ANSWER 4: QUESTION 5: What is the R2 value ( R square) for this regression model? What does this value mean? What does it tell us in terms of evaluating this model? ANSWER 5: QUESTION 6: Which variable has the strongest effect on account balance? How do you know? ANSWER 6