Learn finance, banking, risk, data science and fintech

Create Financial Dashboards Using Excel PowerPivot

Microsoft Excel has always been the spreadsheet software of choice for organizations around the world to perform financial analysis and generate all kinds of reports. In fact you can safely say that most banks and financial institutions will stop functioning if excel is taken away from them for even one day. Companies use excel both as a database and as a tool to analyse their data. However, when it comes to large data sets and complex analysis, excel falls short. It also gets difficult to maintain and update these spreadsheets; they are highly labour intensive and prone to errors. That’s the reason why many companies are moving towards real business intelligence tools such as SAS and Cognos. If you are on a low budget, these expensive BI tools may not be suitable for you.

There are many of us who would like to continue with excel, if only it was more powerful. Thanks to Microsoft, we now have PowerPivot, an excel add-in, that allows you to work with massive amounts of data within excel, perform a variety of analysis and create powerful dashboards that are second to none.

The best part is that it’s a free add-in that you can download from Microsoft’s website and start using right away. PowerPivot Download Link

In the rest of the article I will highlight some of the features and capabilities of PowerPivot that can help you create powerful dashboards for financial analysis.

  • Work with massive amount of data. Excel allows you to have up to 1 million rows in a single sheet. This is a problem when you have larger data sets. With PowerPivot, you can create special sheets that have no limits in terms of how many rows of data you have. It is not uncommon for people to have over 100 million records in this special sheet. PowerPivot uses an in-memory technology called xVelocity that provides unmatched analytical performance to process billions of rows within seconds. Since it’s an in-memory technology, how much data you can process depends on the RAM you have in your computer.
  • Integrate data from different sources. Excel has always allowed us to integrate data from various sources such as XML, MS Access, and SQL Server. Even though you could get the data, there was no easy way to form relationships between these data sets and perform comprehensive analysis. Of course, you could use LOOKUP functions to reference and join data from different sheets; however, it’s not really practical when working with large data sets. PowerPivot makes this easy. You can import data from any data source, and use its data model capacities to easily form relationships between different data sets. For example you can take a data set from another excel sheet and another data set from SQL server and perform an inner join to combine the data.
  • Create formulas using DAX. DAX stands for Data Analysis Expressions. This is a formula and query language for PowerPivot which is similar to excel formulas and is used to perform calculations. It includes some of the functions that are used in Excel formulas, and additional functions that are designed to work with relational data and perform dynamic aggregation. Unlike excel formulas that work with ranges, DAX functions work with tables and columns. You can perform advanced lookups to related values and related tables. You can also easily perform sophisticated aggregations that generally require knowledge of relational database and OLAP concepts.
  • Create professional dashboards. With the help of PowerPivot, you can create powerful dashboards using just excel. You can perform a whole lot of visual analysis and also use slicer to visually filter the data. These are self-contained apps that can also be published to SharePoint 2010 and accessed by users from a browser. Once an app/dashboard is ready, you can schedule it to refresh itself with the latest data. The dashboard is always updated without even opening it.

This summarizes some of the important features of PowerPivot. If you have any questions, feel free to leave a comment below or start a new thread in the discussion forum.  

Try our courses on Data Science for Finance. JOIN FREE

Leave a Reply

Your email address will not be published. Required fields are marked *

Name *