Which industry sector would benefit the most from improved financial modelling standards?

Fimodo speaks to five financial modelling experts to understand how they have (and continue to) deal with compatability issues.

rickard-warnelid

Rickard Warnelid
Corality
www.corality.com

The issue of non-compatibility of Excel 2003 / 2007 is not generally a problem internally within financial modelling teams, medicine unless of course different team members have different versions. It is only first when external communication using the financial model or Excel spreadsheet is required that the problem crystallises.

Dual installations of Excel 2003 and Excel 2007 to solve compatibility issues

At Corality’s financial model audit team we are currently using the work-around of dual installations, thumb i.e. all financial modelling team members run both versions. In theory you can get around the issue using the Excel Compatibility Pack but the technical issues with that solution have to many drawbacks to make it feasible

  • Frequent corruption of Excel workbooks when converting between versions
  • Potential loss of data, treatment logic and functionality when converting from Excel 2007 to Excel 2007
  • Graphical misrepresentation between the versions, i.e. a financial modeller may build something that is looking fantastic in Excel 2007 but after conversion looks pretty average.
  • Direct lack of compatibility like in the example of Conditional Formatting developed in Excel 2003 does not updating correctly in Excel 2007. How Microsoft stuffed this one up is quite surprising as it looks like a pretty straight-forward conversion to me!

Lower productivity in Excel 2007 using the Ribbon

For people who like keyboard shortcuts the Ribbon creates a hurdle to overcome before a financial modeller can get back to full development speed after transitioning to Excel 2007. One of the more common frustrations is that Excel 2007 does not have the native functionality to add a drop-down in the Menus (because there are no ‘menus’….) to quickly access Excel Styles using [Alt + ’].

Fortunately one of our Analysts, Bing Chien, is an absolute Excel/VBA/XML wizard so he has coded an internal Add-in which does adds a quicker access shortcut to the Styles functionality. Using the Ribbon to access Styles is just to slow and the advantage of Styles is significantly diminished without this functionality.

Does Corality build new financial models in Excel 2003 or Excel 2007

When developing new financial models for clients there is no consistently right answer to which version to use. At Corality’s financial modelling team we currently use the approach to develop new financial models in the Excel version used by the client. This way the risk of corrupted workbooks and lost data is minimized. Unfortunately many companies have not performed a consistent across-the-board update to Excel 2007 but have upgraded on desktop/laptop at the time which further complicates the issue. Often we see the Managing Director or the CFO have brand new sleek laptops with Excel 2007, and everyone else on Excel 2003… Which Excel version should you choose then for a new financial model!?

nick-crawley

Nick Crawley
Managing Director, Navigator Project Finance
www.navigatorpf.com

We’ve been here before…

This is indeed an issue, but it’s not a new one. We have had the same issues when financial models were just as likely to be built in Lotus 1-2-3 and needed to be opened in Excel (and vice versa) – this was a much bigger issue than the one we currently are facing. We saw the same issue when Excel transitioned from 95 to 2000. The latest issue is only temporary and is being navigated in the same way – this time I am finding that companies are adopting 2007 much more readily. I think this is because it was marketed better and as a quantum shift rather than an upgrade…it is also now 2010 so its not really that new anymore.

The best approach?

We need to think about the user and other likely users not us as model builders. The big issues are that Excel 2007 has been prettied up and so if the enhanced formatting is used or the extra columns / rows then there will be compatibility issues – so if you are building in 2007 but it is not 100% certain that a user will have no choice but to open in 2003 at some stage then don’t use the additional features – keeping it simple and having a ‘back to basics’ approach – which is good anyway do mitigate this issue and mean we have very few issues with 2007.

Day to day I find that most issues are solved simply by asking some obvious questions:

  • What do you (the client) use, 2003 or 2007?
  • Will anybody need the model that has not got access to 2007?
  • Do you need any of the enhanced features? (my guess is probably not)
  • Do we need the additional computational ability of 2007 (increasingly so actually)

There are various compatibility packs and guides available online but my best advice is if you are unsure then don’t use 2007 just yet. If you are faced with a model that requires more brute force than 2003 can deliver then use 2007 after speaking to the likely users. The additional power was a pleasant surprise, it allows us to model more flexible (but still simple) systems and do more in a single workbook before getting the subtle but important ‘calculate’ message which is not allowed in Navigator models.

This is currently working very well for us and I suspect by mid 2010 enough people would have migrated that it won’t be an issue – just in time for the next version!

john-stroud

