Business Modeling and Planning with PowerPivot - Part 1

Overview

Arguably this could be a very short article. PowerPivot does not support write-back of data to the model nor shared access to the model except in read-only mode via SharePoint. 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 PowerPivot does have something to offer. For almost any Excel-related application PowerPivot provides a more robust and arguably less error-prone environment in which to create table relationships and manipulate data than a worksheet does. Even for larger planning projects, PowerPivot may be a good tool for prototyping parts of the initial design.

Part 1 explains some of the pros and cons of using PowerPivot for financial planning. Some of the issues related to this topic pertain to financial and management reporting applications in general. More detailed explanations and examples will be added in subsequent parts.

Linked Tables

Data entered in a range (of modest size, say < 2,000 rows) in an Excel worksheet can be uploaded to a PowerPivot 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 PowerPivot. This can be done by a variety of means including with cell references, VBA macros, copy/paste-special-transpose, none of which is covered here.

Budget data that a user might input in the layout left needs to be transformed into a table like the one on the right.


When you update a Linked Table the whole PowerPivot table is updated; you can't append data to what is already there (unlike Paste-Append when manually pasting data into PowerPivot). 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. If you import the data from Excel workbooks it is possible to import each business unit budget into a separate table which can be related to a consolidated table, as will be explained in more detail in a subsequent part.If these tables are related to the right dimension or lookup tables then they can be analyzed with one PivotTable. More on this in Part 5.

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 Form data table Wide form data table

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) acounts 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, Value. Part 2 explains the benefits and drawbacks of these two forms.

© 2011-17 Excelcraft.com

Home            Terms of Use            Privacy            Cookies            Contact