Financial Modeling in Excel Being Aware of Advanced Functions and Functionality
Being Aware of Advanced Functions and Functionality
In this chapter, I cover the functions that are most commonly used in financial modeling. If you master this handful of functions, you’ll be able to get started with building and using financial models. As your skills progress, however, you may find that you’re trying to do something that’s difficult to achieve with your existing repertoire of functions. If you find yourself building long and complicated formulas, or it feels like there must be an easier way to do what you’re trying to do, there probably is an easier way to do it. I recommend stopping what you’re doing and if you have access to a mentor or a more experienced modeler, explain what you’re trying to do and see if she can recommend a better way of approach- ing the problem. If you don’t have access to someone you can ask, try to explain your problem in plain English to your favorite search engine and, chances are, you’ll find what you need. If you don’t have any success the first time, try explain- ing the problem a different way.
Say, for example, that you have a list of names, regions, products, and sales as shown in Figure 7-32. You’d like to summarize these numbers to show both regions and products on a dashboard. You think a SUMIF might do the trick, but you’re limited to only one input range.
FIGURE 7-32:
Trying to use the SUMIF function to aggregate with multiple criteria.
So, you open your favorite search engine and search for SUMIF with two ranges. You see a reference to the SUMIFS function. On further research, you discover some online tutorials that you work through carefully, and you discover that this might actually do the trick, as shown in Figure 7-33.
So now, the SUMIFS function is added to your repertoire of functions you can use if and when the situation requires. This is an example of how you can “learn on the job.” By doing a little research, you can learn more advanced tools and functions.
If you’re interested in learning more advanced functions than what I have the space for in this book, I recommend looking into the following functions as a next step:
» COUNTIFS, which is very similar to SUMIFS
» Nesting an INDEX and two MATCH functions to create a more robust lookup than either a VLOOKUP or HLOOKUP
» Payment calculation functions, such as IPMT and PPMT (see Chapter 8 for
more on PMT)
FIGURE 7-33:
The completed
SUMIFS function.
» Date functions such as EDATE and EOMONTH (see Chapter 9 for more on
TODAY)
» The OFFSET function, which can be tricky to follow and audit but seems popular among some modelers
» The INDIRECT function, which is similarly diffi to follow, but can be useful
in certain situations
» Array formulas, particularly TRANSPOSE
Finally, to learn more advanced functions, check out Using Excel for Business Analysis: A Guide to Financial Modeling Fundamentals by yours truly or Microsoft Excel 2016 All-in-One For Dummies by Greg Harvey (both published by Wiley).
