Whilst previous blogs from us may have looked at the arguments for applying IFRS 16 with a purpose developed software tool and without spreadsheets, here we will analyse, critique and review the arguments against the utilisation of just spreadsheets for such a business critical task – a task which should encompass transition to, and ongoing compliance with IFRS 16, audit trail of lease classification and updates and as importantly lease portfolio management.
Why you might consider a "Spreadsheet Solution"
- The business and its people are familiar with spreadsheets
- The existing lease portfolio is “held together’ on a spreadsheet or various spreadsheets
- You are being offered spreadsheet solutions as a path to compliance by advisors, auditors or internal parties.
- It seems cheap, familiar, easy, understandable and immediate – what can possibly go wrong?
Why you *MUST NOT* consider a "Spreadsheet Solution"
If you are aware of spreadsheets, then you will already know of many of the points against. If you have read and understood the requirements of IFRS 16 then you will be no doubt conscious of some of the shortcomings of spreadsheets which again will manifest themselves as you look to attain or retain Sarbanes Oxley (SOX) compliance. If you have ever tried to understand the workings of another’s spreadsheet then you will probably already be reading ahead here to identify a robust, proven, trusted cloud-based solution. You will want the solution to fly and be popular and so the watch phrase to avoid the sting is WASP (will a spreadsheet perform)?
- Fast to deploy, fast to create its database, fast to implement IFRS 16, fast to execute amortisations and fast to access. WASP?
- Any lease database you build will need to be accessible to a multi-user environment where depending on authority levels individuals in that environment can have different levels of access and update capability. WASP?
- Any software solution you employ needs to be a single version with version control, well documented, continually developed, thoroughly tested and capable of creating its own audit trail of changes in software, leases, liabilities or assets. WASP?
- The solution you identify and choose will have consistency in calculations, headings, inputs, validations, rounding, decimal points and reporting. WASP?
- The lease portfolio database created as a result of you transitioning all existing lease liabilities and assets and then recording similarly all data for future leases needs to be cloud-based to ensure its security, integrity, recoverability and manageability.
- All leases are different but entering the detail as they are contracted to needs to be straightforward, intelligible and where needed automated. WASP?
- Must haves - Amortisation schedules capable of even daily discounting, termination notices, complex reporting options, general ledger journal creation. WASP?
- Support, maintenance and regular training are key to successfully achieving and benefitting from compliance. WASP?
- A purpose built, navigable, tested, trialled, robust, single version, secure platform capable of complex amortisation recalculations to reflect updates and changes as they happen is essential to ensure compliance through the life of a lease. WASP?
Other considerations around Spreadsheets
Quality of the build
- Building efficient Spreadsheets is often dependent upon and limited to the technical proficiency and capabilities of the builder/owner.
- How do you ensure that every calculation is being performed correctly and consistently?
- Has the spreadsheet been created applying the correct interpretation of the new standards?
- Is the spreadsheet effectively calculating the data?
Ensuring data integrity
- The individual nature of spreadsheets leads to an over-reliance on a key user.
- New employees will often take an existing spreadsheet and update/change based on a different level of knowledge and experience (e.g. It is not uncommon that 3rd party spreadsheet models use almost identical formulas – NPV and XNPV – in exactly the same way illustrating a lack of technical proficiency).
- Difficulty exists in troubleshooting, navigating and reviewing spreadsheets.
- Testing spreadsheets is another challenging area and, in many instances, will require… another spreadsheet to perform the test.
- Excel lacks the critical controls needed to preserve the integrity of your data.
- Limited access – Spreadsheets are often restricted to one user at any given time (not a 100% accurate statement but “shared” or “collaborative” spreadsheets managed using applications such as Google Drive, Google Sheets/GSuite and Microsoft SharePoint often lead to more problems than the value provided by such multi-user solutions.
- Spreadsheets are always vulnerable to changes either through deliberate or accidental human interventions/errors that are extremely difficult to isolate and correct.
- An incorrect formula, misplaced decimal point or percentage and even a simple typo can dramatically alter the financial position of your organisation
- Ensuring data quality through effective data validation is possible using Excel. However, this is a complex and time-consuming process and becomes problematic to change once created.
- Lack of scalability - In general Spreadsheets perform well when dealing with smaller data sets. However, limitations become apparent when the data set increases. As a result, the practicality of the application suffers.
Performing the technical accounting
- Creating an amortisation schedule in Excel is a convoluted process and often requires a “row per period” approach.
- For example, a 12 period (1 year) lease requires 12 rows applying monthly discounting.
- Daily discounting is more accurate (and needed for mid-month payments etc). However, performing this using a spreadsheet requires even more rows. A 1 year daily discounted amortisation schedule would need 365 rows; a 10-year daily discount 3650 rows etc… All of which increases the size of the spreadsheet and the number of calculations considerably.
- As a result, it is not uncommon that IFRS 16 workbooks take hours to calculate after every change.
- It is possible to accurately calculate ROU assets and lease liabilities in Excel. However, ongoing remeasurement and reporting requirements can become complex and time intensive.
- Creating a complex lease in excel means a workbook per lease. When modelling leases, any modifications result in starting all calculations again from the modification date and posting an adjustment journal to reflect the change in the starting point.
- The simplest method of performing this in Excel whilst maintaining an accurate audit trail is to store a single lease per workbook and to use a separate worksheet for each modification.
- As a result, connecting workbooks becomes more complex.
- Difficulties in accommodating unique accounting periods - For instance, complex XNPV formulas are required to perform 13-period or 4–4–5 (4-5-4) calendars using Excel. Such calendars are extremely common within the manufacturing and retail sectors.
- Companies with leases in more than one country will need to consider the limitations of spreadsheets in handling multiple currencies.
- Spreadsheets linked to other spreadsheets often result in failed and/or corrupted links.
- There is no ‘built-in’ audit trail in Excel. Any audit trail functionality would need to be developed/written using VBA (Visual Basic for Application) coding. This, of course, would require a significant amount of testing and an advanced level of Excel proficiency to execute.
- Although Spreadsheets may appear a cheaper option, they do require more maintenance and may even require external support (at a cost) to sustain.
- A significant amount of manual processes is required to support the use of spreadsheets (e.g. backup/disaster recovery etc)
It is also worth noting that when using a spreadsheet it is extremely difficult to satisfy the internal control criteria set down by Sarbanes-Oxley (SOX) and as a result Excel spreadsheet are generally NOT SOX compliant.
Spreadsheets tend to be individual in nature, rarely understood or useable by any but the author so until someone somewhere changes it then it just may if it ever did perform. When a mistake is discovered, or code corrupted then where is the back-up and recovery. When someone new takes on the task of using, updating and managing the spreadsheet then there will inevitably be a real drop in performance. If a tweak or update is needed then is the original author still with the company, how do you test it, what version do you use, how will it affect all previous work. Just how many rows, columns and worksheets will be created to cater for every asset on every lease? Ask yourself WASP?
In need of a better solution?
Oh, by the way, if you did skip to here or ended up here anyway then you will likely, by now, understand the need for a dedicated solution and should look no further than LLA (LOIS Lease Accounting) from Innervision.
That spreadsheet solution whether generated internally or as is more likely externally often require a monthly support budget that may reach north of £10,000 which begs the question of value for money.
A better and more strategic approach is to look towards purpose-built software solutions that have been designed with lease accounting at its core. Not only have such solutions been coded and pre-programmed to produce all the complex calculations and reporting for you. They have also been rigorously tested to ensure the accuracy of the accounting outputs.
Such solutions may incur an implementation fee and periodic cost but are PROVEN TO PERFORM.
If you’re looking for a solution that is superior to spreadsheets for transitioning to the new lease accounting standards discover how LOIS Lease Accounting will help - download the product brochure below or see for yourself by scheduling a demo with one of our IFRS 16 experts.
Disclaimer: this article contains general information about the new lease accounting standards only and should NOT be viewed in any way as professional advice or service. The Publisher will not be responsible for any losses or damages of any kind incurred by the reader whether directly or indirectly arising from the use of the information found within this article.