By Ryan Wade, Principal Consultant at Diesel Analytics
As seasoned accounting professionals, you may have grown used to using tools like Microsoft Excel for your data analysis and reporting needs. You might be an advanced user of Microsoft Excel and have mastered the use of VBA, advanced formulas, and pivot tables to facilitate your analysis. However, you might not be aware of tools that you already own or are freely available to you that could make your data analysis and reporting much more manageable and robust. In this article, I will list several Excel add-ins and some open source software that can add much value to your data analysis. Let’s get started!
Power Query is an Excel add-in that comes pre-installed in Excel 2016 but is also available as a separate add-in in Excel 2013. Many believe that it is the best thing to happen to Excel since pivot tables! Power Query enables you to combine data from multiple sources into one dataset, pivot or unpivot data, and perform complex data transformations all from an easy to use interface! It allows you to efficiently complete tasks that used to require complicated VBA code or was not possible to do at all. Here is a link to a book that will get you from zero to hero reasonably quickly. If you are a visual learner, then you can watch this informative video from Oz du Soleil that introduces you to Power Query.
My new favorite addition to Microsoft Excel, Power Pivot, is also an add-in that has been available in MS Excel since Excel 2010. Power Pivot enables you to work with much bigger datasets by using the Excel Data Model. The Excel Data Model is based on the same columnar database engine that is used by Power BI and SQL Server Analysis Services. You can use Power Pivot to create calculated measures and KPI’s to develop traditional financial reports, time comparison reports, and dynamic financial reports. Here is a link to a book written by Rob Collie that is geared to helping traditional MS Excel users learn how to use Power Pivot. I also provided a link to a video by Cara Clifford that does an excellent job of introducing Power Pivot.
Some built-in data science capabilities in MS Excel enable you to create elementary regression models and do basic forecasting. However, if your goal is to create more robust regression and forecasting models, then you need to use software that is specifically designed to do that. There is premium software available to you like SAS that you can use, but the software is relatively expensive. Fortunately, there are free open source software options that are as good in many respects as the premium options. One of the most popular options is the programming language R, and Microsoft offers an enhanced but free distribution of R named MRO (Microsoft R Open). You can go here to download it for free.
There are over 12,000 packages that are freely available, and many of them are specifically for forecasting. One of them is a popular package developed by Facebook called Prophet. You can go to this link that contains the vignette that describes how to use Prophet for forecasting. The preferred IDE for R is RStudio. RStudio is available for download here.
Right now the hot topic everywhere is data science. Many are recognizing the value of using advanced analytics to get insights into their data. The R programming language is one option, but another one is Python. Python has been gaining popularity over the last few years, and Continuum Analytics developed a free distribution of Python for the data science community called Anaconda. You can go here to download Anaconda. You can go here to watch the first of a short, 4-part video series that introduces you to data science via the Python programming.
One of the things you get when you install Anaconda is a tool called Jupyter Notebook. The Jupyter Notebook is very popular in the data science community because it makes it easy to share your analysis with others. I believe there are many applications in accounting where this feature could be useful. One example is sharing a forecasting model that is complete with annotations, charts, and graphs that explains your model. Here is a link to a video that introduces you to the Jupyter Notebook.
In closing, the tools listed in this article could be valuable additions to your toolkit. Power Query and Power Pivot makes doing traditional tasks and analysis performed in Excel much easier, and they also enable you to do things that were not possible before. Both of these tools are heavily used in Power BI so if you learn them it will significantly flatten your Power BI learning curve. When you need to do forecasting or other forms of advanced analytics, take advantage of MRO or Anaconda. Even though they are free, they are very powerful and are used by large organizations like Facebook and YouTube to fulfill some of their advanced analytics needs.
Ryan wade is a data analytic professional with close to 20 years of experience. You can view his full LinkedIn profile here.