The debate rages online in LinkedIn and various other forums on some common questions
- Excel hacks and various tips and tricks
- Is DAX and Power Query the better option as more software migrates to the cloud
- Should finance teams learn coding and use VBA or Python or R to solve reporting challenges
- What is the role of Business Intelligence tools like Tableau
- Machine Learning, Artificial Intelligence and Robotic Process Automation (RPA). How will this affect finance teams in the future? What roles will be become obsolete and be eliminated and what does the future hold? What skills need to be learned to remain relevant in the future?
While the above questions may sound like the world has changed rapidly and without learning how to code, finance professionals may turn obsolete. While skills upgrading is recommended, we would caution against panic and to run out and enroll into a Python coding course. There is still much that can be achieved using Excel just with maximizing the functionality available and using a smarter approach to managing data. Ezee Pte. Ltd., has many clients in the Startup and SME space that struggle with managing their data and reporting needs due to their small size or lack the scale to progress to advanced reporting tools. Hence, they are left with Excel as their primary tool and this blog focuses on this segment and how they can get more out of Excel. It is possible to extend the shelf life of Excel in the organization if it is used wisely with a set of common protocols across the business, and truly leverage some of the advanced capabilities that Excel possesses.
Excel – The one truly indispensable tool for every finance professional
Excel is undoubtedly a very powerful and competent tool for personal productivity enhancement. However, most users utilize approximately 10-15% of the features and capabilities available in Excel due to lack of training and awareness on what Excel can do. Most users learn a few formulas and consider themselves advances users. Even formulas have multiple levels and there are so many creative users that combine multiple formulas to perform magic with numbers.
As one moves from Excel as a personal tool to a shared multiuser document the complications and risks start to increase. First there is the varying level of skills and understanding between various users on how to use the formulas, functions, and best practices on designing data flow. Excel is notoriously easy to delete and destroy formulas which introduces data accuracy errors. Second, many people use excel as a data entry cum reporting tool but without using the right functionality such as Pivot Tables, which delivers a much better output for reporting than using a bunch of sum-if formulas. To be able to use Pivot Tables correctly, the data should be properly structured in the first place which many organizations do not consciously think about.
Excel is clearly not a database and has inherent limitations on how large the data sets can get before the file starts to burst at its seams and starts to either hang or restart or get corrupted. But there are still some actions you can take to use Excel as if it were a database using smart techniques. As the organization gets larger and the data explodes, the need for structured databases becomes more urgent. That is where the individual finance professional starts to realize that the limits of Excel has been breached and the environment has changed favoring the skills of Information Technology professionals that can deliver enterprise-wide solutions.
How to think big when starting off on Excel
Whether you are a startup with fewer than 20 employees or an SME with perhaps less than 200 employees, or even a regional or global MNC, a few aspects remain the same. The primary challenge is to express the financial performance and numbers in operational terms. While Finance owns the finance systems in all aspects, from setup, to data entry to financial reporting and maintenance, the results need to be provided to colleagues in various operational departments at a level that they can understand. Prior to ERP’s, the finance data was separate from the operational data. While a lot of progress has been made to marry the two, the operational data and the resulting metrics is still the domain of the operational teams. Finance then becomes responsible to generate the reports but seldom controls how the data is being managed and tracked by operations. Often an interface is required with niche operational systems and the challenges start to multiply and quickly becomes overwhelming.
The question is often asked – does Finance need to learn coding? Our view is that finance must be sufficiently trained on how to organize data sets in a structured format to enable better reporting. Finance should also understand Relational Database Management Structures to start creating data definition tables that can be used across the company. So how can Finance start off with the right mindset at the outset so that it can work closely with the rest of the business and still be able to scale as the business changes accounting systems, or incorporates more subsidiaries, or morphs into a multi-segment conglomerate.
With a bit of advance preparation and thought, finance teams can start to better integrate and influence the operational teams on the basics of data management so that it facilitates organization requirements. Even with Excel and Google Sheets (which has real time multi-user editing capabilities) finance can drive the definitions of important files that are prepared by others.
One solution that is very effective is to create a single spreadsheet with multiple tabs and common definitions that can be shared across the organization assuming that there is no ERP system in place. Some common items that operational teams may need are:
- Legal entity / Company
- Departments
- Products
- Regions
- Countries
- Customers
- Items
- Chart of Accounts
- Employees
- Functional Roles / Levels
The above are just examples and there could be many more such common definitions that are used across the company depending on how the organization is structured and managed, what the reporting lines are, its complexity or requirements. Once this common language is defined (and it is important to constantly updated and manage the active components) then this becomes a baseline which all teams use to capture and define data.
Once this is done, then it is also important for finance to drive the spreadsheets and create dropdown lists using the definitions for the operations team to use and populate their data in a structured table format. Drop downs promote data consistency and accuracy while also saving time. One of the key weaknesses in many companies is that users like to collect data with fancy formatting. Raw data should be collected in a simple unglamorous flat data file structure. If reports are required, then Pivot Tables should be created from this flat data files. Instead, if data is collected and maintained as TABLES, this it takes on some features of a Relational Database and can be linked to other data for consolidated reporting. Reports should only be generated using Pivot Tables that allow advance slicing and dicing to focus on specific areas for analysis.
Named ranges are also relatively underused. Rather than using named ranges in formulas which immediately identifies the source of the data, the more common practice is to link directly to a few cells on the 30th tab of the excel file which is difficult to remember.
While most excel users are proficient in using formulas like SUM, AVERAGE, IF, SUM IF, VLOOKUP, HLOOKUP and the like, there are a host of other formulas that can make the life of a finance professional easy. For example, I like to use MIN, MAX, INDEX, MATCH LEFT, MID, RIGHT, CONCATENATE and various other formulas a fair bit when working with manipulating data, or preparing schedules for prepaid expenses for example where I need to amortize the amounts in different months based on start and end dates. The possibilities are limitless and excel allows you to nest multiple formulas in a single cell.
With every new version of excel, the ODBC (Online Data Base Connection) capabilities are being upgraded. With Microsoft Power BI Desktop the ability to analyze data from multiple sources is super powerful and DAX formulas can make light work of huge data sets. While some time and effort is required to learn these skills, it can leverage productivity and efficiency in a major way. While Power BI Desktop is a free single user product for larger organizations it may be worthwhile to acquire an enterprise license to plug into various data sources like CRM (hubspot or salesforce), Accounting systems and other operational databases running on the cloud.
What is the outlook for the future?
There are a number of technologies that are becoming increasingly mainstream in recent times. Optical Character Recognition or OCR is finding it’s way as a standard feature to scan Supplier invoices. This automation makes the Accounts Payable function more robust, streamlined and efficient.
Robotic Process Automation or RPA further automates repetitive tasks and the embedded Artificial Intelligence allows the computer to learn and predict the actions required for similar repetitive data encountered in the future. The future will likely see more tasks being automated where perhaps the finance resource needs will shift from pure data entry towards one geared to analyze and derive insights into the business operations.
The global economic and business uncertainty is on the uptrend, with technology driving disruptions in multiple industries, product cycles shrinking, and a clear shift to digital adoption and work from home practices, the need for better Financial Planning, Analysis and Forecasts are becoming increasingly important. This is one area where finance can play a pivotal role. Finance will have to keep in step with the technology revolution, constantly learn new and better ways to improve the speed, quality and accuracy of information generated, play a critical role in planning and future direction in terms of providing decision making support and be a better business partner.
The question is being asked whether finance professionals should be skilled in coding in Python or R – computer languages that enable working with large amounts of data. Our view is that those languages are in a completely different domain and while some awareness is certainly helpful spending the time learning coding will not significantly align with the core finance skillset. In our view, it would be better to partner with an IT professional to define the reporting needs and directionally drive the outputs rather than learn how to code. While coding skills are great to have it may not be necessary for one individual to be proficient in both.
Our view is that there is no single answer to the above questions. The right answer depends on nature and size of the business and the projected growth trajectory of the business. The answer can vary depending on whether the business is a listed company and subject to various regulatory reporting obligations or whether the business is private. The answer also depends on whether the business collects enormous amounts of customer data and needs advanced analysis (leveraging Python or R) to make sense of trends and consumer preferences or whether the customer-base is limited, and personal relationships are more suited to understand and drive the business.
In summary, Excel is not dead…yet! The good news is that help is available. Call Ezee Pte. Ltd., for a free no-obligation discussion on how to solve your current challenges Our consultants can review your objectives and requirements and provide coaching, training, tip’s and tricks and even help to develop a strategy to help you automate routine tasks using Excel and prepare better data to the stakeholders. While keeping an eye out for new technologies and developments, first maximize the potential of Excel before you decide to invest in and focus energy into learning new tools. Some of the actions you can take are:
- Provide some form of basic Excel training, Do’s & Don’ts and Excel best practices
- Provide advanced Excel training to power users and especially finance teams
- Develop a strategy to plug directly into different systems or platforms using ODBC or BI tools rather than generating multiple versions of the data by exporting and importing into excel files. Work off the source data as much as possible to drive reports.
- Upgrade power users to the latest version of Microsoft Office Online which is available as SAAS (Software As A Service) to leverage all the product updates and features