Financial Modeling: Functions to Up Your Game

The vast majority of financial modeling can be done with your basic arithmetic operators (+ - x /), but there is also a significant amount of business logic that cannot be easily illustrated without incorporating other functions.

The IF function

Let’s say, for example, that we’ve modeled out an income statement, but we want to add a dividend payment. If we don’t have enough net income available to pay out a dividend (and don’t want to draw funds from our retained earnings), we don’t want to pay a dividend. If we do have enough net income on hand, we would like to pay a $0.10 per share dividend to investors.

This is a perfect place to use Excel’s IF function. The IF function evaluates a certain condition and returns one value if the condition is true and another value if the condition is false. In our case, the function would read as follows:

=IF(net income < dividend payment x # of shares, 0, dividend payment x # of shares)

Specifically, “net income” would reference the cell where net income is calculated. The “dividend payment” would reference a cell that contains the value of the dividend payment, and the “# of shares” would reference a cell the contains the number of shares outstanding.

The “0” input value is the value that is returned if the condition is true. That is, if net income is less than the total amount of dividends that are to be paid, then we won’t pay out a dividend.

Finally, if the statement is not true, and there is enough net income to pay a dividend, we will return the amount of the dividend to be paid (a cell referencing the dividend payment amount multiplied by a cell referencing the number of outstanding shares).

This is just one example of how the IF function might be used in a financial model. This function can be critical for any model that involves conditional logic and is used quite often.

The VLOOKUP and HLOOKUP functions

Now let’s say we’ve created a financial model in which we have a number off assumptions, and we want to create several different scenarios that involve different assumptions. Rather than changing the assumptions each time we want to look at a different scenario, we can create a table of assumptions that include all of our scenarios.

The first column of the table would contain the scenario — scenario 1, scenario 2, etc. The next columns would contain each assumption for the model along with the corresponding values that should be used in each scenario.

To pull these values into our model, we’ll use the VLOOKUP function. Depending on the orientation of your data, you can use the VLOOKUP or HLOOKUP functions to grab data from a table based on a value from the first row or column of the table. Since we put the scenario in the first column of our data, we’ll use the VLOOKUP function because the function will use the vertical orientation of our data to lookup values.

First, we’ll create an assumption cell that will contain our scenario. In this cell, we’ll put “scenario 1” as a placeholder. Next we’ll put the following function of in the assumption cell of each of our assumptions:

=VLOOKUP(scenario, assumption table range, column number of assumption, FALSE)

“Scenario” will refer to the scenario assumption cell we just created. By changing the value in this cell, we will now be able to change the all values for each of our scenarios at once. The “assumption table range” will be a reference to all the cells contained in the assumptions table we created. This will tell the function where to lookup our values.

The “column number” refers to the column of the table that contains the assumption that we want. For example, if you want to pull the assumption from the column right after the scenario column, you would put 2 because it is the second column. The “FALSE” value in the last input for the function refers to the fact that we want an exact match of the value from our scenario assumption cell and the value in the scenario column of our table.

Now we can change the value in our scenario cell and all of our assumptions will automatically update to that scenario. This is just one use of the VLOOKUP and HLOOKUP functions, but it illustrates the concept behind them.

The ability to quickly reference a specific value in a table brings the power of a small database into a spreadsheet and can result in significant efficiencies for financial modeling.

The INDIRECT function

Another function that really opens up doors in financial models and helps to sort data in spreadsheets in general is the INDIRECT function. The INDIRECT function uses values within cells to construct a cell reference. For example, if cell A1 contains the value “B5,” and the cell B5 contains the value “$100,” then the function INDIRECT(A1) would result in the value $100.

At first blush, this function might seem inconsequentially. Why not just reference cell B5 directly? But what if we need to pull values from different worksheets within a model?

Let’s assume we have a model that projects a companies future income statements based on its latest income statement. We want to pull the values from the actual income statement on a separate worksheet tab and use them in our model to forecast new statements.

Each time the company puts out a new financial statement, we would have to copy and paste the new values into our model. To avoid this tedious process, we can use the INDIRECT function to grab the values off the new financial statement just by adding the new statement as a worksheet in our model and changing the value in a single cell.

Here’s how it can work. Create one input cell that would reference your latest financial statement and put “2010” in the cell as a placeholder. For the 2010, financial statement worksheet, make sure the tab is named “2010” (note that using spaces in worksheet names will require you to use single quotes around the name of the worksheet in a formula reference).

Then for each value in our model where we need to reference a value on the actual financial statement, we use the INDIRECT function. Let’s say that the revenue value is contained in cell D8 on the 2010 financial statement worksheet and our input cell for that worksheet is in A5. We would write the following function:

=INDIRECT(A5&"!D8")

Excel will interpret this function as a reference that looks like this:

=2010!D8

This will pull the revenue value on the “2010” worksheet from cell D8. We would use a similar function for all the remaining values that we need to pull, replacing “D8” with the appropriate cell reference on the financial statement worksheet.

Assuming that all financial statements for the company follow the same format, we can now easily update the model for the 2011 financials. We simply copy them into a new worksheet in our model and rename the worksheet “2011.” We then change our input cell to “2011,” and the INDIRECT function will now pull all the values from the “2011” worksheet instead of the “2010.”

There are obviously many ways to go about solving these sorts of problems, but these functions will give a financial modeler some important tools for tackling them. There are many ways that the IF, VLOOKUP/HLOOKUP and INDIRECT functions can be used to pull together data in financial models and other spreadsheet projects. They can even be used together to perform some pretty amazing feats.

Remember that financial modeling is an art form, and you have to think creatively to come up with efficient solutions for modeling financial relationships. The more functions that you’re familiar with, the more creatively and efficiently you can go about finding these solutions.