Planning and Modeling

Business Modeling and Planning with Power Pivot

Overview

 Arguably this could be a very short article. Power Pivot does not support write-back of data to the Data Model nor shared access to it, except in read-only mode via SharePoint or when uploaded to Power BI. Most business planning applications such as budgeting and forecasting require a collaborative effort and the software to support it. However for an individual user preparing a plan or a numerical business model Power Pivot does have something to offer. For almost any Excel-related application Power Pivot provides a more robust and arguably less error-prone environment in which to create table relationships and manipulate data than does a worksheet. Even for larger planning projects, Power Pivot may be a good tool for prototyping parts of the initial design.

Linked Tables

 Data entered in a range (of modest size, say < 2,000 rows) in an Excel worksheet can be uploaded to a Power Pivot data model using a Linked Table. Any changes made to the range can be quickly applied to the model. This provides a way to enter modeling assumptions such as interest rates, gross margin percentages and the like. You can also load budget data this way but there are some caveats. Commonly the user interface for budget data entry has time periods (months say) across columns. This layout needs to be rotated or transposed to a vertical layout for import into Power Pivot. This can be done by a variety of means, probably the best of which is the Unpivot feature in Power Query /Get and Transform Data.

Budget data that a user might input in the layout left needs to be transformed into a table like the one on the right.
Budget template example
Unpivoted budget linked table
When you update a Linked Table the whole Power Pivot table is updated; you can't append data to what is already there (unlike Paste-Append when manually pasting data into Power Pivot). So this makes it more difficult to load data incrementally; if you receive the budget data from the Elizabethan Restaurant several days after receiving Lizzie's Diner budget, they must be updated together if you use Linked Tables. Again, Power Query comes to the rescue with its ability to import a folder full of budget files, Unpivoting each one if necessary. The redundant effort involved in re-importing the budget data of all business units (including those with unchanged data) is usually negligible using Power Query's automated approach.

Accounts, Measures, and Calculated Columns

When manipulating a table of financial data (this also applies to statistical and other forms of numerical data) in a database or in an Excel worksheet you have some choices as to how to bucket the numbers. What does that mean? Here are two examples of number bucketing:
Narrow number bucketing
Wide number bucketing
On the left, what I call Narrow Form, accounts (Sales, Cost of Sales, Overheads) are treated as a dimension ("Accounts" or similar); on the right (Wide Form) accounts are treated as measures (columns of a worksheet). These are the things you can drag into the Values section of a PivotTable. In the narrow form there is only one measure, namely Value.
The wide form is usually best where you have:

  •     relatively few measures (not a chart of accounts)
  •     there are calculations based of off several measures
  •     there are no chart-of-account style hierarchies except very simple ones created using calculated measures

The narrow form is suitable for financial or accounting applications that involve a chart of accounts. A chart of accounts typically includes a hierarchy in which the number of levels above or below a particular level can vary. In the unbalanced hierarchy below on  from the Microsoft AdventureWorks accounts dimension, Other Assets has no levels below, Property, Plant, Equipment has 1 level below, and Current Assets has up to 2 levels below.
An unbalanced hierarchy
Unbalanced hierarchy
A balanced hierarchy
Balanced hierarchy
 In Power Pivot The DAX language has several functions (such as PATH and PATHITEM) that help you navigate such hierarchies. Alternatively you can create your chart of accounts in Power Pivot as a balanced hierarchy (see above) and repeat the levels as necessary to fill up levels that don't exist in the unbalanced form. Then the members at the bottom of each branch (Cash, Trade Receivables, Other Receivables etc) are all at the same level.

Microsoft Analysis Services Multidimensional supports Calculated Members in addition to Calculated Measures. These are calculations within a dimension such as these:
[Account].[Gross Profit] = [Account].[Sales] - [Account].[Cost of Goods Sold]
Calculated Members are not supported in Power Pivot nor in MSAS Tabular.You can create a measure called [Gross Profit] using a somewhat similar definition but its placement in a PivotTable is restricted to the Values section.

So why the fuss about narrow/wide table formats and balanced/unbalanced hierarchies?

The upshot here is that if you are going to perform some degree of financial planning or modeling in Power Pivot, you should use the wide form of table and use Power Pivot's calculated columns for your modeling calculations and simulations. If you are doing your financial modelling outside Power Pivot and prior to importing the data then the narrow form approach may work better for you.

Adding assumptions to a planning model

