For the first exercise, open the LoanCreditRiskData worksheet inside the IntroDataSets.xlsx posted on the course website. Each record in this data range describes a customer’s application for a bank loan. Make a copy of the data range before you start work on the following tasks in case you accidently damage the data more.
- Identify the blank cells in this data range by marking them with FLAG in their cells. How many cells did you identify?
- Delete the records (rows) with missing values that were marked with FLAG in one or more of their cells. How many records did you remove from the Data Range?
- Trim and clean all of the text cells in the Data Range
- Apply the filter to the Loan Purpose column. How many different purposes do you see there? Are there any purposes that seem redundant due to typo, spacing or other problems? Standardize all of the spelling, spacing, capitalizations for each of the ten loan purposes so that all records can be identified as one of these 10 categories.
- Remove any duplicate records that exist in the Data Range. How many did you remove?
- Which field(s) (e.g. columns) have numbers inappropriately entered as text? Convert the column(s) to numeric format.
Now that the data is somewhat cleaned, the bank would like to explore the attractiveness of certain types of applications:
- Use conditional formatting to identify the applications with the top 10% savings. What was the amount of savings for the top 10% savings cutoff point ?
- Using Sort and Filter tools, identify whether Management applications in the Job Field consist of more high credit risk or low credit risk assessments. How many Management applications are there for each credit risk level? How many for each category are applications with the top 10% of savings?
- Using Excel formulas, calculate:
a. The number of applications that own their home
b. The number of applications by people younger than 30 that own their home
c. For Management applications in the Job Field, the average number of months employed
d. The number of applications who have been customers more than 12 months but not more than 36 months
Type your answers in this document. Save your work in the worksheet and submit both the worksheet and your answers here into the Class Exercise 1 Background Problem Set 1 Brightspace submission folder.
Do you need help with this assignment or any other? We got you! Place your order and leave the rest to our experts.