Excel Power Tools for FP&A: Power Query and PowerPivot

Course details

Explore ways you can effectively address the growing need for efficiency, accuracy, and actionable insights in modern FP&A workflows. In this course, Carl Seidman shows you how to connect and automate data imports from various sources into Excel. Find out how Power Query can automate data cleanup, while Power Pivot can help you establish relationships among datasets for insightful analytics. Learn how to leverage DAX to create measures and calculated columns that can handle large data volumes and simplify complex calculations. Plus, discover how to create engaging dashboards using advanced Excel features like KPIs, slicers, timelines, and sets for impactful storytelling. With these skills, you'll be better prepared to lead strategic discussions and drive improvements in your organization’s financial planning and analysis processes.

Learning objectives
Relate the role of Excel’s Power Query and Power Pivot in FP&A to traditional FP&A workflows.
Illustrate how Power Query may be used for data preparation, mastering the user interface, performing common financial data transformations, and automating recurring tasks using applied steps.
Show examples of Power Query for FP&A and business forecasting, driving data into tables for use in dynamic financial models.
Understand the foundations of data modeling with the assistance Power Query by connecting tabular data between datasets and defining data hierarchies for analysis.
Identify how Power Pivot for FP&A enhances data analysis and reporting, contrasting it with traditional PivotTables and data visualization.
Write and apply DAX formulas for advanced analysis in financial planning and analysis applications.

Concepts

0. Introduction

01 - Why do Excel power tools matter for FP&A
02 - What you should know

1. Introduction to Power Query and Power Pivot for FP&A

03 - What does FP&A look like without power tools
04 - Power Query and Power Pivot in modern FP&A workflows
05 - How Power Query and financial systems interact for FP&A
06 - Why Power Pivot matters for FP&A

2. Power Query Navigation

07 - Home ribbon - Core functionality
08 - Transform ribbon - Cleaning and reformatting data
09 - Add Column ribbon - Creating new columns of data
10 - Automating recurring tasks with applied steps
11 - Challenge - Creating a Power Query
12 - Solution - Creating a Power Query

3. Power Query for Financial and Business Modeling

13 - Introduction to dynamic tables
14 - Power Query, dynamic tables, and financial modeling
15 - Challenge - Connect data to a financial model
16 - Solution - Connect data to a financial model

4. Data Modeling and Power Pivot

17 - Introduction to data modeling with Power Query
18 - Establishing tabular relationships and data hierarchies
19 - What Power Pivot is and how to access it
20 - Creating a Power Pivot report
21 - Challenge - Create a Power Pivot report
22 - Solution - Create a Power Pivot report

5. Data Analysis Expressions (DAX)

23 - DAX rules and syntax
24 - DAX calculated columns
25 - DAX measures

6. Power Pivot for FP&A

26 - Power Query and Power Pivot for end-to-end automation
27 - Updating data for FP&A
28 - Writing formulas to perform financial calculations
29 - Designing dynamic financial reports and visual dashboards
30 - Challenge - Application of business scenarios to financial planning and analysis
31 - Solution - Application of business scenarios to financial planning and analysis

Conclusion

32 - Next steps