Advanced Excel for Financial Planning and Analysis (FP&A)

Course details

In this course, explore advanced Excel functions and techniques designed specifically for financial planning and analysis (FP&A). The course covers topics from mastering INDEX XMATCH and dynamic array functions such as FILTER, UNIQUE, DROP, and TAKE, to implementing user-defined formulas with LET and LAMBDA, and enhancing dashboards through intuitive controls. Acquire practical, hands-on skills to streamline data extraction, automate forecasts, and build dynamic financial models.

Learning objectives
Master advanced Excel lookup and reference functions to extract and consolidate complex financial data.
Leverage dynamic ranges and array functions to automate rolling forecasts and streamline trend analysis.
Integrate user-defined functions such as LET and LAMBDA to customize FP&A calculations.
Enhance financial reporting by implementing interactive controls and visual elements, such as linked pictures and camera view, for improved user interfaces.
Apply modern Excel tools and external integrations between Power Query, dynamic tables, and dynamic data sets to drive comprehensive financial analysis.

Concepts

Introduction

Advanced Excel for Financial Planning & Analysis (FP&A)

Introduction to Advanced Excel for FP&A

Excel functions for FP&A

Advanced Excel Lookups for FP&A

INDEX and XMATCH for referencing information and extracting data
INDEX and XMATCH for locating recent financial results
SWITCH for toggling between formulas and across data sets
Multidimensional INDEX function lookups across data sets
CHOOSE for toggling between formulas and across data sets

Analyzing and Working with FP&A Models in Excel

Helper rows and helper columns with accounting underlines
OFFSET, TAKE, and DROP for rolling forecasts and trends
Dynamic ranges for financial analysis and presentations

Dynamic Date Functions in Excel

SEQUENCE and dynamic DATE arrays

Advanced Excel for FP&A with User-Defined Custom Functions

Introduction to LAMBDA and LET functions
LAMBDA functions applied to FP&A
LET functions applied to FP&A
Combining user-defined functions for use in FP&A
BYCOL and BYROW for dynamic FP&A calculations

Consolidating FP&A Data in Excel

VSTACK and HSTACK for consolidated reporting
EXPAND for automated calculations on expanding data
CHOOSECOLS and CHOOSEROWS for reporting and analysis
TOCOL and TOROW for reporting and analysis
Summing across worksheets - FP&A modeling techniques

Segmenting FP&A Data in Excel

FILTER, ISNUMBER, and double unary for reporting analysis
SUMIFS and SUMPRODUCT for financial analysis
UNIQUE for monitoring data updates

Enhancing the User Interface and Experience in Excel for FP&A

Enhancing the user interface with check boxes
Enhancing the user experience with form controls
Camera view, dynamic visuals, and linked objects

Conclusion

Next steps for applying advanced Excel in FP&A