John Stroud
Partner, Digital Advisory
www.digitadvisory.com

Usually I find swearing at the screen and stomping around the office helps.

I work with a duel load of Excel, developing in 2003 then testing in 2007. The main issues are now well documented and can be avoided during development. I think it’s important to know what compatibility is required before development commences as this can save time (& $’s). When issues do crop up there is usually a work around. However when it comes to manipulating the UI (user interface), 2003 and 2007 are completely different, thanks MS! The setting up of custom toolbars requires coding in XML rather than VBA which takes a bit of getting used to.

I agree with the consensus that the UI or Ribbon in 2007 is a less efficient way to achieve the same results. It can be customised or rolled back to a 2003 setup but a personal bug bear is the lack of floating toolbars.

Despite the lambasting Excel 2007 has received, under the hood it’s definitely a better product. By utilising more memory (1Gb to 2Gb), having an unlimited dependency and multithreading calculations, the calculations speeds of large models are significantly improved.

simon-selkrig

Simon Selkrig
Financial Modelling Professional

It’s a matter of adjusting and using the ’07 version through practice, practice, practice

Still adjusting my own customised Excel functions and macros in the ’07 version.

Overall ’07 is much better and has many great improvements, but obviously the menu ribbon is a great challenge to adjust to, and I know many people don’t like it at all.

When I have a problem, I google useful Excel sites like MrExcel.com to overcome these issues

One major issue I encountered in ’07 was that certain Excel add-ins created in Excel 03 require the VBA converters .dll files (VBACV10.dll & VBACV10D.dll) which must be copied into a users’ following location: C:Program FilesCommon FilesMicrosoft SharedVBAVBA6

I have been using it for more than 2 year, although there have been some improvements, I had bad experiences with saving financial models as Excel ’07 Macro-enabled formatted files, which crashed and corrupted complex Excel financial models I was working on. Further you can’t assume if everyone is using ’07, hence it is always wiser to save files as ’03 compatible.

danielle

Danielle Stein Fairhurst
Principal Consultant, Plum Solutions
www.plumsolutions.com.au

The upgrade to Excel 2007 was probably one of the biggest things to happen to my world in a long time! In fact, clients often ask me about the benefits of upgrading to Excel 2007 and whether they should bother yet. The uptake amongst businesses has been painfully slow, and it causes quite a lot of hassle, especially as many organisations seem to be upgrading their users gradually, rather than all at once. This means they have users on different versions which causes a bit of confusion, and many people don’t seem to realise the basic rules of dealing with two different versions. (i.e. If you want a 2003 user to be able to use your model, save it as a .xls file, don’t use new formulas such as SUMIFS, IFERROR etc, don’t use new colours in macros and so on.)

I think any good modeller should be “bi-lingual” in terms of which version they use. I run both, although my preference is 2007, mostly because of the increased capacity, handy new formulas, and improved graphics. I’ve checked out the beta version of Excel 2010 and there are some nice improvements, such as being able to customise your own ribbons which is great and will improve productivity. The File menu has also made a comeback in place of the Office button. Considering the uptake of 2007 was so slow, I find it difficult to imagine that many clients will be using 2010 in the near future. Of course some people will simply leap-frog 2007 and go straight to 2010.

When I run training courses in Excel, I give participants the choice of whether they’d like to use Excel 2003 or 2007 during the course and all of the written materials contain instructions for both versions. I find that it’s a mix of about half and half usually, although I do encourage those who’ve never used Excel 2007 before to give it a go. The first time a heavy-duty Excel user tries out 2007, they invariably find it incredibly slow and frustrating, so I recommend that they test it out in a training environment as this will help them later on when / if they do upgrade. Probably one of the most frustrating things though is when I show people some of the new tools, like Data Bars in Conditional Formatting, Remove Duplicates, etc. they won’t be able to use them when they get back to work unless they upgrade!


Not surprisingly given the current and expected future market conditions it is becoming increasingly common to see project finance debt being structured as a sequential series of loans, purchase
each one being refinanced by the next.

I have seen some horrific modelling as a result of this methodology and so I thought it might be a good idea to write up a neat alternative to having each debt account ‘taken out’ by another ‘debt account’ which requires the duplication of many calculations and takes up many lines without adding much to the model. Lets take a look at how its done using the following example.

Download the Excel file for this tutorial

How is it modelled

We have a slightly simplified senior debt facility of $150m which is a ‘repayment’ facility. Assuming a 7 year tenor and a base rate of 5%.. Gathering this information up within the model we might have something that looks like:

