Principal Cooper Taylor
Share this content

How PowerPivot can help financial reporting

13th Dec 2012
Principal Cooper Taylor
Share this content
AIA

Business intelligence expert Philip Taylor (aka ExcelCrafter) puts the case for using the PowerPivot add-in for management and  financial reporting.

Unlike earlier versions, Excel 2010 offers two approaches to building a PivotTable from a worksheet range:

  • The first is the usual method of clicking in the range and then, on the Insert tab, clicking PivotTable - AccountingWEB’s Excel Zone provides a wealth of resources and tutorials on this technique.
  • The second approach uses the PowerPivot add-in, a free download for Excel 2010 users available from Microsoft: Microsoft SQL Server 2012 PowerPivot for Microsoft Excel 2010.

Early adopters of Excel 2013 will require the Office Professional Plus version to take advantage of the functionality described in this article. Enable the add-in which is automatically installed as part of the Office 2013 installation (named Microsoft Office PowerPivot Add-in for Excel 2013).

Creating a PivotTable with PowerPivot

The steps required for creating a PivotTable using PowerPivot are easier than you might think. Watch this video or follow the instructions below.

Click in the Excel range that holds the data. On the PowerPivot tab, click Create Linked Table (or Add to Data Model in Excel 2013):

CreateLinkedTable with Excel PowerPivot add-in

Tick the box My table has headers and click OK.

After a few seconds, the PowerPivot window will open and display in a table (called Table1 or similar) your data from the Excel range. You can rename this table and you can use the functionality of PowerPivot to merge data from other tables or to use the DAX language to create additional calculated columns. For now let's keep it simple and click on the PivotTable button on the PowerPivot tab; choose to create the PivotTable either in an existing worksheet or a new one:

CreatePivotTable with PowerPivot add-in

The 2010 PowerPivot Field List looks somewhat different from the classic one. It has a Search box, Slicer sections (Slicers can still be added to a classic Excel 2010 PivotTable) but no Defer Layout Update check box for some reason. Also the list of tables and columns is displayed differently (without going into detail). The 2013 version has reverted more to the classic style.

Creating the PivotTable returns you to Excel. Notice that the data range you started out with is now an Excel table that expands and contracts as you add data (by keying or pasting in new rows) or remove data:

Excel 2013 table created with PowerPivot add-in

What’s the advantage of using PowerPivot?

A PivotTable created from PowerPivot data gives you the option (on the PivotTable Tools/Options tab, in the OLAP Tools drop-down) to Convert to Formulas:

ConvertToFormulas

If your report has a report filter, you will be asked if you also want to convert it to a formula. For now say No; you can always click on it, repeat these steps, and convert it later.

The ability to convert a PivotTable to formulas containing Excel cube functions allows you move bits of the report where you want them, just like a regular Excel report. The CUBEVALUE functions retrieve data from PowerPivot based on parameters in the worksheet, typically row labels, column headers and report filters. It is quite easy to add rows or columns later to accommodate new accounts or time periods.

Format a sales report using PowerPivot

You can insert/delete/cut/copy/paste/hide all you want. But your report will still be live-linked to the PowerPivot data model. That is enormously powerful when you are building management reports and dashboards or financial reports that require very specific placement such as profit and loss statements and balance sheets. For this you would need to import your account balances into PowerPivot either directly or by pasting them into a Linked Table.

Modular balance sheet created with PowerPivot

A linked Table can also be used to load reporting journal entries into your PowerPivot model but that is a topic for another article.

Use a linked Table to load journal entries into your PowerPivot model

If you create a PivotTable from a range in the usual way (Insert-PivotTable), then the menu options on the OLAP Tools drop-down will be greyed out and you won’t be able to convert your PivotTable to formulas. While you can use GETPIVOTDATA functions to achieve some flexibility in report placement, these require a visible PivotTable to link to and are not as powerful nor as easy to work with as cube functions.

Using Excel cube functions is just one of several benefits you can derive from getting your data into PowerPivot, either from a worksheet range, from external sources, or both, and using it as your data source for PivotTables and reports.

For further information on PowerPivot, visit the Microsoft PowerPivot page and ExcelZone’s previous tutorials about the add-in.

Philip Taylor is a business intelligence architect/developer who operates an Excel/BI information-and-products web site, www.excelcraft.com

Replies (2)

Please login or register to join the discussion.

avatar
By Excelcrafter
17th Dec 2012 15:11

"So why are so many accountants still fiddling around in Excel?"

That is indeed an excellent question and one frequently discussed both on this site and elsewhere.

George, thank you for your excellent recount of the value of the relational databases as a platform for accounting and financial reporting systems. There are more facts and Dates (pun intended) than I care to admit remembering.

Without getting embroiled in the "fiddling around in Excel question" I would just put forward this view. If you are going to use Excel as an analysis and presentation medium then PowerPivot offers a repository and environment where tables can be related and where "calculated columns", using DAX formulae, are more robust than regular worksheet formulae. Of course errors can still be introduced and this is no single version of the truth. So, as always testing by you and by someone else is essential.

I think that the fiddling around in Excel has a lot to do with the desire to be independent of or less reliant on IT support and not doubt at times has shown itself to be a double-edged sword.

 

Thanks (0)
avatar
By zarathustra
17th Dec 2012 16:41

Why not change your username to...

Phil "the power" Taylor.

Sorry. I'll get me coat.

 

Thanks (1)