Excel has its limits. Sooner or later most dedicated Excel users discover the limitations of their favorite tool and begin a search for better ways to get their work done quickly, easily and with much reduced risk of serious error.
For frequent Excel users, one very attractive alternative is Alteryx Designer. With Alteryx Designer analysts can build workflows to handle many Excel tasks that are tedious, time consuming and error prone. Alteryx workflows are created easily on a graphical canvas with drag and drop tools. Alteryx avoids the need to learn a programming language or master the intricacies of crafting Excel macros. This approach to software building is often referred as “NO CODE” programming.
CYCLE TIME Once built and tested, Alteryx workflows operate against source data files in a fraction of the time required by Excel. By automating previously manual tasks Alteryx significantly reduces or even eliminates the risk of human error. Whenever a configuration or logic error is detected in the workflow, Alteryx will stop the workflow execution and alert the user to exactly the point in the workflow where it has failed.
FILE SIZE With Alteryx the annoying file size limitations of Excel are removed. Data files containing tens of millions of records are no longer a problem. Alteryx eliminates the need to break up huge files into smaller chunks that Excel can digest.
DOCUMENTATION Alteryx users work with a visual interface to create and run their workflows. Since the workflows are rendered as diagrams, the application readily supports much of the work of process documentation. With easy-to-follow workflow diagrams, managers require minimal training to review and approve new or updated workflows created by team members.
Built in tools within Alteryx make it easy to incorporate additional text into workflow diagrams. This added text can include references to business context, descriptions of business logic or pointers to external documents. This approach to thorough documentation provides insurance against the loss of that one key person possessing the knowledge required to operate a critical legacy Excel process.
INTEGRATION Knowing that Excel will not be going away anytime soon, the creators of Alteryx have designed their application to play well with Excel. Alteryx workflows can be configured to read all Excel file formats along with dozens of other formats commonly used for storing data. Alteryx can also write to Excel files plus numerous other data formats.
Often an Excel based analytical process requires that a request be sent to a data source such as an accounting application or an ERP system. Only after the source system has delivered the data in a format readable by Excel can the analysis proceed. Sometimes there are significant delays if more than one person needs to be involved. In many cases Alteryx can be configured to directly extract data from the source system. These workflows can be structured to follow security rules and can even be set up to run automatically on a schedule.
ROI Most times the business case to acquire Alteryx Designer and train users is easy to make. The investment payoff is most often measured in analyst time saved, shorter cycle times, reductions in the risks associated with spreadsheet errors and the comfort of knowing that crucial systems are not vulnerable to disruptions caused by staff turnover. Alteryx allows business users to go beyond the limitations of Excel without calling upon scarce IT resources or becoming software programmers themselves.
Proficiency with Excel is a basic requirement in the accounting world. You would be hard put to find an accounting department not equipping everyone with Microsoft Excel. Why? Excel gets the job done. It handles many routine accounting tasks easily and quickly. And Excel has kept pace with numerous analytical tasks that have become mainstream in the recent past. These include querying complex databases, combining data from multiple sources and visualizing resulting datasets.
With all this functionality why would any accountant want to leave the safety and comfort of working with Excel and the closely related Microsoft family of products. The answer is well documented but is often unrecognized. Unrecognized that is, until Excel becomes the source of a problem. Excel suffers from some serious limitations. These limitations can drive unexpected costs and in certain cases add significant risk. Let’s explore these in detail.
COST Excel is frequently used for complex tasks. A multi-step process, such as the consolidation of financial statements for multiple companies easily consumes many hours. It may need to be executed numerous times a year. This can require a significant investment of time by a senior resource. While Excel macros may speed up the work, the true cost of the process can be high. This doesn't need to be.
Too many complex Excel procedures lack documentation. When the work is re-assigned, a new person will need to master the process. If the previous process owner is unavailable for support and there is no documentation the new accountant may struggle. Quite often they spend significant time and effort to rebuild the process from scratch. In this process of rebuilding, other work can be delayed, and deadlines missed.
Some manually driven Excel processes require the execution of many steps in a very precise order. Performing them out of order or missing a step will alter the results. This can require extra time spent by supervisors to check results. Whenever errors are discovered, the work will need to be repeated and checked again. This extra cost is often hidden and seldom tracked.
SCOPE As of 2021 Excel can handle a single data table of up to 1,048,576 rows. Importing data sets into Excel that exceed this limit will cause Excel to truncate the table and in some cases cause the application to fail and the computer to crash. When approaching its row limit Excel can become slow and unstable. Excel depends on the available memory of the computer it is running on. It also depends on the speed of the processor. There are workarounds, such as splitting a large table across multiple worksheets in the workbook. While this may be acceptable, it adds time and complexity to the exercise and adds risk.
Computer memory and processor speeds continue to grow. This alleviates the Excel scope limitations to some degree. However, the volumes of data that enterprises collect and are expected to analyze is growing even faster. The total volume of data produced globally since computers were first invented has kept doubling every two years. Accountants have not been exempted from this explosion of data. In fact, this ever-growing universe of data has greatly increased management expectations for reporting that integrates financial and operational data across numerous dimensions. This has exposed the limits of Excel at many organizations.
RISK The flexibility of an Excel spreadsheet is its greatest strength and its greatest weakness. It is all too easy to accidently overwrite an Excel formula with a fixed value. (Note: Excel even includes a function that can convert all formulas to fixed values!) When the spreadsheet is updated with new input values the results will be wrong. Errors of this nature can be difficult to detect and correct. The consequences may be serious if the organization is relying on report results to make important decisions.
When Excel workbooks are shared across a team each user may choose to make adjustments to some of the worksheets, resulting in multiple versions circulating within the organization. At the very least this creates confusion and extra work to reconcile conflicting data.
THEN WHAT? When the limitations of Excel start to appear the search for alternatives begins. Special-purpose software for performing complex tasks like consolidations, budgeting and account reconciliation may be attractive. The advantage of this approach is that these applications are designed to overcome the inherent limitation of spreadsheets. The downside is that they can be costly and time consuming to implement. They may require specialised training and skillsets to fully realize all the associated benefits. While this may be acceptable to large enterprises this strategy can impose an unnecessary burden on small to medium sized business.
Another way to overcome the limitations of Excel is to become a programmer. There are two open-source programming languages, R and Python, that are popular for serious data analysis work. While they can scale up beyond the limitations imposed by Excel they require a degree of discipline and effort that is challenging for finance professionals already carrying a large workload. And they don’t overcome all the limitations imposed by Excel.
A third avenue is to adopt some of the more advanced general-purpose data analysis tools such as Alteryx. In our next post we will explain how Alteryx can work with Excel while also overcoming many of the limitations of Excel.
Finance managers share responsibility for ensuring that all assets in the organization are delivering full value. As intangible assets become more critical to organizational success it is more challenging than ever to measure and manage those invisible resources that are seldom represented on the balance sheet or tracked in financial accounts.
INVISIBLE ASSETS Intangible assets are not all managed with the same level of attention. Human capital is managed with the guidance of the human resources department and brand assets are the responsibility of the marketing department. However, in many organizations, the invisible asset class known as data has no clear owner or champion. All too often information technology managers are more interested in technology than information. They take responsibility for the processing, safekeeping and distribution of data but will defer to other departments on issues related to data ownership, data quality, privacy and access. As a result, who owns the data can be unclear. This split responsibility can result in inconsistent policies and missed opportunities when it comes to the full realization of value from data.
Why is this an issue? A constellation of forces are at work. The volume of data being created is exploding, doubling globally every year. Data is no longer stored in one place but resides across the organization and up and down supply chains. Organizational data is in data centres but also in the cloud, on desktops and on mobile devices. Moreover, relevant third-party data that can enhance the value of internal data is available for free or to purchase. With so much data accumulating, many organizations have failed to keep pace.
VALUE PROPOSITION Yet many leading organizations have recognized the competitive value inherent in their data. They have exploited recent advances in data analytics and data science. They have applied machine learning and artificial intelligence to define new products and services. More than just reporting historical performance data, these organizations are predicting the future, optimizing operations to deal with continuous change and developing innovative offerings based on newfound insights from previously ignored data.
DATA AS INVESTMENT What are the keys to driving value from data? Data needs to be managed as rigorously as traditional asset classes like cash, receivables and fixed assets. Responsibility for data governance and custody needs to be assigned, with appropriate metrics and rewards for managing the performance of the data custodians. New investments in data, including efforts to improve data quality and usability needs to be part of overall investment planning. Since not all data initiatives deliver the same yield, so potential rewards and associated risks should inform all data investment decisions.
THE TALENT FACTOR As part of any strategy to enhance yields from data, the organization needs to consider investing in new talent with roles such as data analyst, data architect, data engineer and data scientist. Beyond traditional spreadsheet and business intelligence systems these new team members will be most productive when equipped with more advanced tools for data exploration, data visualization and self-service data analytics. Managing these new resources falls to managers who understand this work and can drive meaningful change. Business analysts will also be critical to ensuring that teams focus on the most promising opportunities. The key is teamwork.
For smaller organizations the stakes are higher as they have less tolerance for experiments that might fail. They are more vulnerable to the larger players in their industry and to disruptions from outside. The path forward demands increased management education and discipline as well as support from experienced outside advisors. As with all organizations, there are no silver bullets and the pitfalls are numerous, particularly when it comes to translating insights into action.
Professional accountants and responsible finance managers share a bias tends towards control, cost containment and cost reduction. While this is familiar ground and a valuable place to begin, realizing significant value from data more often comes from product and service innovations. This means having a clear understanding of customer needs so that data insights can be used to shape and enhance customer experiences. SETTING PRIORITIES Where to start? There are some obvious steps to take. Tackle your data governance challenges first. Establish data custodianship, define data quality standards and privacy policies. Take a full inventory of the organization’s data assets. Learn where in the organization work on analytics is playing a meaningful role and find the data analytics champions. In many organizations the most sophisticated talent can be found in the marketing department, but they could be anywhere. Research your industry to understand how data is shaping the competitive landscape and driving innovation. Finally, educate yourself on the fundamentals of managing data for value. With this preparation you are ready to mobilize the organization to drive value from data. An earlier version of this article was published in 2019 by the Chartered Professional Accountants of British Columbia.