Formation à la modélisation financière sous Excel

Financial modelling on Excel training

Training provided by Ferdinand Petra (in English or French)

Extracts of slides used in this training

1. Introduction

  •  Link between income statement, cash flow statement and balance sheet
  • Main accounting consolidation methods (full consolidation, equity method)
  • Bridge between Enterprise Value and Equity Value
  • Focus on underfunded pension deficits
  • How to build a business plan: income statement, cash flow statement and balance sheet

2. Best practices in Excel financial modelling

  • How to optimize Excel option menu
  • The 10 shortcuts that will enable you to be more efficient and more accurate
  • How to use well the comments
  • What cell formatting to use to model in a professionnal manner
  • Build up of a « football field »
  • Build up of a « waterfall »
  • How to create a drop-down menu
  • How to deal with circularities with Excel?

3. Build up of a full business plan

  • Income statement
  • Cash flow statement
  • Balance sheet
  • Intermediary calculations: PP&A, taxes, shareholders’ equity, associates, non-controlling interests

4. Build up of a DCF model

  • Step-by-step build up of the model using two terminal value methods
  • Sensitivity tables of Enterprise Value vs. WACC and long-term growth
  • Sensitivity tables of Enterprise Value vs. EBIT margin and long-term growth

5. Build up of an M&A model

  • Design of the main financial metrics of both the acquirer and the target
  • Valuation items and market data
  • Transaction assumptions and information on financing
  • Sources & Uses of funds
  • Computation of the pro-forma number of shares of the acquirer (post-transaction) and patrimonial dilution
  • EPS accretion/dilution and sensitivities
  • Computation of synergies to break-even
  • Goodwill computation
  • Transaction impact on the acquirer’s balance sheet (opening balance sheet)
  • Transaction impact on the credit rating of the acquirer
  • Relative P/E rule checking
  • Analysis at various price
  • Contribution analysis
  • Side-by-side analysis
  • Premium paid vs. present value of net synergies – what conclusion to draw?
  • Transaction impact on the acquirer’s ROCE (Return on Capital Employed)
  • What financing to choose for an M&A operation?
  • Indicative decision tree to assess the financing mix for an M&A operation
  • What maximum price an acquirer can pay for a target?

6. Build up of a simple LBO model

  • Impact on the return of equity for the shareholders and on the risk taken
  • Main assumptions underlying the LBO structure
  • Build up of the Sources & Uses of funds
  • Opening balance sheet of the company which is put under LBO
  • Step by step build up of the income statement
  • Step by step build up of the cash flow statement
  • Step by step build up of the balance sheet
  • Computation of IRR for the PE fund, the management of the company under LBO and, if need be, the holders of the Mezzanine or Unitranche debt

7. Build up of a « cash sweep » LBO model

  • Same steps as for the classical LBO model
  • Computation of cash flow available for volontary debt repayment (or drawing on RCF)
  • Creation of the cash sweep mechanism
  • Modelling of each tranche of LBO debt with mandatory and volontary repayments
  • Mechanism of repayment priority between the different tranches of debt