Excel 2019/365    The following features were introduced in Excel 2019 or for those with an Office 365 subscription:

• Custom visuals, such as word clouds, bullet charts, and speedometers, which were previously only available in Power BI. (Note, as with many features of Excel, just because you can doesn't necessarily mean that you should.)
• Microsoft Office now has full SVG graphics support plus the Excel application has 500 built-in icons. These are now supported, which look great on dashboards and infographics.
• The Insights feature has been expanded upon. Click on a table of data and by selecting Insights from the Insert tab, several charts will appear on the right-hand side of the page to give you “insights” into your data. This is probably more of a data analysis tool than one for financial modelling, but still rather handy.
• 3D Models, which are fun, but I struggle to find a use-case for them in financial modelling.
• You can create your own custom functions using JavaScript. It's always been possible to create user-defined functions using VBA, but JavaScript allows for greater interconnection. Again, this is probably not necessary for the garden-variety financial model.
• Excel connects to Flow, which you can use to create automated workflows to automatically collect data or synchronise data sources. This is particularly useful for automating data refreshes for models that need to be constantly updating, such as stock prices or currency exchange rates.
• Excel also connects to Forms, so you can have a nice form user interface, with a very easy-to-use tool, that can be shared through a link.
• Lots of new functions; most notably, IFS, SWITCH, TEXTJOIN, MAXIFS, and MINIFS.
• Map & funnel charts; the latter is just a centred bar chart, but map charts allow you to display data on a map using countries, states, provinces, and even zip codes/postcodes. You can either display numbers as a heat map or colour coded.
• Lots of new features in Excel's ground-breaking Power Query data cleansing feature, including parameters, conditional columns, and new transformations. Note also the name “Power Query” was changed to Get & Transform for Excel 2016 but has now reverted to Power Query, presumably to fit with the rest of the “Power suite”: Power Pivot, Power BI, etc.
• Multiple users can edit at the same time with co-authoring if a file is stored on SharePoint or OneDrive.
• Multiple comments can be made in a cell with threaded comments. See the section “In-Cell Comments” in Chapter 3 for more information.
• If you regularly change your preferences for PivotTables, you can now assign a default behaviour for PivotTables.
• Previously, cells only contained a single, flat piece of text upon which formatting can be applied. With new AI-powered online data types, a cell could have a region or country value from which more information—such as the population, capital city, area, and many more details—can be extracted. The first two data types supported are Geography and Stocks, with more promised.

Note that you must have either Excel 2019 or Excel 365 to use the tools listed above. If a model containing any of these new features is opened in a previous version of Excel, in most cases you'll be able to view the feature, just not make any changes to it. With new functions, however, the formula will simply stop working if opened in a non-compatible version of Excel, which will undermine the functionality of the model—unless viewed using Excel Online. So, if you are building models in Excel that other people with prior versions need to use, then you need to consider the version capabilities as you build.

Excel 2016    Lots of new features were introduced in Excel 2016 and here are some of the most useful:

• Forecast Sheet. The ordinary FORECAST or TREND functions are very useful for forecasting along a linear trend in previous versions of Excel, but if you want to create a forecast based on seasonality, then it's quite a detailed process. The new Forecast Sheet feature makes it very easy to forecast based on historicals, including seasonality, and you can even apply confidence intervals to your forecast. See Chapter 6 for more detail on how to use this tool.
• Additional chart types—such as Waterfall Charts, Histograms, Pareto, Box Plots (Box & Whisker), TreeMap, and Sunburst.
• Better analysis in PivotTables and PivotCharts. PivotTables handle dates and times even better than they did before with automatic grouping, and you can drill down directly into PivotCharts with new plus and minus buttons.
• Power Query (Get & Transform) is now on the Data tab so no need to install it.

Excel 2013    Similarly, here are some of the most useful features that were added in Excel 2013:

• Flash Fill to learn the pattern of the data you're entering and complete it for you.
• Quick Analysis makes it easy to analyse your data with formatting and charts after highlighting with the mouse.
• Chart Recommendations and Customisations; data labelling using “value from cells” and the way that the chart is built changed. The only notable new chart type was the Combo Chart (a chart combining both a line and a column chart on two separate axes), which doesn't cause compatibility issues.
• PDURATION() returns the number of investment periods required for the invested amount to get to the specific value.
• IFNA() allows you to suppress a #N/A error only.
• ISFORMULA() will return the value TRUE if the cell contains a formula.
• FORMULATEXT(); when linked to a cell with a formula, it displays that formula. This can be a useful auditing tool.
• Single window per worksheet allows the same session of Excel to show different files on multiple monitors (my personal favourite).
• New web-based functions help in building links to web services, reading XML content, and connecting with online content.
• You can create relationships between tables for enhanced data analysis without having to consolidate all the information into a single table and create PivotCharts.
• Slicers, previously only available for PivotTables, became available in Excel tables as well.
• A timeline also became available for PivotTable. Similar to a slicer, the timeline allows filtering by dates.
• Power Pivot became a built-in feature of Excel 2013.

Excel 2010    The following features were introduced in Excel 2010:

• Power Pivot was first available as an add-in (see Chapter 1).
• Sparklines (see Chapter 7).
• Slicers in PivotTables (see Chapter 8).
• The AGGREGATE function.
• An overhaul of statistical functions.

Which Features Cause Compatibility Problems?    Below is a summary of commonly used new features which were first introduced in different versions and should be avoided, depending on which version your user has. Of course, you don't need to worry about avoiding any of these if everyone who might possibly ever open the model is using the same version of Excel. If it's a financial model only used internally, and your entire company is on Office 365, then go ahead and use IFS or SWITCH to your heart's content. But if you have cause for concern regarding compatibility, then you should avoid new features which were first introduced in a version more recent than the one your model users are on.

As more users move towards Excel 365 subscriptions, compatibility issues will become less of a problem. For more detailed explanations of the changes between versions and how they impact model building, please see www.plumsolutions.com.au/book to download supplementary material in PDF format. This section also discusses the difference between using Windows and Mac for financial modelling.

This book assumes readers are using Windows Excel 2019, but in most cases, instructions for previous versions and Excel for Mac have also been provided.

Mixed Referencing Exercise

1. Type the table shown in Figure 5.9 into a blank Excel worksheet. 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.

   

2. In cell B2, create a formula, using absolute referencing, that can be dragged across and down the entire block to cell D4 in one action (without editing), hence showing the interest payable for different borrowing amounts at different interest rates.
3. Remember that if you want to anchor the row, the dollar sign goes in front of the row, and to anchor the column, the dollar sign should be in front of the column. Spend a few minutes on this before looking at the answer in the next step, and in Figure 5.10.

   

4. Your formula in cell B2 should look like this: . Copy your formula across and down the block of data so that it looks like Figure 5.10.

Finding, Using, Editing, and Deleting Named Ranges    Clicking on the drop-down arrow next to the name will show all the defined names in the workbook (as shown in Figure 5.12). Clicking on the name will take you directly to the cell(s).

Excel will then move to and select the range. It doesn't matter what sheet you are in when you select the name. This can make finding your way around the named ranges in a model much faster.

Having created a range name, you can now use that name in a formula instead of cell references. For example, if you have created the range name called TotalIncome for the cell containing the total income result, and a range named TotalExpenditure for the cell containing the total expenditure result, you can create a formula (just by typing this text into a cell).

Another example. If you have created the range name Costs for all the cells in a column containing your numerical costs data, you can calculate the total costs using the formula .

A cell does not need to be an input field in order to assign a name to it, although it often is in financial models. The cell can also contain a formula as well as a hardcoded input value.

Named ranges can be useful, but it's not good practice to have too many. They can be confusing, especially if you have not used a consistent naming methodology. It's also quite easy to accidentally name the same cell twice. So, in order to keep the names neat and tidy, you might need to edit or delete them.

Also, if you have added more data to a spreadsheet, and a range name no longer expresses the correct range of cells, you can redefine the named range.

Practical Example 1    Let's look at a practical example of an IF statement in a financial model. In the valuation model shown in Figure 5.18, we have decided that if the value of the deal is greater than the cost of the deal, then we should accept it, otherwise, we should reject it. The syntax is , and because the value is greater than the cost, it will return the decision “Accept”. If any of the inputs in this model change, such that the value becomes lower than the cost, then the formula will automatically show “Reject” instead.

Practical Example 2    Here's another practical example of using an IF statement in a financial model, but this time in a consistent block of data. If you had a list of projects as shown in Figure 5.19, and wanted to show these in a spend schedule, you could use the IF function in cell D3 to do this:

Because mixed referencing has been used, you'll be able to copy the formula in cell D3 all the way across the block of data. You can try this out for yourself by recreating this sheet 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.

Nesting IF and AND Functions    A common nested formula is to nest an IF and an AND function together to test whether or not a certain date or value falls within certain parameters. Let's take a look at a practical example of a useful nested IF formula. 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.

In the example shown in Figure 5.20, we need to forecast the staff costs over the 10-year period, based on start date and end date. There is no BETWEEN function in existence (yet), but we can calculate this with a nested formula www.plumsolutions.com.au/book

Start by evaluating the start date only, ignoring the end date for now. By following these seven steps, let's build an IF statement that will show the cost only if the date in the schedule is greater than, or equal to, the start date of the employee.

1. In cell G7, add the IF function and the result will be $136,800.
2. Add in some cell referencing, so that you'll be able to copy it across and down. Your formula in cell G7 should be .
3. Copy it all the way across and down the range G7:P26.
4. Select a cell and do a spot check by selecting a cell within the block such as cell I10 and using the F2 function key to make sure it's picking up the correct cells, as shown in Figure 5.20.

Now that we've tested and checked this formula and are confident that it's giving the correct results based on the start date, we can go back to cell G7 and add in the end date. We want to test whether the current date (2020) falls between the start date (2020) and the end date (2029), so we can use the AND function to test if 2020 is greater than or equal to 2020 and also less than or equal to 2029:

Of course, we aren't going to hardcode in the dates, so we'll use the cell references instead.

5. Try out this formula, in a blank cell, and you'll get the result TRUE because both of those statements are indeed true:
   
6. Now we can go back to our original formula in cell G7, and replace the G$6>=$E7 part with the entire AND formula in our existing IF statement to give the nested formula:
   
7. Copy the formula all the way across and down the block of data. Your completed sheet should look like Figure 5.21.

Auditing a Nested Formula    With most functions, we recommend the use of the Insert Function dialog box; however, the Insert Function dialog box does not easily work with nested formulas, and as your formulas get more complex, you need to know the syntax of the formula before attempting to nest it. Because an IF statement is one that is very commonly nested, it is important that you know the IF function syntax.

Nesting Multiple IF Statements    Let's take a look at another example of a useful nested IF formula. You can try this out for yourself by recreating this sheet 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. In the pricing table shown in Figure 5.24, the price varies depending on how many items are purchased.

If you bought 63 items, you can see that they would be $10.50 each. If you want to create an automatic calculation for this, you'd need to create a nested formula, starting with the following three steps:

1. Start with a basic IF statement. If your volume is less than or equal to five, the price is $15; otherwise, it's $12 (for now). So your formula in cell B8 would be . With me so far?
2. Once we've got this working, we can make it more complicated. In another cell, say B9, let's do another formula, which will deal with the value if it is greater than five. Create the formula in cell B9.
3. Once we've got both of these working, you can add them together. Go into the formula in cell B9; highlight and copy the whole formula except for the equal sign using a Control+C shortcut. See Figure 5.25.

   

4. Now hit Escape (the copied formula will remain on your clipboard), go back to your formula in cell B8, and replace C4 with the formula you have copied onto the clipboard. Do this by highlighting C4 and pressing Control+V.
5. Your entire formula in cell B8 should now be: .
6. Multiply your price by the volume in cell C8 so we know the total, using the formula .
7. Your sheet should look something like the sheet shown in Figure 5.26.

So, the following rules will apply for the formula in cell B8:

• If cell A8 is less than or equal to 5, the price is $15 each.
• If cell A8 is less than or equal to 50, the price is $12 each.
• If cell A8 is greater than 50, the price is $10.50 each.

Thus, the formula tests cell A1 to see if it is greater than or equal to five. If it is less than five, the formula will stop there and return the first value if true. If it is greater than five, however, it is then tested again to see if it is greater than or equal to 50. If it is, it will stop there and return the second value if true. If it is greater than five and greater than 50, only then will it return the value if false.

IFS Function    An easier way to complete this would be using an IFS function, which was first introduced in Office 365. Rather than breaking it down into several steps, using the IFS function is a much easier way to achieve this result, as it can be done in one step using the Insert Function dialog box. Unlike the nested IF function, where you need to build a separate IF function for each part of the formula, the IFS function allows you to evaluate each section individually within the dialog box:

1. Open up the IFS Insert Function dialog box by searching for it in the Formulas tab, or by typing =IFS( and then using the Control+A shortcut.
2. Evaluate the first part of the statement in the first field. We want to test whether the number of items (63) falls within the first tier, so enter in the Logical_test1 field. If this is true, we want the formula to return the value $15 in cell C3, so enter C3 in the Value_if_true1.
3. Follow the same procedure for the second set of fields by entering in the Logical_test2 field and then C4 in the Value_if_true2.

   In a nested IF statement, we would then just add a “value if else” here, but with the IFS function, you need to give it a third set of criteria to evaluate.

4. Enter in the Logical_test3 field and then C5 in the Value_if_true3 and press OK.
5. The Insert Function dialog box should look like Figure 5.27 and the formula should now be:
   

Note that a nested IF statement or IFS function is an appropriate formula to use in this situation. However, if there were more than three pricing steps in the table, it is recommended that you use a close match VLOOKUP or LOOKUP function instead, as this is a much simpler function than nesting multiple IF statements. (See the section “Building a Tiering Table” in Chapter 9 for how to do this.) Basically, if you find yourself including more than two or three IF statements in a formula, consider whether the formula should be broken into several steps, or whether an alternative, simpler function should be used instead.