TECHNOLOGY

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. 

  • 1
  • 2
  • Next page

Related Articles

New research which examined how organizations are evolving from a traditional...
Despite security worries, adoption of cloud-based financial systems is reaching...
Nearly two-thirds of companies with well-established advanced analytics...
Finance communicates with numbers while operations, sales and marketing, not to...