Financial Reporting

Excel and Power Pivot for accountants and finance managers

If you've ever tried creating a financial report using a PivotTable …

then you know it can be frustrating getting accounts and sub-totals where you want them. And forget inserting rows or columns for spacing or to add ratios or commentary; Excel frowns on that.

So how do you lay out your financial or management reports exactly the way you want it?

The answer is Excel Cube Formula Reports driven by Excel Cube Functions.
Balance Sheet using a PivotTable
Balance Sheet in PivotTable
Balance Sheet as an Excel Cube Formula Report
Balance sheet with cube functions
One of Excel's best kept secrets, Cube Functions can be positioned anywhere in a worksheet, just like any other Excel functions. This is a huge benefit for accountants, financial managers, FP&A staff and anyone responsible for building financial and management reports and dashboards that require very specific placement.

And just like Excel functions you leverage your existing Excel skills - no new products to learn.
List of Excel cube functions
Cube function parameters
Despite the name, Excel Cube Functions work with Power Pivot Data Models (Excel 2010/2013/2016) as well as with Microsoft Analysis Service cubes (Excel 2007+). Excel Cube Functions allow you to pull data into a worksheet using functions that, like other Excel functions, can be placed anywhere. You can freely insert/delete/cut/copy/paste; make measures (values) a report filter; customize your reports just how you want them. They retain the live link to the underlying Data Model or OLAP cubes and have parameters (row labels, column headers or any other cells) that determine what data value each cube function should pull.
Unlike a PivotTable, a Cube Formula Report can retrieve data from multiple sources. With a shared database such as Microsoft Analysis Services cubes, when you refresh your worksheet you and other users get the latest updates and it's all the same version of the truth. In addition, using Excel PivotTables and Excel Cube Functions, you can access Oracle® OLAP (using MDX Provider for Oracle OLAP from Simba Technologies) and Teradata® OLAP Connector

Power Pivot models can source data from many different Microsoft and non-Microsoft databases, including tables and ranges in the same workbook the models are created in. So with Excel 2010 - 2016 and Power Pivot you can generate Excel Cube Functions from an Excel range that you add to your Data Model; in desktop Excel 2013 and 2016, you can even do this without the Power Pivot add-in. This is so much easier and more robust than using the GetPivotData function which requires a visible PivotTable to reference. Plus, there is the potential to share this data in SharePoint or in Power BI if you upload and publish your Data Model,

Maintaining your reports (adding additional months or a new region) can be as simple as copying a row or column and changing the label or heading. To repeat, because cube functions are regular Excel functions you can leverage your existing skills and all of Excel's other functionality - no new products to learn or buy.
So what is the best way to start? That's easy if you use PivotTables.

  1. Build a PivotTable from your Data Model if you have one (add your Excel range to the Data Model if you don't) or OLAP cube and click on it.
  2.  Click on the PivotTable Tools - Options or Analyze tab, OLAP Tools, Convert to Formulas, Convert.

Convert to Formulas menu option
But you'll want to build robust and reliable Cube Formula Reports that are easy to use, re-use, maintain and enhance. And you'll want to do this without regular involvement of your IT staff. Excelcraft has Multimedia Training Kits to get you building these reports and dashboards quickly.
Share by: