This book is subtitled a practitioner’s guide to applied corporate finance and my overall opinion of it is that it is a good read but working on the spreadsheets is not always such a good idea.

The book begins with Part A, a section entitled developing financial models that is split into five sections:

• Overview
• Design introduction
• Features and techniques
• Sample model
• Example model

I found this part of the book very useful and would say that Day has provided a very useful set of information and tools here. Just take a look at the summary of each section of this part of the book to see what I mean, features and techniques, for example:

• Formats
• Number formats
• Colours and patterns
• Lines and borders
• Specific colour for inputs and results
• Data validation
• ...
• Data tables
• Scenarios
• Goal Seek
• SOLVER
• Use of templates and reusable code

That is a lot of information and many techniques to start with. Day’s style, however, is thorough and competent. If you give this part of the book the time and effort it deserves you really will be well rewarded.

Section two of Part A, however, really is a must read: design introduction.

If ever I am required to discuss the design and setting up of a spreadsheet I always try to ensure that my audience begins not by switching on the computer but by taking out a pad of paper and a pencil. Day seems to agree with me in that he is essentially telling his readers the same: don’t do anything until you know what you want to do and how you want to do it. Elementary really!

Design Introduction, then, begins with the idea that whatever design you might adopt for yours spreadsheets, please be consistent, then think logically along these lines:

• Set aims and objectives
• Examine user needs ...
• Set out key variables and rules
• Break down the calculations into manageable groups
• Produce the individual modules
• Menus structure
• Management reports and summaries
• ...

If there is any doubt in your mind of what these headings mean, you need to read this part of Day’s book!

Part B of the book tells us what Day means by corporate finance, from the contents page:

• Analysing performance
• Cash flow
• Forecasting models
• Forecasting financials
• Variance analysis
• Break even analysis
• Portfolio analysis
• Cost of capital
• Bonds
• Investment analysis
• Depreciation
• Leasing
• Company valuation
• Optimisation
• Decision trees
• Risk management
• Data functions
• Data analysis
• Modelling checklist

That list is clearly comprehensive and anyone working their way through it will learn much of what they need to know about corporate finance. The spreadsheet aspects of the book relate mainly to Excel 2003 although aspects of the materials are specifically written for Excel 2007.

I should say before I go any further that it is possible to cherry pick from this book. That is, there is no need to begin with chapter 6, analysing performance, in order to move on to chapter 7, cash flow; and so on. Each chapter can be taken in isolation.

Technically, each chapter is well written and competent. There are descriptions and definitions aplenty in this book: all relevant Excel functions are demonstrated in full, there are many screen shots and all examples are fully worked as they are developed. As far as the text is concerned, none of the examples used is excessively large or complex and that is vital, I think. In a face to face session it is possible to work with delegates and readers on such extensive examples: Day has done well here so that the focus of the book is on the materials and not the examples. The size and complexity argument falls down in some of the CD based version, however: see below.

The Book’s CD

The CD that comes with the book contains all of the spreadsheet files referred to in the book and whilst this is good they do suffer from a major drawback. The files on the CD can be run from the CD and they can be installed on your local drive: easy to do and each spreadsheet follows Day’s own good habits:

• Title page with menu
• Model pages: there may be just a few or a large number ... for example, the Cash Budget Excel file contains 23 work sheets, apart from the title page and the following end of file sheets
• The formats map
• The explanation sheet
• The version sheet
• The audit sheet

Here are a few screen shots for you.

The title work sheet

One of the model work sheets

You can probably just make out the little red triangles at the top right hand corner of most of the cells in that model page: they are comments for Day to tell you a little bit about what is in that cell ... exactly what you might do for your own spreadsheet model readers. From that point of view, these models are good and useful as Day has explained everything.

Here is the unusual standards format map: this is something I have never seen before and what it does is to set out exactly when and why a cell might have a grey background ...

Charts/Graphs ... So What’s Wrong with the Excel Files then?

On the following work sheet you will see a chart:

You might notice that there is a drop down box in row 13: in this case you can click on the drop down arrow and change the focus of that chart. In this case the default chart shows the Profit from Operations; but you could change it to show any one of the following:

That list of alternative charts is extensive and clicking on each alternative works! In this model, the cash budget, there are three charts and by making changes in the drop down box you make changes to all three charts.

It is not obvious that some of the other Excel files are incomplete: that is, you cannot just open one of the files and, for example, start to make changes and then simply read a revised chart/graph. The charts and drop down boxes do not all work in all files and I have to say that trying to get to grips with the changes that are necessary to make them work are a relative challenge. I say this as someone who uses spreadsheets a lot and who has prepared many models along the lines of the models discussed in this book.

I can imagine that many practitioners won’t be as free to use these models as Day would like. I think it is telling that in a brief exchange of emails I had with Alastair Day he emphasised that this book is being widely used in Business Schools around the UK. He didn’t stress the number of commercial and non commercial companies that it might be in use in!

One of the reasons I give for the lack of transparency of the models Day provides is that in some cases, the user or reader may need to use Visual Basic to make things work. Why do I say that, as a non VB user? This comes from an email that Day wrote to me following a question relating to one of his models:

On the first problem check that Solver is marked as a reference in VB.  This is an option in the menus in VB.
On the chart the intention is to provide templates and not necessarily finished models.  If you put the code it should work.

This response came in reply to this, from me:

Dear Sirs,
 
I have just bought the book by Alistair Day, Mastering Financial Modelling in Microsoft Excel and need your help and guidance with some problems with the Excel files.
 
I have just opened the Optimisation I spreadsheet and when trying to use the menu items on the Menu worksheet I receive an error message every time and am taken to a VB screen that tells me that it can’t find the object or library ...
 
When I opened the MFM2_06_Dynamic_Graph_Ratios.slx file and tried to use the dynamic graph on the Ratios sheet I notice that some of the variables do change but not the graph itself. For example, I have just opened the file and the graph is set to show the results of the Return on Equity; when I change that to Creditor Days (but it could be anything), the name of the data set in row 12 changes to reflect my new selection but neither the data nor the chart has changed at all ... the reason is that the results in the range G12:K12 are hard coded although the Comment attaching to each cell shows the formula that assume should be in each of the respective cells.
 
Please note that I am using Excel 2007 exclusively on my computer and that these are the only two files I have tried to use so far.
 
...
 
Best wishes

Conclusions

Day writes well and I heartily recommend the accountant and financial modeller to be prepared to spend time on this book if they are looking for a practical slant on spreadsheet modelling. However, the comment I made on the size and complexity of the models does not necessarily translate to the spreadsheet files themselves.

It is always difficult to take someone else’s spreadsheet file at the best of times and in the larger and more complex examples provided by Day this is perfectly true.

© Duncan Williamson
26^th October 2008

Reference

Alastair L Day (2007)
Mastering Financial Modelling in Microsoft Excel Second Edition
FT Prentice Hall

There is a CD to accompany the book

 

© Webmaster Duncan Williamson 2008