INTRODUCTION

This practical one-day course focuses on using Microsoft Excel to calculate, analyse and evaluate Accounts Receivable and Credit Control information.

Participants will work with practical data and case studies to analyse credit risk, receivables performance, ageing, collection effectiveness and credit-control decisions.

The course is designed with minimal theory and maximum emphasis on practical calculations, Excel analysis and decision-making.

COURSE APPROACH

CALCULATE → ANALYSE → INTERPRET → DECIDE

Minimal theory | Practical Excel exercises | Realistic AR data | Case studies | Management decisions

WHO SHOULD ATTEND

• Accounts Receivable Personnel
• Credit Control Personnel
• Accounts & Finance Executives
• Credit Analysts
• Collection Personnel
• Finance Officers & Supervisors

PREREQUISITES & EXCEL REQUIREMENTS

• Basic knowledge of Microsoft Excel
• Microsoft Excel 2016 or later
• Participants should bring a laptop with Excel installed

COURSE OBJECTIVES

Participants will learn to:

• Calculate key AR and credit-control indicators
• Analyse customer credit and payment performance
• Prepare and interpret receivables ageing
• Evaluate collection effectiveness
• Develop a credit-scoring model and determine credit limits
• Evaluate credit-policy alternatives using Scenario Manager
• Analyse AR data using PivotTables
• Use Excel analysis to support management decisions

COURSE MODULES

MODULE 1: CREDIT ASSESSMENT & FINANCIAL ANALYSIS

Financial Statement Analysis

• Profitability Ratios
• Liquidity Ratios
• Efficiency Ratios
• Leverage Ratios
• Accounts Receivable Ratios
• Interpreting Key Ratios for Credit Assessment

Excel Activity: Financial Ratio Analysis

Calculate, compare and interpret financial ratios to assess overall customer creditworthiness and support credit decisions.

Case Study: Assessing Customer Creditworthiness

Use financial information to evaluate a customer's financial strength and determine an appropriate credit assessment.

MODULE 2: CREDIT POLICY & CREDIT DECISIONS

Credit Evaluation

• 5 Cs of Credit
• Credit Risk Classification
• Credit Scoring
• Credit Limits
• Credit Terms

Excel Activity: Credit Scoring & Credit Limit Model

• Use IFS for credit scoring and risk classification
• Calculate total credit scores
• Use VLOOKUP to determine recommended credit limits
• Develop a customer credit decision model

Case Study: Setting a Customer Credit Limit

MODULE 3: CONTROL & PERFORMANCE OF ACCOUNTS RECEIVABLE

Receivables & Collection Performance

• Receivables Ageing Analysis & Ageing Report
• Receivables Turnover
• Days Sales Outstanding (DSO) / Receivables Collection Period
• Best Possible DSO
• Collection Effectiveness Index (CEI)
• Overdue Receivables Analysis
• Customer Payment Performance
• Number of Invoicing Disputes
• Right Party Contact Rate
• Outgoing Calls vs. Promises to Pay
• Promise-to-Pay Fulfilment Rate
• Identifying Slow Paying & High-Risk Customers

Excel Activity: Receivables Performance Analysis

Calculate, analyse and compare receivables and collection KPIs to identify performance gaps, problem customers and areas requiring corrective action.

Case Study: Evaluating Receivables & Collection Performance

Analyse receivables data and collection KPIs to evaluate credit control performance.

MODULE 4: EVALUATING CHANGES IN CREDIT POLICY

Financial Impact of Credit Policy Changes

• Impact of Changes in Credit Terms
• Evaluating Early-Payment Discounts
• Cost of Carrying Receivables
• Financing Cost & Financing Savings
• Net Benefit / Cost-Benefit Analysis
• Impact on Profit & Cash Flow
• Evaluating Credit Policy Decision Variables

Excel Activity: What-If Analysis & Scenario Manager

• Build a credit-policy decision model
• Change key variables such as:
  • Credit Period
  • Collection Days
  • Discount Rate
  • Sales Volume
  • Bad-Debt Percentage

• Compare alternative scenarios
• Select the most financially beneficial option

Excel Activity & Case Study: Evaluating Credit Policy Alternatives

MODULE 5: PIVOTTABLES FOR AR ANALYSIS

PivotTable Analysis

• Customer Outstanding Balances
• Ageing Analysis
• Payment Status
• Salesperson Performance
• Credit Terms

PivotTable Tools

• Sorting & Filtering
• Ranking Customers
• Slicers
• PivotChart
• AR Management Reporting

Case Study: Analysing AR Performance & Collection Priorities