Modeling & Projecting Financial Statements

Financial statements can deliver a ton of insights, if you know how to uncover them.

This two-day workshop helps professionals in valuation, corporate finance, planning and budgeting, investment banking, asset management, fixed income research, and equity research weave business and financial forecasts into spreadsheet-based projections of financial statements. These projections help professionals value a business, test future scenarios, and gain an integrated view of a business.

The workshop will also discuss tools and techniques to make participants more efficient and effective in developing models and analyzing information.

COURSE PREREQUISITES:

You MUST BRING A LAPTOP with Excel 2010 or above to the session to build models.

The following Excel topics will be covered in the "Optimizing Accounting & Finance with Advanced Excel" seminar. Please consider taking the one-day Excel seminar that precedes this course. Modeling & Projecting Financial Statements assumes that you have mastery of Excel skills covered in that course, which include:

• Commonly used Excel shortcuts
• Excel functions: IF, OFFSET, SUMPRODUCT, HLOOKUP, VLOOKUP, INDIRECT, MATCH, INDEX, NPV, XNPV, IRR, XIRR
• Excel Data Tables (different from Pivot tables)
• Excel Styles; Custom Formatting, Data Validation, Data Grouping
• Iterations and circular references

Knowledge of Excel VBA is NOT required.

A good knowledge of accounting topics such as indirect cash flows, leases, taxes, and options

Agenda

• Introduction to modeling and design: Components of a financial statement model; Skills needed for modeling; Excel overview; Design choices
• Efficient modeling and best practices

• Building blocks of models and step-wise development: Enterprise activities

  • Forecasting EBITDA by forecasting sales and operating expenses
  • Forecasting working capital requirements for future business activity
  • Forecasting capital expenditures, PP&E, and depreciation
  • Non-current operating assets and liabilities: Modeling multi-period effects: When revenues are collected over multiple periods, or expenses are incurred over multiple periods
  • Forecasting reserves and allowances
  • Modeling current and deferred taxes

• Building blocks: Financing activities

  • Understanding and forecasting enterprise free cash flows: Contrasting three approaches to measurement of free cash flows: cash-flow budgeting, equity valuation, and credit risk analysis
  • Forecasting funding needs and interest expense
  • Ensuring that the firm meets its debt-to-equity ratio constraints or debt-to-EBITDA constraints
  • Meeting seasonal funding needs via revolving line of credit

• Overview of integrated models and their drivers

• Valuation

  • Cost of capital and risk
  • Growth and terminal values