It is the aim of this book to provide specialized training for managers, financial experts, young professionals and students in the field of financial modeling. Financial modeling is a skill which is required for everybody who wants to join the field. In addition, financial modeling skills improve career opportunities for financial experts. The book including all the Excel models in the form of a training course is suitable for teaching at universities and colleges, but can equally be used for corporate in-house training courses. The didactic structure of the book also allows self-study of the required financial modeling knowledge. Successful study and training in the area of financial modeling can be demonstrated with the title Certified Financial Modeler©. Exams are administered centrally by the German Institute for Corporate Finance (GICF).

Introduction

Ten questions define the core of financial modeling. Interested parties find answers to these questions in the two workshops on Excel and VBA as well as in the modules Financial Modeling Standards, Model Review, Financial Management, Corporate Finance, Portfolio Management and Derivatives – these are the prerequisites for successfully completing the degree of Certified Financial Modeler.

1. How can managers assure that the decision papers which they receive are not already flawed and faulty decisions are reached as a consequence?

The book combines professional knowledge in the most important fields of finance with thorough programming knowledge in Microsoft Excel and Microsoft Visual Basics for Applications (VBA). This allows the targeted use of financial theory to solve clearly specified problems while minimizing errors. Thus the users can systematically develop Excel solutions for all issues which are relevant for them.

2. How can applied tasks be modeled clearly and in a structured manner?

With the financial modeling standards we provide you with the foundations of financial modeling. Financial modeling is based on a set of principles which will fundamentally change the way you will work in the future. You can structure all models in a way that will facilitate your daily work by greatly increasing efficiency and transparency. Since financial modeling also means constant application and improvement, we begin with a workshop that demonstrates the fundamental methods of financial modeling with reference to an investment decision using MS Excel. Financial modelers aim at the highest possible quality of a financial model. We show you how the model review process can be used to assess the model and assure its high quality.

3. How can macros be used in the modeling process?

The VBA programming workshop provides a first introduction to the programming of macros. They can be utilized to greatly enhance the efficiency of all office applications. It is possible to standardize routines that are very hard to standardize with the basic Microsoft programs. It is impossible to deepen the knowledge about VBA on a purely theoretical level. Required is the constant work with VBA, experimentation and improvement based on the premise of learning by doing. Financial modeling experts always have solid knowledge of VBA and are able to independently develop new programs. Is the knowledge of VBA absolutely essential for financial modeling? We do not think so, since Microsoft Excel offers solutions for almost all types of questions. But knowledge of VBA can help to significantly facilitate the workflow, especially with regard to individualized surfaces and efficient approaches. But it also needs to be pointed out that the use of VBA involves black box solutions, in other words models that are hard to trace, especially for third parties that lack the necessary VBA knowledge. Should those models contain programming errors, it is extremely difficult to identify and remedy them.

4. How can financial modeling be used in applied work in finance?

The foundations of financial modeling are applied in the modules to the core fields of modern finance, namely

• Financial Management,

• Corporate Finance,

• Portfolio Management and

• Derivatives.

The focus is not on basic textbook knowledge, but rather on techniques and approaches used by professionals in applied work in their respective fields. The combination of thorough knowledge in Excel and VBA with professional knowledge in finance defines our understanding of financial modeling. Combining scientific approaches with applied work is our motto. Consequently the team of authors is made up of practitioners in the fields of financial management, corporate finance, portfolio management and derivatives as well as academics with a specialization in these fields.

5. How to create financial models for Financial Management?

Fundamental models of Financial Management are presented in this chapter. They serve as the foundation for the following finance topics of corporate finance, portfolio management and derivatives. The net present value method was already presented in the Excel workshop in order to show some of the possibilities for financial modeling. In this chapter, additional methods of investment analysis, both dynamic and static, are presented.

Solid financing decisions initially require an analysis of the profit and loss statement, the balance sheet and the cash flow statement. This is done in the context of an analysis of key figures. Concerning financing decisions, instruments of internal and external financing are distinguished. The main element of internal financing is financing out of the free cash flow. With regard to external financing, a distinction is made between the instruments of equity financing and debt financing.

