Close the skill gap with the Financial Modeling & Valuation Analyst (FMVA)® Certification >> Enroll today and save!

Dynamic Financial Analysis

Use Excel formulas and functions in financial analysis

Dynamic Financial Analysis

This guide will teach you how to perform dynamic financial analysis in Excel using advanced formulas and functions.

 

INDEX, MATCH, and INDEX MATCH MATCH Functions

 

1. The INDEX function works similarly as the VLOOKUP function by returning a value in a table based on the intersection of a row and column position within that table. For example, we want to find the net income for 2018 from the Income Statement section. In cell E49, we use the INDEX formula =INDEX(A15:I23,9,6) to look up the 9th row and 6th column in the income statement table to extract the net income value.

 

INDEX Function - Dynamic Financial Statement Analysis

 

2. The MATCH function searches for a specified item in a range of cells and then returns the relative position of the item in the range. For example, we want to find out the row and column where the 2018 net income locates in the Income Statement table. We can use the MATCH formula =MATCH(D49,A15:A23,0) to find the row position and =MATCH(E47,A5:R5,0) to find the column position.

 

MATCH Function - Dynamic Financial Analysis

 

3. Combining the INDEX and MATCH functions, we can create a dynamic formula to search for the value we want from the Income Statement table. For example, we want to look up the revenue amount in 2018.  In cell E48, enter =INDEX($A$15:I23,MATCH($D48,$A$15:$A$23,0),MATCH(E$47,$A$5:$R$5,0)).  You can quickly look up a number in a specific year by changing the blue colored input.

 

MATCH MATCH Function

 

Goal Seek (What-if Analysis)

We can use the goal seek function to find out the results we are looking for.  Suppose we want an NPV of 500 and we want to find out the discount rate. Let’s look at this form of dynamic financial analysis in action.

 

1. Press ALT + A + W + G to open the Goal Seek window. Set cell B42 (NPV) to 500 by changing C42 (discount rate). We then find out the discount rate has to be 25% in order to have an NPV of 500. Input 25% in cell I49.

 

Goal Seek

 

2. Another example of goal seek: Suppose we want to earn a net income of $10 million in 2020 and we want to figure out how much revenue we should earn. Press ALT + A + W + G, set cell H23 (2020 net income) to 10,000 by changing cell H11 (target revenue).  We need to earn $122.7 million in revenue to get $10 million net income. Input 122,700 in cell M49.

 

Goal Seek - Target Revenue

 

Dynamic Totals with INDIRECT and SUM Formulas

Recall from Parts I and II, we used the OFFSET function to create dynamic calculations by linking to some reference cells.  We can perform a similar calculation using the INDIRECT formula along with other formulas such as SUM.  For example, we want to sum up the free cash flows from cell E40 to I40.  INDIRECT replaces the direct linking with the names of those cells.

 

1. In cell D42 to D44, enter start column, end column, and row #. Then in cell E42 to E44, enter E as start column, I as end column, and 40 as row #.

 

INDIRECT Function

 

2. To calculate the sum of FCF, we use the INDIRECT formula =SUM(INDIRECT(E42&E44):INDIRECT(E43&E44)). The formula takes the value from E42 to E44 to find the numbers being summed, making the SUM formula very dynamic.

 

SUM Function

 

CELL Function

Assume that you want to move the free cash flow values to another row. Instead of entering the row number every time the values are moved, you can use the CELL function to locate the values.

 

3. In cell E44, enter the CELL function =CELL(“row”,E40) so that the formula looks up for the FCF row number. You can use this formula to look for other things like file address or format.

 

CELL Function

 

COUNTA Function

The COUNTA function returns all the cells that are not blank and is used to count cells that contain information.

 

4. In cell L42, enter the COUNTA function =COUNTA(E40:R40) to count the number of free cash flows. In a later section, we will see how the COUNTA function combined with other formulas will become a powerful tool in financial analysis.

 

COUNTA Function

 

MID Function

MID function returns a value from a cell.  Say you want to extract the column information from cell E40. You can use the MID function to obtain the “E” value.

 

5. In cell N42, use the CELL function to find the address of the E40 =CELL(“address”,E40). The cell should display “$E$40”.  Now in cell N43, enter the MID function to locate the column =MID(N42,2,1).  This formula tells Excel to look at cell N42 and show one letter or number starting the 2nd  You should see “E” in cell N43.

 

MID Function - Dynamic Financial Analysis

 

6. To combine the MID and CELL functions in one formula, you can simply type =MID(CELL(“address”,E40),2,1). It will give the same value “E”.

 

MID/CELL Functions

 

Combining CELL, COUNTA, MID and OFFSET in a Formula

Now we can combine the four functions to perform a very dynamic FCF calculation.  Recall from the previous section, we combine the MID and CELL function to locate a cell value. We can now use that formula to find out the sum of FCF.

 

7. In cell E42, type the formula that we used in the last section: =MID(CELL(“address”,E40),2,1). Now the start column is correctly shown as “E”.

 

MID/CELL Functions

 

8. In cell E43, copy and paste the formula from E42. We need to make a little change to the formula so Excel picks up the end column “I”. Instead of putting E40 as the cell reference, we use the OFFSET function to locate the 5th FCF by linking to cell I43 (# of period).  The formula should look like =MID(CELL(“address”,OFFSET(E40,0,I43-1)),2,1).

 

MID/CELL/OFFSET Functions

 

9. Excel will now automatically calculate the sum of FCF. You can freely adjust the number of FCFs in row 40.

 

FCF

 

Combining IF with AND and OR formulas

We can combine the IF, AND, and OR functions to make a decision based on some criteria.  For example, we would like to make an investment if the IRR is greater or equal to 20% and the NPV is greater or equal to $800,000.

 

10. With all the criterion set up in place, we can use the IF and AND formulas to determine whether we should invest or not. In cell O42, enter =IF(AND(B43>=M42,B42>=M43),M44,M45).  It tells us to invest because it meets both the IRR and NPV criteria.

 

IF Function

 

11. Let’s say we will invest if it meets either one of the two criteria. Instead of the AND function, we will use the OR formula. The formula in cell O42 should look like this: =IF(OR(B43>=M42,B42>=M43),M44,M45).

 

IF/OR Functions

 

Summary of Key Formulas

  • INDEX formula for returning a value: =INDEX(table range, row #, column #)
  • MATCH formula for returning relative position of an item: =MATCH(reference cell, table range, 0)
  • INDEX MATCH MATCH formula to replace VLOOKUP: =INDEX(table range, MATCH(reference cell for row label, table range, 0), MATCH(reference cell for column label, table range, 0))
  • INDIRECT formula to return a reference to a range: =INDIRECT(reference cells)
  • CELL formula to locate the row number of a value: =CELL(“row”, cell #)
  • COUNTA formula to count the number of cells with value: =COUNTA(cell range)
  • MID formula to return a value from a cell: =MID(cell, start position, # values to include)

 

More Resources

Thank you for reading CFI’s guide to dynamic financial analysis. CFI is a global provider of financial analyst certification and career advancement for finance professionals. To learn more and expand your career, explore the additional relevant resources below.

  • Basic Excel Formulas
  • Data Sources in Financial Modeling
  • Excel Shortcuts Overview
  • List of Excel Functions

Free Excel Tutorial

To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

 

Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!