Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

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

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 R2 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 .

image text in transcribed

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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Accounting For Inventory

Authors: Steven M. Bragg

3rd Edition

1642210145, 9781642210149

More Books

Students also viewed these Accounting questions

Question

Understand the process of arbitration

Answered: 1 week ago

Question

Know the different variations of arbitration that are in use

Answered: 1 week ago