In this monthly series, I will be bringing a selection of updates to you about the latest goings-on in the world of spreadsheets, sharing a few top tricks from our bloggers and community posters, and passing on a few words of best practice guidance for all of us that are looking to make better use of Excel.
Modelling is a risky business
This month we were visited at Moorgate Place by Alex Edwards, financial modelling director at SavvyFM. I first met Alex at last year’s ModelOff, a financial modelling competition, where he gave an interesting presentation about the risks inherent in the construction of financial models, and how to combat them. Alex came and presented a webinar on this same topic for us; if you are an Excel Community or IT Faculty member, you can view the whole thing here.
However I just wanted to pick out one particular strand from Alex’s talk, which is to talk about some useful elements of testing that you should consider when assessing a model. Alex’s list is:
- Familiarisation with the model – learning its basic layout and process, looking at how the flow of calculations is structured
- Low level review – checking formulas and calculation consistency, sourcing inputs, and so on
- High level review – Does the overall workbook make sense? Do the outputs differ wildly from your expectations?
- Sensitivities – How much do changes in key inputs affect the outputs?
- Stress testing – How does the workbook handle a massive change in an input? Should expect a massive change in output!
- Discussion – Comparing your understanding and concerns with others is vital
- Subject matter experts – If appropriate, have an expert in the subject take a look.
Tip of the Week: TRIM
I write a weekly Excel blog for the ICAEW, and will be picking some of my favourite tips to share with you here. The first one will be a handy but little-known text-editing function: TRIM.
RIM removes excess spaces from text. Excess spaces are tricky, as they can mess up sorting and data analysis, but are difficult to detect due to being invisible. They are also harder to remove using things like Find & Replace or the SUBSTITUTE function, because we usually don’t want to remove all the spaces – just the excess ones.
By ‘excess’, what we mean is:
- Removes any leading spaces: “ Name” => “Name”
- Removes any trailing spaces: “AGE: “ => “AGE:”
- Replaces double spacing (or more) with single spaces: “SPACE SHIP” => “SPACE SHIP”
TRIM is pretty easy to write:
Naturally, the output of TRIM is a formula – you might want to then paste-values the result back over the original data to permanently make the change.
Slightly technical bit – ASCII (the basic character set) contains only one space character; however Unicode (the full set of all possible language characters) also has a “non-breaking space” that is visually indistinguishable from a space, but works a bit differently in terms of how it is displayed. The non-breaking space is commonly used in webpages and may make its way into Excel if you copy text over from a webpage. Crucially, TRIM won’t remove this character. To make it work properly, you first need to convert the non-breaking space character into a regular ASCII space. The formula you need is:
=TRIM(SUBSTITUTE(address of your text here,CHAR(160),CHAR(32)))
Principle of the Month
Finally in this blog, we will take a quick look at one of the Twenty Principles for Good Spreadsheet Practice. This month we have Principle #6:
Identify the audience. If a spreadsheet is intended to be understood and used by others, the design should facilitate this.
In other words – remember that every spreadsheet of any meaningful size will, sooner or later, have to be read by someone that isn’t familiar with it. That could even be you, six months later when you’ve forgotten all about it! Try and keep the user in mind and make their experience of interacting with your spreadsheet as clean and simple as possible.
That’s all from me for now – see you next month, and until then, happy formulating.
David Lyford-Smith is a Technical Manager in the ICAEW’s IT Faculty and blogs for the ICAEW Excel Community. The Excel Community is an Excel content hub that encompasses webinars, blogs, member Q&A, fortnightly bulletin updates, and more. Find out more at this link.