You may have some standalone assumptions that you do not expect to change over the life of your plan. Or else you would put them in a Linked Table that included members from your planning time table (perhaps the planning time table itself). Your planning table will likely be less granular (perhaps monthly) than the time table you use to track actuals. But for the moment assume that Sales Tax or VAT rate and Staff Bonus rate are fixed for the planning period. You can add a Linked Table to your model that looks something like this in Excel and Power Pivot:
Assumptions Excel linked table
Assumptions Data Model table
 This table does not have a relationship with any other tables. Rob Collie of PowerPivotPro refers to this as a disconnected table. Here are two ways (there may be more) that you can access within your model the assumption values from your assumption tables:

1. Using a measure
Create a new measure in the measures grid section of your model or use the New Measure (Calculated Field in Excel 2013) button on the Power Pivot tab (active when you are clicked on a PivotTable). This screenshot shows the latter:
It doesn't much matter which aggregation function you use here (MAX, MIN, SUM, etc). There is only one value. You can use this measure in a PivotTable or reference it in your Power Pivot model like this:
The slight downside of referencing a measure from a disconnected table in a PivotTable is you'll likely be prompted with a Relationship may be needed warning as shown on the right. If you click on the Create button, you'll get a message No relationship detected and the warning goes away until the next time. Alternatively unclick the Detect Relationships button on the Power Pivot Home tab.
Create relationship dialog
 2. Using the LOOKUPVALUE() DAX function

This is the method I have used to retrieve the Staff Bonus rate assumption.
LookupValue function
Here the parameters of the function are:
[column to return], [column to search], [value to look for]
and the function supports multiple search columns and search criteria. This does not trigger the Relationship may be needed warning but its downside is that it does not make available a measure that can be used in your PivotTable and reports.

Consolidating Budgets

 If the budgets of each of your business units are consolidated in another system (possibly your budgeting/planning or ERP system) or in a database, then you can import them into Power Pivot for Excel to analyze along with actual data. However the next section looks at a way to input directly and consolidate your budgets using Power Pivot.

As mentioned at the start of this article, Power Pivot for Excel is not well suited for any serious budgeting/planning process; it lacks write-back capability from Excel to the model and is a personal BI tool that, by design, does not support collaboration. For enterprises with more complex needs there are many other budgeting and planning tools available including the Excel-centric ones such as PowerOLAP from PARIS Technologies.

A small business with one or two business units might benefit from entering plan data into a model so that it can be analyzed, compared, and reported on with actual data. The business unit budgets could be consolidated into one table or entered in the model using Excel/Power Pivot Linked Tables (adding Data Tables to the model in 2013+ parlance).

Option 1: Budget data for all business units is in one table

Putting the budget data of all business units in one table requires less maintenance to the model when a business unit is added or removed. But updating a Linked Table is an all or nothing process; the contents of your table in Power Pivot is completely emptied and replaced, when you update from the linked Excel table. You can't input the budget data from one business unit on Monday and incrementally add the budget data from a second business model on Wednesday. You must input the budget data of all business units with each update. This means that tweaking a number in one budget requires that all budgets are reloaded.

As already explained, Power Query makes it easy to import a folder full of budget templates, regardless of whether the template data has changes. Nowadays, this is likely to be the preferred approach,

Option 2: Each business unit has its own budget table
Here you have a Linked Table for each business unit. Each corresponding business unit table in PowerPivot must be separately related to the dimension tables such as time, planning chart of accounts, and business unit. This may be acceptable with 2 or 3 business units that don't change frequently if you prefer not to use Power Query.

The example described below is based around a small restaurant business with 2 units:

  •     The Elizabethan - a cloth napkin restaurant
  •     Lizzie's Diner for cheaper fare

Planning is done by month for sales, cost of sales, and overheads. As already mentioned, budget templates in Excel commonly have time periods on columns like this:
Budget input template
For import to Power Pivot the format needs to be a vertical format like this:
Budget input linked table
 There are numerous ways to get the data into this format but the Power Query add-in for Excel (built into Excel 2016 as Get & Transform Data)) has a wonderful Unpivot capability that can be used to transpose horizontal cross-tab budgets. Choose what works best for you, is easiest to set up and maintain, with the least likelihood of errors. Use Excel totals as a check that all the data in the horizontal (input) table ties to the data in the vertical table.

The two Excel tables above (without data) might reside in a workbook that you copy and send out to the business units to be filled out. Then as they are returned, you paste the data from the second table into you master workbook model.

