If you are going to use spreadsheets to create financial projections, it's critical to learn how to use the built-in functions to build the formulas to create your forecasts. Investing the time to learn the key functions will not only help you use spreadsheets faster, but it will also open you up to better ways to lay out the structure of a spreadsheet, analyze data, and communicate your analyses.
Everything here refers to Excel, but these same functions are also in available in Google Sheets and can be used the same way.
I don't care about functions per se, but I care about how to use functions to streamline and improve the user experience for the users of the Foresight templates. Here's five functions that I've found to be important to understand for business modeling:
IF
The IF function is fairly easy to understand, and it's a basic building block of many Excel formulas:
= IF (test this, report this if true, report this if false)
Nesting IFs - using IF functions within IF functions, creating a tree of outcomes based on different tests - is another common need when you have to sort through different conditions to figure out how to calculate a number or report a data point.
The key, though, is to remember that a) creating extensive IF tests can be cumbersome and very difficult to understand and explain to users, and b) there are many different ways to do conditional tests to create calculations.
- MIN and MAX can replace many IF statements that involve simple tests of different conditions, and can help make formulas shorter in those scenarios.
- SUMIF, SUMIFS, COUNTIF, COUNTIFS, and even SUMPRODUCT are additional ways to use conditions to do tests for calculations.
The key is to develop an understanding of what tool to use in different situations, and how to lay out the data and use the formulas to accomplish your goals. Spreadsheet design and data layout matters because it underlines what formulas you will have to use to do create your calculations and build your forecasts. I used to create extensive nested IF statements, sometimes creating formulas that worked, but were essentially incomprehensible to users because of the degree of the testing. Learning and mastering SUMPRODUCT, SUMIFS, COUNTIFS, INDEX and MATCH opened up new ways for me to use Excel, and changed how I approached spreadsheets and data analysis.
INDEX
Index is a very useful fonction for returning the value at a given position. You can pass in a range or an array, and the row and column reference to use, and it will return the value in that cell. It's a fairly simple formula, but it's more powerful than the LOOKUP, HLOOKUP, and VLOOKUP functions that we often use for similar purposes.
I use it often to help me use data based on timing inputs, especially in my structures that use monthly cohorts and where I may make repeating purchases, contract lengths, churn, or other behaviors dependent on time. But there are many other ways to use INDEX to find, lookup, and return data that you can use in your formulas; start by reading about Excel INDEX function ›
INDEX and MATCH
Combining the INDEX formula with MATCH changed how I built financial models. Instead of using VLOOKUP and HLOOKUP, INDEX and MATCH provides much more flexibility and power. Instead of me attempting to explain why, start with INDEX and MATCH at Exceljet, it's a great overview of the theory and practical application of the approach and gives practical examples about how you can use it.
SUMIFS
Everybody knows the SUM formula, a simple formula that allows you to sum multiple values. The common usage is to sum a range:
= SUM(A1:D1)
or a list of values:
= SUM(A1,B2,C3)
but you often find yourself needing to do different types of sums, perhaps every nth row, the top n values, and a variety of conditional sums. To learn the Excel SUM function deeper, read Exceljet on Excel SUM function ›
If you're an intermediate user you likely already know SUMIF, a function where you can sum a range based on a condition for it to test, but SUMIFS extends that functionality to let you test multiple critera. Combined wth COUNTIFS (count across a range based on multiple conditions), it's a tremendously useful function to understand to help you create formulas that can be very consistent across ranges of data.
One of the major ways in which I use SUMIFS is in the cost summaries on the Costs sheets in the Foresight templates. Structurally, I used to create the costs forecasts by laying out the different types of costs into groups - structured like a traditional accounting chart of accounts - which works well, but it also makes it harder to add lines or change the structure of the accounts.
In my recent models, I moved to a flat input of costs - one cost each row - but with a number of dropdown selections next to the cost, so I could allocate the costs to different operating areas, to SG&A or COGS, or other characteristics. Then, in using SUMIFS, I'm able to easily create formulas that sum up the costs in different ways, without requiring a lot of SUM links directly to lines, making it simpler, easier to change, and more robust for analytical purposes.
Learn more at Excel SUMIFS Function ›
SUMPRODUCT
SUMPRODUCT is an example of a fairly simple function that has a tremendous amount of versatility embedded in it. It's not just a different way to do a SUM; SUMPRODUCT multiples ranges or arrays together and returns the sum of the products, but doesn't work like a normal array function, and has a lot more power built into it.
After INDEX and MATCH, SUMPRODUCT is the next function that unlocked for me a new way to think about builing the financial model templates.
If you want to learn SUMPRODUCT deeper, start here: Excel SUMPRODUCT Function ›