Part 1: Add the Credit Line information from Source 3 to Full Set

We are changing the original credit line policy so that a minimum credit line of $2,000 is being established, even if the customer had an original credit line of $0, it is now changed to $2,000. Create a new column called New Credit Line in Source 3 worksheet to reflect these changes. Use the VLOOKUP() function to put this updated information into the Full Set worksheet. You will notice that even if you do it correctly, there will be some errors (N/A values).

Question: Which customer(s) lacks usable data when you apply the VLOOKUP() function? Explain what is causing this problem? [5 points]

Part 2: Add the Missed Payments information from Source 3 to Full Set

We have observed an anomaly in the Missed Payment field. In the Source 3 worksheet, currently if a customer has no missed payments, their value for that field is NONE. We want to add a new column called Missed Payments 2 to convert the string value of NONE to a numeric value of 0. Use the IF() function to do this. Once you do the transformation, use the VLOOKUP() function to bring the data in the Missed Payments 2 column into the Full Set worksheet.

Question: Write the data transformation rule for the missed payment field (list the syntax of the IF() function and explain the criteria you used to transform the data). [5 points]

Part 3: Add the Country information from Source 3 to Full Set

We have observed a lack of consistency in the Country field. Notice that United States string is represented in several different ways. Choose one of those representations and transform the remaining Country data so that the value for the United States is consistent across all customers. Create Country 2 column and use it to hold the transformed data. Use an IF() statement to transform the data. Then use the VLOOKUP() function to bring the data into the Full Set worksheet.

Question: Write the data transformation rule for the country field (list the syntax of the IF() function and explain the criteria you used to transform the data). [5 points]

Customer Name	Original Credit Line	Missed Payments	Country	Country 2
Allan Strate	$10,000.00	1	USA	United States
Cathrine Delamater	$20,000.00	3	USA	United States
Christin Tillinghast	$8,000.00	5	United States	United States
Daniela Becknell	$10,000.00	0	United States	United States
Darryl Manuelito	$10,000.00	0	US	United States
Eleanor Aburto	$10,000.00	0	Canada	Canada
Elnora Willison	$20,000.00	3	US	United States
Erik Vire	$0.00	0	US	United States
Eve Haak	$5,000.00	0	United States	United States
Jamie Sabat	$5,000.00	0	Canada	Canada
Jamie Woodbridge	$5,000.00	5	USA	United States
Julio Willard	$10,000.00	0	USA	United States
Kisha Grauer	$7,500.00	0	USA	United States
Leota Vonderheide	$7,500.00	3	USA	United States
Lonnie Ludy	$10,000.00	2	USA	United States
Max Kindle	$10,000.00	0	USA	United States
Milagros Fehrenbach	$0.00	0	USA	United States
Odessa Smith	$20,000.00	0	USA	United States
Roslyn Plott	$20,000.00	0	US	United States
Roxie Prewitt	$20,000.00	3	US	United States
Suzan Plock	$7,500.00	0	US	United States
Tyrone Hine	$20,000.00	2	US	United States