I hold the firm belief that financial modelling is an art. There is no right way to replicate a business process through formula. However, there are always better ways. No matter how much work or planning has gone into your state of the art program, someone can usually find a way to improve portions of it. To build an effective model is daunting. It requires much planning, work, and collaboration. Just thinking about it could postpone the process forever. This is why I am recommending a work by John Tennent and Graham Friend titled “Guide to Business Modelling.” It may be worth perusing just for the motivational value of encourage you to start. If you already have a working model, the text contains useful suggestion for improvement.
The book begins with a chapter on how models can support decision making. For example, they can be for analysis, for assistance in making a correct choice, or for proper implementation of projects. The authors assume that most people will be using Microsoft Excel. Hence, an entire chapter titled “Useful Items to Include in the Modeller’s Toolbox,” is devoted to helpful formulas and Excel tips. They strongly suggest that the modeller use range names whenever possible to facilitate input and calculation. The usefulness of functions such as OFFSET and CONCATENATE are show, as well as short cut keys that can reduce the workload. As any user of Excel already knows, the best macros are the simple ones that can do the repetitive tasks and these too are recommended.
The book continues by reviewing the stages of the modelling process and shows these clearly on a flow cart. It suggests that the modeller start by identifying and collecting the proper data and creating the right team. The scope and goals of a project must be SMART. That is Specific, Measurable, Achievable, Relevant, and Time-bound. Each stage of the process is reviewed with the emphasis that they must align with the overall objectives of the business. The authors recommend the use of at least three Excel sheets, one for input, others for work, and one for output. Also important are version control and a development log. With these in place the authors proceed to examine a scenario based forecasting approach. They then identify and explain each stage of the process.
The budget process is a major concern to for many accountants. The book addresses this area clearly with chapters on revenue, operating cost forecasting as well as modelling for capital expenditures and working capital. In the area of revenue forecasting they explore extrapolation, causative, and judgemental techniques and provide a quick review of regression analysis. The forecasting functions already in Excel are not ignored. Other chapters cover the modelling of funding and valuation. A chapter on analysis covers this topic well and shows how to calculate useful ratios, the weighed average cost of capital (WACC), and the economic value added (EVA).
The closing chapters deal with testing and debugging. These include range tests, stress test, and user testing. These are to eliminate technical and conceptual flaws. To make the model most effective one should consider turning the spreadsheet into an application. This can be done by using hyperlinks, recording simple macros, and attaching these macros to buttons. A separate chapter is devoted to this topic and offers several good suggestions. Finally, a dreaded subject is also explored. That of documentation. I am certain that every professional person has spent many additional hours working on projects because documentation was missing or not up-to-date. Thus, it deserves additional mention and this is ably covered.
Should this text not be available at your local bookstore, it can be obtained online at economist.com.
