105: Training on Financial Modeling And Evaluation Seminar

Module 1: Introduction to Financial Modeling and Excel Fundamentals (Day 1)

• Morning:
  • Introduction to Financial Modeling: Purpose, types of models, best practices.
  • Setting up Excel for Financial Modeling: Formatting, keyboard shortcuts, efficient navigation, data entry techniques, working with styles and themes.
  • Basic Excel Functions: SUM, AVERAGE, IF, VLOOKUP, HLOOKUP, INDEX/MATCH, COUNT, COUNTA, COUNTIF, SUMIF. Practical exercises using financial data examples.
• Afternoon:
  • Working with Ranges and Named Ranges: Improving model readability and maintainability. Dynamic Named Ranges using OFFSET.
  • Introduction to Formulas and Error Handling: Absolute vs. relative references, error checking functions (IFERROR, ISNA, ISNUMBER, ISBLANK). Formula auditing tools.
  • Building a Simple Financial Model: A basic example, e.g., a loan amortization schedule, to reinforce learned concepts.
  • Excel Functions Covered: SUM, AVERAGE, IF, VLOOKUP, HLOOKUP, INDEX/MATCH, COUNT, COUNTA, COUNTIF, SUMIF, OFFSET, IFERROR, ISNA, ISNUMBER, ISBLANK.

Module 2: Core Financial Modeling Concepts and Techniques (Day 2)

• Morning:
  • Time Value of Money: Present Value, Future Value, Discounting, Compounding. Excel functions: PV, FV, NPV, IRR, XNPV, XIRR, RATE, NPER.
  • Cash Flow Analysis: Understanding different types of cash flows, projecting cash flows.
  • Building a Projected Income Statement: Revenue forecasting, cost projections, and profit calculations. Using forecasting techniques (TREND, FORECAST.ETS).
• Afternoon:
  • Building a Projected Balance Sheet: Asset, liability, and equity projections, linking to the income statement.
  • Financial Ratios: Calculating and interpreting key ratios (profitability, liquidity, solvency). Using array formulas for efficient ratio calculations.
  • Practical Exercise: Building a simple 3-year financial projection model for a hypothetical business.
  • Excel Functions Covered: PV, FV, NPV, IRR, XNPV, XIRR, RATE, NPER, TREND, FORECAST.ETS.

Module 3: Credit Risk Assessment and Loan Modeling (Day 3)

• Morning:
  • Credit Risk Analysis: Key factors to consider, qualitative and quantitative assessment.
  • Loan Amortization Schedules: Building detailed schedules with varying loan terms and interest rates. Using PMT, IPMT, PPMT functions.
  • Credit Scoring and Probability of Default: Introduction to basic credit scoring methodologies. Using statistical functions (CORREL, STDEV).
• Afternoon:
  • Loan Structuring: Analyzing different loan structures and their impact on cash flows.
  • Modeling Loan Covenants: Incorporating covenants into loan models using nested IF statements and data validation.
  • Practical Exercise: Building a loan model for a specific scenario, including sensitivity analysis on key assumptions.
  • Excel Functions Covered: PMT, IPMT, PPMT, CORREL, STDEV.

Module 4: Valuation and Sensitivity Analysis (Day 4)

• Morning:
  • Business Valuation: Introduction to different valuation methods (DCF, Comparable Company Analysis).
  • Discounted Cash Flow (DCF) Modeling: Building a DCF model to value a business.
  • Terminal Value Calculation: Estimating the value of a business beyond the projection period. Using perpetuity growth method and exit multiple method.
• Afternoon:
  • Sensitivity Analysis: Using Data Tables, Scenario Manager, and Goal Seek to analyze the impact of changes in key assumptions.
  • Monte Carlo Simulation (Introduction): Briefly introducing the concept and its applications using add-ins or array formulas.
  • Practical Exercise: Building a valuation model and performing sensitivity analysis on key inputs.
  • Excel Functions Covered: Data Tables, Scenario Manager, Goal Seek.

Module 5: Advanced Excel Techniques and Model Presentation (Day 5)

• Morning:
  • Advanced Excel Functions: SUMIFS, COUNTIFS, AVERAGEIFS, IFS (for complex logical tests), OFFSET, INDIRECT, CHOOSE, LOOKUP, MATCH, TRANSPOSE.
  • Working with Charts and Graphs: Creating effective visualizations of financial data. Using different chart types and formatting options. Creating interactive charts.
  • Data Validation: Ensuring data integrity and model accuracy. Using custom data validation rules.
  • Introduction to Power Query: Importing and transforming data from various sources. Cleaning and shaping data for analysis. Automating data import processes.
• Afternoon:
  • Model Auditing and Review: Techniques for checking model accuracy and identifying potential errors. Using formula auditing tools (Trace Precedents, Trace Dependents, Error Checking).
  • Building User-Friendly Models: Designing models that are easy to understand and use. Using clear labels, consistent formatting, and input cells. Protecting worksheets and workbooks.
  • Introduction to Macros: Recording and running simple macros to automate repetitive tasks. Understanding the basics of VBA (Visual Basic for Applications).
  • Presenting Financial Model Results: Communicating model insights effectively to stakeholders. Creating dashboards and reports.
  • Case Study Review: Applying the learned concepts to a real-world case study.
  • Excel Functions Covered: SUMIFS, COUNTIFS, AVERAGEIFS, IFS, OFFSET, INDIRECT, CHOOSE, LOOKUP, MATCH, TRANSPOSE, Data Validation, Power Query, Macros, VBA basics.