By Andrew Ross, Performance Analytics Corporation
Tax Practitioners may strive for a high-performing tax department, but the tools they use may be preventing them from getting there. In this blog, we explore the challenges in using spreadsheets as the primary tool.
Tax, like many parts of any organization, is always looking to improve; where can we do it faster and reduce risk through automation of routine tasks. One of the more regular activities in the tax function, the tax provision immediately comes to mind and is often the first process considered. The tax provision process of most multi-jurisdictional companies employ Excel spreadsheets to collect, process and consolidate the information required to prepare the tax provision. With such extensive use of Excel, there is always a risk of errors in the process.
When using Excel in the tax provision, there is a significant risk of error; the extent of the risk might not be material but the question for Head of Tax is “Will you know whether the error is material or not?”. The extent of the error differs and most errors are not comparable to the estimated $24 million USD error at TransAlta (Brethour, Globe and Mail, June 4, 2003). While this is an extreme case of potential risks of using Excel spreadsheets, any material error that makes it past the Head of Tax is a concern that will give a tax professional sleepless nights.
In excel, there are key factors that impact the risk of error based on the 1) formulas and advanced functions used, 2) updating of data, and 3) maintenance of the templates.
1) Formulas and Advanced Functions
The tax provision process typically makes extensive use of Excel spreadsheets and includes extensive use of formulas and/or advanced functions to move data within the template and calculate the provision. Most tax professionals are the template owners and they know their spreadsheets and are intimately familiar with the formulas and functions used. Some of these templates are getting better at documenting the calculations and explaining how data is calculated in a visual manner. No matter how familiar someone is with their template there still exists a risk of an error in formula.
Various studies have been performed to look at the risk of errors in Excel spreadsheets and based on the different studies it is consistent that 85-95% of all spreadsheets have errors in them (Powell, Baker and Lawson, Journal of Organizational and End User Computing, Sept 2009). The main factors that create the errors is the use of formulas, advanced functions used and size of the spreadsheet.
All three of these contributing factors are used extensively in tax provision templates which increases the risk of an error in the tax provision. This applies to any tax provision process built using Excel spreadsheets.
2) Data Updates
Tax provision templates uses various approaches to bring data into Excel spreadsheets, such as the month end financial results. The process of updating the latest consolidation data uses an extract of the latest month end actual data which is often copied and pasted into the template by the user or tax professional. Sometimes this will require some adjustments to the format due to changes in structures, such as a new account.
More advanced tax provision templates have integrated the ability to pull directly from their ERP, financial reporting tool or consolidation software using an Excel Add-in or direct connections to the data source.
The challenge is there is no control or validation of the latest changes in data that is loaded into the template. Often the tax professional is left having to review the financial data at the last minute to ensure the numbers are updated and if numbers have changed, identify what specifically has changed to confirm there is no tax impact in the change. This has to be managed in all the spreadsheets, as there is no central monitoring process to track the updates in the tax provision process and identify where were the latest changes impacted. With multiple tax provision Excel templates, the risk associated with updating the financial data could never be eliminated.
In each situation, the tax team member will need to manually review the data to ensure the results have not changed. The risk of errors in the process increases based on the number of updates in the financial data and the number of jurisdictions involved.
3) Maintenance
Most tax provision Excel templates require regular maintenance to update the functionality/formulas for changes in tax law, organizational changes in accounts or legal entities and sometimes other updates for changes in the organizational structure or data used. While this is timely and extensive in multi-jurisdictional environments, most of this work is performed by a tax professional outside of reporting windows, in “down time”.
The challenge is with more maintenance changes the risks of errors increase. During regular maintenance, errors in the tax provision templates often are related to changes impacting the formulas and advanced functions used. As new columns and rows are added or removed in the tax provision templates, the references used (ie. cell based references) are not automatically updated correctly causing errors in the formulas or functionality. Some errors are not always readily identifiable.
Often more complex changes will require testing to confirm the formulas and functionality are producing the desired results.
While the risk of errors in the tax provision spreadsheets will keep a tax professional up at night, it is not the only risk or challenge that impacts tax professionals.
Collaborative Environment
Typically, the ability to share a template with others in your local tax department is not an issue as the templates are stored on a central network or cloud storage. The challenge becomes more apparent in a multinational organization which require the sending and receiving of Excel spreadsheets.
Sharing of information outside of the tax group is often difficult as others don’t have access to the tax drives or cloud-based storage areas. This requires tax professionals to email templates or parts of their templates to others in the organization. This provides a manual control over the data as tax can limit the information sent based on the Excel worksheet sent or received. There are risks in this approach as the manual process of data sent out is no longer managed and controlled by tax. Then there are the typical challenges of what version of the data is being shared and has something changed. Too often tax manages the sending and receiving of spreadsheets with Email, where a search of email folders is used to identify exactly what was sent to whom.
Security
With the tax provision Excel templates, security is managed based on restricting access to the file storage location to the identified tax professionals. There is often no security employed in the individual spreadsheets or at the data level in the spreadsheets.
This appears to be satisfactory because only the immediate members of the tax team access the files. The problem with this approach is that it doesn’t address the security when sending templates back and forth via email or through some type of portal nor does it address situations where tax needs to share the data with other internal employees of the organization or external people such as auditors. This limited security is a risk for tax which they must manage manually and track offline.
As more advanced functionality is used in Excel which permits the templates to be shared among multiple people, this will expose the tax function to a greater security risk.
Audit Trail of Changes
The audit of the formulas and understanding how calculations and data are used is a common challenge for tax templates. Most template owners know their spreadsheets and can walk someone through it. With some organizations, the documentation in the tax provision templates is more complete and thorough to capture the specific steps of the calculations and using visual clues to help users interpret the data calculations and flows.
True audit trail to track changes in formulas and data, while possible in excel, would not be viable as it would be an enormous performance drain and make the template virtually unusable.
Tax tends to rely on external tools like network access to determine who made changes last and to archive a previous version. While this is not ideal it is a patch that has been used in smaller tax departments of 2 to 5 tax professionals. For a larger department, not having a comprehensive audit trail exposes the process and department to unnecessary risk.
Replace Spreadsheets for Tax Provision
When using a packaged tax provision application like OneStream’s Tax Provision, multinational organizations can rapidly achieve their goals of automating their tax function and their goal of a high-performing tax department. OneStream’s Tax Provision eliminates the inherent risks of using Excel and provides the ability to automate most of their process, makes it easier to complete the tax provision with less risk, and enables a faster close. In addition, OneStream’s Tax Provision tools automates routine tasks of updating financial data and structures to virtually eliminate routine maintenance tasks and allow the tax staff to focus on the review and analysis of information. More tax professionals are looking at opportunities to streamline and automate routine tasks in the tax function; automating the tax provision is an easy opportunity to save time and money and reduce risk in the process.
Read more about OneStream for Tax Provision at Tax Provision | Performance Analytics
If you would like more information or to discuss tax provision solutions further, feel free to contact me at Andrew.Ross@PerformanceAnalytics.com or call 647-289-7409
