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 assuredthat 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.Apply the filter to row 2, so you can perform filter at any time

d.Leave column H blank

e.Name the cell I2 as "Match".

f.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.

h.Leave the column O blank

i.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, write a formula under column I to compare columns B & K.

If a loan number is a match, show "0", otherwise show "1". You may not add/remove column/row. Move to later steps

i.Enter the 5 key attributes names on cell P2 through T2. You may not add/remove column/row.

j.Add 2 columns in U and V and name them Total and Overrides respectively

k.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.

n.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.You can use referencing for column J. 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.

o.Write formula(s) under column U that adds the number of discrepancies calculated from columns P to T.

p.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"

q.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.

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 SQ1 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. 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.

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 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.

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 Canvas

Additional Guidelines:

1. This is a group project.Members of a team will receive the same score.
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.
5. Submit your Excel file only. Name your Excel file as the following:

LastName_FirstName_Group#_Class#

i.e.: Do_John_7_24416

The class number is a 5 digit number, which is listed on your syllabus as well as Canvas.

please help me with step 6.M