® BOOKS FOR PROFESSIONALS BY PROFESSIONALS A v o n The Basics of Financial Modeling The ability to create and understand financial models that assess the valuation of a company, the projects it undertakes, and its future earnings/profit projections is one of the most valued skills in corporate finance. However, while many business professionals are familiar with financial statements and accounting reports, few are truly proficient at building an accurate and effective financial model from the ground up. In his short book, The Basics of Financial Modeling—an abridgement of his Handbook of Financial Modeling—Jack Avon equips financial professionals with a quick overview of the tools they need to monitor a company’s assets and project its future performance and prospects for specific initiatives. Based on the author’s extensive experience building models in business and finance— and teaching others to do the same—The Basics of Financial Modeling takes readers step by step in a quick-read format through the financial modeling process, starting with a general overview of the history and evolution of financial modeling. It then moves on to more technical topics, such as the principles of financial modeling and the proper way to approach a financial modeling assignment, before covering key application areas for modeling in Microsoft Excel. Designed for beginning to intermediate modelers who wish to expand and enhance their knowledge of using Excel to build financial models, The Basics of Financial Modeling covers: • The accounting and finance concepts that underpin working financial models • How to approach financial issues and solutions from a modeler’s perspective • The importance of thinking about end users when developing a financial model • How to plan, design, and build a financial model A nuts-to-bolts guide to solving common financial problems with spreadsheets, The Basics of Financial Modeling is a one-stop resource for anyone who needs to build or analyze financial models. Shelve in ISBN 978-1-4842-0872-4 53999 Business/Accounting and Economics User level: Beginning–Advanced SOURCE CODE ONLINE 9781484208724 www.apress.com For your convenience Apress has placed some of the front matter material after the index. Please use the Bookmarks and Contents at a Glance links to access them. Contents About the Author                                             ix Acknowledgments                                             xi Introduction                                                 xiii Chapter 1: Financial Modeling: An Overview                     1 Chapter 2: Financial Modeling Best Practices                     11 Chapter 3: Modeling Functions and Tools                        51 Chapter 4: Planning Your Model                              115 Chapter 5: Testing and Documenting Your Model                131 Chapter 6: Designing and Building Your Model                  147 Chapter 7: Financial-Based Calculations                        181 Chapter 8: Logical-and Structural-Based Calculations            209 Appendix A: Keyboard Shortcuts                               225 Index                                                       227 Introduction This book is written from the perspective of a financial modeler and, even though the concepts may seem rather basic to the seasoned modeler, most of these concepts are the building foundations for any financial modeler. it has been becoming increasingly evident in recent years that the demands— and therefore the requirements—of the financial modeler have been greatly expanded from the typical spreadsheet jockeys of yesteryear. The playing field has changed, and modelers are now seasoned professionals in their own right and come from areas such as accounting, engineering, and legal. Modelers are expected to be commercially savvy as well as analytical. in addition, while modeling requires a certain amount of hands-on development, the modern-day financial modeler should also be comfortable with presenting and communicating to all levels of business. Your typical modeler today is not just someone who has a strong command of iT but also has other skills such as project management, change control, financial and commercial acumen, stakeholder management, and communication skills. finally, there is a presumption that modelers are highly skilled with excel (and to some extent Microsoft access, particularly for data analytics). Though touted as a spreadsheet application, Microsoft excel is so much more. in the right hands, it’s a serious development tool with very powerful analysis features. as the versions of excel have improved since excel 2000, it has become a respected development platform in its own right. applications written using excel are now often found alongside those written using Visual Basic for applications (VBa) code, c++, .neT, and so on, as part of many corporations’ core suite of business-critical applications. indeed, excel is sometimes used for the client end of web-based applications, made particularly easy with excel XMl import/export features. if i had to select just one key takeaway to impart to financial modelers for your futures, it would be the indispensability of your acquiring the ability to work with and understand your co-workers and the wider public. There is a shift happening such that pure modeling is no longer enough. With all the information you gain from this book, you must realize that all your technical skill will mean very little unless you can combine it with engaging people. do that and your career in modeling will take you to the higher echelons in a relatively short time. xiv Introduction My hope for you as you read this book is that you acquire an inside understanding of some of the aspects of modeling that will make you come back for more. i also hope that you become more aware of the numerous possibilities of how to model and don’t feel restricted to following one methodology. Maybe along the way, as you read this book, you will begin to feel comfortable with starting to call yourself a financial modeler. C H A P T E R 1 Financial Modeling: An Overview This chapter serves as a background to financial modeling by providing a definition and describing the financial modeling environment. Financial Modeling: A Definition Modeling is a specific discipline that often but not exclusively uses spread- sheets. Financial modeling is, in fact, a part of financial analysis and emphasizes the interpretation and output of inputs and variables. A suitable definition should mention processes, variables, and quantitative relations, hence the fol- lowing definition: Financial modeling is a theoretical construction of a project, process, or transaction in a spreadsheet that deals with the identification of key drivers and variables and a set of logical and quantitative relationships between them. 2 Chapter 1 | Financial Modeling: An Overview Microsoft Excel as the Modeler’s Tool Financial modeling is very closely linked with the history of spreadsheets, specifically the development of Microsoft Excel. Over time, spreadsheets have become the primary tool for the flexible manipulation of data, and Excel is the dominant spreadsheet tool. Of course, there are a number of other model- ing software programs, including Oracle Essbase, MoSes, and SAS Financial Management, that primarily aid in financial analytics. One aspect that almost all these proprietary software programs lack, however, is flexibility. The result has been a boon for Excel, as it has filled the gap due not only to its flexibility but also because of its relatively low learning curve. The other reason for Excel’s success as a modeler’s tool is due to the suc- cess of the Microsoft Office suite. MS Office is arguably the most dominant and widely used suite of applications in software history. As its dominance has increased, so has the number of applications it offers and the versatility of those applications. For modelers, being able to design and build models in Excel, write Visual Basic for Applications (VBA) code, and integrate the code (VBA macros) into the larger Visual Basic suite to create custom software applications, is a real benefit that is unparalleled. Modelers can also link their models to a data store by using Access, which can be integrated together with SQL server, and then create dynamic, data-driven presentations through PowerPoint. They can then take these PowerPoint presentations and produce automated documentation and user manuals in MS Word all in one place. Functional spreadsheet applications were first developed in the 1980s with the release of Lotus 1-2-3. Microsoft Excel for Windows was first released in 1987 as Excel 2.0. The very first version was Excel 1.0, which was released in 1985 and was an Apple Mac version only. It was not until the release of Microsoft Excel 95 that spreadsheet applications became widely used within the business world. It was also at this point that financial modeling began to emerge as a bona fide discipline around the world. Since the release of Microsoft Excel 95, spreadsheet application functionality has improved at an exponential rate, providing model developers with the tools to construct increasingly sophisti- cated models. Today, modeling is recognized for its ability to enable business decision-making and solve often complex questions about the future. The demand for expe- rienced modelers has risen steadily over time. This trend will continue for some years to come because more and more businesses are realizing that an experienced financial modeler can provide significant added value to any process, business, or project. In order to meet the increasingly complex demands of model users, financial modelers have significantly improved their modeling skills, not only by being technical modelers but by being experts in business in their own right. Hence, it’s quite noticeable that modelers are increasingly coming from professions such as accounting and engineering. The Basics of Financial Modeling 3 ■ Note Excel Versions to Date Excel 1.0 This version was released by Microsoft in September 1985. This was not a serious modeling tool at this stage and therefore lacked most of the common functionality that we associate with Excel applications today. Excel 5.0 (Version 5) This version was released in 1993 and gave us the first look at Visual Basic for Applications (VBA). Excel 95 (Version 7) Released in 1995, Excel 95 was a reworked, 32-bit version of Excel 5.0. Although there was little change from previous versions, this version was noticeably more stable and had better integration with the other Microsoft Office applications. Excel 97 (Version 8) This version was released in 1997 and was a major upgrade. Significant changes included a full VBA editor with separate code modules, user forms, and class modules. One of the most useful enhancements for VBA programmers was the introduction of Event Procedures. The entire structure of Command Bars (menus and toolbars) was completely changed and enhanced. On the user interface side of Excel, Conditional Formatting and Data Validation were added. Excel 2000 (Version 9) Released in 1999, an updated version of the VBA language (VBA6) was introduced, incorporating modeless user forms and some much needed new language functions such as Join and Split. Excel 2000 was the first version to support the COM Add-in model, which allowed users to write add-ins that could work in any Office applications. Excel 2002 (Version 10) Released in 2001, there were no substantial changes on the VBA component of excel. On the user interface side, Smart Tags and the Formula Evaluation tool were probably the most prominent additions. The overall appearance of Excel was modified to provide a softer color palette, and the ability to recover corrupt files was substantially improved. Excel 2003 (Version 11) This version only had some minor enhancements but did include the introduction of XML. 4 Chapter 1 | Financial Modeling: An Overview Excel 2007 (Version 12) Released in 2007, this version was a major upgrade from the previous version. Similar to other updated Microsoft Office products, Excel 2007 used the new ribbon menu system. This was different from what users were familiar with, but the number of mouse clicks needed to reach a given functionality was generally less. For example, removing grid lines only required two mouse clicks. Most business users would agree that the replacement of the straightforward menu system with the more convoluted ribbon dramatically reduced productivity in the beginning, although this method is now accepted as being more intuitive. Excel 2010 (Version 14) Released in 2009, this version featured a few enhancements but was more about making Excel an online application. Excel 2010 was designed to enable working through cloud services. Even with the enablement of cloud services to promote collaborative working, there still remains the central issue that Excel is a single-user environment and therefore cloud has not had the impact that could have been gained were it a multi-user application. Excel 2013 (Version 15) This version was released in August 2012 and included minor enhancements, such as the increased smoothness of the user interface and the enhancement of the display of graphics. Where Are Financial Models Used? When we talk about financial modeling, we are really looking at quite a range of tasks, including data analysis, scenario analysis, financial management, information processing, software development, and project management. Models are very specific to each situation and will often contain confidential information. As a result, there are very few physical examples of financial models available in the public domain. Financial models are used in the finance departments of most organizations, but particularly are employed in these areas: • Investment banking: Risk modeling, option pricing models, and various quantitative models • Insurance: Insolvency models, actuarial models, risk models (Monte Carlo simulations) • Retail banking: Funding models (models that can assess client viability by using a number of metrics), credit models • Corporate finance: Capital budgeting models, cost of capital, financial statement analysis, governance models (SOX compliance testing) The Basics of Financial Modeling 5 • Governments and institutions: Econometric analysis-based models (used to forecast the socioeconomy in a country or region), macroeconomic models (used to analyze the like effect of government policy decisions on variables such as foreign exchange rates, interest rates, disposable income, and the gross national product) • Outsourcing and BPO (business process outsourcing): Cost modeling, price and margin models, bid models There are differing types of financial models, depending on their objectives and goals, such as the following: • Transactions: Used in acquisitions, divestments • Investments: Used in capital projects such as procuring new equipment and property development • Corporate finance: Used to assist in deciding the best capital/corporate structure of a company • Project financing: Used by banks to show if borrowers will be able to meet repayments and stay within the covenants set by the bank • Joint venture: Used to calculate returns to various parties at various exit times • Bids and tenders: Bid models are used to assess the cost of the proposal and to derive the final market price. The following figures are from a commercial bid model that was designed and built for a large telecommunications organization in order to understand the profits, losses, and cash flows for their outsourcing deals. As such, Figure 1-1 to Figure 1-4 should provide a visual as to what the parts of a specific type of financial model may look like.