Planning & Analysis

Tips and Trickery related to Financial Planning and Analysis

Excel reports that combine data from Power Pivot and Power BI

 Let's say that you have some budget data in an Excel Data Model. In Power BI (PBI) the corresponding actual historical data has been published by your finance department. You want to conduct some performance analysis between the two data sets.

As you may know, a 2016 enhancement to PBI allows a Data Model published to PBI to be analyzed in Excel 2016 using the aptly-named Analyze in Excel menu option. So how can you mash up your local budget data with your PBI actuals? You can create two PivotTables, one from each source but each has its own set of row labels so it is difficult to get Actual and Budget in adjacent columns with the correct alignment.

Using Excel cube functions you can achieve the required layout and alignment, and use one common slicer to filter data from both data sets as you can see in the visual below. Watch the video.
Combine Data Model and Power BI

Same-store sales

Same-store sales and comparison to prior year

This video shows one way to build your Power Pivot Data Model for easy comparison of current year results to the same period in the prior year. Also explains a method to track year-to-year %age change of same-store sales.

Cumulative-and-monthly balances PivotTable

Handling cumulative GL account balances in Power Pivot

P&L account balances imported from an accounting system/ERP general or nominal ledger are often cumulative, year-to-date balances. This article shows one way to extract monthly values in your Data Model.

Planning assumptions example

Business Modeling and Planning with PowerPivot

How you can add assumptions and modeling capabilities to you Power Pivot Data Model. Read more >

Workbook Data Model icon

Excel 2013/2016 Data Model

 This video will help you learn:
  •     if your version of Excel 2013 has it
  •     some advantages the Data Model offers

Retrieving text in a PivotTable

Slicing and dicing text values in a PivotTable

From time to time you may want to retrieve text (as opposed to numeric) values into a PivotTable to slice-dice-pivot the text. Or you want to get properties/attributes of a dimension member in a PowerPivot model using Excel cube functions. Here is one solution.

Analyzing by QuickBooks Class

Analyzing QuickBooks with Class

How to use Excel and Power Pivot to leverage your use of transaction categorization by Class in QuickBooks data. Read more >

Building Actual vs. Budget comparisons into your Excel dashboards

Excel Power Pivot dashboard
The frequent requirement to compare actual performance to budget can be difficult to achieve with a Pivot Table because Actual and Budget data often come from two different sources. With PowerPivot or the Excel 2013 Data Model you can leverage cube functions to obtain the layout you need. Part 2 explains how you can add sparkline charts and KPI status and trend icons.

Excel CUBEVALUE function that returns no data causes #VALUE error in downstream calculations

 In an Excel Cube Formula report a CUBEVALUE function may sometimes legitimately return no data for that intersection in the database (that product was not sold in that region during that time period). A calculation in the worksheet that refers to the blank CUBEVALUE cell involving +, -, *, / and some other calculations will likely return a #VALUE error. This occurs because the apparently blank CUBEVALUE cell is actually returning a zero-length text value, which causes many numerical calculations to fail. Wrapping your CUBEVALUE functions with IFERROR() that returns zero on error is one way to fix the problem. But there is a somewhat more elegant approach for most situations, that uses a little-known Excel function, N().

The function tool tip describes N as:
Converts non-number value to a number, dates to serial numbers, TRUE to 1, anything else to 0 (zero).
Depending on how many downstream calculations you have pointing to your CUBEVALUE functions you can decide if it is easier to wrap the CUBEVALUE functions in N functions or (if there are a small number of downstream calculations) wrap the components of the cell calculations that reference a CUBEVALUE function. Watch this 3 minute video for more explanation.

Share by: