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