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 analyze 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 labor 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. If you have Microsoft Office 2016, the good news is that PowerPivot comes pre-bundled with it so you don't have to download anything.

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 a 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 datasets. 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 the 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 a 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 datasets. PowerPivot makes this easy. You can import data from any data source, and use its data model capacities to easily form relationships between different datasets. 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 the 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.