SPECIALISTS IN FINANCIAL MODELLING

Collaboration is an inevitable feature of today’s working environment, and financial modellers are no exception. In the interests of building good working relationships and reliable outcomes when building a financial model, it’s important to cultivate processes that take Excel version compatibility into account.

When you are building financial models or spreadsheets that others will need to use, the sensible starting point is to find out which version of Excel they’re using and check for known incompatibilities. Creating a model containing a TreeMap chart, for example, simply will not show up at all in any version of Excel prior to 2016 except as a white, blank square where the chart should be. Obviously, you’ll save yourself some trouble when building a model if you don’t include features that are not supported in earlier versions of Excel which may be utilised by others. The problem is that we don’t always know which features are included in which version.

The biggest culprits are often new functions. It’s very frustrating to build a model with a TEXTJOIN function, only to discover that the model user sees a #N/A error instead of the function result. I can tell you from personal experience that the alternative to a TEXTJOIN can be a very long CONCATENATE or ampersand (&) solution which is not much fun to build.

Excel Version Compatibility

To help you avoid the pitfalls of mismatching models, I have compiled a list of some of the features that were introduced in various versions over the past decade. It’s impossible to be comprehensive without writing a book on the subject, but what I have come up with is a list of the most notable features introduced in each version. I have placed an asterisk next to those which are not compatible with prior versions and which can cause serious problems and undermine the functionality of the model when opened in a previous version of Excel. These most troublesome features are itemised separately at the end of this article.

Excel 2019/365

The following features are being introduced in Excel 2019 or to those with an Office 365 subscription:

  • Custom visuals to create chart types (previously only available in Power BI)
  • Full support for SVG graphics and 500 built in icons (that can be used creatively to create infographics & charts)
  • The Insights feature for quick analysis of tables
  • 3D Models
  • Custom functions in JavaScript
  • Excel connects to Flow & Forms
  • Online data types (such as stocks & geography)
  • Map & funnel charts
  • Lots of new functions such as IFS, SWITCH, TEXTJOIN, MAXIFS and MINIFS*

Get more detail on each of these features in this article on What’s new in Excel 2019 or see David Benaim’s vimeo channel on 2019 only Excel features.

It’s also worth noting that Power Query’s name had inexplicably been changed to Get & Transform in 2016 but in now reverting to Power Query 2019.  Halleluiah.  Much simpler.

If you’re a Mac user, then this version has finally embraced some advanced Excel features, like Pivot Charts, a customisable quick access toolbar, slicers and Flash Fill. It’s now mostly equivalent (besides the companion products Power Pivot & Power Query)

Excel 2016

A host of new features were introduced in Excel 2016. Here are some of the most useful:

  • The Forecast Sheet uses new functions such as FORECAST.ETS which uses an exponential triple smoothing algorithm*
  • Additional chart types such as Waterfall Charts, Histograms, Pareto, Box Plots (Box & Whisker), TreeMap and Sunburst*
  • Better Analysis in Pivot Tables & Pivot Charts. Pivot tables have automatic grouping and new plus & minus buttons
  • Power Query (Get & Transform) is now on the Data Tab so no need to install it

Excel 2013

There was an extensive features and functions update for 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 column chart on two separate axes) which doesn’t cause compatibility issues.
  • PDURATION() returns the number of investment periods required for the invested amount 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 pivot charts
  • 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
  • Sparklines*
  • Slicers in PivotTables*
  • The AGGREGATE function
  • An overhaul of Statistical functions

*Not compatible with previous versions

So which features do I need to worry about?

You don’t need to worry about avoiding any of them 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 IFS or SWITCH to your heart’s content. But if you have cause for concern regarding compatibility then these are the features you need to be aware of, and possibly avoid, depending on the version of Excel each participant is using:

Version Feature
Excel 2019/365 – Custom visuals
– Map charts
– New Functions such as:

  • IFS
  • SWITCH
  • TEXTJOIN
  • MAXIFS
  • MINIFS
Excel 2016 – Forecast Sheet (because of the functions used such as FORECAST.ETS)
– New Chart types such as:

  • Waterfall Charts
  • Histograms
  • Pareto
  • Box & Whisker
  • TreeMap
  • Sunburst
Excel 2013 – Slicers were introduced to tables as well as PivotTables
– New Functions such as:

  • PDURATION()
  • IFNA()
  • ISFORMULA()
Excel 2010 – Slicers for PivotTables
– Sparklines

 

Ideally we’d all be working on the latest version of Excel with all the bells and whistles, but back here in the real world the potential for inadvertent non-compatibility remains an ongoing issue. To expand on the above list, please feel free to add your own personal favourites that you think merit a mention.

On a positive note, as more users move towards Excel 365 subscriptions, compatibility issues will become a thing of the past and personally, I can’t wait.