Answer Bank Case Study Manipulating Excel Data

I work at a Bank in HR and developed a test for new hires to take home. It is a one page Excel file with Attributes and a one page Excel file with fake random account opening data (no real info), given reject rates and you have to review data and come up with assumptions as to why it is happening and root cause for rejects. takes 1-3 hours. Depending how good you are at sorting Excel data. Want someone outside bank to do it and see if they get same results. I email you the excel files and you email back. By this test, we would like to evaluate the following skills: 1. Ability to analyze the data. 2. Be able to ascertain the root cause(s) of the problem. 3. Document audit findings in a clear and structured way. 4. Suggest improvements in internal controls environment based on the identified findings. 5. Present all of the above in a clear and concise audit report. Background: Bank Mobile App performs the verification of their customers identity before the opening of their accounts through the Know Your Customer (KYC) process. The verification is performed by checking the following: - (1) the government-issued photo ID of the customer to confirm that the document is valid and authentic. - (2) the facial picture of the customer to confirm that the face in the picture is the same with that on the submitted ID The customer will 'pass’ the KYC process and get onboarded if they 'pass’ the documentation and facial similarity checks. If the result of any check is not 'clear’, the customer has to then submit all of the photos again. The pass rate is defined as the number of customers who pass the KYC process (both the Documentation and Facial Similarity checks) divided by the number of customers who attempt the entire process. Each customer has up to 2 attempts to pass KYC checks. Problem statement: The pass rate has decreased substantially in the recent period. Questions: 1. Based on your analysis, briefly explain what testing you would expect to perform on the report spreadsheet before analysing the data. 2. What are the root causes of the KYC pass rate decreasing? 3. What are possible solutions to resolve any identified root causes and would you recommend any changes to the internal control system? Please write an audit report, outlining the root causes and suggested solutions. Relevant files: • Reports of all Document and Facial Similarity checks • Attributes explaining the results of each check performed The candidate is free to use any method including Excel or any scripting language to parse and analyse the data. Please show all your work and assumptions Skills: Excel Macros, Data Mining See more: bank statement excel data xls, case study examples telecomunication data, case study data center, financial statement case study excel, firms face turbulence uncertaintydiscuss role strategic planningresponse outline perspective strategy support answer case study , bank loan case study oracle plsql, financial data analysis case study, national bank case study workforce planning, furniture bank case study, regression case study using excel, excel data analyse, answer law case study assignment, case study report excel, how to answer a case study assignment, manipulating excel data in r, example of how to answer a case study, bank of america big data case study, vijaya bank case study, grameen bank case study, case study on big data analytics pdf