Many studies over the past ten years have shown that up to 90% of models contain significant errors. Generally these errors go undetected, but there have been some well-publicised cases where they have resulted in financial loss and embarrassment.

For example, an employee at a US investment fund omitted the minus sign on a figure of $1.2 billion while transferring data between spreadsheets, thus “creating” a $2.3 billion gain instead of the actual $0.1 billion loss. The company announced to shareholders a distribution of $4.32 a share; a month later, when the error was detected, it had to announce that there would be no distribution (further information at Dr. Panko’s website; see “Other sites”).

The vast majority of such errors could have been avoided by following a small number of key quality principles in building models, as explained below. (References to spreadsheet features assume the use of Microsoft Excel^TM, but are equally applicable to other spreadsheet software.)

The hallmark of a good model is that it is clearly laid out and easy to understand, so minimising the possibility of errors such as that mentioned above. Some key steps to achieving this are:

• Design, then develop, then test
• Separate the inputs from the calculations
• Use only one formula per row
• Avoid balancing figures
• Use protection and locking

Read the rest of this entry »

There are many issues that can present a challenge in modelling. Discussion of these can be found at some of the websites listed on our Links page, and there are also many books covering spreadsheet software packages, although these tend to focus on technical points rather than quality and business issues.

Some of the most frequently encountered issues are:

• Circular formulas
• Interest calculations
• Currency and inflation
• Mixed time periods
• Large models
• Visual Basic^TM for Excel^TM

Read the rest of this entry »

Mike worked at KPMG from 1982 until 1999, initially as a tax specialist and then as a financial modelling consultant. From 1996 to 1999 he was a Principal Consultant responsible for developing KPMG’s London-based financial modelling service.During this time he wrote a Financial Modelling Handbook which set out the standards for KPMG’s financial modelling work, and he was also involved in re-designing and presenting training courses in financial modelling, both within KPMG and to external clients.

In 1999 Mike left KPMG to fulfil a longstanding ambition to travel around the world. On his return to the UK in 2000 he then set up his own financial modelling consultancy, TheModelAnswer.

You can download a printable version of Mike’s CV in MS Word 6.0 format (right-click then click on ‘Save Target As’ or ‘Save Link As’).

Following are examples of some spreadsheet models developed by Mike over the course of his career:

• Trading terms evaluation
• Pricing decision support
• Transport franchise bid
• Power generation industry
• Debt restructuring
• Payroll and tax grossing-up
• Lease evaluation and rental adjustment
• Management accounting and consolidation

Read the rest of this entry »