+1(978)310-4246 credencewriters@gmail.com
  

Need help completing this project.Acct 307 Excel Project
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. 

0. Apply the filter to row 2, so you can perform filter at any time
0. Leave column H blank
0. Name the cell I2 as “Match”.
0. Add the 5 key attributes for CAP under column J through N. You may not add/remove column/row.
0. Repeat step 6.a and type “CAP” instead of SQ1”. Highlight the cell with blue.

0. Leave the column O blank
0. Under column I, 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 of Recon tab (column K). In other words, populate a formula under column K to find matching loan # in CAP. Then, wrAsset_System_Mnemonic As_Of_Date Loan_Number_Display Underwriting_Section Risk_Rating Purchased_Credit_Impaired Impairment_Status Net_Available_Credit Recorded_Investment Principal Principal_Participation Days_Delinquent Non_Accrual_Status Non_Accrual_Date TDR_Status TDR_Date Facility_Type Maturity_Date Call_Report_Code Lien_Position Rate_Index
SQ1 12/31/2017 SQ1-00009886 2-V.a 3 NonPCI N – – ##0 – 0 N NULL No NULL Revolver 5/27/2019 9b2 Unsecured V
SQ1 12/31/2017 SQ1-00009121 2-V.a 3 NonPCI N – 1,300,000.00 ##1300000 – 0 N NULL No NULL Revolver 8/18/2019 9b2 Secured V
SQ1 12/31/2017 SQ1-00002253 2-V.c 3 NonPCI N – 15,087,333.34 ##15087333.34 – 0 N NULL No NULL Revolver 3/12/2020 4a Secured V
SQ1 12/31/2017 SQ1-00003412 2-V.a 3 NonPCI N – – ##0 – 0 N NULL No NULL Revolver 1/21/2020 9b2 Secured V
SQ1 12/31/2017 SQ1-00008871 2-V.d 3 NonPCI N – – ##0 – 0 N NULL No NULL Revolver 11/14/2020 4a Secured V
SQ1 12/31/2017 SQ1-00007969 2-V.a 3 NonPCI N – – ##0 – 0 N NULL No NULL Revolver 5/27/2019 9b2 Unsecured V
SQ1 12/31/2017 SQ1-00004180 2-V.a 3 NonPCI N 11,118.38 – ##0 – 0 N NULL No NULL LC 4/22/2019 9b2 Secured F
SQ1 12/31/2017 SQ1-00003364 2-V.a 3 NonPCI N – – ##0 – 0 N NULL No NULL Term 6/27/2019 9b2 Secured V
SQ1 12/31/2017 SQ1-00002864 2-V.a 3 NonPCI N – – ##0 – 0 N NULL No NULL Revolver 4/22/2019 9b2 Secured V
SQ1 12/31/2017 SQ1-00007847 3-D 3 NonPCI N – 98,739.78 ##98739.78 – 0 N NULL No NULL Term 11/1/2039 1d Secured F
SQ1 12/31/2017 SQ1-00005358 2-V.a 3 NonPCI N – – ##0 – 0 N NULL No NULL Revolver 5/27/2019 9b2 Secured V
SQ1 12/31/2017 SQ1-00006412 2-V.a 2 NonPCI N 374,314.67 – ##0 – 0 N NULL No NULL LC 12/31/2019 9b2 Secured F
SQ1 12/31/2017 SQ1-00004720 2-V.a 4 NonPCI N – 2,200,000.00 ##2200000 – 1 N NULL No NULL Term 3/28/2019 9b2 Secured V
SQ1 12/31/2017 SQ1-00005421 2-V.d 4 NonPCI N – 4,000,000.00 ##4000000 – 0 N NULL No NULL Revolver 8/31/2019 4a Secured V
SQ1 12/31/2017 SQ1-00001546 2-V.a 3 NonPCI N – – ##0 – 0 N NULL No NULL Term 7/28/2019 9b2 Secured V
SQ1 12/31/2017 SQ1-00006318 2-V.c 4 NonPCI N – 450,000.00 ##450000 – 0 N NULL No NULL Revolver 9/10/2019 4a Secured V
SQ1 12/31/2017 SQ1-00008843 2-V.d 4 NonPCI N – 6,350,000.00 ##6350000 – 0 N NULL No NULL Revolver 2/13/2020 4a Secured V
SQ1 12/31/2017 SQ1-00008490 2-V.c 4 NonPCI N – 15,000,000.00 ##15000000 – 0 N NULL No NULL Revolver 7/28/2020 4a Secured V
SQ1 12/31/2017 SQ1-00003929 2-V.c 3 NonPCI N – – ##0 – 0 N NULL No NULL Revolver 5/24/2019 4a Secured V
SQ1 12/31/2017 SQ1-00003723 3-D 3 NonPCI N – 88,653.57 ##88653.57 – 0 N NULL No NULL Term 2/17/2029 1d Secured F
SQ1 12/31/2017 SQ1-00009811 2-V.a 4 NonPCI N – 469,440.00 ##469440 – 0 N NULL No NULL Revolver 4/9/2019 9b2 Secured V
SQ1 12/31/2017 SQ1-0000703SQ1

