Advanced Excel Modeling - Power Pivot

Post date: Sep 17, 2018 11:47:58 PM

For the professional modeler Excel's advanced features improve transparency and efficiency while reducing complexity and risk. They also provide greater flexibility, scalability and connectivity. These benefits grow in value as models become more complex and connected to other models, data sources and people.

In the previous lesson we discussed the data model. This lesson uses the data model to provide records to a PivotTable.

Input Tables

Process Tables

Data Model

Slicers

PivotTables

Pivot Charts

Lesson Objectives

At the end of this lesson you will be able to:

    • Create a PivotTable based on the Data Model

This is under construction.

Click this link for the course's start

What is a PivotTable?

From Overview of PivotTables and Pivot Charts:

A PivotTable is an interactive way to quickly summarize large amounts of data. You can use a PivotTable to analyze numerical data in detail, and answer unanticipated questions about your data. A PivotTable is especially designed for:

    • Querying large amounts of data in many user-friendly ways.

    • Subtotaling and aggregating numeric data, summarizing data by categories and subcategories, and creating custom calculations and formulas.

    • Expanding and collapsing levels of data to focus your results, and drilling down to details from the summary data for areas of interest to you.

    • Moving rows to columns or columns to rows (or "pivoting") to see different summaries of the source data.

    • Filtering, sorting, grouping, and conditionally formatting the most useful and interesting subset of data enabling you to focus on just the information you want.

    • Presenting concise, attractive, and annotated online or printed reports.

In this model we are using Power Pivot. Power Pivot combines data and data relationships with DAX functions (which are very similar to Excel functions) and PivotTables.

Power Pivot requires Office 365 (most editions) or standalone Excel 2013 or 2016. See Where is Power Pivot?

How To Create a Data Model Based PivotTable

Once we have a Data Model (see previous lesson) we can use it to create PivotTables that process data from multiple tables and contain calculations (measures) not possible in standard PivotTables. The process of creating is very similar to normal PivotTables once we get passed the start.

    1. We start by clicking the Power Pivot then Manage.

    1. This brings up Power Pivot's mini-app. Use Power Pivot's Ribbon. Click Home tab then PivotTable

    1. Choose where to place the PivotTable then click OK. Drag the fields we want to either the Filters, Columns, Rows or Values boxes (bottom right).

    1. Click the PivotTable Name: box and enter a meaningful name for the PivotTable. I prefer to prefix PivotTables with pvt. If they have just one field then I use that field as the remaining portion of the name. If it uses multiple fields I typically use the first letter in each.

    1. To change the headings simply type over them directly on the PivotTable. To change number formats right click on any number in a column and select the desired number format. Repeat for all numeric columns.

We now have a simple and concise summary of our model's data which our customer can filter and sort without any chance of corrupting our calculations.

Lab

      1. Using Power Pivot's PivotTable icon to create PivotTable pvtFIAdd tblLP[Description] to the PivotTable's Rows box

      2. Add tblProcess[Term] to the Filters box

      3. Add tblProcess[fx Fees] to the Values box.

      4. Add tblProcess[Interest Costs] to the Values box

                1. There is a possibility Excel will choose a function other than Sum for this field. Should that happen go to the Values box and click the dropdown arrow in the Sum of Interest Costs field. Then select Sum in the Summarize Value Field by listbox.

      1. Change the column headings to Loan, Fees, and Interest.

      2. Right click any value in the Fees column then select Number Format from the Pop-up menu and change the number format to 2 decimals (with comma if that is appropriate for your region)

      3. Right click any value in the Interest column and format it the same way.

      4. Gray out the Grand Total row (don't delete it).

      5. Use PivotTable Tools > Analyze > PivotTable Name: and name this pvtFI

      6. Using Power Pivots Pivot icon, create PivotTable pvtPCAdd tblLP[Description] to the PivotTable's Rows box

      7. Add tblProcess[Term] to the Filters box

      8. Add tblProcess[Payment] to the Values box.

      9. Add tblProcess[Total Costs] to the Values box

            1. There is a possibility Excel will choose a function other than Sum for this field. Should that happen go to the Values box and click the dropdown arrow in the Sum of Interest Costs field. Then select Sum in the Summarize Value Field by listbox.

      1. Change the column headings to Loan, Payment, and Costs.

      2. Right click any value in the Payment column then select Number Format from the Pop-up menu and change the number format to 2 decimals (with comma if that is appropriate for your region)

      3. Right click any value in the Costs column and format it the same way.

      4. Gray out the Grand Total row (don't delete it).

      5. Use PivotTable Tools > Analyze > PivotTable Name: and name this pvtPC

Assignments

Quiz

    1. A distinct advantage Excel has over printed reports or PDFs is:

      1. Excel can be displayed on an iPhone

      2. Excel can show graphs

      3. Excel can be interactive

      4. All of the above

      5. None of the above

    3. Customers want summaries. They only want detail when summaries look wrong.

      1. TRUE

      2. FALSE

    2. Multiple PivotTables can be placed over a single data table.

      1. TRUE

      2. FALSE

    2. How does segregating Outputs from Process protect model integrity?

      1. It lets users play with Outputs while protecting formulas from changes

      2. PivotTables are more secure

      3. Most users don't know how to get to the data model.

      4. All of the above

      5. It doesn't.

    2. How do PivotTables improve transparency?

      1. Their source references and formulas are easier to examine

      2. They provide drilldown

      3. All of the above

      4. They don't

    2. How do PivotTables speed development?

      1. They automatically adjust to changing table sizes

      2. It's quicker to drag and drop fields than to create SUMIFS() or SUMPRODUCT()

      3. Changes are amazingly quick

      4. All of the above

      5. They don't

    2. How do PivotTables reduce risk?

      1. They are more advanced

      2. They replace a sea of formulas

      3. They are compiled C++ code

      4. All of the above

      5. They don't

Summary

PivotTables can present large and complex data sets in concise, easy to understand summaries. They reduce model risk by replacing a sea of formulas, improve transparency with drilldown, and speed development. Power Pivot extends the power of PivotTables by including multiple tables into one PivotTable and adding measures that are not possible with regular PivotTables.