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?
I’m writing to you from the beautiful Sunshine Coast where I’ve been holidaying this week. We spent a day at the Gold Coast on the way to soak in some sunshine and the exciting atmosphere of the Commonwealth Games. I wanted to share with you this link to a special data visualisation that Chandoo has built especially for the games. Rather than just looking at the medal tally, it calculates medals per participant and has been built using Power Query (Get & Transform) and it is connected to gc2018.com, so all you have to do is refresh (Ctrl+Alt+F5) to automatically update medal tallies – very cool!
Chandoo (otherwise known as Purna Duggirala) is best known as 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. He will be visiting Australia next month for a special Australian Masterclass event. Book before 27th April to take advantage of the earlybird prices. Download the Brochure Here
Advanced Excel & Dashboard Reporting
Sydney 21-22 May
Brisbane 4-5 June
Melbourne 18-19 June
Perth 25-26 June
Power Excel – Power BI, Power Pivot, Power Query
Sydney 23-24 May
Brisbane 6-7 June
Melbourne 20-21 June
Perth 27-28 June
Chandoo is also available for a limited number of in-house sessions. Please enquire to book Chandoo to come to your organisation during his trip to Australia for a private in-house session on the following dates:
- 17-18 May – booked out
- 28-29th May – booked out
- 31st May – 1st June – available
- 11-15th June – available
- 2-6th July – available
Purna Duggirala (Chandoo) 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 and over the past few years, he has held dashboard contests on his blog to find the “world’s best dashboard”. Throughout the contests he has received hundreds of entries, ranging from interesting, awesome and truly mind-blowing. So, what does it take to create a truly awesome dashboard? Review the recording of this interactive webinar as Chandoo takes us through his critiquing process as he assesses the good, the bad and ugly of dashboard hopefuls in their pursuit of data visualisation fame and glory.
Enter your details below to view the recording and download the accompanying files.
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!
Hosted by The Grid Media, Danielle Stein Fairhurst from Plum Solutions will be visiting the UK for a special event in August/September 2018. Download the brochure
Building a Business Case: Don’t Let your Boss Rip your Financial Model Apart
In this interactive workshop we will integrate concepts such as pricing, costing and returns on investment into an Excel based financial model that we will build from scratch individually to assess the viability of a new product or venture.
Advanced Financial Modelling: Change the Game
This fast-paced course build on existing Excel modelling skills and apply new techniques to better analyse financial data, predict revenues and costs, assess risks and forecast economic inputs in a time-efficient and effective way.
Data Storytelling in Excel: Soar out of the Debris
Learn to interpret data and summarise its meaning into a format that we will each then transform into easy-to-read tables, graphs and infographs using Excel that can be taken home and used again and again.
Danielle will also be available for in-house sessions in the UK in September, so please get in touch if you’d like to book her in.
Last month I posed the question why, yet again, we didn’t see any female faces in finals of the 2017 ModelOff Financial Modelling World Championships. Boy, did that open a can of worms. I was inundated by public and private messages of both support and rancour, and I was blown away by some of the responses, particularly the private comments along the lines of “Women aren’t good at Financial Modelling because it’s just not in their nature” by a genuinely well-meaning gentleman who felt the need to very sweetly mansplain the reasons behind the low rates of female participation. Wow, it’s worse than I thought.
The lack of female representation in the financial modelling industry as well as in the ModelOff competition has always bugged me but hey, it’s a competition of skills and the best candidate won. End of story, right? I’m not so sure any more. All of the current research tells us that girls are doing as well as boys at high school and that STEM subjects are no longer considered purely the purview of ‘the boys’ with girls also topping many science subjects.
So why are there so few senior females in finance, and why, with the exception of Hilary Smart’s victory in 2013, do we continue to see no female faces at ModelOff, when I know there are plenty of well qualified women? Where are they, and why aren’t they represented?
Besides my obvious qualifications as a female financial modeller and author I’m no diversity expert but I feel a responsibility to try to understand and explain the reasons behind the disparity and hopefully make some positive changes for future competitions. I’ve been working closely with the organisers at ModelOff who are keen to encourage more women to enter, and to achieve our goal of at least one female face in the 2018 finals.
Without turning this into a battle of the sexes, I’d like to lay out some of the arguments I received. Basically, the lines of reasoning fall in to the following areas:
What problem? I don’t see an issue at all. None of the women made the grade but neither did anyone over the age of 44.
This is – to my mind, the most insidious of all arguments because it promotes the status quo. If it wasn’t an issue, why the disparity? It didn’t occur to me that I’d need to articulate the problem because to me it’s perfectly obvious that every industry at its peak should have a representation of both male and female but judging by the response, it seems that it needs to be spelt out. This is a systematic problem; a case of nurture, not nature (despite what my mansplaining friend seems to think). We need to challenge and change the status quo.
The ModelOff competition is being organised and promoted by mostly white males and attracts mostly white males. We need to figure out how to break this “circular reference” (forgive the pun) by adding a few more women into the mix.
Lack of gender diversity is bad for everyone in the financial modelling industry for many reasons but the one that bothers me the most is that it discourages other women from entering this field. If a new female finance or accounting graduate considering her career prospects, sees no other women at the top – or even near the top, it makes her wonder if she can make it. She wonders if it is worthwhile pursuing a career as a financial modeller if she’s unlikely to make it to the top. With a lack of female role models, she’s more likely to pursue a different career.
The ModelOff Competition brands itself as the pinnacle of achievement in the industry. Ask yourself, men, if you saw a page full of women in the ModelOff competition, would you feel like it was a place you should definitely head to for a career – or would you consider that maybe this group, being all women, probably isn’t a place for you to thrive. Who will mentor you? Who is like you? Who will you shoot the breeze with about life? Do you think ‘new graduate you’ would have seen a group of white women 20 years older than you and thought – yes, my place is here, I’ve found my calling? Consider the future of your daughters, do you only want your sons to achieve and be recognised or do you want your daughter to have the confidence to stride forward in any career she chooses? Women like New Zealand’s Prime Minister, Jacinda Ardern who has just announced she is planning to have her baby and go back to leading the country after six weeks are certainly great role models for aspiring career women who also want to have a family.
Financial modelling is a fantastic career choice for those with the right temperament but the skills needed for financial modelling are many and varied. Diversity leads to a smarter and more creative workforce, and financial modelling teams can benefit by including from those of differing backgrounds, gender and ethnicity. In most fields diversity of backgrounds, gender and ethnicity ads depth and understanding to any project, collegial thinking is not just words, it’s diversity of thought in action. Both men and women bring different perspectives and perceptions to the financial modelling world.
Women have better things to do. They aren’t interested in competing
I’m wondering what better things women have to do…maybe they’re home cooking dinner for their families if some of the responses I received were anything to go by; while their husbands are working late improving their skills? There are highly skilled female financial modellers out there. A glance at my own LinkedIn connections shows that roughly 20% of those who are confident enough of their skills to list “financial modeller” in their title are women. This is a woefully small percentage, considering 52% of all new accounting graduates are women but even if perhaps only 20% of financial modellers are women, then out of 16 finalists we should be seeing at least 3 female finalists, so surely we can get at least one, if not more, to make it to the finals?
I’m convinced that there’s no conscious or unconscious bias in the questions or in the way the competition is run. One of the three judges is Maria Shevchenko and 2013 champion Hilary Smart is on the question design team. But the competition simply doesn’t attract its fair share of female competitors. Possibly one of the reasons for this comes down to the way the event is promoted and the gung-ho, take-no-prisoners approach of the marketing in previous years, such as calling the finals “The XL Smackdown”. I can recall attending the first ModelOff Meetup in Sydney which had remote control cars racing around the room. No prizes for guessing who the target market was! I’ve asked many women why they haven’t entered, and although the competition results are never made public, they often just don’t want to compete even if they are confident of their skills. Research suggests that women simply aren’t motivated to compete against others, but are happy to compete against themselves, so an approach of “beating your personal best” would be more attractive to female competitors.
By raising this as an issue you are reducing the achievement of the men who worked hard to get there.
Not at all. Those who make it to the finals should be very proud of their achievements and can enjoy the status of having been a “ModelOff Financial Modelling World Championship Finalist” right throughout their career and champions even have to suffer Excel geeks bothering them for fan pics at every function they attend.
To join in the conversation about this topic, please see this post on LinkedIn.
Copying and pasting charts or tables directly from Excel into PowerPoint is not a straightforward as it sounds! When you are embedding charts, tables or (heaven forbid) a spreadsheet into a PowerPoint slide, it’s best to paste the image in as a picture or JPEG.
If you do a straight Control-C Control-V copy and paste, the original Excel file is automatically embedded in the PowerPoint document and this is not usually recommended, for a number of reasons;
- File size Each time you copy and paste a chart or a table, it copies all the data, and if you’re doing a few of them, the PowerPoint document will end up enormous.
- Confidentiality Sometimes don’t want the data to be accessed from the slides for confidentiality reasons. I’ve heard plenty of horror stories of modellers sending out PowerPoint files to clients and inadvertently including confidential data in the PowerPoint document which can then be accessed by anyone who pokes around in the chart. In one particular example, a supplier sent through their final presentation for a bid tender submission, and the vendor was able to access the supplier’s entire financial model which contained all their pricing calculations!
- Colour themes When we do a straight copy and paste the colours of your chart take on the themes and styles of the PowerPoint slide. This can be frustrating, when you’ve got the chart looking exactly the way you want, and it suddenly looks different when it’s been pasted. Use “Paste Options” and select “Keep source formatting” to get around this.
- Version control If you’ve got shapes, extra graphics or text boxes on your chart then these have a tendency to move around if you adjust the size or position of the image in PowerPoint. It’s best to get the image looking exactly the way you want, and then paste it in as a picture to ensure it does not change again.
Instead of using Control-V to paste the image into PowerPoint, right-hand click instead, and select the “Picture” option. Note that the exact same concepts apply if you are copying and pasting from Excel into Word, Outlook or any other Microsoft product.
Remember that PowerPoint is intended to be used as a presentation tool, not for storing and manipulating data. It’s best to make any changes to the chart in Excel, and then copy it into PowerPoint once it’s finished. The concept is exactly the same as using a PDF to send a final report. When you’ve put together a report in Excel, Word or specialised software, it’s much more professional to convert it to PDF before sending it out. This way we can ensure that the final report looks exactly the way that we intended it to without worrying that the end user will make inadvertent changes, or not be able to view it properly due to licensing issues.
Of course, you may still deliberately choose to embed the Excel image, leaving the data in the PowerPoint slide for documentation purposes, or in case you need to make a change. That’s absolutely fine, as long as you realise it’s there and are able to deal with the problems outlined above.
To learn more about creating charts and presenting data visually, attend our Data Analysis & Dashboard Reporting course which run regularly in Sydney, Melbourne and Brisbane.
Congratulations to Alvin Woon, the new 2017 ModelOff Financial Modelling World Champion! Every year, 16 ModelOff finalists are flown to compete at the Microsoft headquarters in New York or London. Yet again, not one female made it to the finals and when the organisers posted an image of 16 finalists of varying age, ethnicity and background – but all male – it predictably sparked a storm of online criticism.
The first couple of years ModelOff had at least one female finalist and in 2013 Hilary Smart won it but that victory was short-lived because the last two years running we’ve had male finalists only. My view is that the ModelOff attracts men who thrive on competition whereas women don’t feel the need to prove themselves. Even if we can explain the result, it doesn’t help. It’s an embarrassment to our industry that we can’t provide at least a few female modellers – I know there are plenty of us out there! My financial modelling training courses are always at least 50/50. Personally, I’ve never competed because I no longer model the long hours I used to and although I’m an industry expert, I don’t think I’d make the finals and I don’t need to put myself through it. Do all female modellers feel that way?
The finalists are “chosen” by two anonymous online rounds so the selection panel doesn’t see race, age or gender when assessing the scores. It’s entirely skills-based so there’s no way that bias can come into it – the problem is that women simply don’t enter the competition. Finding female speakers on the topic is apparently just as difficult. I was the only female speaker at the Sydney ModelOff GTC for the past two years running and that’s just GOT to change. I know the organisers well; they are keen to address this problem and want to hear from the financial modelling community. I’ll be meeting with them soon to discuss how we can tackle this issue and I’m keen to hear what ideas you’ve got to attract more women!
There’s nothing quite like experience to really hone and sharpen your Excel modelling skills. Every hour that you spend nutting through a problem on the job means that the next time you come across a similar problem, you’ll solve it in a matter of minutes, not hours. This is especially true for creating complicated Excel formulas, or for model design and layout. So often I come across a problem when building a modelling solution for a client and I know I’ve seen this problem before. Whilst it might have taken me half an hour to model it out the first time, often using trial and error but the next time it’ll only take a few minutes. The more experience you’ve got, the quicker and better modeller you’ll be.
Build Muscle Memory
When you first start using Excel to solve business problems, you’ll probably start pretty simply and you’ll need to think through the build of very basic formulas. As time goes by, and you’re performing similar tasks over and over, certain functions and shortcuts become part of “muscle memory” so that, like playing the piano, or driving a car it becomes second nature. By performing repetitive tasks over and over and over again, the solution will be singed into your memory and you’ll never forget it. This experience will help you to diagnose and solve future problems. You’ve seen this somewhere before and the solution can be found by using your well-honed judgement.
Let me give you a example. It’s somewhat embarrassing, but I’m sure everyone has had a similar experience. When I started using Excel for the first time at an investment bank in London, I can remember one of my very first tasks was to calculate work in progress for a weekly report. There was a list of asset codes on one page, and I needed to add the asset description onto the report, so I spent a happy hour or so copying and pasting the project names from one sheet to another. This was fine and my boss was happy. The next week she asked me to do it again, so off I went, copying and pasting to my heart’s content. By the third week I was getting pretty fast at it but I started to get that niggling feeling that I wasn’t making the best use of my time. I’m sure you’ve had this feeling before, and I’ve learnt over the years not to ignore it. If you feel as though there’s an easier way of doing things – there probably is. So I asked around (there wasn’t any Google in those days) and someone showed me a VLOOKUP. Bingo! My life was changed forever, and I never forgot it. Would I have remembered a VLOOKUP if I hadn’t gone through the pain of doing it manually? Probably, but it was burnt into my memory and was my “go to” function for quite a while (until I discovered an INDEX / MATCH.)
The Role of Training in Developing your Skills
So does that mean that on the job experience is everything and you shouldn’t come on a training course? Not at all. Training is great for accelerating your learning and exposing you to ideas and solutions you would not otherwise come across. But training alone won’t make you a good financial modeller. This is why in my training courses I have such a hands-on component, and students are expected to work through the solutions for themselves. Those who attend come away with a download of knowledge, templates, resources as well as a copy of my book, but if they don’t actually apply their newly acquired skills, it’s all for nothing. Use it or lose it! A good financial modeller is constantly learning new tools and techniques and then applying those newly acquired skills to their work to really cement the knowledge. You might know lots and lots of Excel formulas, but knowing exactly where and when to use them is where your real skill lies. Constantly look for opportunities to apply your modelling skills in the workplace. Technical skills coupled with experience are what employers are looking for and you’re going to become a better financial modeller by actually applying your knowledge, not memorising functions. (That’s what Google is for.)
I’ve come across a lot of preconceptions (and misconceptions) about the idea of financial modelling; many people think it sounds very elaborate and esoteric, even mystical. Some question whether the skill is a science or a creative tradecraft in the industry, and I’ve even heard suggestions that it’s a dark art! But what is financial modelling really – an art, science, or just plain magic?
A financial model takes a theoretical idea or problem and translates it into a practical construct we can use to see what might happen in the future. Like a crystal ball? Well, I suppose so, but it’s far more accurate! For example, you might be worried about business risk and want to know what will happen to profitability if costs from a particular supplier were to increase. You can build a model to represent the business and run scenarios to see how profitability might be affected if these costs were to increase. In this case, inputs are costs, volume and pricing, and outputs are profits. A good financial model has these inputs and outputs clearly defined and as we change the pricing inputs, the profitability projections will automatically change. Being able to perform simple scenarios or sensitivity analysis in a model like this, the instant information can really seem like magic. So Financial Modelling is magic then? Well no, not really. Once you know how it’s done, you can break it down into a set of processes and in the same way as a card trick, it can be taught!
Is Modelling an Art or a Science?
As there are many components that make up a financial model, the skills needed for financial modelling are many and varied. A modeller must be a good mathematician as well as someone who can think creatively and who possesses sound logic in order to pre-empt possible outcomes and make sound and reasonable assumptions about the business. Like a fortune teller, you need to be able to read the situation and ask the right questions, assess possible roadblocks and side-step potential pitfalls. A good modeller will not delve into unnecessary detail, knowing when to use rough estimates for assumptions, and when to perform precisely detailed calculations.
Simply put, the financial modeller must get the calculations correct, understanding the relationship between numbers, which data to collect, and be able to define and explain the assumptions made for future projections. There are also the considerations of other variables, such as stakeholder buy-in and the wider environment. How likely is it that the decision makers are going to change the growth rate assumptions over time? If not, a simple single input for growth will suffice. Otherwise, a separate input cell needs to be created for every single time period of the model, which makes the model more complex to build as well as audit and understand. A good financial modeller can make the trade-off between simplicity, clarity and sophistication or complexity and has the ability to see how their model fits into the bigger picture.
So are these artistic skills or scientific skills? In my opinion, they need both.
The scientific side of modelling is in quantifying the business performance, getting the mathematics correct and understanding the relationships between the quantitative data. In order to engage and motivate investors, the numbers must be accurate and clear. This, in basic terms, means ensuring that data used reflects the true situation clearly and concisely, and highlights the key information. Taking this approach shows that precision and complexity are key features of the financial model, and will evoke confidence to those viewing and using the model.
A financial modeller certainly needs a certain level of artistic skill for the aesthetic design and layout of a model. This is an area that many modellers and analysts struggle with, as aesthetics simply do not come naturally to left-brain thinkers like us. (Now, I know that this left brain / right brain theory has been largely debunked but I still think there’s something to it!) We are mostly so concerned with accuracy and functionality that we often fail to notice the way the model looks to someone else viewing it for the first time. Although it’s just a simple matter of taking our time when formatting, most of us could not be bothered with such trivial details as making models pretty, and consequently most models I see use the standard gridlines, font, and black-and-white colouring that are Excel defaults.
I’m certainly not suggesting that you embellish your models with garish colours, but you should take some pride in your model. I’d highly recommend that you spend a few moments to change the styles and formatting as you build the model so that it looks less like a clunky spreadsheet and more like a professional, reliable, well-crafted model you’ve taken your time over. Research shows that users have more confidence in models with aesthetically pleasing formatting than those without, so one of the fastest and easiest ways to give your model credibility is to simply spend a few minutes on the colours, font, layout, and design.
The artistic nature of a financial modeller is also needed when the model requires forecasting of future performance. Crystal ball gazing is probably not a skill that you possess, so you will need to spend some time thinking about how this is best done accurately. A set of assumptions must be made to make predictions or forecast outcomes in your model.
If a financial model is required for an untested startup or product, then the model really is a true art; even the most basic calculations will involve a lot of assumptions and rationale explanations. Working out which assumptions to use is a difficult task, especially if you don’t have prior history to go on. Sometimes it’s simply a matter of coming up with what you think is a reasonable assumption for the base case, and then performing sensitivity analysis on these inputs to see what the impact would be if you got it wrong (because let’s face it, they are highly likely to be wrong in this sort of situation!) Use your influence to gather data, perform careful research and analysis, or engage subject matter experts to help you to get some comfort around the inputs and assumptions. Before presenting the output of the model to stakeholders, ask for their feedback and input on the logic and calculations which have gone into the model. Use the knowledge of those around you; involve them right from the start of the project. Get your audience and stakeholders to participate in the magic of the financial model building process, because as every great magician knows, audience participation increases engagement and attention span so that because they are involved in your model, they are willing for it (and you) to succeed!
In the 1990s when the internet sector was growing very quickly and the potential returns from internet-based companies soared to giddy heights, investors were lured into decisions based on impossible situations within models. They believed financial models depicting growth rates above 1000%. Of course, a financial model is only as good as the assumptions that go into it and these models were not faulty, rather the assumptions were incorrect. The incredible potential of these companies based on this exciting new product called the world wide web caused irrational behaviour as it triggered a belief that anything was possible and logical reasoning no longer applied. The financial models did not rely upon accurate calculations and had wild assumptions integrated – metrics predicting future performance such as page views and clicks – before there was any understanding of how these metrics could increase revenue. Because these growth rates were based on unsustainable assumptions, it was only a matter of time before the dot-com bubble collapsed to cause widespread misery to all involved.
No matter what else, the calculations within a model must be realistic and must consider mathematical principles as well as incorporating clever and logical guesswork. Like a good card trick, your audience will want to know how you achieved it, and unlike a magic show, you cannot keep your “tricks” to yourself! You need to be able to explain the magic (i.e. how you came up with the calculations) to continue the performance.
1. “Reducing Overconfidence in Spreadsheet Development”, Dr Raymond R. Panko, 2003
I was pleased to be invited to speak again this year at the ModelOff Sydney Global Training Camp (GTC) held at the Microsoft Offices last week. It was, as always a great learning and networking event and I thoroughly enjoyed spending a solid two days surrounded by others who also get very excited about financial modelling!
As well as hosting the GTC, the team at ModelOff have done some great things over the past few years to advance the standing of financial modelling as a discipline in its own right. The ModelOff Financial Modelling World Championships are held every year and registrations are now open for the online Round 1 on 23rd September. If you fancy yourself a modelling whiz kid and want to gain some bragging rights at work, why not test your skills against the world’s best and see how they stack up? The results are not published and only the names of the finalists are made public so there’s no harm in giving it a go!
In addition to the championships, ModelOff has now partnered with The Marquee Group and formed the Financial Modeling Institute (FMI) to launch a new financial modelling certification. I’m proud to announce that Plum Solutions is one of the first approved training providers. The certification is at three levels and first exam will be held on 21st October, and again in May and we have launched providing a Level 1 FMI Advanced Financial Modeler Exam Preparation course available in Melbourne and Sydney. This course is available as public workshop or in-house at your organisation.
What does the FMI do?
- Advances the financial modelling profession by delivering and administering the Financial Modelling Certifications and supports high quality education and training for financial professionals
What is the Level I: Advanced Financial Modeler (AFM) exam?
- The Advanced Financial Modeler (AFM) is the first of three certification levels offered by the FMI
- This exam forms the foundation for subsequent certification levels and focuses on the skills required to design and build integrated financial models
Why earn the AFM certification?
- Skill Validation: Demonstrate advanced financial modelling proficiency to yourself and employers
- Personal Development: Invest in yourself by earning certifications that are challenging and revered by the industry
- Career Flexibility: Obtain a skillset that is globally relevant and respected across multiple lines of business
What is the exam format and how should I study?
- 4-hour exam held at global testing centres
- The next exam date is October 21, 2017
- All exams are in English
- You will be required to bring a valid piece of government-issued photo identification to the testing centre on exam day (e.g. passport or driver license)