pic-1

A traditional way of modelling a refinancing is to have another set of debt repayment mechanics (facility) which take over the account that is being refinanced. This approach, approved
although arguably more transparent, viagra buy
requires a lot of lines in the spreadsheet. The way I am presenting below is a lot more efficient and further more allows inputs to be laid out more clearly too. In the schedule below, “Financing 2” refinances “Financing 1” and so on.

pic-2

In the schedule below I have set it so that the first facility is refinanced. This triggers “Financing 2” with its associated margin and fee.

pic-3

These inputs drive the following timing flags. The schedule below shows that on the dates prescribed a refinancing ‘event’ happens which sets the ‘repayment’ flag per facility.

Binary flags used for refinancing

Application of binary, refinancing and repayment flags is fundamental to this approach.

pic-4

The single account, multi-facility, method drives off of a re-setting repayment flag.

Credit foncier repayment

Should you want to try this for yourself the extract below shows the total principal and interest resulting from the initial and subsequent financings.

pic-5

The particular inputs show a smooth repayment profile which is punctuated by several refinancing events.

pic-6

If you harness this approach it is straight forward to make each refinancing a little more sophisticated with additional functionality such as

  • A grace period
  • A different repayment method
  • A Ballon % on a given date

Download the Excel file for this tutorial

Fimodo speaks to four financial modelling experts to understand how they see the benefits of financial modelling standards.

nick-crawley

Nick Crawley
Managing Director, medical
Navigator Project Finance
www.navigatorpf.com

This is a tough question – as it will annoy people in it! I will step back and qualify the sectors as ‘financial’ and the typical industries that use financial models to procure finance. I can’t comment on say decision making models in the R&D of a pharmaceutical or aero engineering company for example. I would also say that to improve standards you need to have standards in the first place, this
the industry as a whole doesn’t. It would be remiss of me not mention BPM’s efforts through the Spreadsheet Standards Review Board which definitely deserves a mention but is not widely known about in my experience globally even though they have been available since 2003. Awareness is critical step to be taken on the way to acceptance.

My perspective

I see a lot of financial models prepared by analysts, both banking and corporate, large accounting firms and one-man bands seeking to raise or advise on a project finance transaction. The upshot is that it never ceases to amaze me of the truly terrible average standards of modelling, understanding of basic principles of finance, presentation of important information and care that is taken with most projects involving some pretty big numbers! If you can’t spell principal then you shouldn’t be modelling it! – is that unfair?! I don’t think so.

What sector?

The flow of models that would benefit from improved standards are the models we see prepared by corporate (and banking) analysts and engineers in the core industrial sectors (mining, power, oil and gas, renewable fuels etc). These models generally fail on most of the benchmarks for assessing a model. This might sound harsh but it is through no real fault of their own – I think it is an awareness issue and probably due to the path that executives in those industries follow to get where they are combined with a lack of ‘corporate memory’. My experience is in these cases responsible people have generally been engineers, geologists or accountants rather than bankers or consultants focused on presentation and flexibility.

Standards?

Proper standards, let alone their improvement, require an official body, such as

  • Taxation
  • Accounting
  • Ethical lending
  • Medicine
  • Quantity surveying
  • Petroleum / Geology reporting

….the world of financial modelling hasn’t got one and without wanting to sound pessimistic I don’t think it is likely to have a meaningfully accepted one.

The reason, in my opinion, is because on the whole, financial modelling is at the moment seen as a task rather than a profession. An official body would need to be

  • Formed
  • Marketed properly
  • Accepted by a fragmented market across a wide range of industries with different challenges.
  • Regulate the practice of the standards
  • Train the industry
  • Evolve…

Where standards do exist owners are not incentivised to adapt, each probably thinking they have cracked the nut. In my mind this makes it even more important for project companies to invest in the services of consultancies who have an refined their own methodology and have had them battle tested and constantly being challenged and refined.

rickard-warnelid

Rickard Warnelid
Corality
www.corality.com

This is a very interesting question as it goes to the core of Excel financial modelling standards (or dare I say the complete lack of financial modelling standards in certain sectors).

How to define industry sectors of financial modelling?

When discussing financial modelling standards, we often assume that we are relating the discussion to the sophisticated end of the spectrum. This segment of the world typically involves people from the

  • financial sector
  • large corporates
  • research agencies
  • governments

There is however another way of looking at the world when it comes to ‘financial modelling’, even though this specific sector would probably relate to this skills set as ‘spreadsheeting’.

