Credit Administration

Advanced Excel Skills for Credit Administration and Monitoring

The programme will help participants use Excel to monitor credit facilities, identify exceptions, track outstanding documentation, and prepare accurate management reports.

3 days per group; 2 groups (6 delivery days total)Duration BeginnerLevel Practical exercises using a simulated loan portfolio, credit documentation register, and covenant monitoring records.Learning format 4Modules Bank employeesAcademy learning

About this course

The programme will help participants use Excel to monitor credit facilities, identify exceptions, track outstanding documentation, and prepare accurate management reports.

Target participants: Managers, Senior Officers, and Officers in Credit Administration and the CAD Monitoring Unit.

Proposed duration: Three days per group, delivered to two groups over six training days. Both groups will cover the same programme.

Delivery approach: Practical exercises using a simulated loan portfolio, credit documentation register, and covenant monitoring records.

Learning objectives

  • Clean and reconcile loan data from different reports.
  • Build reliable registers for facilities, documentation, collateral, and covenants.
  • Automate monitoring of repayment dates, document expiries, and review deadlines.
  • Identify overdue accounts, missing documentation, and breaches of approved terms.
  • Analyse portfolio movements and prepare interactive monitoring dashboards.
  • Apply spreadsheet controls that improve accuracy, confidentiality, and traceability.

Programme structure

1Day 1: Preparing reliable credit monitoring data
Day 1: Preparing reliable credit monitoring dataDesign a master credit facility register with consistent customer and facility identifiers.
Structuring and cleaning dataClean a loan extract containing duplicate facilities, inconsistent branch names, missing dates, and incorrectly formatted balances.
Advanced formulas for monitoringCalculate facilities due for review, outstanding documentation by officer, and exposure by branch or product.
Matching and reconciling recordsReconcile a facility approval register against a loan extract and produce an exception list.
Input controls and exception highlightingBuild a controlled register that flags incomplete records and approaching deadlines.
2Day 2: Automating CAD monitoring schedules
Day 2: Automating CAD monitoring schedulesCreate a documentation tracker showing missing documents, overdue conditions, and follow-up ownership.
Facility and repayment monitoringBuild an arrears monitoring schedule using scheduled repayments and actual payments.
Arrears ageing and escalationProduce an ageing report and a priority follow-up list without double-counting facilities.
Collateral and security monitoringDevelop a collateral tracker with expiry alerts and facilities requiring action.
Covenant and approval-condition monitoringBuild a covenant tracker that separates compliant, breached, and awaiting-information cases.
Combining recurring reports with Power QueryCombine monthly loan extracts and refresh a monitoring report without repeating manual cleaning.
3Day 3: Analysis, dashboards, and reporting controls
Day 3: Analysis, dashboards, and reporting controlsPrepare a CAD monitoring summary with drill-down to supporting records.
Monitoring dashboardsCreate an interactive dashboard for the CAD Monitoring Unit.
Management reportingProduce a management report with an action register showing owner, deadline, and status.
Spreadsheet assurance and protectionReview a workbook containing deliberate errors and implement practical controls.
Integrated practical assessmentComplete and present a working CAD monitoring workbook.

Prerequisites / requirements