6. How to create financial models for corporate finance?

The chapter on corporate finance focuses on issues of corporate planning and valuation, which are at the core of every corporate finance transaction. Since corporate valuation is a central topic in the field of investment theory, this chapter, the Excel workshop, the VBA workshop and the chapter on Financial Management are tightly linked.

The implementation of knowledge in corporate valuation in financial modeling offers the big advantage of systematically capturing and understanding the complex linkages between planning, determination of cash flows and cost of capital and the final valuation. The determination of the cost of capital in corporate valuation is based on modern portfolio theory. In this way, a link is established with the module on portfolio management.

7. How to create financial models for portfolio management?

Portfolio management deals with the allocation of wealth. Central aspects for the asset manager are the tradeoff between return and risk of the portfolio and the measurement of these variables. It is also demonstrated how portfolios can be optimized in the context of active and passive portfolio management. This is done with reference to the capital market models that are generally accepted by practitioners.

8. How to create financial models in the field of derivatives?

The valuation of options and futures is explained in the chapter on derivatives. Derivatives remain mysterious for many students and practitioners in the field of finance and are thus frequently reserved for a small group of experts. In our opinion, this is due to the fact that the knowledge about derivatives is not taught in a transparent manner. In financial modeling it is demonstrated how the valuation of options and futures is conducted, which input factors are required and what type of algorithm is behind the calculations. Once the valuation of derivatives in financial modeling has been understood, it becomes clear that the process resembles the valuation of a company and that questions of risks are treated in a manner that is familiar from portfolio management.

9. Who needs a deep understanding of financial modeling?

All graduates and practitioners in the field of finance should possess a thorough knowledge of financial modeling. But experience shows that many financial experts have no or only rudimentary financial modeling skills.

At the same time, banks, financial service providers and other companies for their finance and controlling departments are urgently looking for people with thorough knowledge in Excel and VBA, who are able to develop models that address concrete applied issues. In that sense, financial modeling skills are competencies that offer outstanding opportunities for beginning or advancing a career in finance.

10. How can I demonstrate my financial modeling skills?

Everybody who has worked through this book and digested its contents possesses outstanding skills in the field of financial modeling. In order to give you an opportunity to showcase your qualifications, we offer an examination which, upon successful completion, grants the title of “Certified Financial Modeler.” This book defines the contents of the certification course for the “Certified Financial Modeler.” Participants in the certification course “Certified Financial Modeler” are provided with practice questions and sample solutions, which are structured in the same way as this book and which clarify in a concise manner the linkages among the individual aspects contained in all eight parts.

The examination to obtain the title of “Certified Financial Modeler” is offered and administered exclusively by the German Institute of Corporate Finance – GICF. Additional information about the “Certified Financial Modeler” including the registration forms can be found at www.certified-financial-modeler.de. We wish you gainful reading and look forward to your suggestions and comments at info@dicf.de.

Part I Financial Modeling Standards

Part II Model Review

Part III Workshop Excel: From the Real World to the Financial Model

Part IV VBA

Part V Financial Management

Part VI Corporate Finance

Part VII Portfolio Management

Part VIII Derivatives

Participants of the Certified Financial Modeler (CFM) receive the models with the calculations and figures listed in the book for the processing of their case study.

• Chapter 2 Financial Modeling Standards
• Chapter 3 Model Plausibility Checks
• Chapter 4,5,6 Workshop Excel 1
• Chapter 4,5,6 Workshop Excel 2
• Chapter 4,5,6 Workshop Excel 3
• Chapter 4,5,6 Workshop Excel 4
• Chapter 4,5,6 Workshop Excel 5
• Chapter 4,5,6 Workshop Excel 6
• Chapter 4,5,6 Workshop Excel 7
• Chapter 4,5,6 Workshop Excel 8
• Chapter 8 Investment Appraisal
• Chapter 9 Financing
• Chapter 10 - 11 Corporate Finance
• Chapter 12 - 13 Portfolio Management
• Chapter 14 Derivatives