Establishing a Competitive Edge as an Excel User in Corporate Finance

FP&A professionals are always cognizant of the basics of competitive Excel. However, it is not always the case that these workers utilize the best approaches and practices to this perennial application. That said, it is crucial for finance workers to understand how not only to master excel for corporate finance professionals, but also to learn how to set themselves apart as proficient Excel users.

Getting More Out of Formulas

1. INDEX/MATCH:

The MATCH function assists in finding the relative position of information in the spreadsheet. For example: if you have a cell that reads “25,” you should set the MATCH function to look for cells with the number 25, and the results will show the cell housing the data that you’re seeking.

The INDEX formula returns the value of a cell, or an array of cells row and column numbers. These functions, together, are useful for parsing numbers in different but similarly arranged data sets – quarterly revenues for different products over the same timeframe, for instance.

2. VLOOKUP and XLOOKUP:

These functions enable you to find content in an Excel table’s cells. While this may seem quite simple, parsing the parameters of the search is fairly involved. You have to build syntax including the range, and set the search for exact or approximate matches, etc. The detail with which you can craft your VLOOKUP search will dictate the detail of the insights you draw from your spreadsheet data, whatever you’re parsing.

XLOOKUP is a functionality similar to VLOOKUP with greater flexibility in returning results. Color coding is a powerful tool to visualize data. This is why you can set columns, cells, and rows to a variety of colors in Excel. Where the real value of color coding lies is that it helps make information clearer and identify trends in data.

For instance, when creating a historical weather data spreadsheet in black-and-white it will look like an opaque block of text that’s difficult to understand. But if you use conditional formatting to set the cells housing higher-than-average temperature values to appear red, lower-than-average temperatures to appear blue and average temperatures to appear green, you can assimilate months and years of weather data at a glance.

3. Data Simulations:

There are many kinds of data simulations. GM may use them in order to forecast net income by predicting structural and purchasing costs; financial planners use it to forecast investment returns, Lilly uses simulations to figure out the optimal plant capacity for each medication it manufactures; etc. etc.

The mathematical technique powering Excel is called the “Monte Carlo simulation.” The algorithm plugs in random numbers for different variables and performs the forecasting function thousands of times to create average probabilities of different numerical outcomes. If you’re hiring someone with “analyst” in their title, they should be able to run these simulations.

4. Pivot Tables and Reporting:

A pivot table is a summary of data from a larger set. The format of the pivot table lets you parse different statistics in different ways, comparing different correlations and relationships among values (“pivoting”). This is handy to draw insights easily from financial or statistical data. Adding pivot charts to your sheets should also be in your Excel expert skill set. To build a bar graph or other similar data visualization, the pivot charts feature dynamically parses the data you’ve set up in a pivot table. As mentioned in the advanced conditional formatting entry above, communicating the importance and context of data in a table can be difficult without some sort of graphic interface.

5. Macros:

Macros are simply a set of commands the user builds and saves to execute automatically whenever the process is needed. For instance, you might want to see all your clients who are past due on their payments at the end of each month. You could set up a macro that includes commands to create a table from your accounts receivable data, limited by date and formatted to show past-due payments in red. Once you’ve set up this macro, you can run it again and again with a single mouse click.

Integrating Advanced Tools

In addition to improving individual skills in Excel formulas as a finance professional, you can improve your productivity in comparison to others in your field by adopting cutting edge technologies. FP&A software has proved to elicit significant improvements in accuracy, efficiency, and insights yielded from data collection in corporate finance. How this relates to Excel us is the fact that there are certain financial software products that serve as a direct complement to Excel use.