Key Excel Functions in TAS, FDD and M&A / Deal Advisory
Excel is fundamental in financial due diligence (FDD) and other transaction advisory services (TAS), mergers and acquisitions (M&A), and private equity, playing a crucial role in data analysis and financial modeling.
Mastering the key Excel functions is essential for success, especially in the first years of your career. If you're already involved in deal advisory, preparing for interviews or simply interested in benchmarking your skills against industry standards, this article is made for you.
Let’s explore the key functions in Excel for transaction/deal advisors.
Work Faster with Accelerate Excel
Simplify your Excel work with the Accelerate ribbon comprising tools for M&A Transaction/Deal Advisory, FDD and other Excel power users.
Core Functions
SUM/SUBTOTAL
The SUM function is the most fundamental tool in data analysis and financial modeling. You’ll use it to aggregate line items in financial statements and other breakdowns.
The SUBTOTAL function can be used instead of the SUM function. The key difference between SUM and SUBTOTAL is that SUBTOTAL ignores other SUBTOTAL functions and hence the way you reference ranges is different.
The number 9 here specifies that the SUBTOTAL calculates like a SUM.
Financial Due Diligence Example: SUM function (left) vs. SUBTOTAL function (right) to aggregate nested hierarchy levels
Choosing between SUM or SUBTOTAL is a Matter of Personal Preference
Using SUBTOTAL is much more efficient because you can easily add or remove SUBTOTAL functions without adjusting other SUBTOTAL functions. Unlike the SUM function, each SUBTOTAL is independent of the others. However, this independence means that errors in intermediary SUBTOTALs might be harder to spot.
Our recommendation: If you are free to choose, go with SUBTOTAL for its efficiency. Mistakes can happen with SUM formulas too. Stay diligent and review your work carefully.
SUMIFS
The SUMIFS formula is the standard for conditional summing, ideal aggregation where single or multiple criteria are involved.
Financial Due Diligence Example: SUMIFS to pull data for an income statement / P&L statement
Tip: Even if you have only one criterion, use SUMIFS instead of SUMIF, as SUMIFS can handle single criteria as well and this way you will ensure a consistent syntax. And it will be easier to add additional criteria should you require this later in the project.
INDEX MATCH
The INDEX MATCH formula is the gold standard for lookups. You can do single or multi-criteria horizontally and/or vertically and your formula has a broader Excel version compatibility compared to the newer Xlookup function.
Example: INDEX MATCH to lookup a value
Note: You might think to use INDEX MATCH instead of SUMIFS in the FDD example above. While INDEX MATCH can work, SUMIFS is usually better. That’s because INDEX MATCH only returns the first match, whereas SUMIFS adds up all the values that meet your conditions. If the amounts for a certain period and account are split across multiple rows, INDEX MATCH won’t capture everything.
IFERROR
The IFERROR function in Excel is a tool for handling errors in your formulas. It lets you specify a value when a formula results in an error, basically to replace those #VALUE!, #DIV/0! with something better looking like “n/a” (for not available / not applicable).
Other Key Functions
COUNTIFS
The COUNTIFS function counts the number of cells in a range that meet multiple criteria.
For example: If you have a spreadsheet with a list of clients and their sales figures, you can use COUNTIFS to find how many clients have sales between 0 and 500,000.
LEFT, RIGHT, MID, LEN
The LEFT function extracts a specified number of characters from the start (left side) of a text string. For example:
The RIGHT function extracts a specified number of characters from the end (right side) of a text string.
The MID function extracts a specific number of characters from a text string, starting at a specified position.
The LEN function returns the number of characters in a text string and you would typically use it in combination with LEFT and RIGHT.
UNIQUE
The UNIQUE function can be used to get a dynamic set of unique values, such as account numbers or customers.
Tip: Try it out as the unique function is easier than copying columns and removing duplicates.
IF, AND, OR
Logical functions like IF, AND, and OR can be used for all sorts of things to strip out values based on logical conditions. One typical example where you would use this in FDD is churn analysis.
RANK
The RANK function can be used to determine the rank of a number within a range of numbers. For example, useful to determine the rank of a customer based on revenue.
Adding rank columns allows you to prepare a top customer breakdown based on SUMIFS functions.
If your customer is in a flatfile structure, you would need a RANKIFS. Unfortunately, such an Excel function does not exist and needs a workaround. Here is a great resource to learn more.
AVERAGE
The AVERAGE function can be used (who would have thought) to calculate averages. You’ll mostly use this in relation to Net Working Capital (NWC) analysis to determine trends.
Advanced Functions
Excel’s CUBE Functions
Without going in detail, CUBE formulas (e.g. CUBEMEMBER and CUBEVALUE) relate to PowerPivot and PowerQuery. These formulas pull values from Excel’s Data Model and you can slice and dice them like a PivotTable but without the constraints of a PivotTable. You can use them for a more dynamic setup compared to SUMIFS.
Sounds interesting? Check this out: Excel's Hidden Power: Why CUBE Formulas Beat SUMIFS in Transaction Advisory
INDIRECT
INDIRECT functions can be useful dynamic referencing in complex models. It lets you strip out a cell reference or part of a cell reference from a formula and put it into another cell.
If E4 contains the worksheet name and E156 is the cell reference for EBITDA, this formula will return the EBITDA value.
Example to obtain a value using the INDIRECT function
How Does Accelerate Excel Facilitate the Use of Excel Key Functions?
Accelerate is an additional ribbon in Excel, alongside ‘File’, ‘Start’, ‘Data’ and others. It provides tools to increase efficiency by reducing repetitive tasks and streamlining the steps required to perform various Excel tasks. Below are some tools that relate to the key Excel functions mentioned.
SUM/SUBTOTAL
Create SUM: Quick shortcut to insert a SUM function referencing the current selection. This tool works differently than Excel’s native “Autosum” function. You can conveniently insert the SUM not only next to the reference but anywhere, also on another worksheet. Learn more.
Create SUBTOTAL: If you are a SUBTOTAL user then this one is a no-brainer – I do not need to tell you how annoying it is to type “=subtotal(9,” every time you add a SUBTOTAL. With Accelerate, you select the range to be summed up and hit the tool / a shortcut. Learn more.
C&P Σ (Copy & Paste SUM/SUBTOTAL): Analysis often contain multiple, nested hierarchy layers with SUM or SUBTOTAL on dozens of rows. If you add new columns, you need to get those SUM or SUBTOTAL functions to the new column, which means that you need to copy/paste each row separately. With Accelerate you can just select the range and the tool copy/paste the SUMs or SUBTOTAL functions on a row-by-row basis. Learn more.
INDEX MATCH
Create Index Match: Even for experienced users, INDEX MATCH might be a bit tricky, especially if multiple criteria are involved, as the syntax is quite different from the single-criteria case. With Accelerate, you have a tool that creates the formula based on several range inputs. For the criteria ranges, you do not even need to adjust the full range references as they will be automatically aligned to the INDEX / Lookup range. In the Accelerate tool, you also enter all criteria ranges first and then the criteria cells, reducing the switching between sheets - no big deal but a bit more convenient on a stressful Excel day! Learn more.
Convert Index Match to Direct Cell Link: Index Match is great but sometimes we just prefer normal cell links. With Accelerate, you can convert Index Match formulas to a direct link to the matched cell. Learn more.
SUMIFS
Create Sumifs: Add SUMIFS formulas based on several user inputs. Even if you are an expert on SUMIFS, the convenience this tool provides will make you never want to write a SUMIFS function manually again. Learn more.
Convert SUMIFS to Direct Cell Links: As for Index Match, you can convert SUMIFS into direct cell links to the matched values. Learn more.
IFERROR
With Accelerate, you can easily wrap all formulas in the selection into IFERROR functions. You can specify the value to be shown in case of errors within the add-in’s settings. Learn more.
INDIRECT
Create Indirect Worksheet Reference: The tool converts a direct cell reference into an indirect one by stripping out the worksheet part of the references and putting it into another cell. A handy tool if you generally know about INDIRECT but are not that robust when it comes to syntax. Learn more.
Remove Indirect References: Tool to convert all indirect references to direct ones, removing the indirect functions. Depending on the specific use case, it may be good to remove indirect references to reduce complexity. Learn more.
Conclusion
Mastering these key Excel functions is non-negotiable for anyone serious about a career in FDD/TAS and other M&A/deal-related roles. These skills are the backbone of effective data preparation, analysis and presentation in Excel. By honing your proficiency in these essential Excel functions, you not only enhance your efficiency and accuracy but also position yourself as a valuable asset in the competitive world of financial advisory.
What are some Excel functions you find particularly useful or are there any you're curious to learn more about? Let us know in the comments!