You have to do two broad tasks Task 1 Prepare a dataset for a feeding into a machine learning algorithm for predicting delinquency (aka delay). This means the all character categorical fields need to be converted into a numeric category. - Along the same lines and add columns for other categorical fields to create binary class ( 0/1) or multiple class (1,2,3 etc.) in new columns on the data set. - No original fields should be overwritten. Create New columns for all data transformations - Please use Nested IF. Points will be deducted for the use of IFS() function. Task 1 - 35 Points Task 2 Your Boss also wants some descriptive analysis of the data set done. You may need to use a combination of the original character fields and new numerical fields in some calculations. For e.g. for the full data set this rate = \# of customers with delinquency flag = "Y" / 150 Task 2: Q 1- 5 points each, Q 6-9 10 points each Questions Answer (need to see the Formula here or reference to formula calculated somwehere on the file) 1 What is the Delinquency rate (\#of delinquent customers / \# of loans) in the full file? 2 What is the proportion of customers with credit score >500 in the dataset 3 What is average loan balance among delinquent customers? 4 What is average loan balance among good (non-delinquent customers) 5 What is the proportion of female customers in the data set? 6 What is the number of customers who have borrowed in the Wedding or Medical categories? 7 How many customers with home_ownership = "Rent" have take a loan for a car? 8 What is the \# of customers who have a mortgage, have taken a loan for car or personal reasons and are >25 years old 9 What is the total loan balance for the segment of customers in question 8 You have to do two broad tasks Task 1 Prepare a dataset for a feeding into a machine learning algorithm for predicting delinquency (aka delay). This means the all character categorical fields need to be converted into a numeric category. - Along the same lines and add columns for other categorical fields to create binary class ( 0/1) or multiple class (1,2,3 etc.) in new columns on the data set. - No original fields should be overwritten. Create New columns for all data transformations - Please use Nested IF. Points will be deducted for the use of IFS() function. Task 1 - 35 Points Task 2 Your Boss also wants some descriptive analysis of the data set done. You may need to use a combination of the original character fields and new numerical fields in some calculations. For e.g. for the full data set this rate = \# of customers with delinquency flag = "Y" / 150 Task 2: Q 1- 5 points each, Q 6-9 10 points each Questions Answer (need to see the Formula here or reference to formula calculated somwehere on the file) 1 What is the Delinquency rate (\#of delinquent customers / \# of loans) in the full file? 2 What is the proportion of customers with credit score >500 in the dataset 3 What is average loan balance among delinquent customers? 4 What is average loan balance among good (non-delinquent customers) 5 What is the proportion of female customers in the data set? 6 What is the number of customers who have borrowed in the Wedding or Medical categories? 7 How many customers with home_ownership = "Rent" have take a loan for a car? 8 What is the \# of customers who have a mortgage, have taken a loan for car or personal reasons and are >25 years old 9 What is the total loan balance for the segment of customers in question 8