Custom Reporting Solutions for ASC 842 Lease Accounting

By Shilpa Boggram Sathyamurthy, CPA, CA

Despite years since its introduction, ASC 842 compliance continues to pose challenges for organizations, particularly as business operations become more complex with expanding portfolios, acquisitions and mergers. This increasing complexity makes the need for customized reporting solutions more critical.

While many lease accounting software solutions offer out-of-the-box reporting features, some also provide customization options to meet the organization’s specific needs. However, as organizations grow, they often rely on multiple lease accounting systems, complicating the consolidation of lease data across platforms.

Additionally, some organizations may have specific master data attributes related to accounting that need to be integrated into lease reports that are not maintained within the lease accounting software, while others require more granular reporting to align with their internal processes. In such cases, developing custom reporting solutions can improve efficiency in ASC 842 compliance, offering a more tailored and sustainable approach to meet the unique requirements of the organization.

This article outlines aspects of building and designing custom reporting solutions using lease accounting data from an organization's lease accounting software.

Project Plan

The first step in developing custom reporting solutions, as with any project, is to create a detailed project plan. This plan should define the following at a minimum:

• Key Milestones - Establish timelines for each phase of the reporting solution development, including requirements gathering, design, testing, and deployment.
• Resources - Allocate the necessary personnel, technology tools and budget.
• Process Owners - Identify the individuals responsible for key aspects of the project, ensuring accountability and proper project execution.
• Timeline - Ensure realistic deadlines are set based on the complexity of the requirements and the size of the organization’s lease portfolio.

Having a structured project plan ensures that the development process stays on track and resources are used effectively to meet the project’s objectives.

Requirements Gathering

Before starting the development of custom reports, a comprehensive understanding of the organization's reporting needs is essential. This phase involves collaboration with key stakeholders, including accounting, finance, internal audit, and operations teams, to collect the following:

• Report List - A list of all the reports needed for ASC 842 compliance, specifying the fields and data points that must be included in each report. The specifications should include the required view for different consumers of these reports.
• Report Owners - Identify the owners for each report and ensure their involvement in user acceptance testing (UAT) and other stages of the process.

Common Key Reports for ASC 842 Compliance:

• Disclosure Reports - To support ASC 842’s disclosure requirements such as: Weighted Average Lease Term, Weighted Average Discount Rate and Future Obligations Report - for both commenced and non-commenced leases.
• Lease Cash Flows - Report to ascertain the information relating to leases to support the preparation of the organization's Cash Flow Statement or supplemental cash flow information as applicable.
• SOX Control Reports - To ensure compliance with Sarbanes-Oxley, these reports help in performing balance sheet reconciliations and detailing lease-level data such as: Right of Use (ROU) Asset, ROU Asset Amortization, Lease Liability (Short term and long term), and Lease Balances Roll forward report.
• Additional Control Reports - Based on internal control processes, organizations should review whether further control-related reports are needed.
• Ad-Hoc and Analytical Reports - For decision-making and strategic planning, these reports are often focused on portfolio analysis or lease performance metrics.

Selecting the Tools

Selecting the right tools for data extraction, transformation and reporting is critical to building efficient custom reporting solutions. Consider the following stages and tool requirements:

• Data Extraction - Evaluate the data volume, integration needs and compliance requirements to determine whether to use existing in-house tools or invest in robust ETL solutions.
• Data Mapping and Calculations - Custom reports may require complex data mapping, especially if multiple lease accounting systems are in use or if company specific accounting attributes are required to be mapped that are not already captured in the lease accounting software. Mapping data from different platforms is critical to ensure consistency and accuracy. Depending on the custom report requirements, the tool will need to be capable of performing calculations to provide the desired results.
• Consolidated Reporting and Visualization Dashboards - Integrate data from various systems into a single report or dashboard for clear, comprehensive analysis. Visualization tools and dashboards can be used to present the consolidated data in an easily interpretable format for decision-makers. Based on the organization’s data volume, complexity and requirements, decisions should be made on what visualization tools should be used.

Testing and Validation

Following the development of custom reports, it is crucial to validate their accuracy and functionality:

• User Acceptance Testing (UAT) - Define clear UAT procedures and establish success criteria. Engage end-users in the testing process to ensure the reports align with business requirements and function as expected.
• Audit Engagement and Compliance Assurance - Engage with both internal and external audit teams early in the process to ensure compliance with Sarbanes-Oxley (SOX) regulations. This collaboration helps confirm the integrity of key reports and ensures that they meet the standards for ASC 842 and other GAAP requirements, through independent testing and validation.

