One of my favourite scenes from The Office is when Michael’s accountant tries to explain to him the concept of the budget surplus and why they needed to spend it before the end of the day.
I was only kidding. One of them IS Power Query. Seeing as Power Query is one of the best things to happen to Excel data analysis since the invention of the …. PivotTable, how could I possibly write about splitting data without mentioning Power Query? Note that Power Query is now confusingly called Get and Transform in Excel 2016 but I’m going to stick with the name Power Query – for now.
Guest post by Lance Rubin
Have you noticed lately how many finance roles have “financial modelling” listed as a core skill? Financial modelling is now one of the most sought after skills in today’s corporate world and demand for financial modelling experience continues to outstrip supply! The changes we see in our everyday lives are reflected in the corporate world and the finance industry is no exception.
Over the past decade our lives and the way we interact with technology has changed significantly. Consider how we listen to music today compared to the past (Spotify), how we watch television (Netflix), how we travel (Uber) or how we communicate and talk to others (WhatsApp, Viber, Skype and social media).
It should therefore come as no surprise that the core skills of finance professionals too have changed significantly with proficiency in financial modelling emerging as one of the most highly sought after pre-requisites. Even the term “financial modelling” wasn’t as widely used as it is today nor was it easy to predict that the specialist skill that was previously contained within Corporate Finance or M&A transactional modelling teams would now become a core requirement of the mainstream finance role.
Over my 18 year career in financial services across professional accounting firms, corporate finance and advisory as well as retail and wholesale banking businesses, I have watched with interest the rise in the demand for “value add” services from a finance function. This interest has not only led me to establishing Model Citizn, a specialist financial modelling consulting firm, but also joining Banjo as their CFO and consulting for a leading professional services firm within their financial and business modelling team.
In recent years I have developed a theory that financial modelling expertise has become the predominant talent requirement for career advancement in Finance and it seems I’m not the only one with this opinion. Two large professional firms previously published white papers on a related topic which is easy to understand but much more difficult to implement. The Ernst & Young paper “The DNA of the CFO” (published in 2010) and the Deloitte paper “Changing Focus: Finance business partnering in Australia” (published in 2013) both point to the changes in services and expectations on CFOs and their teams to deliver more insight and strategic decision making that is commercially on point. The words “insight”, “strategic decision making” and “trusted advisor” all rely on financial modelling as a key ingredient in both producing, extracting and delivering this value-add.
As a member of a Finance team it’s just not good enough anymore to simply deliver the historical results and explain what happened, (even with the ubiquitous fancy dashboard report!). The harsh reality is that no one cares unless that explanation can be used to make a strategic decision which will enable real value creation and hence incremental increase in the valuation of the company, its returns or efficiency in the capital structure.
In my experience those explanations, although valuable, generally arrive too late to be of any significant use as the business has already moved on. The rate and quantum of changes to business trading conditions are so rapid that even 24 hours to make a decision might be too long. Think about an importing or exporting business that has just witnessed the Aussie dollar drop below 80 cents to the US $ or a transport company seeing petrol prices drop below $1.10 per litre. A decision on the price at which to take advantage or hedge against these movements to manage business performance becomes critical. Whilst some larger businesses may have a treasury function, many don’t, and the finance function needs to understand the impact on the overall performance, updated forecasts or budget setting. To be able to answer some of the businesses’ most complex questions, such as those above, and provide insight requires strong modelling skills to dynamically adjust the future performance of the business given changes to certain key assumptions like currency and petrol prices. Some of these assumptions are so sensitive that “back of the fag packet” just won’t work anymore. I’m not suggesting that a high level sense check isn’t worth doing – it is always good to cross check the more detailed bottom up analysis. A robust three-way model that is updated monthly to enable re-forecasting and iteratively refining the business algorithm of future performance becomes very powerful and at times critical for the survival of the business during difficult trading conditions.
Not so long ago I was charged with trying to transform the role of finance partners in a large banking institution. The evidence supporting the transformation was sound, and the roadmap had been laid out clearly, but multiple attempts at moving the teams towards the “trusted advisor” had failed. In my opinion it wasn’t the lack of drive, senior leadership buy-in or people’s interest that had led to the failure but something more fundamental. The crisp and clear articulation of what the change would look like on a practical level once successful was missing. The picture of what the new world looked and felt like was still too grey to enable people to trust the roadmap and the change itself.
On the surface it sounded great when we used words like “so what?” to help people understand insight, but this was still too obscure e.g. so what if revenue dropped? We then moved to words like “now what?”, which was closer to being more action and decision orientated but still not quite there.
However, to answer a question like, “now what?” one has to hypothesise by making a bunch of assumptions and testing these through time. It’s at this point that expertise in financial modelling becomes critical. But, no financial modelling training was ever provided to the team and it was assumed that everyone who used Excel (which is pretty much the entire function) were great modellers or analysts. I mean, why shouldn’t they be? They have all been using Excel for decades, so surely they can do it? This was the missing piece of the puzzle in executing this transformation and an assumption that was deeply flawed.
Of course, we know that the skills needed to be a good financial modeller go quite a bit further than simply having good Excel skills. If the person performing the analysis doesn’t have the ability to create a robust, dynamic, rolling driver based financial model to test hypothetical scenarios how can you ever answer a question like “now what?” accurately? The skills required to build a robust three-way model is as different to using Excel day to day as riding a bicycle is to MotoGP. One could try and assume that riding a bicycle is no different to riding a motorbike, but racing around a MotoGP track is very risky and dangerous if you don’t know how to control the bike around a bend.
When I was hiring finance professionals, in my previous role as Head of Performance Management (in a major banking institution) I soon discovered that the hires I made with strong financial modelling skills were able to add significantly more value and were quicker than those without it. I subsequently started to roll out internal training courses with a leading financial modelling consultancy firm and changed the way we recruited with a strong focus on financial modelling, even asking candidates to build a financial model as part of the recruitment process.
As more of the finance production activities (preparing management and statutory reports) gets automated and offshored there will start to be an ever increasing focus on finance teams adding more value through insights and financial modelling.
Danielle Stein Fairhurst recently ran a new webinar which was streamed live from Dubai for Informa Middle East. The recording is now available.
My love of Financial Modelling and Excel is always leading me to new and exciting places. I just returned from Perth yesterday where I presented the one-day Data Analysis & Dashboards as a closed private in-house workshop. Next week I’m also looking forward to travelling and presenting at the PASS conference in San Jose, California for a couple of days.
We recently held a Meetup for the Brisbane Modellers’ Meetup group and I’m pleased to say that the video is now available below. The speaker Eris O’Brien is a highly experienced modeller and at this informative presentation he took a look at the basics of incremental cash flows for both new, standalone projects, and for projects at existing sites, to see if we can isolate the incremental effects, or
The recording & transcript of our first Plum Webinar is now available! This hour-long webinar was free to attend and during Part One of this webinar, online trainer, Danielle Stein Fairhurst led us on a short technical demonstration of how to create scenarios using plain Excel without any add-ins or other software. Reduce uncertainty in decision making using built-in standard Excel tools to reduce volatility in outcomes by performing sensitivity and scenario analysis to mitigate risk. Danielle demonstrated tools such as manual selection, data tables and scenario manager, and then took some brief questions relating to scenarios from the general attendees.
Part Two: Following the presentation, Danielle answered a limited number of work-related Excel questions from active members of our online training community. If you would like opportunity to submit advance questions for a future webinar, then please register for an online course today!
You can view the recording below. The recording features several Excel files and if you would like a copy of the accompanying Excel files as well as the Power Point Slides, please enter your details to access the Files.
Automating repetitive tasks with Excel Macros can dramatically cut down the time you spend building financial models, and can significantly increase your productivity. A well-written (or recorded) macro enables you to repeat operations that you would normally do by hand but much more quickly and reliably. If you find yourself repeating the same action over and over again whilst building or editing your models then the use of VBA can increase speed and accuracy.
At our recent Melbourne Meetup, we heard from from Macro & VBA expert, Marcelo Mendonca from Rixena who specialises in Business Productivity Improvements, and has trained hundreds of professionals from engineering, management, finance, supply chain in Australia, India, South Korea and Brazil. Marcelo gave us a practical demonstration covering:
• Best practice for Financial Modelling with VBA
• When to use a formula and when to use a Macro
• Tricks for model-building such as finding broken links, or hard-coded numbers using VBA
• Case studies of when the use of macros saves time and increases reliability of financial models
When you decide your financial models are not as good as they should be, should you immediately send your team on an advanced Excel training course? Whilst this is certainly helpful, there’s a great deal more to financial modelling than being good at Excel!
No, it’s not a silly question and since the “Modern Excel” was introduced in 2010, the changes have been pretty subtle and it can be a little difficult to tell the difference.