Table Of ContentFinancial
Modeling
An Introductory Guide to Excel
and VBA Applications in Finance
Joachim Häcker
Dietmar Ernst
Global Financial Markets
Global Financial Markets is a series of practical guides to the latest financial
market tools, techniques and strategies. Written for practitioners across a range
of disciplines it provides comprehensive but practical coverage of key topics
in finance covering strategy, markets, financial products, tools and techniques
and their implementation. This series will appeal to a broad readership, from
newentrantstoexperiencedpractitionersacrossthefinancialservicesindustry,
includingareassuchasinstitutionalinvestment;financialderivatives;investment
strategy; private banking; risk management; corporate finance and M&A,
financialaccountingandgovernance,andmanymore.
Moreinformationaboutthisseriesat
http://www.springer.com/series/15011
JoachimHäcker•DietmarErnst
Financial Modeling
An Introductory Guide to Excel and VBA
Applications in Finance
JoachimHäcker DietmarErnst
GermanInstituteof GermanInstituteof
CorporateFinance CorporateFinance
Nürtingen,Germany Nürtingen,Germany
GlobalFinancialMarkets
ISBN978-1-137-42657-4 ISBN978-1-137-42658-1(eBook)
DOI10.1057/978-1-137-42658-1
LibraryofCongressControlNumber:2017946169
©TheEditor(s)(ifapplicable)andTheAuthor(s)2017
The author(s) has/have asserted their right(s) to be identified as the author(s) of this work in
accordancewiththeCopyright,DesignsandPatentsAct1988.
This work is subject to copyright. All rights are solely and exclusively licensed by the Publisher,
whether the whole or part of the material is concerned, specifically the rights of translation,
reprinting,reuseofillustrations,recitation,broadcasting,reproductiononmicrofilmsorinanyother
physicalway,andtransmissionorinformationstorageandretrieval,electronicadaptation,computer
software,orbysimilarordissimilarmethodologynowknownorhereafterdeveloped.
The use of general descriptive names, registered names, trademarks, service marks, etc. in this
publicationdoesnotimply,evenintheabsenceofaspecificstatement,thatsuchnamesareexempt
fromtherelevantprotectivelawsandregulationsandthereforefreeforgeneraluse.
Thepublisher,theauthorsandtheeditorsaresafetoassumethattheadviceandinformationinthis
bookarebelievedtobetrueandaccurateatthedateofpublication.Neitherthepublishernorthe
authorsortheeditorsgiveawarranty,expressorimplied,withrespecttothematerialcontained
hereinorforanyerrorsoromissionsthatmayhavebeenmade.Thepublisherremainsneutralwith
regardtojurisdictionalclaimsinpublishedmapsandinstitutionalaffiliations.
Coverimage©BlackoutConcepts/AlamyStockPhoto
CoverdesignbyAliceTomlinson
Printedonacid-freepaper
ThisPalgraveMacmillanimprintispublishedbySpringerNature
TheregisteredcompanyisMacmillanPublishersLtd.
Theregisteredcompanyaddressis:TheCampus,4CrinanStreet,London,N19XW,UnitedKingdom
Preface
This book is structured around the instruments of modern Finan-
cial Management. Strategic decisions of corporations are increasingly
supported by financial models. These are created by an expert, the
financial modeler. In the context of financial modeling he creates
applications based on financial knowledge in the context of modern
standardized software – Excel in particular.
This textbook provides comprehensive training in the field of fin-
ancial modeling for managers, financial experts, young professionals
and students. These skills provide excellent career prospects. Quick
and interactive learning is assured due to the structure as a training
course which includes applied examples that are easy to follow. All
applied examples contained in the book can be reproduced step by
step with the help of the Excel files. These files are also available
for download at www.certified-financial-modeler.de in addition to the
web pages of the publisher provided on the first page. There you find
additional files dealing with the topic of financial modeling. Best res-
ults will be obtained if you directly apply the theoretical contents and
make use of the online-offering as you work your way through the
text.
This textbook is suitable for teaching at the university and college
level as well as for in house-training at corporations. It is also possible
tostudythematerialindependently.Masteryofthesubjectmattercan
be demonstrated via the title “Certified Financial Modeler” which is
awarded by the German Institute of Corporate Finance.
v
vi Preface
This book contains the following topics:
(cid:129) Financial Modeling Standards
(cid:129) Model Review
(cid:129) Financial Modeling using Excel
(cid:129) Financial Modeling using VBA
(cid:129) Investment Appraisal and Financing
(cid:129) Corporate Finance
(cid:129) Portfolio Management
(cid:129) Derivatives
We would like to thank the publishing house Macmillan Publishers
and its employees for the always pleasant and constructive coopera-
tion. A special thank you goes to Aimee Dibbens and Nicole Tovstiga
from Macmillan Publishers for the ideas and support during the cre-
ation of this volume. We would also like to express our thanks to
our sponsor, Volksbank Göppingen. Furthermore, we would also like
to thank Lukas Breuer, Martin Geißler, Shushanik Hakobyan, Kathar-
ina Hirning, Timo Huguet, Nico Kletti, Finn Pilath, Marc Schurer, Julia
Stange, Thorsten Steinhilber, Julian Wellge and Simon Zieglgruber for
their support and contributions,which made the success of this book
possible. We also thank the numerous readers for their contributions
concerning the contents of the book.
We wish you enjoyable reading and success in financial modeling.
The editors Dietmar Ernst and Joachim Häcker with the authors
Michael Bloss Mario Dirnberger
Dietmar Ernst Joachim Häcker
Manuel Kleinknecht Georg Plötz
Sebastian Prexl Bernhard Röck
Contents
1 Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
1 HowCanManagersAssurethattheDecisionPapers
whichTheyReceiveAreNotAlreadyFlawedandFaulty
DecisionsAreReachedasaConsequence?. . . . . . . . . . . . . 1
2 HowCanAppliedTasksBeModeledClearlyandina
StructuredManner? . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
3 HowCanMacrosBeUsedintheModelingProcess? . . . . . . . 2
4 HowCanFinancialModelingBeUsedinAppliedWork
inFinance? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
5 HowtoCreateFinancialModelsforInvestmentandFinancing? 3
6 HowtoCreateFinancialModelsforCorporateFinance? . . . . 3
7 HowtoCreateFinancialModelsforPortfolioManagement? . . 4
8 HowtoCreateFinancialModelsintheFieldofDerivatives? . . 4
9 WhoNeedsaDeepUnderstandingofFinancialModeling? . . . 5
10 HowCanIDemonstrateMyFinancialModelingSkills? . . . . . 5
2 FinancialModelingStandards . . . . . . . . . . . . . . . . . . . . . . 7
1 ExecutiveSummary . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
2 Introduction,Structure,LearningOutcomesandCaseStudy . 8
3 FoundationsofFinancialModeling . . . . . . . . . . . . . . . . . . 10
3.1 WhatAreModelsandWhatIsFinancialModeling? . . . 10
3.2 AnalyzingtheModelRequirementsandDefining
aTaskList . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
3.3 StructuringFinancialModelsinModules . . . . . . . . . . 14
vii
viii Contents
4 Current State of Financial Modeling in Theory and
AppliedWork . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
4.1 LiteratureonFinancialModeling . . . . . . . . . . . . . . . 16
4.2 DifferentApproaches–IdenticalAims . . . . . . . . . . . 18
5 FinancialModelingStandards . . . . . . . . . . . . . . . . . . . . . 23
5.1 Top-10FinancialModelingStandards . . . . . . . . . . . . 23
5.2 150FinancialModelingStandards . . . . . . . . . . . . . . 25
6 ImplementingtheTop-10FinancialModelingStandards
withReferencetoanExample . . . . . . . . . . . . . . . . . . . . 36
6.1 DefinetheModelingPurpose . . . . . . . . . . . . . . . . . 37
6.2 Separate the Problem into independent
Subsections(Modules) . . . . . . . . . . . . . . . . . . . . . 38
6.3 ProvideaGraphoftheFlowofDataandthe
ModelStructure . . . . . . . . . . . . . . . . . . . . . . . . . 40
6.4 SeparateInputsfromOutputs. . . . . . . . . . . . . . . . . 43
6.5 ChooseaUnifiedLayoutfortheWorksheets . . . . . . . 44
6.6 UseUnifiedFormatting . . . . . . . . . . . . . . . . . . . . . 46
6.7 AvoidComplexFormulasandUseOnlyOneType
ofFormula. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
6.8 AvoidCircularReferences . . . . . . . . . . . . . . . . . . . 50
6.9 WorkwithControlFunctions . . . . . . . . . . . . . . . . . 50
6.10 PresenttheResultsProfessionally . . . . . . . . . . . . . . 51
7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
FurtherReading. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56
3 ModelReview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
1 ExecutiveSummary . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
2 Introduction,StructureandLearningOutcomesand
CaseStudy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
3 FundamentalsofModelReview. . . . . . . . . . . . . . . . . . . . 61
3.1 TheTerm“ModelReview”. . . . . . . . . . . . . . . . . . . 61
3.2 StepsintheModelReviewProcess . . . . . . . . . . . . . 63
4 ErrorsinFinancialModels . . . . . . . . . . . . . . . . . . . . . . . 65
4.1 QualitativeErrors . . . . . . . . . . . . . . . . . . . . . . . . 66
4.2 QuantitativeErrors . . . . . . . . . . . . . . . . . . . . . . . 66
5 ErrorDetection–RecognizingandFindingErrors . . . . . . . . 67
5.1 LogicInspection . . . . . . . . . . . . . . . . . . . . . . . . . 67
5.2 Tests. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
Contents ix
5.3 AnalysisTools . . . . . . . . . . . . . . . . . . . . . . . . . . . 70
6 ExamplesofApplicationsofAnalysisTools . . . . . . . . . . . . 71
6.1 BriefMarketOverviewofAnalysisTools . . . . . . . . . . 72
6.2 ModelReviewwithMicrosoftExcel. . . . . . . . . . . . . 75
6.3 ModelReviewwithOperisAnalysisKit(OAK) . . . . . . 92
7 ControlCalculations . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
7.1 ControlCalculationsthatShowtheConcrete
NumericalDeviation . . . . . . . . . . . . . . . . . . . . . . 101
7.2 BinaryControlCalculations . . . . . . . . . . . . . . . . . . 102
8 MeasurestoAssessthePlausibilityoftheResults. . . . . . . . . 105
8.1 SensitivityAnalysis. . . . . . . . . . . . . . . . . . . . . . . . 105
8.2 ScenarioAnalysis. . . . . . . . . . . . . . . . . . . . . . . . . 106
9 Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
10 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
Notes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
FurtherReading. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
4 WorkshopExcelPartI . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
1 ExecutiveSummary . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
2 Introduction,Structure,LearningOutcomesandCaseStudy . 113
3 WhyStudyExcel?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
4 DevelopingaFinancialModel . . . . . . . . . . . . . . . . . . . . . 117
4.1 DefiningtheTaskList . . . . . . . . . . . . . . . . . . . . . . 117
4.2 IdentifyingtheKeyTasks . . . . . . . . . . . . . . . . . . . 119
4.3 VisualizingAbstractRelationswithBubbleCharts . . . . 120
5 VisualDisplayofaFinancialModel. . . . . . . . . . . . . . . . . . 121
5.1 StructuringaFinancialModel . . . . . . . . . . . . . . . . . 121
5.2 DocumentingaFinancialModel . . . . . . . . . . . . . . . 123
5.3 StructuringLargeFinancialModelsErgonomically . . . . 123
5.4 BordersandLines:ProvidingStructuretotheData . . . 130
5.5 UsingColorstoHighlightImportantElements . . . . . . 132
6 SideNote:UsingKeyCombinationsandtheRibbonto
WorkinExcel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134
6.1 KeyCombinationsandSequencesinExcel . . . . . . . . 134
6.2 DifferentTypesofKeyCombinationsinExcel . . . . . . 135
7 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
FurtherReading. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140