The large ugly world of consumer Excel spreadsheets

To understand the extent of the problems with lack of standards in consumer spreadsheets, try this at him. Ask you mum(!) to tell you a bit more about her approach to financial modelling standards. This is unlikely to more of a reply than a frustrated look from your mum. Even simplifying the question to ‘how do you build spreadsheets’ is not going to get a structured answer.

In my view the ‘sector’ that would benefit the most from improved standards is the general public! I am in no way suggesting aiming for the high standards of Corality of any other professional financial modelling consultants but a couple of hours of training would go a long way.

simon-selkrig

Simon Selkrig
Financial Modelling Professional

Hard to generalise here about specific industries, because one company may have, due to the arrival of a financial modelling professional from a reputable firm or with fantastic modelling experience, may implement best practice financial modelling across the company. This company may be one of the few in its industry, which is an industry that is not renowned for its financial modelling sophistication.

Less financially literate industries, where the finance or accounting function traditionally just reported historical, actual, differences, and performed vanilla budgeting, without more complex forecasting or sensitivity analysis

Some sectors use historical cost to form the basis of future investment decisions rather doing sophisticated financial modelling via forecasting and sensitivity analysis

Proprietary companies who haven’t had the resources and ability in the past to perform best practice financial modelling

Aviation, while although I saw some good financial models, overall it was lacking error free, value-adding and auditable financial models.

danielle

Danielle Stein Fairhurst
Principal Consultant, Plum Solutions
www.plumsolutions.com.au

One of the things I love so much about Financial Modelling is the fact that it is so easily applicable across different industries. I think any sector could really benefit from improved standards. Over the years I’ve become a “general specialist”. I specialise in Financial Modelling in Excel, but am not industry specific. I know a little bit about a lot of different industries and that’s why communicating with the client and really “getting inside their head” is really important, and assumptions documentation is absolutely critical. Working out the purpose of the model, what the required inputs and outputs are and what industry-specific information is relevant to the model is a skill in itself!

The people that come on my courses are from many different backgrounds and it’s fascinating to see how people that come from very different industries can all benefit from implementing Financial Modelling principles of best practice to their models.

Financial Modelling in Excel is, by and large, still a rather unregulated discipline and one that could hugely benefit from improved standards. There are so many different ways to model exactly the same thing which makes models difficult to follow, audit and validate.

Recently I’ve seen some larger companies starting to put some guidelines and standards around their models rather than just letting their analysts go off and create their own ad hoc models. Not only are they standardising their assumptions, but also the look and feel, as well as modelling methodology; for example having standard guidelines on how to include escalation, or how to calculate a payback period. This is a great start, and a practice that I think many companies would benefit from.

Recent posts by Fimodo

