Information Systems homework help
Information Systems homework help. 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 Square 1 (SQ1) System to CAP. It is previously confirmed that SQ1 data is 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:
- Date
- Loan number
- Principal
- Maturity date
- TDR Status
Follow the below steps to complete your project:
- Preparation: (1 point) Create 3 new tabs/worksheets in the Excel file. Name them “Recon”, “Difference”, and “CAP”.
- Reorder your tabs in a way that follow this order: SQ1 > Recon > Difference > CAP > OverRides > Notes > Rubric.
- 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.
- CAP: (1 point) Insert the CAP data using a certain Excel function from the Note file to the CAP tab on the Excel file.
- 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. Resize screenshots to reflect only appropriate windows.
- Recon: (8 points)
- Type “SQ1”in cell A1 and merge the cell for A1 through G1 and highlight it in Green color
- Enter 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.
- 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. Freeze pane and filter should be applied to columns and all tabs in the Excel file.
- Apply the filter to row 2, so you can perform filter at any time
- Leave column H blank
- 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.
- Add the 5 key attributes for CAP under column J through N. You may not add/remove column/row.
- Repeat step 6.a and type “CAP” instead of SQ1”. Highlight the cell with blue.
- Leave the column O blank
- Enter the 5 key attributes names on cell P2 through T2. You may not add/remove column/row.
- Add 2 columns in U and V and name them Total and Overrides respectively
- Repeat step 6.a and type “Diff” instead of “SQ1”. Highlight the cell with red.
- 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.
- 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.
- 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.
- Write formula(s) under column U that adds the number of discrepancies calculated from columns P to T.
- Write formula(s) under column V (Overrides) to look up Codes (Column E of Overrides) and Comments (Column I of Overrides) related to each loan in Overrides worksheet. If applicable, your formula in column V should show comments in the Overrides worksheet column E (Codes) followed by a dash “-” in-between and then 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”
- Difference: (5 points)
- Type “Loans not in CAP” in cell A1, and “Loans with attribute variances” in cell A20. Name D2 as “SQ1” and E2 as “CAP”
- Insert formula(s) in D3 and E3 and below to represents all the loan numbers that do not exist in CAP. If a loan is in SQ1, then cell D3 should show the loan number using referencing to Recon tab. Cell E4 should show 0 using formula(s), excluding referencing.
- 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. Cell E22 represents all attribute names with variances. Your end result for the above 2 steps should look like the following:
- 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.
- 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. 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. Start your opinion by saying whether you believe the conversion was complete and accurate.
- Generate a 3D Pie Bar for the table referenced in the step (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 outside of the pie slices. Ensure the pie title reads “Quantity Check Figure”. Highlight the SQ1 slice in green, and CAP in blue.
- Enter your name and today’s date into cell D35 & D36.
- Review your answers and formula for completeness, accuracy, and appropriateness.
- Name your Excel file as the following. The class number is listed on your syllabus as well as Titanium.
LastName_FirstName_Class#
- Submit only Excel file on Titanium. Do not submit Instructions or Note files please.
Additional Guidelines:
- This is an Individual project.
- There shall not be any type of visible errors in the entire worksheets. Errors could be showing up as #N/A, #REF, #Error, etc.
- All the formula at the first available row of data should be the same as the following rows’ formulas.
- The Excel Project is due as specified on the syllabus. Late submission or emailed projects will not be accepted/graded.
- Do not change/add/delete anything in Rubric tab.
- Start working on your project early. As discussed at the beginning of semester, office hour is not available from 4/6 through 4/14. However, I will be responding to my emails. You may expect delay in response time during that period.