planning, valuations, mergers, projects, and data analysis. For a number of years Michael was an instructor at the School of Business & Economics of the University of Laverne, Athens Campus. A regular contributor to financial and business journals, Michael has taught specialized courses in Financial Analysis & Business Modelling, Credit Analysis & Cash Flow Forecasting, and Investment Evaluation & Appraisal.
Michael holds both MSc and PhD degrees from the Department of Physics and the Electronic & Electrical Engineering Department of University of Surrey as well as an MBA from the University of La Verne California. He also holds a BSc degree in Applied Accounting from University of Oxford Brookes (with honours) and is a Fellow of the Association of Chartered Certified Accountants (ACCA).
Part One
Developing Corporate Finance Models
Chapter 1
Introduction
Chapter 1 answers some simple questions about financial modelling, such as: What is it? Who does it? What are the steps in building a financial model? But above all, why is financial modelling the single most important skill-set for the aspiring finance professional?
The framework for the development of a spreadsheet-based financial model is illustrated by using a simple concrete example. A spreadsheet is used in order to calculate the funding needs of a 2/10 net 30 credit policy on a certain turnover. The inputs and the output of the model are defined. Building this model is relatively straightforward. The model-builder needs to input estimates for certain items (i.e. turnover) and then make sure that the mathematical formulae are correct. From this simple base, the steps of the financial modelling process are described in order to build sophisticated and interconnected models for the income statement, balance sheet, and cash-flow statement, as well as “good/bad/base” scenarios that can be changed with a simple click or two. This ability of spreadsheets to deal with a lot of numbers, work with them, and produce answers is stressed, as well as the use of Excel as the ideal tool for financial modelling.
1.1 WHAT IS FINANCIAL MODELLING?
If you Google the term “financial model” you will get approximately 350 million hits. Yes, that's right. Financial modelling has become the single most important skill-set for the aspiring finance professional. But what exactly is a financial model and what does financial modelling do? Investopedia1 defines financial modelling as the process by which a firm constructs a financial representation of some, or all, aspects of it. The model is usually characterized by performing calculations, and makes recommendations based on that information. Moreover, Moneyterms2 defines a financial model as anything that is used to calculate, forecast, or estimate financial numbers. Models can range from simple formulae to complex computer programs that may take hours to run. Finally, according to Wikipedia,3 financial modelling is the task of building an abstract representation (a model) of a real-world financial situation. This is a mathematical model designed to represent (a simplified version of) the performance of a financial asset. Similar definitions exist on other financial websites like BusinessDictionary.com, Divestopedia, etc. Financial modelling is a general term that means different things to different people. In the context of this book it relates to accounting and corporate finance applications and usually involves the preparation of detailed company-specific models used for decision-making purposes and financial analysis. While there has been some debate in the industry as to the nature of financial modelling – whether it is a tradecraft, such as welding, or a science – the task of financial modelling has been gaining acceptance and rigour over the years.
Financial models can differ widely in complexity and application: some are simple 1-page sheets built to get a “quick-and-dirty” estimate of next year's net income. Some span more than 40 worksheets and project various scenarios of the value of a company.
Although financial models vary in scope and use, many share common characteristics. For example:
1 Reported financials for past years are the basis for most projection models. To forecast financial statements we make use of key performance drivers derived from historical records.
2 Projecting future years for the 3 main financial statements – the income statement, the balance sheet, and the cash flow statement – is typically the first step. Income statement estimates for EBITDA and interest expense as well as balance sheet leverage statistics such as debt/equity and interest coverage are often the most important model outputs.
3 Incorporating financial statement analysis through the use of ratios. More often profitability, liquidity, and solvency ratios are calculated in order to pinpoint any weaknesses in the financial position of a company.
4 Performing valuation. Valuation involves estimating the value of a company using various techniques although the most commonly used are comparable company multiples and discounted cash-flow modelling.
5 Conducting various forms of sensitivity analysis after a forecast model has been built. These analyses are often the real reason a model was built in the first place. For example, sensitivity analysis might be used to measure the impact on one model output – say free cash flow – from the changes of one or more model inputs, say revenue growth or the company's working capital needs (“What happens to free cash flow if we increase sales growth by an extra 2 % next year and at the same time reduce the payment terms to the suppliers by 5 days?”).
Financial modelling is about decision-making. There is always a problem that needs to be solved, resulting in the creation of a financial model.
Financial modelling is about forecasting. In the post 9/11 environment, forecasting has become much more difficult because the economic environment has become much more volatile. Since profit is not the only important variable, a projected financing plan into the future is imperative for a business to succeed.
Financial modelling is the single most important skill-set for the aspiring finance professional. It is as much an art as a science. Financial modelling encompasses a broad range of disciplines used across many fields of finance. A good financial modeller must first of all have a thorough understanding of Generally Accepted Accounting Principles (GAAP) and the statutory accounting principles. They must know how the 3 financial statements work and how these are linked together. They need to know corporate finance theory and be able to apply it in valuation exercises. They will have to be adequate in forecasting. Finally, they will have to think analytically, be good at business analysis, possess industry-specific knowledge and, last but not least, have strong Excel skills. The applications of the above skill-sets are immense and somebody can develop them by applying and also by practising them.
In this book we look at the basics of most of these disciplines. In Chapter 2 we cover the fundamentals of accounting theory and the interrelationship of the 3 financial statements. In Chapter 3 we apply this theory in practice to build the proforma financial statements of a sample company of interest. In Chapter 4 we examine various forecasting techniques related to sales, costs, capital expenditures, depreciation, and working capital needs. In Chapter 5 we cover the theory behind Discounted Cash Flow (DCF) valuation.
During the financial crisis, the G20 tasked global accounting standard-setters to work intensively towards the objective of incorporating uncertainty into International Financial Reporting Standards (IFRS) (e.g. favourable and unfavourable scenarios are requested in estimating the fair value of an investment). In addition businesses are asked to prepare various scenarios in order to prove that they will be financially viable into the future and thus secure funding from their lenders or raw materials from their suppliers. Chapters 6, 7, and 8 deal with these types of uncertainty. Chapter 6 deals with sensitivity analysis, Chapter 7 elaborates on building