["

The programme will help participants use Excel to monitor credit facilities, identify exceptions, track outstanding documentation, and prepare accurate management reports.

Target participants: Managers, Senior Officers, and Officers in Credit Administration and the CAD Monitoring Unit.

Proposed duration: Three days per group, delivered to two groups over six training days. Both groups will cover the same programme.

Delivery approach: Practical exercises using a simulated loan portfolio, credit documentation register, and covenant monitoring records."]

Practical outputs

  • A practice dataset and step-by-step exercise guide.
  • A master credit facility register.
  • Credit documentation and approval-condition trackers.
  • Collateral, insurance, and covenant monitoring templates.
  • An arrears ageing and follow-up schedule.
  • A CAD monitoring dashboard and management reporting template.
  • A workbook quality-control and refresh checklist.

Expected outcomes

  • Clean and reconcile loan data from different reports.
  • Build reliable registers for facilities, documentation, collateral, and covenants.
  • Automate monitoring of repayment dates, document expiries, and review deadlines.
  • Identify overdue accounts, missing documentation, and breaches of approved terms.
  • Analyse portfolio movements and prepare interactive monitoring dashboards.
  • Apply spreadsheet controls that improve accuracy, confidentiality, and traceability.

Detailed programme information

Training content, practical exercises and delivery arrangements

Programme OverviewPurpose and target participants

The programme will help participants use Excel to monitor credit facilities, identify exceptions, track outstanding documentation, and prepare accurate management reports.

Target participants: Managers, Senior Officers, and Officers in Credit Administration and the CAD Monitoring Unit.

Proposed duration: Three days per group, delivered to two groups over six training days. Both groups will cover the same programme.

Learning OutcomesCapabilities participants will develop
1

Clean and reconcile loan data from different reports.

2

Build reliable registers for facilities, documentation, collateral, and covenants.

3

Automate monitoring of repayment dates, document expiries, and review deadlines.

4

Identify overdue accounts, missing documentation, and breaches of approved terms.

5

Analyse portfolio movements and prepare interactive monitoring dashboards.

6

Apply spreadsheet controls that improve accuracy, confidentiality, and traceability.

Detailed Training ProgrammeDaily modules, training content and practical application
Day 1: Preparing reliable credit monitoring data
1Day 1: Preparing reliable credit monitoring data
  • Understanding CAD monitoring requirements: translating facility approvals, disbursement conditions, repayment obligations, and reporting needs into a structured workbook. Distinguishing facility-level records from customer-level records.
Practical application

Design a master credit facility register with consistent customer and facility identifiers.

2Structuring and cleaning data
  • Excel Tables, consistent column headings, standard date and number formats, removing duplicates, identifying blanks, correcting text and numeric inconsistencies, and handling imported account numbers with leading zeros.
Practical application

Clean a loan extract containing duplicate facilities, inconsistent branch names, missing dates, and incorrectly formatted balances.

3Advanced formulas for monitoring
  • Relative and absolute references; IF, AND, OR, IFERROR; SUMIFS, COUNTIFS, and AVERAGEIFS; text functions for standardising identifiers; date functions for monitoring deadlines. Distinguishing missing data from genuine zero balances.
Practical application

Calculate facilities due for review, outstanding documentation by officer, and exposure by branch or product.

4Matching and reconciling records
  • Lookup techniques using XLOOKUP or INDEX/MATCH, identifying unmatched records, detecting duplicate lookup keys, and comparing approved limits with disbursement and outstanding balances.
Practical application

Reconcile a facility approval register against a loan extract and produce an exception list.

5Input controls and exception highlighting
  • Data validation, controlled status lists, formula-based conditional formatting, and checks for invalid dates, missing owners, and inconsistent facility information.
Practical application

Build a controlled register that flags incomplete records and approaching deadlines.

Day 2: Automating CAD monitoring schedules
6Day 2: Automating CAD monitoring schedules
  • Credit documentation monitoring: tracking required documents, receipt dates, verification status, conditions precedent, conditions subsequent, waivers, and responsible officers.
Practical application

Create a documentation tracker showing missing documents, overdue conditions, and follow-up ownership.

7Facility and repayment monitoring
  • Tracking approved limits, utilisation, outstanding balances, maturity dates, review dates, and repayment obligations. Calculating overdue amounts and days past due from payment-level data.
Practical application

Build an arrears monitoring schedule using scheduled repayments and actual payments.

8Arrears ageing and escalation
  • Creating configurable ageing bands, monitoring changes in arrears, distinguishing overdue instalments from total outstanding exposure, and setting escalation triggers based on the Bank’s approved rules.
Practical application

Produce an ageing report and a priority follow-up list without double-counting facilities.

9Collateral and security monitoring
  • Tracking valuations, insurance expiry dates, perfection status, security documentation, and valuation review deadlines. Calculating coverage using clearly defined exposure and eligible collateral values.
Practical application

Develop a collateral tracker with expiry alerts and facilities requiring action.

10Covenant and approval-condition monitoring
  • Recording covenant requirements, reporting frequencies, thresholds, actual results, compliance status, breaches, and remediation deadlines. Handling missing covenant information appropriately.
Practical application

Build a covenant tracker that separates compliant, breached, and awaiting-information cases.

11Combining recurring reports with Power Query
  • Importing files, setting data types, appending periodic extracts, merging registers, removing duplicates, and refreshing reports. Understanding the difference between a current portfolio snapshot and historical movements.
Practical application

Combine monthly loan extracts and refresh a monitoring report without repeating manual cleaning.

Day 3: Analysis, dashboards, and reporting controls
12Day 3: Analysis, dashboards, and reporting controls
  • Portfolio analysis with PivotTables: analysing exposure, arrears, documentation exceptions, and upcoming maturities by branch, product, officer, and customer segment. Using filters and slicers.
Practical application

Prepare a CAD monitoring summary with drill-down to supporting records.

13Monitoring dashboards
  • Selecting useful indicators, building charts, highlighting priority exceptions, and presenting reporting dates and data coverage clearly.
Practical application

Create an interactive dashboard for the CAD Monitoring Unit.

14Management reporting
  • Preparing concise reports on overdue reviews, missing documents, covenant breaches, expiring securities, and unresolved exceptions. Comparing reporting periods and explaining material movements.
Practical application

Produce a management report with an action register showing owner, deadline, and status.

15Spreadsheet assurance and protection
  • Formula auditing, reconciliation totals, input/output separation, protection of formula cells, controlled sharing, version management, and documenting refresh steps. Understanding that worksheet protection does not replace secure file access.
Practical application

Review a workbook containing deliberate errors and implement practical controls.

16Integrated practical assessment
  • Cleaning data, reconciling facilities, updating monitoring schedules, identifying exceptions, and presenting findings.
Practical application

Complete and present a working CAD monitoring workbook.

Training MethodologyDelivery approach described in the brief

Practical exercises using a simulated loan portfolio, credit documentation register, and covenant monitoring records.

Expected Learning OutcomesTraining outcomes as specified in the brief
Clean and reconcile loan data from different reports.
Build reliable registers for facilities, documentation, collateral, and covenants.
Automate monitoring of repayment dates, document expiries, and review deadlines.
Identify overdue accounts, missing documentation, and breaches of approved terms.
Analyse portfolio movements and prepare interactive monitoring dashboards.
Apply spreadsheet controls that improve accuracy, confidentiality, and traceability.
Core dashboard indicators

The practical dashboard will include:

Total outstanding exposure and number of active facilities.

Facilities approaching maturity or annual review.

Overdue instalment amounts and facilities in arrears.

Outstanding credit documentation and overdue approval conditions.

Expiring insurance policies and overdue collateral valuations.

Covenant breaches and information awaiting submission.

Exceptions by responsible officer, branch, and action deadline.

Definitions, ageing bands, and escalation thresholds will be aligned with I&M Bank Rwanda’s approved policies and reporting requirements.

Practical Case StudiesCase studies and exercises from the training brief
  • Case study 1 — Pre-disbursement control: Review a facility approval and documentation register to identify unmet conditions, missing evidence, and items requiring escalation.
  • Case study 2 — Monthly portfolio monitoring: Reconcile two reporting extracts, identify new and closed facilities, investigate unexplained balance changes, and prepare a portfolio movement summary.
  • Case study 3 — Early warning and follow-up: Combine arrears, covenant, documentation, and collateral information to prioritise facilities for review. Participants will distinguish verified exceptions from incomplete information.
  • Case study 4 — Management reporting: Present the most significant exceptions, their supporting evidence, responsible officers, and proposed follow-up actions.
Role-specific emphasis
Training Materials and DeliverablesParticipant deliverables described in the training brief

Training materials and participant deliverables

1A practice dataset and step-by-step exercise guide.
2A master credit facility register.
3Credit documentation and approval-condition trackers.
4Collateral, insurance, and covenant monitoring templates.
5An arrears ageing and follow-up schedule.
6A CAD monitoring dashboard and management reporting template.
7A workbook quality-control and refresh checklist.
PreparationPreparatory guidance in the source brief

Participants will receive:

Recommended preparation: Participants should bring laptops with Excel installed. Before delivery, the Academy training team and the CAD team should confirm the available Excel features, priority reporting needs, and any anonymised templates suitable for customisation. Live customer data should be replaced with anonymised or simulated records for training.

Participant level / Emphasis
  • Managers — Dashboard interpretation, exception prioritisation, escalation oversight, report approval, and spreadsheet governance.
  • Senior Officers — Reconciliation, quality assurance, monitoring design, investigation of exceptions, and consolidation of reports.
  • Officers — Accurate data capture, register maintenance, report refresh, documentation tracking, and follow-up updates.

Assessments

Assessment requirements are provided with your learning materials.

Group activities

Activities and collaboration opportunities are available to enrolled employees where applicable.

Resources

Course materials are available through your learning account once access is granted.

I&M Rwanda Academy certificate

Certificate eligibility is subject to the course's completion and assessment requirements.