Financial modeling is a crucial concept for business leaders to understand and execute effectively, but few have the tools necessary to do so. While many professionals are familiar with financial statements and accounting reports, not many are truly proficient at building an accurate and practical financial model from the ground up. The Handbook of Financial Modeling provides these skills and so much more.
Now in its second edition, The Handbook of Financial Modeling takes into account the new tech released since its successful initial release. Author Jack Avon uses his expertise to analyze the changes and improvements in industry-wide financial modeling through the past five years, in addition to instilling core concepts for readers of all experience levels. Approaching your company’s financial issues with a modeler’s perspective will transform and improve the rest of your business career’s trajectory.
Financial professionals, students, business leaders, aspiring CFOs, and more will come away with all the tools necessary to precisely and efficiently monitor an organization’s assets and project future performance. The engaging case studies and Avon’s expert analysis leave you prepared to monitor and predict your organization’s finances effectively. Financial modeling’s latest technology is at your fingertips, and this book’s deep understanding of the topic ensures that you stay ahead of the pack.
What You Will Learn
- Approach financial issues and solutions from a modeler's perspective
- Discover the importance of thinking about end users when developing a financial model
- Plan, design, and build a fully functional financial model
Who This Book Is For
Analysts who would typically be middle management, VPs, and associates. It is also written for business graduates and MBA students.
Author(s): Jack Avon
Edition: 2
Publisher: Apress
Year: 2020
Language: English
Pages: 351
City: New York
Contents
About the Author
Acknowledgments
Introduction
Chapter 1: The Role of a Financial Modeler Today
The financial modeler
Where financial modelers are being used
Modeling roles for the future
Data and business intelligence possible job specification
Expert systems’ possible job specification
Commercial and business possible job specification
Conclusion
Chapter 2: Types of Financial Models
Financial statement models
Understanding the financial statements
Cash vs. accrual
What about cash flow?
How the income statement, cash flow, and balance sheet are linked
Net earnings
Depreciation and amortization
The working capital
Getting the cash
Consolidation model
Watch the file size
Complex links
Be clear about the outputs
Mergers and acquisition models
The M&A model nuisances
Create operational forecast
Build the assumptions
Projections
Combine the acquirer and the target companies
Leveraged buyout model (LBO)
Collect purchase price assumptions
Create sources and uses list
Create financial projections
Transaction balance sheet adjustments
Debt and interest schedule
Credit metrics
Calculate DCF and IRR on initial investment
Exit
Budget and forecast models
Establish the historical data
Create a template for the assumptions
Run assumptions against the historical data
Develop the outputs
Compare
Conclusion
Chapter 3: Review of Best Practices for Financial Modelers
Workbook assumptions
Workbook cover
Workbook table of contents
Workbook styles
Worksheet classification
Worksheet titles
Worksheet documentation
Worksheet error checking
Keep constituents separate
Reduce implicit assumptions to minimum
No constants within a formula
Inputs drive the model, not calculations
One input to an assumption
Specify the units of measurement
Logic flow of calculations
Avoid circularities
Consistent formula across rows
Do not mix time periods
Avoid long and complex formula constructions
Merge cells on data
Avoid fonts with same color background
Avoid using VBA on critical calculations
Conclusion
Chapter 4: The Modeling Life Cycle Explained
Feasibility
The draft modeling project scope
The current analysis
Current requirements
The approach
Evaluation
Review
Conclusion
Scope
Key concepts of a project scope
Project justification
Project scope objectives
Product scope description
Model acceptance criteria
Project constraints
Project assumptions
Requirements
Conclusion of requirements
Specifications
Documentation and timelines
Financial modeling project plan
Goal log
Risk log
Issue log
Model design
Model build
Model testing
Model review
Handover and maintenance
Conclusion
Chapter 5: Planning and Designing Models
Planning to build a model
Having an understanding of cash?
It’s all in the detail!
Create scenario analysis and sensitivity analysis
Assumptions: how realistic are they?
Missing key elements in financial statements
Build a realistic timeline
Know the key metrics and indicators
Developing a feasibility assessment
Scoping the modeling project
The specification and strategy
Defining model outputs
Defining calculations
Defining the inputs
Designing the financial model
Managing complexity
Building your model
Define the modeling tool(s) for the job
Have a clear approach
Get approval of the outputs
Timescales
Developing styles and templates
Define the structure of the workbook
Working with the inputs
Working with the calculations
Working with the outputs
Recap
Planning for errors
Creating the error template
Displaying the errors
Example of planning a financial model
The brief
About the feasibility study
The assessment
Problem statement
Business environment
Business vision
Business units
Business location
Business information
Business technologies
Business problem
Business opportunity
Requirements statement
Business drivers
Business requirements
Feasibility assessment
Option one: Implementing off-the-shelf software
Description
Assessment
Results
Risks
Issues
Assumptions
Option two: Developing a financial model tool
Description
Assessmentx
Results
Risks
Issues
Assumptions
Feasibility ranking
Ranking criteria
Ranking scores
Feasibility result
Conclusion
Chapter 6: It’s All About the Outputs
Deriving the outputs
A sample output
Step 1: build the inputs
Step 2: take the inputs into the workings
Step 3: link the workings to the outputs
Conclusion
Chapter 7: Model Build
Setting the environment
The scope
The requirements and specifications
Step 1: Deciding the type of model
Step 2: Creating the worksheet template
Step 3: Create the model style worksheet
Step 4: Time for some control
Step 5: Create the model cover worksheet
Step 6: Build your timeline template
Step 7: Inputs, set up the constants
Step 8: Inputs, create data input template
Step 9: Inputs, create scenario inputs
Step 10: Inputs, allocations
Step 11: The calculations (workings) worksheet
Step 12: Create error checking
Step 13: Build in the documentation
Step 14: Added-value modeling
Conclusion
Chapter 8: Financial Modeling and Accountancy
Accounting primer
The need for accounting
Accounting and business
Sole proprietorships
Partnerships
General partnership
Limited partnership
Master limited partnership
Corporation
Types of accounting
Cash-based accounting
Accrual accounting
Cash vs. accrual accounting
Accounts
Account types
Assets
Liabilities
Long-term liabilities
Capital
Key accounting concepts
The accounting equation
Assets = owner’s equity
Assets = liabilities + owner’s equity
The double-entry system
Assets = liabilities + owner’s equity
Journals
Chapter 9: The Implications of Accounting Rules for Modelers
Linking inputs to financials
Understanding when to use financial statements
Chapter 10: Modeling Scenarios Explored
The importance of the start dates
Reflecting real or nominal costs
Dealing with discounts
We need our margin
Chapter 11: Calculations for Financial Modelers
The difference between functions and formulas
The base functions
Worksheet headers and labels
The SUM() function
SUBTOTAL()
Lookups and math
INT()
MOD()
INDEX()
MATCH()
INDEX() and MATCH() combined
Other useful functions
IFERROR()
EOMONTH()
Using best practice calculations
Whole range calculations
Calculating time periods
Array calculations for single values
Formulas that are based on a condition
Getting the closest match to an input target
Using crosstab calculations
Applying the right function to your calculations
Functions that look up information
Functions that require a condition
Functions that are driven by dates
Functions for precise situations
Mathematical functions
Functions dependent on text
Calculations involving logical and information functions
Math in modeling
BODMAS
The math in formulas
Conclusion on calculations
Chapter 12: The Importance of Documentation
Providing documentation in models
Documentation structures
Introduction
Assumptions and inputs
Macro handling
Outputs
Known issues
Documentation standards
Using cell comments
Thoughts about the application to be used
Chapter 13: Model Stress Testing
Why test?
Who should test?
When to test?
Types of tests
The test plan
Numeric tests
Robustness tests
Environment tests
Boundary tests
Macro tests
Specification tests
Unique formula tests
Black box testing
White box testing
Error handling testing
Compliance or static testing
Management control testing
Stress test
System performance test
Functional testing
What do you test in functional testing?
How to test your model
The testing cycle
Common types of errors to test
Formulas not copied
Wrong reference
Sum over the wrong range
Relative and absolute references
Unit errors
Commonly misused functions
Other functions that often cause errors
The test file
Change requests
Points to keep in mind when testing
Chapter 14: Model Audit and Review
How to perform a model audit
The process
The low-level review
The high-level review
Sensitivities
Final review report
Final say
Chapter 15: The Role of VBA in Financial Models
What is VBA?
Using Visual Basic for Applications (VBA) in financial models
The Visual Basic Editor (VBE) in Excel
Basic understanding of VBA
Coding in VBA for modelers
VBA project explorer and code windows
Case study: how VBA benefits models
Why is VBA good for modeling?
Reduces manual time
Provides quicker processing times
Utilizes events
Enhances the model user’s experience
Chapter 16: Operis
Introducing Operis
How Operis structures its work teams
Operis and financial modelling teams’ careers
Overview and conclusion
Chapter 17: Financial Modeling, Where Next?
Future demand for financial modeling
Evolutions in financial modeling
Appendix A:
Modeling Glossary and Terminology
Income statement terms
Cash flow statement terms
Balance sheet terms
Appendix B:
Ready-Made Functions
Appendix C:
References
Index
