In this supplemental bonus lesson, I'm going to tell you about my top five favorite Excel features that help you as a financial modeler. Number five on the list is so subtle that few people know about it, you may not even realize there is a choice. And this feature is called edit in Excel. And it's buried in the Excel options. When this feature is turned on, allows you to double click on any cell and edit the formula as it speaks right in the cell. However, what is the alternative if this feature is not turned on?

Well, this is where it's interesting. To turn it off, you go under File, XML options, go to the advanced section of the XML options, and we're going to turn off this radio button. Allow editing directly itself. Now when we do this, it's going to have an interesting effect in in essence, it's going to allow us to double click on the cells. But this time, instead of allowing us to edit, it's going to take us to the first cell reference it finds in the cell. So for instance, when we double click on F 10, it's going to take us over to the budget worksheet and where that number came from.

So for instance, another way I like to improve the documentation of my financial model is to reference the various line items to the source assumptions and or sub schedules. Let's look at column C, where I've set up a few note references to various assumptions and sub schedules. I've referenced my sales to note one, and you can see that I've inserted a reference in the formula bar. When I double click on this note reference one. It takes me directly down to my sales growth assumptions. This can be an easy way of navigating your way around very large models.

Number four on my top five favorite Excel feature list is grouping, you find the grouping function in the data section of your Excel ribbon. grouping is designed for use with data analysis, in essence, giving you the ability to drill down into subtotals. by revealing hidden lines of data on a worksheet, I use grouping as an alternative to hiding rows and columns. I have a standing policy to never hide rows or columns. And the main reason for this policy is because hidden rows and columns are so easily missed in the review process. So for example, let's say that we want to hide our new column see this reference column from our view.

One way is to use this feature when you right click on a column and hide the column. The problem is there's no notation indicating that there's a hidden column and unless you're consciously looking at the alphabet You'll have no idea that there's possible content, or assumptions or calculations in column C. The alternative is to highlight column C and using this grouping function, hit group. And I'll end up putting this little bracket above column C with a minus sign. When you click on the minus sign, it collapses the column and replaces it with a plus sign. So now there's an indication that there something here that's just been hidden from your view, you can hit the plus sign once again to make it appear. Number three on the list of my five favorite Excel features for the financial analyst is the formula audit function, which you will find in the formula section of your Excel toolbar.

This is a very handy function for tracing and vouching, inputs and outputs of formulas. When your model isn't reconciling this function proves invaluable for hunting down those errors in logic So, to use it, you can highlight a cell that has formulas in it. Click on the trace precedents to figure out which cells feed into that calculation. You can double click on these arrows to jump to the cells of origin. Double click again on the arrow and it takes you back to the cell. If you want to figure out where that cell is feeding further downstream, you click on trace dependence.

Once you're finished reconciling your model, you can remove the arrows by clicking this feature. Number two on the list of my five favorite features of Excel that help financial analysts is that camera and I love the camera. Now, to find the camera you go up to your Quick Access toolbar and click on this little drop down error. Then select more commands. And once you're in here, you'll choose the commands from all commands. And in here, you'll go down to the CS and select the camera and included in the Quick Access Toolbar.

You will now have an icon that looks like this. Now let me tell you how I use this feature. This feature is particularly helpful when you have a complex model with a lot of different worksheets. And you are going to be playing and working in various aspects of the model. But you want to know the impact that the changes you're making have on the consolidated model. In the course we talked about the importance of the current ratio, so let's highlight the current ratio line.

We'll click on our camera to an essence take a picture of that row. Then we're going to jump over to our sub models. And at the top, we're going to paste a picture of that row into this line. You'll notice that I have frozen the pain Have my model so that I my role descriptors and my column headers are locked. Now the consolidated current ratio stays ever present as I'm working on the model company, financial model, so that when I get down to the assumptions, I can change the assumptions. I can see what impact those changes are having on the consolidated current ratio in real time without having to flip back and forth and scroll between the various worksheets.

You can take this snapshot and you can copy it to as many different worksheets as you want. Finally, my number one favorite feature for financial analysts is the x NPV and x IRR feature. Whether you're doing a valuation or a capital budget, you will find that you're very often discounting cash flows to determine net profit In value or internal rate of return. To use the x NPV function, you associate a range of dates with a range of cash flows to calculate x NPV. The same occurs with the ex IRR calculation, associating a range of date with the range of cash flows. Notice the significant impact that this subtle change in assumptions can have on net present value and ex IRR.

So there are a handful of Excel functions that I find helpful I hope you find helpful as well. This barely scratches the surface of the analytical power contained within Excel, but that's an entirely different course. Until next time, I'm Blair cook