The ASC 842 lease accounting standard represents a significant shift in how organizations report their leases. Before ASC 842, operating leases were not included on the balance sheet, which neglected to provide a full picture of cash flows from leases. This meant companies and investors were unable to identify how much debt was carried within a business’ lease obligations.
The new lease accounting standard requires organizations to include operating leases and financial leases on the balance sheet, which increases visibility into leasing costs and arrangements. This ensures an accurate depiction of company financials. Compliance with ASC 842 is essential for transparency, accuracy, and financial accountability.
The calculations that are involved in staying compliant are extremely susceptible to error – particularly if done without automation. However, many individuals and organizations initially turn to Excel templates for managing their lease accounting needs. In this blog post, we will explore both Excel templates and technology options for ASC 842 compliance.
Using Excel Templates for ASC 842
Excel templates can seem like a convenient and cost-effective solution for ASC 842 lease accounting. They offer flexibility in customization and can be tailored to an organization’s specific needs. However, setting up and managing Excel templates for ASC 842 compliance comes with its own set of challenges.
Creating Customized Templates
Setting up Excel templates for ASC 842 can be a time-consuming process. You’ll need to design templates that accurately capture all lease data, including lease terms, payments, and commitments. Creating these templates from scratch can be complex, and errors in template design can lead to inaccuracies down the line. That said, here are some tips to get you started:
Step 1: Define Your Lease Data Categories
Before you start building the template, it’s crucial to identify the data categories you need to track to comply with ASC 842. Common categories include:
- Lease Details: Lease ID, lease term, commencement date, termination date, etc.
- Payment Information: Monthly/quarterly/yearly lease payments, initial direct costs, etc.
- Lease Modifications: Any changes or modifications to the lease terms.
- Discount Rate: The rate used to calculate the present value of lease payments.
- Lease Liability and Right-of-Use (ROU) Asset: Calculations of these values over time.
- Lease Classification: Operating or finance lease classification criteria.
- Lease Documents: Attachments for lease agreements, amendments, disclosures, etc.
Step 2: Create a New Excel Spreadsheet
Open Excel and create a new blank spreadsheet. You can start with a clean sheet or use Excel’s pre-designed templates as a starting point.
Step 3: Set Up Columns and Headers
In your spreadsheet, create columns for each data category identified in Step 1. Label each column with appropriate headers, such as “Lease ID,” “Commencement Date,” “Lease Term (Years),” “Monthly Payments,” and so on. You can also add headers for any additional information, such as lessor details, lease modifications, and classification criteria.
Step 4: Format and Customize the Template
Format the columns to ensure that data is displayed correctly. You may want to adjust column widths, apply cell formatting (e.g., date format, currency format), and add borders for clarity.
Customize the template further by adding dropdown lists, data validation, or conditional formatting to enforce data consistency and accuracy. For example, you can create dropdown lists for lease classification options or lease modification types.
Step 5: Add Formulas
Incorporate Excel formulas to calculate values automatically. For ASC 842 compliance, you’ll need to calculate the present value of lease payments, lease liability, and ROU asset. These calculations involve using the discount rate and the lease payment schedule.
For instance, you can use the NPV (Net Present Value) function to calculate the present value of lease payments over time.
Step 6: Set Up Tabs and Document Management
Create separate tabs or sheets within the Excel file for lease documents and attachments. Each document should be appropriately labeled and organized for easy reference.
Step 7: Testing and Validation
Before using the template for actual lease accounting, thoroughly test it with sample data to ensure that calculations and formulas work correctly. Validate the template’s accuracy against known lease scenarios.
Step 8: User Training
Provide training to the relevant personnel on how to use the template effectively and input data accurately.
Remember that while this basic Excel template can help you get started with ASC 842 lease accounting, as your organization’s lease portfolio grows, and complexity increases, you may want to consider transitioning to specialized lease accounting software to streamline the process and ensure compliance more efficiently. Such software offers automation, audit trails, and advanced reporting capabilities, reducing the risk of errors and enhancing accuracy.
Best Practices for Data Entry and Formula Setup
When using Excel for ASC 842 lease accounting, implementing best practices for data entry and formula setup is crucial to ensure accuracy, compliance, and efficient lease management. Here are some best practices to consider:
- Consistent Data Input: Ensure that lease data is entered consistently and uniformly throughout the spreadsheet. Use standardized naming conventions and units (e.g., dollars, square feet, months).
- Data Validation: Implement data validation rules to prevent incorrect or inconsistent data entry. Use dropdown lists, date validation, and other data validation features to guide users.
- Data Review: Periodically review and audit the data entered into the spreadsheet for accuracy and completeness. Regularly check for errors, missing information, or discrepancies.
- User Training: Provide training to the individuals responsible for data entry. Ensure they understand the ASC 842 requirements and how to accurately input lease data into the spreadsheet.
- Documentation: Document data sources, assumptions, and any changes made to lease agreements or data. This documentation is crucial for audit purposes and maintaining a clear audit trail.
Formula Setup Best Practices:
Excel templates require meticulous data entry to ensure accuracy. Formula setup can also be prone to human error. Mistakes in data entry or formulae can lead to incorrect calculations, potentially resulting in non-compliance with ASC 842. Keep these tips in mind:
- Consistent Formulas: Use consistent formulas throughout the spreadsheet to calculate values like the present value of lease payments, lease liability, and ROU asset. Avoid mixing different formulas or calculation methods.
- Cell References: Use cell references (e.g., cell names or structured references) instead of hardcoding values within formulas. This makes it easier to update data without having to modify the formulas manually.
- Check Formulas for Accuracy: Double-check all formulas for accuracy and ensure that they are correctly referencing the appropriate cells and ranges.
- Use Named Ranges: Define named ranges for critical data ranges or input cells. Named ranges make formulas more readable and easier to manage.
- Separate Calculations: If your spreadsheet includes complex calculations, consider separating them into different worksheets or sections. This enhances the clarity of the workbook and makes it easier to troubleshoot issues.
- Formula Auditing Tools: Excel offers built-in auditing tools such as the “Trace Precedents” and “Trace Dependents” functions. Use these tools to trace the flow of data and formulas within your spreadsheet.
- Error Handling: Implement error handling in your formulas, such as IFERROR or IF statements, to provide meaningful error messages or alternative calculations in case of errors.
- Documentation: Document complex formulas and calculations for reference and troubleshooting. Include explanations of how the formulas work and any assumptions made.
- Regular Review: Regularly review and validate the accuracy of your formulas, especially if there are changes to lease agreements or data inputs. Ensure that formulas remain up-to-date and compliant with ASC 842 requirements.
- Version Control: Implement version control practices to keep track of changes made to formulas. Clearly document when and why changes were made.
Best Practices for Document Organization
Keeping track of lease documents within Excel templates can be challenging. Without a centralized repository, it becomes easy to misplace or lose critical documents, which can create audit complications and compliance issues. Here are some best practices for document organization in Excel for ASC 842:
- Use Separate Tabs or Worksheets: Create separate tabs or worksheets within your Excel workbook to store different types of lease documents. For example:
- One tab for lease agreements
- Another for lease amendments
- A tab for disclosures and correspondence
- A separate tab for audit documentation
- Clear and Consistent Naming Conventions: Develop a clear and consistent naming convention for your documents. Include relevant information such as the lease ID, document type, and date. For example: “LeaseID_Agreement_2023-10-24.pdf.”
- Folder Structure: Consider creating a folder structure outside of Excel to complement your organization. Store actual documents in folders based on lease ID or categories (e.g., active leases, terminated leases, amendments). Excel can reference these documents using hyperlinks or file paths.
- Hyperlinks: Use hyperlinks within your Excel spreadsheet to directly link to the corresponding documents stored in your folder structure. This allows easy access to documents with a single click.
- Document Tracker: Create a document tracking table or list within Excel that includes columns for document name, document type, lease ID, location (file path or hyperlink), and any additional notes or comments.
- Version Control: If you have multiple versions of lease documents (e.g., lease agreements with amendments), clearly indicate the version number or date in the document name. Keep the most recent version easily accessible.
- Document Index: Consider creating an index sheet within your Excel workbook that lists all lease documents with their associated lease IDs and types. This can serve as a quick reference guide to locate documents.
- Color Coding and Formatting: Use color coding or formatting to highlight critical information or identify document status. For example, you could use different colors to distinguish between active leases and terminated leases.
- Document Metadata: Include metadata in your Excel spreadsheet, such as lease start and end dates, lessor information, and lease classification. This allows you to quickly filter and sort documents based on key criteria.
- Regular Auditing: Periodically review your document organization system to ensure that it remains up-to-date and compliant with ASC 842 requirements. Remove obsolete documents and update document links if necessary.
- Backup and Security: Ensure that you have appropriate backup and security measures in place for your document storage system, especially if it contains sensitive lease information.
- Training: Train your team members on the document organization system to ensure everyone understands how to access and manage lease documents effectively.
Common Challenges with Excel Templates for ASC 842
While Excel templates offer a degree of flexibility, they come with several inherent disadvantages:
- Data Accuracy
Human errors in data entry and formula setup can lead to inaccuracies in financial reporting, potentially resulting in compliance violations and financial penalties.
- Security Risks
Excel files are susceptible to security breaches. Sensitive lease data may be at risk if proper security measures are not in place, jeopardizing data privacy and compliance.
- Version Control
Managing multiple versions of Excel templates can be confusing and prone to errors. Ensuring that everyone is working on the latest version can be challenging, leading to data inconsistencies.
- Audit Complications
During audits, Excel templates can complicate the process. Auditors may spend significant time verifying data accuracy and formulae, which can lead to additional audit costs and delays.
Excel Templates vs Lease Accounting Software for ASC 842
Transitioning to specialized lease accounting software like Visual Lease offers several advantages over Excel templates:
- Data Accuracy and Automation
Lease accounting software automates data entry and calculations, reducing the risk of errors and ensuring compliance with ASC 842.
- Enhanced Security
Lease accounting software typically comes with robust security measures, safeguarding sensitive lease data from potential breaches.
- Version Control and Collaboration
Software solutions facilitate version control and collaboration among team members, ensuring that everyone is working with the latest, most accurate data.
- Audit Readiness
Lease accounting software streamlines audit processes by providing auditors with easy access to accurate, well-organized lease data.
Leaving cumbersome spreadsheets for a purpose-built platform
While Excel templates may initially seem like a cost-effective solution for ASC 842 lease accounting, their limitations and potential pitfalls can lead to compliance issues, security risks, and inaccuracies. To ensure compliance with ASC 842 and streamline lease accounting processes, organizations should consider transitioning to specialized lease accounting software like Visual Lease. Making this transition can save time, reduce errors, enhance security, and ultimately contribute to more accurate and efficient lease accounting practices. Learn more about switching from Excel to Visual Lease.
- May 10, 2023Visual lease allows us to move away from manually tracking hundreds of lease in excel and does all the heavy lifting for us.Posted on
- May 17, 2023Before Visual Lease we tracked everything in Excel. With over 70 leases now, it was a win win situation!Posted on
- Nov 4, 2022ASC 842 reporting. This was a huge project for us in 2019 as we prepared for the transition from ASC 840 to ASC 842 and the annual audit. We were tracking everything using Excel before and, without Visual Lease, it would have been really difficult.Posted on
- Oct 26, 2022“We initially used Excel to track key data and journal entries. Now we use VL to perform all calculations and report on our financials, saving us time and less human error in our calcs.”Posted on
- May 17, 2022This software streamlines our Accounting for leases and completely replaces our existing Excel solution (for both GAAP and IFRS)Posted on
- Oct 20, 2022The ease of use and the lease accounting function which allows you to run multiple lease calculations and scenarios for a lease. It has allowed us to move away from excel based spreadsheets and calculations and automate the calculations. Additionally we have realized great benefit from the centralized tracking and administration. We set up system alerts which send out emails to allow us to stay on top of upcoming term renewal windows so we can assure we make the best strategic decision.Posted on
- Oct 22, 2020The biggest problems we have solved with Visual Lease are the ASC 842 accounting entries and having a great way to store and calculate hundreds of leases. These calculations are far more accurate than anything an excel could do.Posted on
- Oct 22, 2020I can easily make modifications to a lease (extension, renewal, payment amount change) and the system will accurately calculate the updated lease schedule and generate the appropriate accounting entries. This is so much better than our previous Excel system which was very manual.Posted on