Locked lesson.
About this lesson
An overview of the key functions used for financial modeling in Excel.
Exercise files
There are no related exercise files for this lesson.
Quick reference
Functions Overview
In this lesson we will walk through some of the key functions used in Financial Modeling.
When to use
An overview of some of the key functions most commonly used when developing a financial model in Excel. As a rule of thumb, a formulae should be no longer than your thumb.
Instructions
- Functions within formulae
- General terminology: “=FUNCTION(Argument_1,Argument_2,…)”
- Embedding Functions
- Writing formulae with functions embedded within other functions
- “Staggered Development Principle”
- ALT + ENTER to space out formulae over multiple lines
- A function can contain any or all of the following:
- Equals sign
- Function name
- Parenthesis
- Arguments
- Functions do not all return values, i.e. a number
- Some functions return other types of data including TRUE / FALSE, or a worksheet range
- Everyday functions:
- SUM, AVERAGE, MIN, MAX
- Logic functions:
- IF, AND, OR, NOT
- Other functions:
- CHOOSE, INDEX, MATCH, OFFSET
- Function Tips
- Press ‘F2’ to edit a cell instead of double-clicking on it
- Press ‘Esc’ to exit a cell without making any changes
- Use the ‘fx’ button next to the formula bar to learn how an Excel function works
Login to download
- 00:03 Finally, it's time to actually think about the Excel functions we're
- 00:08 going to use regularly when building financial models.
- 00:12 You maybe surprised for
- 00:14 what I'm actually gonna to cover there, I've got a list of 12.
- 00:18 Other this he doesn't try to speak, to get use commonly.
- 00:21 And they're not just complex or as remote as you might think they might be.
- 00:26 And the ones you might already know possibly because you should keep it
- 00:30 simple, don't go for the show of functions.
- 00:33 Use the ones when you can step them out to apply the rule of thumb.
- 00:37 But before I do that,
- 00:38 I'm just going to have look at functions overview to put everything into context.
- 00:46 So first of all, the difference between functions and formulae,
- 00:49 because people use the two terms interchangeably, it's not true.
- 00:53 The function is the building block of a formula.
- 00:55 So that's the thing that gets capitalized and has arguments and does something.
- 00:59 The formula is the thing, everything, that starts with the equals sign.
- 01:03 Sometimes, you put one function inside another, that's called embedding.
- 01:08 You can do this in Excel 2003 and earlier.
- 01:11 You had a limit of seven times you could do this.
- 01:14 But it's up to 63 times in Excel 2007 onwards.
- 01:19 If you do that, do you remember once you finished it,
- 01:23 to find a big wet fish, and slap your face with it as much as you can because really,
- 01:29 you shouldn't be building formulas like that.
- 01:32 Use the Rule of Thumb.
- 01:34 Step it out.
- 01:35 If you do have to have a longer formula, do remember you can use Alt+Enter,
- 01:39 holding the Alt button down to space out a formulae with multiple lines.
- 01:43 Remember, you can't put extra spaces in because that's the intersect
- 01:48 operator to which we showed when we were looking at range names.
- 01:53 So what is a function then?
- 01:55 Well, most contain an equal sign because they're in there for the calculation.
- 02:00 They'll have the actual function name.
- 02:02 They'll always have brackets, because that's what distinguishes
- 02:05 a function from a range name, but they won't necessarily have arguments.
- 02:10 So, we saw when we were looking before,
- 02:12 the rand function that generates a random number between zero and
- 02:16 one required no arguments whatsoever, just an open and close bracket.
- 02:22 Not all the functions are return values.
- 02:24 Some of them will give you true or
- 02:26 false or other things like text if you code them up correctly.
- 02:30 Another to everyday functions you can use in here as well.
- 02:34 Some of these will be going through in the top 12, some of them are won't.
- 02:38 The fact is some of you don't you recognize.
- 02:41 One of the things we find when we're trying to actually build them on list,
- 02:45 we don't know what we don't know.
- 02:48 There are hundreds of Excel functions out there, and
- 02:51 Microsoft actually claims anyone that knows 30 or
- 02:54 more functions well is probably justified in calling themselves an expert in Excel.
- 03:01 But how do you find the function you want?
- 03:02 You can't go to Excel and say, actually can you find me the one that does?
- 03:07 Cuz Excel help is sophisticated, but it's not quite that good, and
- 03:11 this is where this flow chart comes in.
- 03:13 I find this a very useful flow chart.
- 03:15 All you do is you start at the top and
- 03:17 you answer the question, what do you want to do with your Excel formula?
- 03:20 We then split that up into five categories.
- 03:23 Be it calculate, modify, use functions, user range of data, and data time.
- 03:29 I've highlighted calculating in black because they
- 03:32 tend to get used more regularly, but don't read anything in to it other than that.
- 03:37 And you follow the flow chart through.
- 03:39 Those at the top of the table,
- 03:41 they're used all the time, in the middle used regularly, and those at the bottom,
- 03:45 not so much, but you might the function you're looking for.
- 03:48 So once you see one and say, I wonder if they does it,
- 03:51 you can then use Excel Help, to go and hunt it out.
- 03:54 Talking of which, you've got your function tips.
- 03:58 Yes, when you're typing your formula,
- 04:00 press F2 to edit a cell instead of double-clicking on it helps.
- 04:03 It's safe if you stuff it up.
- 04:05 And if you're not sure what the function does, press the fx button right next to
- 04:09 the actual formula bar to learn how an Excel function works.
- 04:13 So as an example, I presume everyone here knows the BAHTTEXT function.
- 04:19 That's the member of the Simpsons family who's always on the mobile phone or is it?
- 04:23 You could actually click on the effects button and will show you the converts of
- 04:28 numbers into text in the language of box and good old tie.
- 04:32 That's really cool you'll be good if those are in english but that does not exists,
- 04:37 never quite understood what that function that is.
- 04:39 But it gives you idea what resources are available to you
- 04:44 when creating formulas using functions that you are unfamiliar with.
- 04:49 So, which of these functions are you or are you not familiar with?
- 04:53 Let's go through the top 12 functions in a subjective list,
- 04:57 I must confess, the things I would use when building a Financial Model.
Lesson notes are only available for subscribers.