What data software suits your needs?
Following on from my article about the differing data analysis skills needed in audit versus in business, let’s look at some of the key data analysis products, their functionality and the skills required to use them to maximum effect.
Query instruments
One of the most popular query instruments is PowerQuery, a part of MS Power BI and latest versions of MS Excel. It allows working solely with ‘wizards’ (without-command operations) making it accessible to a user with no programming knowledge. It automates the work with vast amounts of data.
The more traditional instrument for data query is in MS Access. It works with SQL, the most common database query language in the world. However, we can work solely within Access interface (including many handy ‘wizards’ for even better usability) or combine it with SQL if needed.
Access includes Expression builder, which transforms queries into an Excel formula-like expression. It is easy and intuitive and will serve the most basic needs for a database query in a financial analyst’s work.
Visualisation tools
MS Power BI is one of the strongest visualisation tools on the market. It allows you to choose virtually any visual presentation and automatically re-renders all the visuals for you. It also allows third parties’ visuals to be included.
However, the modelling of relational databases (RDBs) should be done first if you are to use the full potential of its interactive visuals. Without modelling, MS Power BI becomes just a shiny (and more expensive) version of MS Excel.
MS Excel offers sufficient visualisation tools for static presentations (to use in MS PowerPoint or as PDF, for example). It offers a more manual, straightforward layout, which is easy to learn. MS Power BI requires more time to learn due to its high level of interactivity.
Relational databases
MS Power BI has an RDB engine, which supports its analytic visualisation tool. However, some employers want to include not only graphs but Excel spreadsheets, which is impossible in MS Power BI. Instead, they can use matrices, which are not based on spreadsheets (consisting of cells) but data tables (consisting of columnar data).
Working with tables requires learning the DAX command language, which is like Excel formulae but for columnar data arrays (which makes it more complex). There are also customised, simplified visual matrices for MS Power BI, which enables users to construct matrices without much (or any) use of DAX. Financial Reporting Matrix by Profitbase is an example of such matrix software.
Search hundreds of roles from all over the world on ACCA Careers
Sign up for a job alert tailored to your desired location and role
PowerPivot is a built-in plug-in for MS Excel, similar to MS Power BI RDB engine. It allows the user to model the relationships between data tables (which are either converted from Excel sheets or queried from external files). It then allows you to construct a power pivot table, which looks like a classic MS Excel’s pivot table, but uses RDB model. You can construct pivot tables from all the data tables uploaded to the RDB engine.
The same DAX language is used in PowerPivot to create complex calculations and custom data columns/rows (called ‘measures’). So the combination of classic MS Excel with PowerQuery and PowerPivot produces a functionality quite similar to MS Power BI.
The benefit of using MS Excel with PowerTools is the ability to use (and present) Excel spreadsheets and formulae simultaneously with DAX, which is simpler to learn compared with MS Power BI (where you don’t have spreadsheets and cannot use Excel formulas at all).
MS Excel + PowerTools is particularly useful for internal working files, where you need to perform complex calculations without caring about visualisation.
MS Access is a classic and time-tested database management system. Unlike MS Power BI, which strives for better visualisation, or Excel, which allows complex workings directly with data, MS Access offers a smooth user experience through user-friendly graphical user interface (via ‘Forms’ and ‘Reports’).
With MS Access you create not just reports, but user-orientated applications. Arguably, MS Access is the closest you can get to a data engineering tool without needing much knowledge of programming. That’s because there’s a branched collection of macros that satisfies the most basic of needs in coding. However, MS Access has a steep learning curve.
Hypothesis testing
Statistical methods are needed for carrying out more advanced analysis. These tend to be required for more senior industry roles. However, beginners can use MS Excel’s statistical formulae. Financial analysts should start experimenting with formulae calculating means, interquartile ranges, standard deviation, correlations, trying to utilise them in their financial analysis in preparation for more senior roles.
Implementation methodology
The above software and tools should be implemented without losing the quality of the data. Analysts can use the ‘Accurate’ model (information must be accurate, complete, cost-beneficial, user-targeted, relevant, authoritative, timely, easy-to-use) to ensure data quality.
Remember that any changes must be done only if information qualities rise substantially, and never solely for the sake of changes.
Author: Yeldar Rakishev ACCA
Read the author's previous article on the differing data analysis skills needed in audit versus in business here
More information
This article was first published in Student Accountant in April 2023 | Get the SA app now