Change Management and Maintenance

After deploying the custom reporting solution, ongoing maintenance and updates are essential to maintain compliance and adaptability:

• Version Control - As ASC 842 guidelines evolve or business requirements change, it is vital to implement version control for reports. This ensures that updates are tracked and managed systematically.
• Change Management - Establish a structured change management process to manage updates to reporting tools. This process should include approval workflows and rigorous testing procedures to maintain report accuracy and compliance.
• Access Management - Implement strict access controls to sensitive lease data, ensuring that permissions align with organizational policies and compliance requirements.
• Audit Review - Consider incorporating the testing of custom reports into the annual audit plan to ensure regular review and validation, further safeguarding compliance and report accuracy.

Data Extraction Using SQL

Lease contracts are initially entered into lease accounting software and processed. The resulting lease data and associated calculations are stored in a database. To extract the necessary data for reporting, a SQL query can be written. SQL is a standard programming language used for managing and manipulating relational databases. It allows users to interact with a database by performing various tasks such as querying data, updating records, inserting new data, and deleting data.

Accounting teams typically would need to involve technology support and data teams to access the database and write the appropriate SQL query. This query will need to ensure the extraction of the required fields at the necessary level of aggregation. Additionally, SQL can be used to map master data or other identification fields that may not be included directly in the lease accounting software’s data, ensuring comprehensive data coverage. If the foreign exchange rates are not maintained in lease accounting software, the mapping of exchange rates could also be performed using the SQL query.

Data Extraction and Mapping via Power Query

The Power Query add-in in Excel allows users to connect to the database and extract the necessary data into excel. Power Query can then perform data transformation tasks, such as cleaning, mapping, and combining data from multiple tables. Whether using SQL code or Power Query functions, the process of data mapping ensures that relevant master data is integrated, providing a complete view of the lease information required for the report.

Power Query’s ability to handle different data sources and transformations is particularly useful when the lease data is spread across multiple systems or when additional contextual data needs to be incorporated.

Aggregation and Summarization

Once the data is extracted and mapped, Excel pivot tables can be used to aggregate and summarize the data. Pivot tables are a powerful feature in Excel that can filter, group, and summarize large datasets, making them ideal for creating customized reports and dashboards.

Designing the Report and Visualization Dashboard

The final step in the process is designing the report. By leveraging Excel’s capabilities, users can efficiently design a dashboard that visualizes key metrics, such as lease liabilities, payment schedules, and asset values. In this example, Excel pivot tables are used to aggregate data, which can then be formatted into a dashboard. This provides a user-friendly interface for stakeholders to access, analyze, and make decisions based on the lease data.

Considerations for Larger or More Complex Lease Data

While building custom reports using in-house tools such as SQL, Power Query, and Excel is a feasible option for companies with smaller volumes and less complex lease data, organizations with larger data sets or multiple data sources may require more robust solutions. For such cases, it may be more efficient to adopt advanced Extract, Transform, Load (ETL) tools available in the market. These tools offer enhanced capabilities to process large volumes of data, connect to various data sources, and provide advanced error handling and monitoring features, ensuring scalability and reliability for enterprise-level reporting. As a result, they ensure scalability and reliability, which are essential for enterprise-level ASC 842 compliance and reporting.

However, creating this custom reporting solution using in-house tools could serve as an initial setup before investing and transitioning to a more robust ETL tool. This interim approach allows organizations to process data more efficiently and gain deeper insights into their lease data management needs. It also serves as a valuable opportunity to evaluate whether the company's requirements align with the anticipated full-scale automation setup, or whether adjustments need to be made. In this sense, the in-house solution can act as a proforma, providing a foundation for future automation decisions while ensuring that the current reporting process is robust and scalable.

Conclusion

Developing custom reporting solutions for ASC 842 compliance provides organizations with the flexibility to adapt to evolving business needs, while ensuring full regulatory compliance and a consolidated reporting framework.

By following a structured approach starting with a comprehensive project plan, gathering detailed requirements, selecting appropriate tools, and conducting thorough testing and maintenance organizations can build tailored, sustainable reporting solutions that improve efficiency and support strategic decision-making.

About the Author: Shilpa Boggram Sathyamurthy, CPA, CA, is a Senior Manager, Accounting, at DoorDash. Contact her at shilpa.boggram@gmail.com.

Thanks to the Sponsors of Today's CPA Magazine