Comments for “Which industry sector would benefit the most from improved financial modelling standards?”

  1. Peter Faber says:

    Interesting article and an important topic for modellers. Pretty funny that the MD of a modelling consultancy (I’m guessing with their own methodology) should reach the conclusion that it’s ‘even more important for project companies to invest in the services of consultancies who have an [sic] refined their own methodology’ (-;

    I really like what the FAST standard is trying to do – http://www.FASTstandard.org – several firms that I do business with are implementing the standard and a standardised approach seems to have a big productivity impact.

  2. Sourav says:

    Just gone through the above post, sounds intresting.

    and indeed there are standards followed in the finanicial modelling society.

    Please visit:

    http://www.fast-standard.org/

  3. Sourav & Peter,

    thank you for your comments around financial modelling standards. I agree that FAST is a good public initiative but it is also worth highlighting that all participants in the panel have very similar standards that they are using. Arguably the Navigator ‘standard’ is the most well-known of these standards and we at Corality share that methodology when it comes to development of new financial models as it has (in my view) the best compromise of productivity and presentation. The FAST standard in my view has a higher productivity factor but the presentation is not as neat and professional.

    http://www.navigatorpf.com/resources

  4. Jane Miller says:

    Interesting conversation. I would have to disagree with Rickard – to say that the FAST standard is not professional is total rubbish.

    We’ve been using the standard in our bank for a while now and I have to say it beats any approach to modelling I’ve seen elsewhere. I only wish that more of the models we receive in the bank were built using the standard then it wouldn’t take us so long to read and understand them and it would make it much easier to change them. I look forward to the day when more companies adopt it.

  5. Jane,

    You seem to have misunderstood my comment as I was only referring to the _presentation_ of the FAST standard – not the overall impression. From a model auditor’s perspective (which is the prime focus of Corality) I completely agree that the robustness of the FAST standard way exceeds market average. However, from a decision maker’s perspective I am of the view that FAST makes calculations sheets look overly technical compared to for example the Navigator methodology. From a pure formula calculation point of view I would say that both methodologies have equally strong focus on transparency, simplicity and flexibility.

  6. John Tavern says:

    I attended a Navigator training course in New York last year and the concepts I picked up have definitely been useful. I can’t really compare it to the Fast methods but know for sure that our clients love the models I prepare now!Before we all had (5 people in the team) our separate way of building models but we are all consistent now.

  7. J says:

    Going by the “principles” of best practice modelling, I do not think there are much differences between BPM, Navigator or FAST. The fundamentals are essentially the same e.g. consistency of formula across rows, no hardcodes in formulae etc

    Some practical differences that may occur may be the degree of application of the above principles e.g.
    1) reference to blank cells (common for opening balances in greenfield projections) – do we block it out (shade grey etc) or not?
    2) what is considered as hardcodes? Is (“1″ considered a hardcode in a period counter formula? Do we have to name range “1” to “One”?

    I favour a more “principles based” than a “prescriptive based” approach and on principles/spirit there are not much difference.

    On another note, I do appreciate the efforts of Navigator in advocating its modelling techniques to the general public (client and non-client) through its website. This does help in raising the general level of good modelling practices.

    Would definitely welcome the opportunity to see some of BPM and FAST models being presented or made downloadable.

  8. Nick Crawley says:

    Clearly a topic of much debate and one I have seen many times…

    Having devoted the early part of my professional life to the topic my observations, from the point of view of a transaction professional are:

    1) Any ‘modelling methodology’ needs to be able to be written on 1 side of A4 – otherwise it gets too technical, boring and easily forgotten in the heat of a transaction.

    2) Presentation + Audibility = Confidence

    3) ‘Speed to build’ doesn’t rank in the top 10….

    The main commentators in the field come at it from different angles, all of which have different objectives. An interested, non-specialised observer should probably take a bit from each…

    Nick

  9. Nick Crawley says:

    Hi Jane,

    What other methodologies have you evaluated ?

    Nick

  10. Eris O'Brien says:

    Nick,

    I agree that standards are important and really appreciate the work that Navigator is doing in promoting better practices in financial modelling.

    I have also seen the results of an effort to implement standards across multiple government departments and my view is that the issue is people related.

    1) Decision makers don’t understand financial modelling, the complexities or the need to do it better. Consequently they don’t fund it.

    2) Financial Modelling is often seen as the dirty job that you have to do when you enter industry, which you then get past.

    3) Clients are often reluctant to pay more than the rate of a first or second year graduate when hiring one of the big firm. This not only reinforces the second point above, but means that mastery of the full set of skills is rare, and often not valued (unless a PI claim is lodged).

    4) On the client side, many of the people involved barely understand basic finance concepts well, and the analysts may be barely hanging in there. Getting them to understand the core financial principles can be hard given the project’s time constraints and when you try to get them to move to best practice you can lose them completely – they think you are being a nitpicking perfectionist and lock you out.

    5) Many clients don’t like paying for financial modelling full stop. If you think a job will take 200 hours to do properly, then they may give you 20-30 hours if you are lucky. I can’t tell you how many times I have a $500m plus project give me a few days to deliver something to take the client the next step. Do my models follow core modelling principles – yes, do I have time to follow a prescriptive standard – not a chance, the client won’t pay for the extra time and I’m out of business.

    6) Simply following a standard won’t produce a commercially sensible model. I have reviewed and fixed up models built to standards by most of the main players that were overcomplicated and nonsensical in terms of commerciality. I am doing such a job for the next fortnight. The model was of a high standard, but is almost completely unusable by the client.

    So, to take a leaf out of the software development industry, a financial model should always be designed with the end user in mind – they may be using it for years. Standards that solely promote ease of use and audit by other professional modellers will by default fail.

  11. Eris O'Brien says:

    Answering the sector question – infrastructure, mining (even the majors), non-major power (renewable companies) and more. The modelling standards are pretty terrible – with a few shining lights.

    Somehow the lack of attention on the modelling appears to be justified by how poorly the estimates (assumptions) are done.

  12. HARSH VARDHAN SINGH says:

    SIR,
    I am doing a project on financial modelling in a firm. can u suggest me how to start building a proper well designed suitable model for the firm.

Comment on this Article