Meetup Presentation: Turning up the Heat with Excel Dashboards

Heat maps have evolved considerably in recent versions of Excel from a matrix of colours to become a powerful graphical representation of data displayed in a dashboard. Excel has the capacity to add conditional colour to images based on an agreed set of parameters or key performance indicators. With a little corralling, a heat map nestled inside a dashboard can add a powerful visual element to an otherwise plain and simple report.

You will hear from Dashboards and VBA expert, Marcus Small who leads us in this highly practical and hands-on workshop session. This 60-minute presentation covers:

  • The creation of an Excel heat map from scratch. Generates the scalable vector file (SVG) from Wiki Commons and passes it through Inkscape to Excel.
  • Builds in the formulation and creates the VBA which will generate a predetermined colour scheme for a dashboard where the heat map will form the centre piece.
  • Layout and design when creating a dashboard, maximising design with very little effort.

By the end of the session you will know how you can create an interactive heat map using just about any imaginable image and include it in your own Excel Dashboards.

Marcus Small holds a Master’s Degree in Finance from ANU and has worked in the Big 4, Investment Banking and within industry both here and overseas. He is the founder of www.thesmallman.com, a website dedicated to Excel and VBA development with an acute focus on dashboard design and financial modelling. Additionally, Marcus moderates both Ozgrid and Chandoo forums, which are amongst the web’s largest online Excel resources.

Webinar: Building Error Checks in Financial Models

We know that a well-designed financial model contains in-built, automatically calculating error checks and a good financial modeller is always looking for opportunities to build error checks into their model.

Join financial modeller, Danielle Stein Fairhurst for this free live webinar filled with practical tips and tricks for creating simple as well as more complex error checks in your everyday financial models.

Throughout the webinar we cover:

  • Which errors need checking?
  • Which Excel error values not to use.
  • Why you should love your error values!
  • Types of model error.
  • Excel Horror Stories.
  • Strategies for reducing error.
  • Sense-checking cognitive bias.
  • Using error checks.
  • Circular References.
  • Tips for finding errors.

This webinar was held on Thursday 15th September. The webinar has now ended but replay and files are now available.

Financial Modeling for Dummies

I am very excited to announce that I’ve just signed with my existing publisher, Wiley to start work on a new book Financial Modeling in Excel For Dummies. Yes, that’s not how I normally spell “modelling” I know, but they tell me a large part of the world spells it that way. And apparently that’s a pretty big market 🙂


Winter Webinars – Infographics & Dashboards in Excel

This month our subscriber numbers have just hit 10,000, (wow!) and in this edition, it’s all about webinars!  We’ve got several great webinars you can register for, and learn with, all from the comfort of your home or office.  (And don’t forget you can always use them towards your CPD).  It’s mid-winter here in Sydney and I must say, I’m enjoying the opportunity to stay snug and warm in my home office and I’ve even been keeping my ugg boots on (shhh!).

Webinar: Infographics in Excel

Nothing conveys complex information more quickly and effectively than a well-built and visually appealing infographic.  They are designed to entertain as well as inform, and the popularity of the infographic in social media has made them an effective way to distill complex information and draw attention to issues that may have otherwise been overlooked.


Use your Budget Surplus

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.


Three Ways to Split Data without Using Power Query

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.

Demand for Financial Modelling Skills Reaches new Heights

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 – no longer available) 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.

Author, Lance Rubin is the CEO and Founder of Model Citizn, a specialist financial modelling consulting firm within financial services.