Financial Planning, Budgeting and Forecasting with Excel

Whether or not a spreadsheet is considered the right vehicle to gather planning, budgeting, or forecasting data, it is very commonly used for that very purpose. What is more clear is that Excel workbooks are not the right vehicle for consoliudating nor storing plan data, except for the most simple of business structures.

However Excel does integrate tightly with other tools to provide an excellent and familiar platform for planning applications. Learn more …

Excel and Power Pivot for accountants and finance managers

Excel, Power Pivot, and cube formula reporting can be enormously powerful tools in the hands of accountants and finance managers, Click here to learn more.

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 as an Excel Cube Formula Report

Bal sheet in PivotTable Bal sheet in cube formula report

Excel Cube Functions prompts

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.

Trace cubevalue precedents

Despite the name, Excel Cube Functions work with PowerPivot models (Excel 2010/2013) as well as with Microsoft Analysis Service cubes (Excel 2007+). To learn more about PowerPivot and download the free add-in, click here Excel Cube Functions allow you to pull data into a worksheet using functions that, like other Excel functions, can be placed anywhere. So feel free to insert/delete/cut/copy/paste at will; make measures (values) a report filter; customize your reports just how you want them. They retain the live link to the underlying PowerPivot model or OLAP cube 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

PowerPivot 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/2013 and PowerPivot you can generate Excel Cube Functions from an Excel range. This is so much easier and more robust than using the GetPivotData function which requires a PivotTable to reference. Plus, there is the potential to share this data in SharePoint.

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.

Convert to formulas

So what is the best way to start? That's easy if you use PivotTables.

  1. Build a PivotTable from your PowerPivot model if you have one (turn your Excel range into a Linked Table if you don't) or OLAP cube and click on it.
  2. Click on the PivotTable Tools tab, OLAP Tools, Convert to Formulas, Convert

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:

© 2011-17

Home            Terms of Use            Privacy            Cookies            Contact