PowerPivot for Tax Pros can be a fantastic foray into the world of business intelligence and analytics for tax.? If you haven?t heard of it or haven?t been using it, this Microsoft Excel ?add-in? solution brings your regular Excel spreadsheets and pivot tables to the next level and truly is in the category of business intelligence (BI) tools.? A lot of companies already have many analytics options purchased and in use within IT, Finance, Marketing, or for the enterprise as a whole.? However, very few tax pros are leveraging the power of these tools sets and the ability to slice and dice large amounts of data in a multi-dimensional way.
So, what is Excel PowerPivot? It?s a?FREE?add-in for Excel 2010 designed by Microsoft. It adds a lot of power to Excel by extending the capabilities of PivotTable and data summarization (data layering, consolidation, etc.). It also allows tax pros to import data from multiple sources such as Excel, Access, SQL Server, SharePoint, and many other databases you might need to tap into for your provision and compliance cycles. Designed to add and integrate large amounts of data in Excel workbooks, this new solution can create remarkably dynamic reports. Further, if rumors are true, Microsoft may be building this functionality into the next baseline version of Excel.
So, why would you want to use this solution versus regular Excel today (and regular pivot table functionality)?? ?
- Tax Pros Can Combine?Data From Different Sources & Build Relationships
This has to be one of the most important features in PowerPivot for Excel. Excel has always been able to connect to different data sources such as SQL Server, XML, Microsoft Access and even web based data.? However, it was challenging to create various relationships between data across different data sources. You were able to do this VLOOKUPs to ?join? data, and/or via the use of other third party add ins. Tax pros need to do these ?joins? every day. ?With the need to tax sensitize entity data structures as well as account structures and details, these relationships need to be created easily, be flexible and changeable.? Tax pros also need to reduce the amount of possible error in creating and maintaining these relationships.? Also, ?regular Excel can be ?impractical for large data sets while PowerPivot for Excel is built to accommodate very large data sets as it does not have the 1,048,000 row limit of Excel.
- Tax Pros Can Create Complex and ?Sexy? Analytical Visual Models
PowerPivot for Excel allows you to display a variety of visual data to your Excel worksheets. You can return data in a PivotTable, PivotChart, Chart and Table (horizontal and vertical), Two Charts (horizontal and vertical), Four Charts and a Flattened PivotTable. You can even create a worksheet that has many outputs!? This provides a dashboard view of the data that makes analysis really easy. So, for example, you might have a provision spread that shows the prior and current years and have another dimension for business units, single entities and legal entity structures.? You could run multiple trend analysis as well as current year profiles across operating units versus legal entities.?These dashboards can also be built so that they are dynamic and interactive, so that you can change the view or way you are seeing the data at the click of a button.? You can use ?Slicers? to quickly visually filter data.
- Tax Pros Can Use DAX to Create Calculated Fields for Slicing and Dicing Data
DAX stands for Data Analysis Expressions and is the formula language used in PowerPivot tables. These are similar to Excel formulas, and can create calculated columns. You can create date or YEAR slicers to filter easily by tax year, calendar year, quarter, month or day.
I could go on and on?but, I thought I?d share this great YouTube instruction video on how to get started. ?It?s great. ?Check it out!
I will try to share more BI tips and tricks as this is a hot topic for tax pros. ?If you are not leveraging some sort of business intelligence solution today in your work, you are missing out on some amazing power and functionality! ?I would love to hear what else you are using. Perhaps Cognos, Tableau, SAP Business Intelligence, or other solution sets ? ?Share what is working and what isn?t!
I will try to share more BI tips and tricks as this is a hot topic for tax pros.? If you are not leveraging some sort of business intelligence solution today in your work, you are missing out on some amazing power and functionality!? I would love to hear what else you are using. Perhaps Cognos, Tableau, SAP Business Intelligence, or other solution sets ?? Share what is working and what isn?t!