Excel Project
Acct 307 Excel Project – Fall 2019 The attached files represent data from a system conversion that occurred recently in the Sigma Bank. The Sigma Bank recently converted one of its systems from System Square 1 (SQ1) to CAP. You are assured that SQ1 data is complete and accurate. The data extraction in SQ1 is shown in Excel, while the data from CAP is shown in the note file. You, as an accountant in the Sigma Bank, are planning to ensure the completeness and accuracy of the recent system conversion to ensure the data in CAP mirrors SQ1. To document your review, you are going to create a reconciliation file in Excel. In a nutshell, the reconciliation file is designed to demonstrate the completeness and accuracy of data in both systems. In this case, you are demonstrating the completeness and accuracy of the loans as well as their attributes through a reconciliation file. There are multiple loans in the SQ1 systems, which were converted to the new system -CAP. Each loan has various attributes, such as loan number, principal, maturity date, etc. Your goal is to verify the conversion completeness and accuracy of the loans, including certain key attributes. To do so, you are asked to verify the following 5 attributes: 1. Date 2. Loan number 3. Principal 4. Maturity date 5. TDR Status Follow the below steps to complete your project: 1. Preparation: (1 point) Create 3 new tabs/worksheets in the Excel file. Name them “Recon”, “Difference”, and “CAP”. 2. Reorder your tabs in a way that follow this order: SQ1 > Recon > Difference > CAP > OverRides > Notes 3. Use your professional judgment to ensure formatting all cells across worksheets is consistent and appropriate. For example, all cells under a specific column should be Accounting formatted with 2 decimals. Review cells and adjust any abnormal formatting accordingly. 4. CAP: (1 point) Insert the CAP data using a certain Excel function from the Note file to the CAP tab on the Excel file. 5. Capture the screenshots of all of your steps to insert the Note file to Excel. Save those screenshots at the designated area on Notes worksheet. 6. Recon: (8 points) a. Type “SQ1”in cell A1 and merge the cell for A1 through G1 and highlight it in Green color b. Find the above mentioned 5 key attributes as well as Loan_Purpose, Collateral Description onto row 2. Your first few columns and rows should look like the following. c. 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. g. Apply the filter to row 2, so you can perform filter at any time g. Leave column H blank g. Name the cell I2 as “Match”. Write a formula under column I that it verifies if the loan number under SQ1 category (column B) is equal to values under CAP category (column K). If a loan number is a match, show “0”, otherwise show “1”. You may not add/remove column/row. g. Add the 5 key attributes for CAP under column J through N. You may not add/remove column/row. g. Repeat step 6.a and type “CAP” instead of SQ1”. Highlight the cell with blue. i. Leave the column O blank g. Enter the 5 key attributes names on cell P2 through T2. You may not add/remove column/row. g. Add 2 columns in U and V and name them Total and Overrides respectively g. Repeat step 6.a and type “Diff” instead of “SQ1”. Highlight the cell with red. m. Use referencing (='SQ1'!B2), instead of hardcoding figures to populate values in columns A and B for SQ1 data. Use a formula (excluding referencing) to pull the appropriate value for columns C through G. You may not add/remove column/row. g. Use various formula(s), excluding referencing, to look up respective values for CAP columns J through N. You should not use referencing for CAP figures. Instead, use formula(s) such as, If, Iferror, and vlookup to lookup values in the CAP worksheet. Preferably, use formulas that we discussed in the class. You may add columns/rows. Capture the screenshots of all of your steps for column L (Principal). Save those screenshots at the designated area on Notes worksheet. g. Utilize certain formula(s) under column P through T as you see fit to complete the comparison process between 5 key attributes between SQ1 category (green) and CAP category (blue). In the event any of these attributes’ values were the same in SQ1 & CAP, show 0, otherwise show 1. Specifically, use nested formula for column T. g. Write formula(s) under column U that adds the number of discrepancies calculated from columns P to T. g. 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” 7. Difference: (5 points) a. Type “Loans not in CAP” in cell A1, and “Loans with attribute variances” in cell A20. Name D2 as “SQ1” and E2 as “CAP” b. Insert formula(s) in D3 and E3 and below to represents all the loan numbers that do not exist in CAP. c. 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. d. 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. e. Populate a table 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. Type “SQ1” and “CAP” in D28 and D29. Type a formula in E28 and E29 to count the number of loans in each dataset. Calculate the difference in E30. 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. f. Generate a 3D Pie Bar for the table referenced in the step above (e) on cell G20 that shows and the respective pie slice for SQ1 & CAP. Each pie must show category name, value, and percentage. Those attributes must be placed at the outside end of the pie slices. Ensure the pie title reads “Quantity Check Figure”. Highlight the SQ1 slice in green, and CAP in blue. g. Enter your name and today’s date into cell D35 & D36. h. Review your answers and formula for completeness, accuracy, and appropriateness. i. Submit your Excel file on Titanium Additional Guidelines: 1. This is an Individual project. 2. There shall not be any type of visible errors in the entire worksheets. Errors could be showing up as #N/A, #REF, #Error, etc. 3. All the formula at the first available row should be the same as the following rows. 4. The Excel Project is due as specified on the syllabus. Late submission or emailed projects will not be accepted. 3 Asset_System_MnemonicAs_Of_DateLoan_Number_DisplayUnderwriting_SectionRisk_RatingPurchased_Credit_ImpairedImpairment_Status Net_Available_Credit Recorded_Investment Principal Principal_Participation Days_DelinquentNon_Accrual_StatusNon_Accrual_DateTDR_StatusTDR_DateFacility_TypeMaturity_DateCall_Report_CodeLien_PositionRate_Index SQ112/31/2017SQ1-000098862-V.a3NonPCIN - - ##0 - 0NNULLNoNULLRevolver5/27/20199b2UnsecuredV SQ112/31/2017SQ1-000091212-V.a3NonPCIN - 1,300,000.00 ##1300000 - 0NNULLNoNULLRevolver8/18/20199b2SecuredV SQ112/31/2017SQ1-000022532-V.c3NonPCIN - 15,087,333.34 ##15087333.34 - 0NNULLNoNULLRevolver3/12/20204aSecuredV SQ112/31/2017SQ1-000034122-V.a3NonPCIN - - ##0 - 0NNULLNoNULLRevolver1/21/20209b2SecuredV SQ112/31/2017SQ1-000088712-V.d3NonPCIN - - ##0 - 0NNULLNoNULLRevolver11/14/20204aSecuredV SQ112/31/2017SQ1-000079692-V.a3NonPCIN - - ##0 - 0NNULLNoNULLRevolver5/27/20199b2UnsecuredV SQ112/31/2017SQ1-000041802-V.a3NonPCIN 11,118.38 - ##0 - 0NNULLNoNULLLC4/22/20199b2SecuredF SQ112/31/2017SQ1-000033642-V.a3NonPCIN - - ##0 - 0NNULLNoNULLTerm6/27/20199b2SecuredV SQ112/31/2017SQ1-000028642-V.a3NonPCIN - - ##0 - 0NNULLNoNULLRevolver4/22/20199b2SecuredV SQ112/31/2017SQ1-000078473-D3NonPCIN - 98,739.78 ##98739.78 - 0NNULLNoNULLTerm11/1/20391dSecuredF SQ112/31/2017SQ1-000053582-V.a3NonPCIN - - ##0 - 0NNULLNoNULLRevolver5/27/20199b2SecuredV SQ112/31/2017SQ1-000064122-V.a2NonPCIN 374,314.67 - ##0 - 0NNULLNoNULLLC12/31/20199b2SecuredF SQ112/31/2017SQ1-000047202-V.a4NonPCIN - 2,200,000.00 ##2200000 - 1NNULLNoNULLTerm3/28/20199b2SecuredV SQ112/31/2017SQ1-000054212-V.d4NonPCIN - 4,000,000.00 ##4000000 - 0NNULLNoNULLRevolver8/31/20194aSecuredV SQ112/31/2017SQ1-000015462-V.a3NonPCIN - - ##0 - 0NNULLNoNULLTerm7/28/20199b2SecuredV SQ112/31/2017SQ1-000063182-V.c4NonPCIN - 450,000.00 ##450000 - 0NNULLNoNULLRevolver9/10/20194aSecuredV SQ112/31/2017SQ1-000088432-V.d4NonPCIN - 6,350,000.00 ##6350000 - 0NNULLNoNULLRevolver2/13/20204aSecuredV SQ112/31/2017SQ1-000084902-V.c4NonPCIN - 15,000,000.00 ##15000000 - 0NNULLNoNULLRevolver7/28/20204aSecuredV SQ112/31/2017SQ1-000039292-V.c3NonPCIN - - ##0 - 0NNULLNoNULLRevolver5/24/20194aSecuredV SQ112/31/2017SQ1-000037233-D3NonPCIN - 88,653.57 ##88653.57 - 0NNULLNoNULLTerm2/17/20291dSecuredF SQ112/31/2017SQ1-000098112-V.a4NonPCIN - 469,440.00 ##469440 - 0NNULLNoNULLRevolver4/9/20199b2SecuredV SQ112/31/2017SQ1-000070392-V.a4NonPCIN - 180,560.00 ##180560 - 0NNULLNoNULLRevolver4/9/20199b2SecuredV SQ112/31/2017SQ1-000058062-V.c1NonPCIN 43,968.75 - ##0 - 0NNULLNoNULLLC9/30/20194aSecuredF SQ112/31/2017SQ1-000071092-V.c1NonPCIN 26,398.25 - ##0 - 0NNULLNoNULLLC4/30/20194aSecuredF SQ112/31/2017SQ1-000015522-V.d5NonPCIN - 3,124,457.00 ##3124457 - 0NNULLNoNULLRevolver4/1/20194aSecuredV SQ112/31/2017SQ1-000030452-V.a3NonPCIN - - ##0 - 0NNULLNoNULLRevolver10/19/20199b2SecuredV SQ112/31/2017SQ1-000041872-V.d4NonPCIN - 700,000.00 ##700000 - 0NNULLNoNULLRevolver7/12/20194aSecuredV SQ112/31/2017SQ1-000085372-V.a3NonPCIN - - ##0 - 0NNULLNoNULLTerm6/12/20199b2SecuredV SQ112/31/2017SQ1-000056013-D3NonPCIN - 89,395.25 ##89395.25 - 0NNULLNoNULLTerm7/20/20411dSecuredF SQ112/31/2017SQ1-000084452-V.b8NonPCIY - 50,518.28 ##1654411.76 - 0Y3/1/2017Yes7/17/2018Term12/31/20194aSecuredV SQ112/31/2017SQ1-000090082-V.a3NonPCIN - 750,000.00 ##750000 - 0NNULLNoNULLRevolver8/5/20199b2SecuredV SQ112/31/2017SQ1-000099312-V.a3NonPCIN - - ##0 - 0NNULLNoNULLRevolver5/27/20199b2SecuredV SQ112/31/2017SQ1-000042712-V.a2NonPCIN - - ##0 - 0NNULLNoNULLRevolver6/3/20199b2SecuredV SQ112/31/2017SQ1-000084292-V.a3NonPCIN - - ##0 - 0NNULLNoNULLRevolver7/28/20199b2SecuredV SQ112/31/2017SQ1-000063032-V.a3NonPCIN