CHAPTER 6Functions for Financial Modelling

In this chapter, we will introduce the most useful functions commonly used in financial modelling. Whilst there are hundreds of functions that can be used in a model, I have attempted to list only the ones you are most likely to come across, and that you will find most useful when building models. Please don't stop here, though! There are many more wonderful Excel functions that you will find useful in your financial modelling and business analysis.

AGGREGATION FUNCTIONS

COUNTIF, SUMIF, COUNTIFS, and SUMIFS are very handy functions to know for modelling and are basic knowledge once you start learning DAX, the formula language for Power Pivot. They add or count ranges of data, and count amongst some of my favourite, most frequently used functions.

COUNTIF

COUNTIF is used to count the cells that match specified criteria. For example, let's say you have sold $6,160 worth of different products on a particular day, as shown in Figure 6.1. You can create this sheet yourself or a template can be found, along with the accompanying models to the rest of the screenshots in this book, at www.plumsolutions.com.au/book.

A spreadsheet with 2 tables. The top table has columns for product (column A), customer (column B), and sales (column C). Cell C14 is selected with a value of $6,160. The formula =SUM(C2:C13) is indicated in the formula bar.

FIGURE 6.1 Sales List

You'd like to know how many (in terms of number of products) you've sold of each product: books, CDs, and DVDs. Follow these six steps:

  1. In cell B18, use the Insert Function dialog box to find the COUNTIF ...

Get Using Excel for Business and Financial Modelling, 3rd Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial