I’m just back in the office after attending Excel Summit South here in Sydney last week and I’m still processing it all. For the past few years, some of the Excel MVPs from around the world do a tour Down Under and tell us all about the cutting edge features of our favourite software:) MVP stands for Most Valuable Professional awarded by Microsoft to just a handful of professionals in recognition for their contributions to the Excel community. It’s just a fantastic experience to have two full days surrounded by these experts of the industry. I had a great time catching up with old friends, and making new ones.
One of the Excel MVPs who presented at the Brisbane event is Mynda Treacy and I just wanted to let you know that her free Dashboard webinars have just been re-launched today and will be running for the next ten days. You can take your pick from;
• Creating Excel Dashboards • Dashboards with Power Query & Power Pivot • Power BI Dashboards for Excel Users
The live webinars are only available until 15th August but after that you’ll still be able to watch the replay.
Should
people working in finance, such as financial modellers bother to learn Power
BI? Whilst not a modelling tool, Power BI is incredibly useful for preparing,
crunching and presenting data and there’s no doubt that Microsoft is truly
committed to its place in the market with the speed and frequency of updates
and feature improvements.
When my “excel-lent” friend
Chandoo visited me in Sydney recently prior
to his presentation at the financial
modellers’ meetup group, we had a good chat
about all things Excel and modelling and decided to hit record. He
wanted to know my thoughts about how useful Power BI was for financial
modellers. You can watch the video below, or you can find an in-depth
break-down and analysis on his blog.
Watch the recording of this live webinar as we learn how to create a multi-dimensional model for your budget vs. actual performance data and how to create interactive reporting on it. Compare retail performance of Appraisal Owls (fictional company) over last 12 months and analyse results in Power BI. Chandoo (aka Purna Duggirala) is the founder of chandoo.org, an award-winning Excel and visualisation site which has over 50,000 members and 1.6 million visitors each month.
Chandoo is returning to Australia this year in June for his Dashboard and Power Excel training Masterclasses in Sydney, Melbourne, Brisbane and Perth so if you’re planning to attend the classes, be sure to listen in to the webinar recording to get a sneak peek of what Chandoo has in store for these live classes!
If you watch the recording, you’ll notice that Chandoo assumes a bit of prior knowledge of Power BI, so don’t worry if you don’t follow it all the first time around. In his masterclass he starts from scratch and assumes just an intermediate knowledge of Microsoft Excel.
Enter your details at the link below to get immediate access to the recording, as well as the downloadable resources Chandoo made available during the webinar.
I wrote a blog article a while ago with links to some good Excel and Financial Modelling online resources. Due to its popularity at the time, and the fast moving pace of the online world, I thought it was time to update it! www.plumsolutions.com.au/free-stuff is a good place to start, but there are a lot of other online resources available as well.
Last week I was interviewed by Matthew Bernath for his new show, “The Financial Modelling Podcast”. We had a great chat about modelling trends, how to learn from other financial modellers and the meetup groups that I run in various locations. Matthew is based in Johannesburg and I was pleased to host him as a guest speaker at our meetup group earlier this year on his visit to Sydney.
Podcasts are such a great way of learning and keeping up with trends on different topics so I thought I’d share with you my playlist and list some of my favourites for you. I’m sure this will give you a bit of an insight into my random and nerdy tastes!
PS – I usually listen to these on 1.5 times speed. Just because I’m always in a hurry.
My Excel Online Podcast with John Michaloudis. Episode publications are a little sporadic but he’s got some good interviews in there (even if I do say so myself – you can listen to my episode here).
From the Trenches – Real Life in the Accounting Industry. One of my favourites – very relevant for those in the Australian accounting landscape.
CPA Australia Podcast – Some subjects are a little dry but there are some great soundtracks from conferences.
Strength in the Numbers Show with Andrew Codd. Once you get used to his lovely Irish accent it’s good stuff, although the episodes are very frequent, so I don’t always get through them all.
The Deal Room with Joanna Oakey has interviews and tips & tricks relating to the world of business sales and acquisitions. It sometimes comes from a legal rather than a finance angle so every episode is not always relevant.
These ones are less accounting and finance, and more data visualisation:
Story Telling with Data with Cole Nussbaumer Knaflic. Another fave – always filled with interesting topics relating to data storytelling and presentation skills.
Policy Viz with Jonathan Schwabisch. Interviews from different data visualisation professionals.
Analytics on Fire with Mico Yuk and Ryan Goodman who interview their customers and BI specialists on business intelligence, analytics and design etc.
Here are a few that have popped up on my radar which look interesting so I’ve subscribed but I haven’t had a chance to listen to them yet:
These are some of my other absolute favourites, in no particular order, not necessarily relating to finance and accounting but fascinating nonetheless!
Planet Money is very well-produced and looks at current events affecting the US economy in a fun, interesting way.
No Filter – Mia Freedman chats to different people all with fascinating stories.
The Minimalists – How to live a meaningful life with less. Each episode is FAR too long but such a great message we all need to hear.
Big Ideas from ABC Radio National has recordings of the best of talks, forums, debates, and festivals, casting light on the major social, cultural, scientific and political issues in Australia.
Simplify from Blinkist. Recorded in Berlin, it’s all about simplifying your relationships, health, happiness and other random subjects.
Please feel free to let me know your own favourites and I’ll update this article if your recommendations make it to my subscribe list!
Each time an updated version of Excel is launched, new features are introduced and subtle changes in the look and feel take place. The biggest change was way back when the big jump from Excel 2003 to 2007 occurred. The introduction of the Ribbon and change of file types caused all sort of difficulties for heavy-duty Excel users, financial modellers included. In the versions since then, the changes have been somewhat less traumatic for users and in recent years Microsoft has been moving towards a subscription-based model, making the changes incrementally rather than all at once.
Although Excel 2019 is technically not yet available, many of the new features are already accessible if you’re on the Office 365 subscription. Whilst preparing the third edition of my first book, Using Excel for Business Analysis, I’ve been on the latest, most up to date version of Excel through the Microsoft Insider Program and have been using the new tools a little earlier than I would normally have done.
What’s the difference between Excel 2019 and Excel 365?
There’s not much difference between them right now in terms of functionality; it’s just a different licensing arrangement. Excel 2019 is the perpetual, bought-outright, stand-alone version of Excel and Excel 365 is the subscription-based version of the software.
What is the difference between a perpetual and a subscription-based licence?
Historically, Microsoft has always sold a “perpetual” licence that is purchased outright and owned forever – I can still remember purchasing my first laptop together with a hideously expensive CD containing Microsoft Office. With this type of licence, the user does not receive any updates to their software until the next version is released and installed. Large organisations with many users that have purchased perpetual licences often wait several years before upgrading due to the cost and are usually at least one or two versions behind. This means that at any one time there has always been a wide range of versions in use in the general community. It is not unusual for me to run a public training course and have three or four different versions of Excel being used by different participants in the class. As a consultant, I have always had to take care to find out the oldest version of Excel the client is likely to be using and make sure that I don’t use any features or functions in the model that won’t work in their version of Excel.
Users with perpetual licences understandably become impatient because they don’t have access to new features they have seen or heard about, or because they cannot view or use or view the new features those with later versions have included in a model. When the upgrade finally does happen, the updates so eagerly anticipated by some, can cause confusion and frustration for others, either because they dislike the sudden new look or can’t find what they are looking for.
With subscription-based Office 365, updates are regularly released and any changes are gradual, which makes it easier for users to become accustomed to the differences. Organisations on the subscription model can choose their “update channel” which will determine how often updates are made, either monthly or semi-annually. In theory, all versions should be the same, but these differences in the frequency of updates means that not all users receive the updates at the same time.
Whilst it is possible to purchase an Excel 2019 licence, Microsoft is strongly encouraging users and organisations to take up the subscription option, presumably because they prefer the recurring revenue and stable cash flow it generates. As more organisations move to this model, the compatibility problem of users being on different versions will become less of an issue and should make my life as a trainer and consultant a lot easier! To seal the deal, Microsoft announced in 2017 than those running Office 2016 on a perpetual licence will be unable to connect to Microsoft’s cloud-based services after 2020, and I expect 2019 will have similar limitations. It seems likely that Office 2019 will be the last version that Microsoft offers as a perpetual licence.
So, what’s new in Excel 2019 / Excel 365?
The following features are being introduced in Excel 2019 or to 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 / post codes. 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 & 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
If you regularly change your preferences for Pivot tables, 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 that 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 these 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.
So, in conclusion, there’s no need to wait for Excel 2019 – it’s already here! When the perpetual version of 2019 does become available it’s highly unlikely to contain any ground-breaking new features, as it will be based on the features already available to those with a 365 subscription.
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.
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*
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.
Danielle Stein Fairhurst graduated from Griffith University with a Bachelor of International Business Relations. Since then, she has completed a Diploma of Accounting and Finance from ACCA in London and an MBA at Macquarie University and now runs Plum Solutions, a consultancy specialising in financial modelling and analysis. She is the author of two books and travels regularly to deliver specialised training courses and consulting services. Griffith University visited Danielle at her home in Sydney to find out more about how her first degree contributed to her career path.
What would you say if a client asked you to build a financial model using…. Google Sheets? This is exactly what happened to management consultant Lisa Barham. Lisa uses financial modelling as a key part of her toolkit in providing CFO services to her clients and like most modellers and finance professionals, she feels most at home using Excel. It’s generally assumed that if a client wants a financial model built, it will be built in Microsoft Excel but earlier this year, she was engaged to build a model to aid in a key decision. The client relied heavily G-Suite products and the CEO asked if the model could be built in Google Sheets.
Guest Blog Post: Google Sheets versus Excel
by Lisa Barham
Whilst a serious financial modeller will always need a full-fledged version of MS Excel, there are some benefits to using Google Sheets that make it worth some consideration. Microsoft and Google parent Alphabet were recently valued as the third and fourth most valuable companies in the world and with just 2% between them in market capitalisation, it’s clear the battle is not over. For the time being, Google Sheets is here to stay, and it’s prudent for financial modellers to arm themselves with knowledge in the Sheets versus Excel debate.
Google Sheets is part of the Google G Suite (formerly Google Apps). It is a free app that can be used to create new spreadsheets or open existing MS Excel spreadsheets. G Suite is growing in popularity at a faster rate than Office 365 and is used across a range of industries.
Image source: Okta
Office 365 and Google Apps usage by industry
Image source: Okta
The Google Sheets interface has drop-down menus as well as a “ribbon” of commands. The platform mirrors MS Excel and is quite intuitive, although some functionality does take a bit of getting used to, such as “Enter” jumping to edit a cell rather than moving down a row and the fx function builder being displayed but inactive (!).
Cost and Collaboration
Google Sheets is included in the G Suite basic professional office suite which is currently priced at $5/month/user, making it free for anyone with a business Gmail account.
The main strength of Google Sheets is its collaboration capability, which supports multiple team members providing input to a spreadsheet. Of course, with Office 365 you can co-author files on the cloud (via OneDrive) just like you can with Google Sheets, however issues arise if users do not have the latest version of Excel downloaded and an active Office 365 subscription. Sheets are easily accessed via a web browser and team members can make changes to the spreadsheet at the same time with updates displaying instantly.
Integration
Because Sheets integrates with the Google Platform, there are certain functions that you can use to enhance your work. For example you can translate the contents of a cell using the function GOOGLETRANSLATE(). Other functions such as GOOGLEFINANCE() lets you use the power of Google to retrieve current or historical information about specific securities.
Version control
Version control has long been an issue when dealing with multiple users and iterations of a file. Google Sheets (File>Version history) allows you to see every edit made by every user, and the time the change was made. Owners can choose to revert to former versions or accept changes. Although this is a useful feature, when combined with multiple users with varied levels of sophistication, changes can get out of control and files can quickly become corrupted. In situations with multiple users, a backup version should be housed in a separate location or sheet protection may be enabled.
Functionality
Many of MS Excel’s features are available in Google Sheets but with basic functionality. For example, Sheets lets you protect data at a user level, but any input cell ranges must be manually added one by one making protecting sheets more time consuming than Excel. MS Excel’s Cell Styles is a more user-friendly way of achieving the same outcome.
From a model build perspective, one option is to develop the model in Excel and open the completed model with Google Sheets, where changes can be made collaboratively. It’s relatively easy to move between Sheets and Excel using the Open With Sheets and Download as MS Excel functions. The ability to move between Sheets and Excel means the two can be used interchangeably in some instances, but not all.
Opening an Excel file with Sheets will result in the loss of some functionality, for example: hyperlinks; graphs; data tables; and pivot tables, although some of these functions are available if built natively in Google Sheets. Google continues to add functionality such as row and column grouping, which was previously unavailable. For the more technically minded, Google Sheets allows users to build their own functions using Java Script. New functions can be shared / accessed via the add-in store. One such function is Solver, which is not available in Sheets but can be accessed via a free add-on. Speed of function execution is problematic: a basic Goal Seek equation took 7 seconds to execute.
Speed
Google Sheets is built to be fast and nimble when dealing with simple files, however page load speed is compromised by complex files containing large amounts of data. Model development in a native Google Sheets environment will naturally be slower for an experience modeller when compared with MS Excel although this gap will reduce as modellers develop experience with Sheets.
Summary
MS Excel is best for handling large amounts of data and advanced features, however if growth in prevalence continues modellers will increasingly find themselves asked to work with Sheets.
Recommendation: you should trial Google Sheets and start familiarising yourself with the product, allowing you to speak from a position of authority when you find yourself drawn into the Excel versus Sheets debate.
This guest post was written by by Lisa Barham, Director of Effigy Consulting. Lisa presented at our Brisbane Modellers’ Meetup group in August on “Working to client specifications: can you build a financial model in Google Sheets?” Lisa took us through how she worked with the CEO to let the business use Google Sheets for the aspects of the model best served by it, while still building the core functionality in Excel. You can view the recording of this session in our private Facebook Group.
Since the introduction of Microsoft Excel 2010 several versions ago, Excel has been available in 64‐bit; this has been a topic of discussion and interest for many Excel users. With all the buzz around the 64‐bit version, many of us wonder: Is 64‐bit Excel better than 32‐bit Excel? Should I make the switch? Is 64‐bit MS Excel the solution to poor Excel performance?
by Danielle Stein Fairhurst
These are some of my other absolute favourites, in no particular order, not necessarily relating to finance and accounting but fascinating nonetheless!
What would you say if a client asked you to build a financial model using…. Google Sheets? This is exactly what happened to management consultant Lisa Barham. Lisa uses financial modelling as a key part of her toolkit in providing CFO services to her clients and like most modellers and finance professionals, she feels most at home using Excel. It’s generally assumed that if a client wants a financial model built, it will be built in Microsoft Excel but earlier this year, she was engaged to build a model to aid in a key decision. The client relied heavily G-Suite products and the CEO asked if the model could be built in Google Sheets.
Since the introduction of Microsoft Excel 2010 several versions ago, Excel has been available in 64‐bit; this has been a topic of discussion and interest for many Excel users. With all the buzz around the 64‐bit version, many of us wonder: Is 64‐bit Excel better than 32‐bit Excel? Should I make the switch? Is 64‐bit MS Excel the solution to poor Excel performance?