When updated to the Power Pivot model, Lizzies Budget table looks like this:
Budget input Power Pivot table
 There is a calculated column called Month Key that I'll explain shortly.

In the above screenshot you can see a tab for the Elizabethan Budget table. There are also tables for PlanningMonths, BusinessUnits, and PlanningChartOfAccounts. While you can get away without these tables (users of regular PivotTables have done this for years) these dimension or lookup tables as they are called will provide a much richer schema with which to analyze your data, combine business unit data, compare data with actuals, and create multi-level hierarchies.

In the case of actual data stored daily in your model, you can take advantage of Power Pivot's built in time intelligence to calculate "to-date" aggregations for week, month, quarter, and year as well as do same-period-last-year comparisons. In this schema diagram notice how each business unit budget table on the left is related to the three dimension or lookup tables on the right. If you add another business unit, its budget table must have similar relationships added resulting in a little extra, one-time maintenance.
Budget tables schema
Here is the Excel-side PlanningMonths table and the corresponding Power Pivot-side table. Like the budget tables, the latter also has a Month Key calculated column that I use to relate the tables together. It is also used to specify that months are sorted chronologically and not alphabetically. You can see the DAX formula used to create the key which is a 6-digit integer representing YYYYMM.
Planning months in Excel
Planning months in Data Model
Having a table with columns for months quarters and years allow you to take advantage of Power Pivot's multi-level hierarchies as shown here in the Y-Q-M hierarchy. In this case the name of the level, Quarter say, is the same as the field (Quarter) but need not be:
Year-quarter-month hierarchy
 Here is a very simple PivotTable sourced from the budget data of the two business units:
Example of budget pivot table


Performance Analysis - Comparing Budget Data with Actuals

In this example I have added a (linked) table of daily actual data. As already mentioned, actual data is commonly more detailed than budget. In this case actuals are daily whereas the budget is monthly. However the chart of accounts (sales, cost of sales, and overheads) are the same. In reality it is likely that actuals would be stored in a general/nominal ledger or ERP system by detailed account code at a minimum. I have also added a (linked) table for Time that has days as its most detailed level.
Actuals table
The data diagram looks like the screenshot below. Notice that the Actuals table has relationships to the Business Unit and Planning ChartOfAccounts tables (just as the Budget tables do); instead of being directly related to the PlanningMonth table, Actuals are related to the Time table at the daily level using a Time Key field (more on this shortly). Comparing Actual and Budget is only meaningful at the month level (or quarter or year) since there is no budget data at the daily level. To allow side-by-side Actual vs Budget comparisons you must link the lowest common time level (month) in the Time table to the corresponding level in the PlanningMonths table.
Data schema including Actuals
When creating relationships to a date-related table with day-level detail, I like to use key fields that are Calculated Columns  that contain integers. The DAX functions are almost identical to the Excel functions. In this model:

Time Key =YEAR([Day])*10000+MONTH([Day])*100+DAY([Day])

I use a similar function for the Month Key that relates the Time table to the PlanningMonths table, except that I do this in the Excel table:

Month Key =YEAR(DATEVALUE([Month]))*100+MONTH(DATEVALUE([Month]))

The reason for this is I like to use this key as a Sort By column in PowerPivot. If I create this function in DAX, PowerPivot complains of a circular reference (to the effect that I am trying to sort the source data column (Month) by the results of a function on that column). Excel can handle this but PowerPivot for some reason cannot. So for date 02/17/2014 the Time Key is the integer 20140217 and the Month Key would be 201402. Integers are good for creating relationships between tables because they can result in faster queries, though typically this is more noticeable with larger tables.

So here is an example of a PivotTable that compares Actual (Sales, Cost of Sales, and Overheads) to Budget for any time period in the year/quarter/month (Y-Q-M hierarchy) and for either business unit or both (Ctrl-click on both Lizzie's and Elizabethan slicer buttons).

Note that Actual and Budget are essentially measures, items that can be dragged into the Values section of a PivotTable. In this video, I show an example of adding a Scenario dimension with members Actual and Budget. This offers a bit more analytic flexibility than using measures for Actual and Budget.

I also added some quick-and-dirty conditional formatting. If you want more precise formatting and report placement you can convert the PivotTable to a cube formula report (PivotTable Tools - Options/Analyze tab, click OLAP Tools - Convert to Formulas).

Actual vs Goal analysis

Share by: