The Advanced Excel Skills You Need for Financial Planning & Analysis

Job descriptions for financial planning and analysis (FP&A) positions tend to list advanced Excel skills among the requirements. But what are the key skills required?

Let’s look at three broad areas where FP&A might interact with Excel.

FP&A must be fluent in best practices around building models. In Excel, these include separating inputs, calculations and outputs, never hard-coding a formula, and color-coding your variables and parameters

Data and data analysis

There is a running joke in BI communities that the most used feature in any business intelligence solution is the ‘Export to Excel’ button. That is because Excel is flexible, and allows you to immediately review your data and test it by building models off the extract.

FP&A must be fluent in looking at data via pivot tables and data tables, and then sorting and sifting through data using filtering functions. Higher level fluency can be achieved by adding the Power Pivot add-in to your base installation of Excel so that you can, in the word of Microsoft, “mash up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.”

Microsoft has worked hard recently to expand Excel’s ability to obtain and manage data. It has always been easy to create or import a flat text file, such as a CSV or text delimited file, where rows of data are put into a single row separated by a comma, paragraph mark, semicolon or other notation.

Excel has now expanded its “Get External Data” to “Get & Transform” functions in the 2016 version, designed to replicate the typical data vendor’s ETL functionality: extract, transform and load.

In Excel, you can now:  

  • Connect to a data source, starting with a new query to pull data from a file, database, online or cloud source
  • Transform, or perform simple editing functions on your data
  • Combine, or create tables from a single or multiple sources
  • Share, in which you save or send, presumably using Microsoft Office tools

You get extra credit for building regressions to help determine covariance and key drivers.

Building models

The process of building models may start with the very data set just imported. There are sets of functions that FP&A should know to manage the data, whether it is for analysis or application into a model.

Some of these include picking out data points, including HLOOKUP, VLOOKUP, OFFSET, CHOOSE and INDEX, MATCH, COLUMN, ROW, and preparing text and date data using CONCAT and logical  formulas (IF, AND, OR, LEFT, MID, RIGHT).

FP&A must be fluent in best practices around building models. These include separating inputs, calculations and outputs, never hard-coding a formula, and color-coding your variables and parameters to rapidly identify them. 

Then, we should add error-proof checks into the model, and use the formula auditing tools to navigate through it. To enhance collaboration, and since we often share models around the enterprise, we need to add security features to lock sheets or cells, and establish drop-down menus for users.

Advanced modeling also includes using GOAL SEEK from the ribbon bar’s What-If Analysis. Extra credit for getting familiar with the ribbon bar’s Forecast Sheet to automatically make projections based on historical data.

It is clear that we will be using Excel alongside other specialized tools for years to come. It is our responsibility to learn to use it well

Reporting

In many ways, reporting is equal parts art and science. But advanced skills here include the ability to build dashboards, knowing which graph tells the story best, and presenting your information in the clearest way possible – clear message, uncluttered graphs, consistent formatting of font style and size, decimals, alignment, and so on.

The reports and exports should be able to tell a story if they were read by your boss without you there to explain the page.

With 750 million installations of Excel worldwide, it is clear that we will be using this spreadsheet alongside other specialized tools for years to come. It is our responsibility to learn to use Excel well.

About the Author

Bryan Lapidus, FP&A, is a contributing consultant to the Association for Financial Professionals (AFP). This story first appeared on the AFP’s website.

Copyright © 2017 Association for Financial Professionals. All rights reserved.

Suggested Articles

Some of you might have already been aware of the news that Questex—with the aim to focus on event business—will shut down permanently all media brands in Asia…

Some advice for transitioning into an advisory role

Global risks are intensifying but the collective will to tackle them appears to be lacking. Check out this report for areas of concern