EXCEL – FUNCTIONS AND FORMULAS

The most important point about Date and Time Functions is that they depend on your Windows or Mac OS system settings!

If dates or times are not recognized when you enter them in Excel, go to Control Panel → Clocks and Regions or Clock, Language, and Region → Region and change the Short Date and Long Date formats to the settings you want (in Windows).

In Mac OS, go to the Apple symbol in the top left of the screen → System Preferences → Language & Region → and select your region there or click “Advanced” to customize the formats; use the Dates and Times panes there to change these settings.

Once you've made sure your date and time inputs are correct, the most useful functions are:

The DATE function is useful for creating unambiguous dates in Excel to avoid problems with different date formats.

You often use EDATE and EOMONTH in financial models to create annual, quarterly, and monthly headers at the tops of schedules:

Data Validation (Alt, A, V, V or Alt, D, L on the PC; no shortcut on the Mac, so go to the Data tab in the ribbon menu) lets you restrict the inputs of certain cells.

For example, you can use it to ensure that the user can enter only 0 or 1 in a certain cell:

You can also use it to allow users to select from a range of values in a drop-down menu, as we do with the scenarios in the Walmart model:

When “Allow” is set to “List,” checking the “In-cell dropdown” box will create the drop-down menu shown above.

Data Validation offers many options: Decimals, Lists, Dates, Times, Text Length, and Custom.

But you should not go crazy with it – it’s pointless to validate every single input cell in the model. Use it for the most important assumptions, and in input boxes where you want to create drop-down menus.

Also, in some versions of Excel, you may not be able to navigate to “Source” cells in other spreadsheets when you select “List,” so you’ll have to type out references to other sheets manually.

We usually use Data Validation to set up the scenario selection drop-down menu, and then INDEX and MATCH, as shown above, to set the numbers based on the selected scenario.

If you want to use less efficient and less flexible functions for scenarios, CHOOSE and OFFSET are good choices.

CHOOSE selects a value or action from a list based on an index number you pass in, such as:

It’s not a great choice for scenarios because you cannot pass in an entire range of cells for the values – you must hard-code them or link to individual cells separated by commas.

OFFSET starts in a specific cell and then moves down a certain number of rows and over a certain number of columns; it can also return a range of cells rather than an individual cell if you specify the height and width:

This function above will start in cell A1, move down 5 rows, move to the right 5 columns, and then retrieve a 3x3 range of cells starting in that top-left corner.

OFFSET is useful because of this ability to return a range of cells, which lets you write formulas such as:

This one will sum up the 4-row x 5-column area starting in cell I33.

OFFSET can be helpful in schedules that show a company’s spending over time and its allocation in each year, such as the one for CapEx and Depreciation below:

If we break down this formula:

The first part after the IF checks to see that the current year in row 49 is the same as or after the one on the left-hand side in column P, meaning that there may be some Depreciation.

Then, the OFFSET function starts in the top-left cell ("% Allocation"), stays in the same row, and then moves over the appropriate number of columns for the year we're in.

In row 1 and column 3, we're in Year #3 for the Year #1 spending. This formula becomes T$50 – ($P53 – $P$53), or 3 – 0, so we move over 3 columns.

In row 3 and column 4, we're in Year #4 for the Year #3 spending. This formula becomes: U$50 – ($P55 – $P$53) or 4 – (2021 – 2019) = 4 – 2 = 2, so we move over 2 columns.

In other words, it finds the relative position for the Depreciation percentage based on the year of the spending and the year we're in.

Besides these schedules, it’s best to avoid OFFSET because:

1) It’s a volatile function, similar to HLOOKUP and VLOOKUP, so any changes elsewhere in the file will cause formulas using OFFSET (and their dependents) to recalculate.

2) It’s difficult to trace and audit formulas when OFFSET returns a range of cells rather than a single cell.

A circular reference means that the input of a cell depends on the output of that same cell.

For a very simple example, let’s say that we set cell Q14 equal to R14 + Q14:

Since Q14 adds itself to determine the output, it’s a circular calculation, and Excel will generate an error unless you’ve enabled iterations (Options → Formulas or Calculation → Enable iterative calculations).

A circular reference could be direct, as it is here, or it could be indirect.

For example, in many financial models, the company’s Interest Income depends on its Cash balance: a higher Cash balance means more Interest Income.

But the Cash balance also depends on the company’s Interest Income – higher Interest Income means a higher Cash balance!

Depending on the model’s setup, that relationship could create a circular reference. You’ll know it when you see text at the bottom of the Excel window that reads “Calculate”:

Circular references come up mostly with Interest Income and Interest Expense in financial models, and in calculations for a company’s implied share price in a DCF.

Some people argue that you should never include circular references in models since they can cause ascading errors and make it very difficult to modify formulas and find errors.

But you will encounter models with circular references, so you must know how to work with them.

The key point is that you must always include a way to DISABLE circular references in your models so that you can edit formulas more easily.

Using the average balances for the Interest on Cash and Debt in models creates circular references because the Ending Balances depend on Interest during the period – but the Interest during the period depends on the Beginning balance and the Ending Balance.

So, you can build in an on/off switch by adding an input cell for “Average Balances” and then checking for the condition in the interest calculations:

If you ever run into a problem with a formula, it’s helpful to trace precedents (inputs or cells that flow into that formula) and trace dependents (outputs or cells that are affected by that formula).

