Version 1.0
Prepared by: @Lorraine Sebata
Approved by: @Davinia Munroe @Marcia Wilkinson
Reviewed date: 2025-09-12
Next review date: 2026-09-11
This procedure outlines guidelines for aggregating data on outstanding loan balances, analyzing customer counts across various segments, and determining the overall exposure and performance of the loan portfolio. It supports effective monitoring of portfolio health and helps in strategizing future lending activities based on reliable data insights.
The Accountant prepares this document and provides details to the Communication Officer for each month following the preparation of monthly financials. It provides for the systematic gathering, analysis, and interpretation of data essential for monitoring the health of the loan portfolio. It allows for assessing trends in loan balances, identifying changes in customer behavior, and evaluating the overall performance and risk profile of the portfolio, enabling proactive risk management and strategic planning for future lending activities.
Accountant: Responsible for downloading data, calculating the loan book and number of customers, analyzing portfolio trends, and preparing the report for submission.
Communications Officer: Receives the monthly report and utilizes the data for internal and external communications and reporting.
Management: Utilizes the reported data for strategic decision-making, risk management, and performance evaluation.
5.1.1 Log into the Loan Software (Turnkey)
5.1.2 From the main menu, navigate to 'Monitoring & Reporting'
5.1.3 Select the 'Aging Trial Balance' option
5.1.4 Enter the reporting date in the field labeled 'Balance as of, dd/mm/yyyy' and update
5.1.5 Once the data is generated, export the report to Excel by selecting the '.xlsx' format
5.1.6 Open the downloaded spreadsheet and ensure all data fields are correctly populated
5.2.1 In the 'Aging Trial Balance' spreadsheet:
Apply the SUM formula to total the amount of outstanding loans to determine the Loan Book
Identify the number of unique customers by counting the distinct customer records in the spreadsheet
5.2.2 Cross-check calculations by filtering data by customer ID to ensure no duplicates or missing entries
5.2.3 Verify that all figures align with previous reports and investigate any significant variances
5.3.1 Compare the current Loan Book amount and Customer count against prior months
5.3.2 Identify any significant changes in loan balances and customer numbers
5.3.3 Flag any anomalies or unusual trends for further investigation
5.4.1 Summarize findings, including key insights on loan book movement and customer behavior
5.4.2 Prepare a brief commentary on observed trends and any potential risks
5.4.3 Draft an email to the Communications Officer including:
Loan Book amount for the reported period
Total number of customers
Any notable insights or required actions
5.4.4 Attach the updated spreadsheet for reference
5.5.1 Review all figures and ensure accuracy before submission
5.5.2 Maintain a record of each monthly report for future reference and audit purposes
5.5.3 Schedule follow-ups for any flagged concerns requiring management attention
Aging Trial Balance Report (from LMS)
Monthly Loan Book & Customer Count Report (Email/Summary document)
Internal Communication Records
All Accountants involved in calculating the loan book and customer count will receive mandatory training on this SOP and related policies annually or upon significant revisions.
This SOP will be reviewed annually by the Accounting Department and relevant stakeholders to ensure its continued effectiveness, compliance, and alignment with business objectives.
By following these steps, the loan book and customer count reporting process remains accurate, efficient, and valuable for strategic decision-making and risk management.
Loom