Working Capital & Accounts Receivable Aging Analysis
Age unpaid invoices, find how much cash is stuck, and plan faster collections.
About this internship
Data Collection & Accounts Receivable Cleaning
- Ledger Extraction: Pulling open invoice data from the company's accounting software or ERP system (e.g., Tally, Zoho Books, SAP, or QuickBooks), including invoice numbers, customer names, issue dates, due dates, and outstanding balances.
- Data Wrangling: Using Python (
pandas) or Excel to isolate fully paid or disputed invoices, correct discrepancies in payment terms (e.g., Net 30 vs. Net 60), and standardize customer names.
AR Aging & Dashboard Construction
- Aging Bucket Categorization: Building a dynamic data model to categorize outstanding invoices into standard time intervals based on how many days they are past due:
- Current (Not yet due)
- 1–30 Days Past Due
- 31–60 Days Past Due
- 61–90 Days Past Due
- 90+ Days Past Due (High Risk)
- Trapped Cash Quantification: Calculating the exact rupee amount locked in each bucket and identifying concentration risk (e.g., “Customer X represents 45% of all cash stuck in the 90+ days bucket”).
- Interactive AR Dashboard: Developing a visual dashboard in Tableau, Power BI, or Excel that lets management instantly filter overdue cash by territory, sales representative, or individual customer.
Efficiency Metric & Credit Analysis
- Days Sales Outstanding (DSO) Calculation: Computing the company’s average collection period (Average Accounts Receivable ÷ Total Credit Sales × Days in Period) to benchmark historical collection efficiency.
- Collection Effectiveness Index (CEI): Measuring the percentage of available cash that the collections team successfully recovered during a given period.
- Bad Debt & Provision Modeling: Identifying chronic late-payers and calculating an "Allowance for Doubtful Accounts" to project how much stuck cash might ultimately turn into a write-off.
Collection Strategy & Process Optimization
- Dunning Process Automation Design: Designing a structured, automated communication workflow for late payments (e.g., automated email reminders 5 days before due date, SMS alerts at 7 days past due, and escalated phone scripts at 30 days past due).
- Payment Term Incentive Modeling: Running a cost-benefit analysis on offering early-payment discounts (e.g., a "2/10 Net 30" policy, where customers get a 2% discount if they pay within 10 days) to see if the faster cash flow outweighs the margin reduction.
- Credit Policy Refinement: Drafting recommendations to tighten credit terms for consistently high-risk clients based on the data findings.