Here are the key commands and shortcuts:

If you’re checking for consistency, it is far more efficient to press Ctrl + [ and Ctrl + ] than to go into each cell individually and press F2 or Ctrl + U.

But the simplest commands here are often the most useful ones: it’s not that helpful to see 157 arrows on the screen pointing in different directions, or to see 38 different cells selected.

Here’s an example of the Ctrl + [ shortcut for Select Direct Precedents used in cell I68:

By contrast, here’s the Shift + Ctrl + { shortcut for Select All Precedents in the same cell:

And then here’s the Trace Precedents command (Alt, M, P in PC/Windows Excel):

When you enter formulas in Excel, you could run into a variety of errors.

For example, maybe you divided a number by 0 (undefined), maybe you entered a function name that doesn’t exist, or maybe you used the wrong inputs for a function.

Here’s a summary of the different types of errors:

You could check for each error separately using functions such as ISNA and ISNUMBER, but the easiest solution is to use the IFERROR function, which checks for all these errors:

=IFERROR(Value or Calculation or Formula, Output If It Produces an Error)

If there is no error, IFERROR will display the output as is; if there is an error, it will display a message that you specify. A few examples from the Walmart model include:

Comments or Notes allow you to explain where a certain input, formula, or calculation came from, or why you’ve set up the model a certain way.

You can press Shift + F2 in both PC and Mac Excel to leave these comments, and you can delete comments by pressing Esc and then Del.

This feature is less useful than it seems; in practice, only one person (usually an Analyst or Associate) “owns” a financial model in investment banking.

If senior bankers want to make edits or changes, they’ll often print out the model or presentation and leave their notes on the printed pages.

Sensitivity tables let you vary the assumptions in a model and look at the output under a range of different outcomes.

All investing is probabilistic because you can’t possibly know what will happen 5, 10, or 15 years into the future – but you can come up with a reasonable set of potential scenarios.

Therefore, sensitivity tables are critical in making investment decisions and advising clients.

Internally in Excel, sensitivity tables are known as “data tables,” and you can access them in the ribbon menu under the Data tab → “What-If Analysis.” In PC/Windows Excel, the shortcut is Alt, D, T or Alt, A, W, T.

A properly set-up and formatted sensitivity table looks like this (taken from the Walmart DCF, where we vary the Discount Rate and Terminal Growth Rate to assess the company’s implied value):

To create this table, you must clear the white area shown above, link to the formula you want to sensitize in the top-left cell (the blank, light-blue one), select the whole area, including the white and light-blue cells, and then enter the row and column inputs:

There are a few important setup rules with sensitivity tables as well:

1)The input variables and output must be on the same spreadsheet as the table. You cannot use assumptions or drivers from other sheets, such as the 3-statement model, in this table.

2)The numbers in the input row and column cannot be linked to or from anything that’s in the model. Start each input row or column with a hard-coded number and then hard-code the rest or make them change by simple percentages or numbers.

3)The row and column inputs and the output must be related in some way. If the inputs do not affect the output, the table will show no changes as you vary the numbers.

4)Set “Workbook Calculation” in Options or Preferences to “Automatic except for data tables” or your spreadsheet will slow down, especially with many tables. You can then press F9 to refresh or update the tables.

5)Enter a direct link to the output you want to sensitize in the top-left-hand corner of the table. And then, select everything and go through the steps shown above. “Row Input Cell” should be a direct link for the input in the top row, and “Column Input Cell” should be a direct link for the input in the left column.

6)You cannot modify individual cells in the table once it has been created. If you want to change something or select different inputs or outputs, you must delete and re-enter the entire table.

You will see sensitivity tables in almost every financial model of intermediate complexity and beyond,so you must know how to use them.

They aren’t that difficult, but many students make mistakes with the points above.

While sensitivity tables are useful for assessing valuations and potential returns and making investment decisions, sometimes, you want a single “answer.”

For example, what’s the most that you could pay for a company and still generate a 20% IRR if you sell it in 5 years?

Or, what’s the maximum a company is worth today if it grows by 10% per year and is worth $500 million in 10 years?

To answer these questions, you can use the Goal Seek feature (Data → What-If Analysis → Goal Seek, or Alt, A, W, G on the PC) and the Solver Add-In (Alt, A, Y3 on the PC).

The Solver Add-In is external, but it comes with Excel and can be enabled quite easily (Options or Preferences → Add-Ins → Manage Excel Add-Ins).

To use either of these, your model cannot contain circular references.

If your model does contain circular references, remove them by changing the formulas to use the Beginning Cash and Debt (for interest), and deleting circular share-price calculations.

You use Goal Seek to answer simple, one-variable questions, such as “What would the purchase price have to be for the IRR to be 15%?” Here it is in Excel:

This one produces $155 as the answer, meaning that the asking price would have to drop to $155 for the IRR to increase to 15%.

Solver allows you to add multiple constraints so you can answer questions such as, “If the maximum cash flow growth rate is 5%, and the maximum apartment sale value is $230, how much would we have to pay upfront to earn a 10% IRR?”

Here are the options Solver gives you to answer this question:

It produces an answer of $188 here:

With this $188, the growth rate is still just 3.0%, and the sale value is $225. If you want to use the maximum values for both of those, you need to change the constraints and use = rather than <=.