Bank Audit Using Ms-Excel: A Comprehensive Guide by CA Saran Kumar

Explore the roadmap of a bank audit session in Ernakulam branch led by CA Saran Kumar, focusing on utilizing MS Excel for auditing tasks. Discover new LFAR updates, core banking solutions, NPA identification strategies, mindset of auditors, and more to enhance your audit process effectively.

• Bank Audit
• Ernakulam Branch
• CA Saran Kumar
• MS Excel
• Auditing

zoya Follow

Uploaded on Aug 05, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Ernakulam Branch Bank Audit Using Ms-Excel by CA Saran Kumar U saran@chaireturn.com +91 702-234-5678 Date: 28h March 2021

2. Roadmap of the Session Getting Ready for the Bank Audit What s New in New LFAR Introduction to Core Banking Solution Different CBS and Menu List Unstructured Data to Structured Data Inferences out of stratification NPA identification from the Structured Data Ledger Account Analysis Sampling and Reporting Conclusion CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 2

3. Mindset of the Auditors Compile Material Next Year Attend Seminars Circle Sign Report Step into Bank CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 3

4. 80:20 Approach CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 4

5. CBS Environment Application Server User/staff enters information into screens Database Server Structured Data Format Text to Column using Delimited (mostly Comma) Reporting Server Unstructured Data Format Text to Column using Fixed Width / Delimited CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 5

6. CBS Major CBS B@nks24 Finacle Flexicube CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 6

7. Large Advances Calculation A) Balance as on 31.03.2021 Funded B) Balance as on 31.03.2021 Non-Funded C) Total Advances Funded (+) Non-Funded Lower of Below is Large Advance i) 10% of C ii) Rs. 10 Crore Refer Working File 1 CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 7

8. Quick Mortality in Accounts A) Sanctioned Date B) NPA Date C) B-A If the C is <365 then all those accounts to be mentioned in LFAR 5.b.ii CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 8

9. Interest Rate vs Accounts Use Pivot Table for Interest Get Maximum Interest for each Product Type or GL Get Minimum Interest for each Product Type of GL If the difference, is high then examine further In case of adverse, please report in LFAR 5.b.iii CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 9

10. Sanctioning Authority Insert a Column in the Loan Balance File Besides Sanctioned Amount User VLOOKUP() from the Master Data of Sanctioning Authority Vs Limit If the Difference is Higher, then check whether properly reported to Higher Authority Report all the cases in LFAR 5.c.i CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 10

11. Other Stratification Customer ID vs Account ID Stratification of Advance Portfolio Stratification of Year-wise Advances Stratification of Irregularity Stratification of Arrear Condition CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 11

12. Finacle Commands CCODCHK - Credit and Interest Debits Check M27R Report of Accounts Sanctioned, Renewed and enhanced during the period of time ADTRPT Various Audit Reports Available ADTRPT-28 Audit Report to generate "range wise interest rate account list to identify accounts where interest rate is not in accordance with Bank s interest pattern. HLARSH EMI Calc Loan Repayment Schedule CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 12

13. Unstructured Data to Structured Data Text to Column Filter Remove junk data Keeping data ready for the analysis CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com 13

14. Thanks for your patient hearing!!! CA Saran Kumar U +91 702-234-5678 saran@chaireturn.com