The Handbook of Financial Modeling : A Practical Approach to Creating and Implementing Valuation Projection Models

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. 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 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**Analysts who would typically be middle management, VPs, and associates. It is also written for business graduates and MBA students.