populate a list at B28 to ensure you have not missed any loans in your system conversion reconciliation

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

11. Write formula(s) under column U that adds the number of discrepancies calculated from columns P to T.
12. Write formula(s) under column V (Overrides) to look up Codes and Comments related to each loan in Overrides worksheet. If applicable, your formula in column V should show comments in the Overrides worksheet column E (Codes) combined with a dash “-” in-between and the followed by column I (Comments). The combination of column E (Codes) and I (Comments) should be reflected in one cell. For example, for loan #SQ1-000050144 your override formula must show: “Payoff Date - Fully charged off loan”
13. Utilize freeze panes function in Excel to ensure the cells that contain headers (row2), loan numbers, and dates (column A and B) are always showing/fixed, regardless of scrolling up, down, right or left in the workbook.

 

 

4.    Difference: (5 points) 

1. Type “Loans not in CAP” in cell A1, and “Loans with attribute variances” in cell A20. Name D2 as “SQ1” and E2 as “CAP”
2. Insert formula(s) in D3 and E3 and below to represents all the SQ1 loan numbers that do not exist in CAP. 
3. Name cell D21 as “Loan #”, and E21 as “Attribute Name ” Do a similar process as last step for loans with different key attributes in Cell D21 and G21. You can use referencing for cells under column D. You may populate data under column E with formula. Your work should look like the followings. The numbers or attributes appeared on screenshot are hypothetical and only for presentation purposes. If a loan is not in CAP (missing in CAP), then it should not show up under the “Loans with attribute variances” table.

4. Generate a pivot table in Difference worksheet cell G3 that shows the Loan Purpose (SQ1 worksheet column AE) with the percentage of each category compared to the grand total. Categories with 0.00% must be not showing/eliminated.

Populate a list at B28 to ensure you have not missed any loans in your system conversion reconciliation. Perform a completeness check to ensure all and only loans in SQ1 made it to CAP. To do that, type “SQ1” and “CAP” in D28 and D29. Type a formula in E28 and E29 to count the number of loans in each dataset. Get the SQ1 loan quantity from the SQ1 tab. Similarly, get the CAP loan count from the CAP tab. Calculate the difference in E30. Difference is calculated by SQ1 figure minus CAP. Analyze the result in one paragraph with less than 100 words in Cell A32. Express your opinion about whether the data conversion was successful or not. Support your opinion with findings in the Excel file. Your final work should look like the following. The numbers or attributes appeared on screenshot are hypothetical and only for presentation purposes

Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme