Professional Memo



5320 MemorandumTo:4320 studentsFrom:Dr. Gary G. BergDate: TIME \@ "MMMM d, yyyy" September 9, 2020Re:ACCT 4320 assignments 4 (Lending Club) Assignment 4:This is a pivot table assignment using Lending Club Rejected Loan Requests for 2013 and 2014.Download the file Access database file from class web siteThis file has over two million recordsThis assignment will require scrubbing the data in Access, importing data into Excel, and doing pivot table analysis in Excel;There are several short Zoom presentations to provide guidance for this assignment. Clean the data: Open the Access fileYou will need to clean the data per the instructions in problems 5b, 6a, 7a.Sorting of the data before scrubbing is not necessaryCreate three tables from the original tableThe original data base file has over two million records. Before the data can be imported into Excel, it will need to bebroken out into three tables due to the 1,048,576-record limitation of ExcelCreate Three tables using the Make table Action Query20132014 Jan June2014 July -DecImport the three tables from Access into ExcelCreate a field for a risk score bucket and assign a bucket classification for each record using the classification below.300Bad600Poor650Fair700Good750Very Good800ExcellentThe best way to complete this would be the use of the use of the Vlookup function (easiest option)Create a field far a debt-to income bucket and assign a DTI bucket classification for each record based on the classification below. DTI’s <10% are low, DTI’s >= 10% and < 20% are medium, and >= 20% is high.0%Low10%Medium20%HighUse power query to combine the data into a data modelCreate the following pivot tables to compare 2013 to 2014 using power queryNumber of rejected loans in each risk bucket and percentage breakdownNumber of rejected loans in each DTI bucket and percentage breakdownNumber of rejected loans based on employment length and percentage breakdownNumber of rejected loans by month and percentage breakdownNumber of rejected loans by state and percentage breakdownUse the paste special feature to copy and paste your pivot tables into a word document as an imageWord for windows formats areEnhanced metafile(preferred) orBitmapI do not know the format that word for MAC would useRight up a short report for management addressing the following (this should at the beginning of your word document with pivot tables to follow. Are there any issues with the assumptions made for deleting records in the data scrubbing process? Explain.What factor seems to be the main reason for loan rejections and why would this be the case?Would you recommend any change to your pivot analysis? If so, what additional analysis would you do with the data provided. Explain. Submit the completed file via the D2L dropbox.Files submitted to the wrong dropbox will receive a grade of 0On rare occasions, a file may be corrupted in the upload process to D2L. It is your responsibility to make sure that you upload the correct file and to make sure the file uploaded correctly. . Failure to do so will result in a grade of zeroThis is not a group assignment. Students should work independently. Copying/plagiarism will result in a grade of 0 for all parties involved.Due on 09-15-2020, 11:30 p.m. Remember, file must be virus free. (Any assignment received containing a Virus receives a grade of 0)Gary G. Berg, PhD., CPAEast Tennessee State UniversityCollege of Business & TechnologyDepartment of AccountancyBox 70710Johnson City, TN 37614423-439-5336423-439-5274 (fax)bergg@mail.etsu.edu ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download