EOD_DATE Loan_Number NET_AVAILABLE_CREDIT PRINCIPAL PRINCIPAL_PARTICIPATION NON_ACCRUAL_PAID_INTEREST CUMULATIVE_CHARGE_OFFS DEFERRED_ORIGINATION_FEES MATURITY_DATE TDR_STATUS TDR_DATE NON_ACCRUAL_STATUS NON_ACCRUAL_DATE RISK_RATING DAYS_DELINQUENT RATE_INDEX COUPON_INTEREST_RATE OBLIGOR_NUMBER FACILITY_TYPE NAICS_CODE Group ADDRESS2 ADDRESS3 CITY STATE ZIPCODE LOAN_ORIG_AMT LAST_RENEWAL_DATE LAST_EXTNSN_DATE NO_OF_RENEWALS LOAN_PURPOSE INTEREST_RATE_INDEX INTEREST_RATE_SPREAD BORRWR_RATING_DATE SHARED_NATIONAL_CREDIT GUARANTOR_NAME LOAN_TYPE COLLATERAL_DESCRIPTION PAYMENT_FREQUENCY INT_RATE_RESET_INTRVL LAST_PYMNT_DATE STAGE INDUSTRY_GROUP PCI_PURCHASE_DISCOUNT NON_PCI_PURCHASE_PREM_OR_DISCT PCI_ALLL_RESERVE IMPAIRMENT_STATUS IMPAIRMENT_DATE PURCHASED_CREDIT_IMPAIRED CALL_REPORT_CODE UNDERWRITING_SECTION FOREIGN_LOAN UG CRC LOCK LOCK_ID AS_OF_DATE_ID RECORDED_INVESTMENT

12/31/17 SQ1-00009886 0 0 0 0 0 NULL Monday, May 27, 2019 N NULL N NULL 30 NULL Y 5.5 F1641 Y 523910 Equity Funds Group NULL NULL NY NY 10022 10/3/13 6/5/18 12/8/06 7 WORKING CAPITAL PRIME RATE 12/29/1899 5/17/18 N NULL SECURED LOANS NULL MONTHLY Daily 11/17/08 EQUITY FUNDS GROUP EQUITY FUNDS GROUP 0 0 0 N NULL N 9B2 2-V.A N 2-V.A NULL 0 NULL NULL – 0

12/31/17 SQ1-00009121 0 1300000 NULL 0 0 NULL Sunday, August 18, 2019 N NULL N NULL 30 NULL Y 5.5 F2024 Y 523910 Equity Funds Group NULL NULL BOSTON MA 2210 2/19/66 8/20/18 8/28/07 7 WORKING CAPITAL PRIME RATE 12/31/99 7/30/18 N NULL SECURED LOANS CAPITAL CALL RIGHTS MONTHLY Daily 3/1/19 EQUITY FUNDS GROUP EQUITY FUNDS GROUP 0 0 0 N NULL N 9B2 2-V.A N 2-V.A NULL 0 NULL NULL 1,300,000.00

12/31/17 SQ1-00002253 0 15087333.34 NULL 0 0 NULL Thursday, March 12, 2020 N NULL N NULL 30 NULL Y 5.5 15308 Y 562910 Portfolio NULL NULL DURHAM NC 27712 2/27/95 2/26/19 10/23/08 7 WORKING CAPITAL PRIME RATE 12/31/99 2/20/19 N NULL SECURED LOANS BLANKET LIEN NEGATIVE PLEDGE MONTHLY Daily 2/28/19 EXPANSION STAGE INDUSTRIAL – ENERGY 0 0 0 N NULL N 4A 2-V.C N 2-V.C NULL 0 NULL NULL 15,087,333.34

12/31/17 SQ1-00003412 0 0 0 0 0 NULL Tuesday, January 21, 2020 N NULL N NULL 30 NULL Y 5.5 F1709 Y 523910 Equity Funds Group NULL NULL WOODSIDE CA 94062 11/26/26 1/28/19 11/20/08 9 WORKING CAPITAL PRIME RATE 12/31/99 1/18/19 N NULL SECURED LOANS BLANKET LIEN W/ CAPITAL CALL RIGHTS MONTHLY Daily 2/24/14 EQUITY FUNDS GROUP EQUITY FUNDS GROUP 0 0 0 N NULL N 9B2 2-V.A N 2-V.A NULL 0 NULL NULL – 0

12/31/17 SQ1-00008871 0 0 NULL 0 0 NULL Saturday, November 14, 2020 N NULL N NULL 30 NULL Y 5.5 10935 Y 541219 Portfolio 3RD FLOOR NULL FAIRFIELD CT 6824 6/5/53 11/15/18 1/7/09 8 WORKING CAPITAL PRIME RATE 12/31/99 3/20/19 N NULL SECURED LOANS BLANKET LIEN W/NEGATIVE IP MONTHLY Daily 5/11/15 LATER STAGE BUSINESS PRODUCTS AND SERVICES 0 0 0 N NULL N 4A 2-V.D N 2-V.D NULL 0 NULL NULL – 0

12/31/17 SQ1-00007969 0 0 0 0 0 NULL Monday, May 27, 2019 N NULL N NULL 30 NULL Y 5.5 F1641 Y 523910 Equity Funds Group NULL NULL NY NY 10022 8/16/51 6/5/18 3/12/09 9 WORKI

  
error: Content is protected !!