Visualizing Exposure and Managing Customer Lease Receivables
An overview of how an unorganized lease receivables approach in Excel can overwhelm an organization and tips on how to manage the data effectively.
Visualizing Exposure and Managing Customer Lease Receivables
With commercial aviation emerging from a worldwide health crisis, the post-pandemic landscape is constantly evolving. Severe labor shortages as a result of the pandemic’s staff cuts have created significant headwinds as global airlines attempt to mount a comeback. With airline customers affected, lessors are continually facing the challenge of dealing with late payments and requests for lease payment deferral or repayment agreement restructuring.
Challenge
Managing lease receivables in Excel in today’s business environment is a burdensome task and can quickly overwhelm a lessor’s platform. Consequently, manually monitoring cash receipts across various bank accounts in different countries on a daily basis can inhibit the provision of accurate, real-time data points to the management team to inform their strategic decision-making.
- Limited data visualization in Excel: Collating data across more than one dimension (i.e. MSN, Operator, Lease, Asset type, SPV, etc) becomes cumbersome when visualizing the dataset within Excel.
- Tracking different types of lease receivables: Deferral or power by the hour (“PBH”) arrangements have created new varied types of receivables. Tracking more than one or two types of receivables across an entire fleet becomes challenging when trying to plot the data across an all-encompassing Excel chart.
- Refreshing data filters or fields in real time: Updating an existing report with unique data filters requires manual updates to the Excel spreadsheet, necessitating a refresh of the data behind the Excel graph, along with a reformatting of the corresponding updated Excel chart or graph.
- Updates to receivables data in Excel is a manual and time-consuming process: Cash activity and Accounts Receivable reconciliation is required daily to provide up-to-date information imported into Excel and manually manipulated into a summarized format, resulting in an excessive use of organizational resources and the margin for human error through manual data entry.
- Excel disorganization: Cash activity across numerous accounts aggregated in spreadsheets leads to difficulty in collaboration across multiple users and it is difficult to introduce workflow features to enhance review and reporting functionality.
Solution
Here are some ways to manage receivables data more effectively:
- Frequent cash receipt downloads and cash account reconciliations: Utilizing banking register downloads through online banking portals into .xlsx or .csv format to reconcile cash account balances can help in minimizing input error. Daily or frequent recording of cash receipts provide near real-time receivables data.
- Building a strong reporting and AR analytics framework helps concisely present information around receivables liquidity and overall exposure: If resources allow, building a robust AR reporting structure in Excel utilizing PivotTables allows for limited multi-dimensional reporting across the portfolio. A reporting framework can be established to provide standardized metrics, such as days outstanding trends and overall AR exposure (net of security deposits and any maintenance reserves).
- Utilizing filtered down reports and select distribution lists: With the sheer volume of receivables held on some lessors balance sheets, a portfolio wide receivables snapshot is often cumbersome to navigate by various users of the reporting. Keeping such reporting in a filtered and concise format and utilizing dimensions (i.e. region, SPV financing, etc.) to provide only relevant information will help make the report more digestible to colleagues.