Financial Modelling with Excel

Who Should Attend?

 You should attend if your work requires the use of Excel to develop financial models. In particular:

• corporate planners responsible for developing or enhancing forecasting models
• corporate finance practitioners building or auditing valuation
• those involved in structuring or auditing funding models
• analysts producing or using financial forecasts for corporates and project

Course Design

The course is designed:

•   to cultivate best practice
•   with the emphasis on practical applications;
•   as a workshop to encourage an interactive approach;
•   using case studies adapted from the real world;
•   at a pace to suit all delegates

Pre-requisite Knowledge

In advance of the course, a basic knowledge of Excel is assumed. This knowledge should include at least the following:

• the Excel screen menu and toolbar
•  moving around a worksheet and between worksheets in a workbook
• changing column width
• entering labels
•  entering, selecting, copying and editing cell contents
•  centering titles
• saving, closing & retrieving the worksheet
•  auto fill
•  entering formulae
• relative and absolute cell referencing
• formatting, previewing and printing

Summary

Course Objectives

To develop practical skills and concepts for:
 

• financial modeling within a robust financial/accounting framework
• use of Excel as an advanced modeling tool
• modeling financial forecasts
• reconciling the projected accounting statement
• financial projections, valuations and funding

DAY 1

Introduction

• Overview of course and content
• Financial framework for modeling

Review of Key Excel Functions

• Worksheets and workbooks
• Copying and naming sheets
• Linking sheets and books
• Relative and absolute references

Advanced Excel Functions and Techniques

• Array arithmetic
• Grouping sheets
• Conditional statements
• Using names

Financial Modeling Exercises

• Sales growth
• Profitability
• Capital expenditure and depreciation
• Cover ratios

Corporate Modeling Case Study

• Scope of the forecast model
• Modular layout and sub-model definition
• Income flows
• Operating expenditure
• Capital expenditure and depreciation

Basic Modeling Concepts

• “Best-practice” - modeling principles
• Advanced Excel Functions and Techniques
• Iterative calculations and alternative solutions (application to the calculation of interest)
• Tracing and auditing
• Goal Seek and Solver
• Excel financial functions

DAY 2

Corporate Modeling Case Study

• Working capital and current assets
• Tax
• Dividends
• Funding and interest payments and receipts
• Reconciling the financial statements

Advanced Excel Functions and Techniques

• Sensitivity, scenario and optimization tools
• Solver
• One-way Data Tables
• Scenario Manager
• Two-way Data Tables
• Data Validation
• Advanced Scenario Management
• Conditional formatting

Corporate Modeling Case Study

• Modeling value
• DCF Valuation
• Multiples
• Modeling Funding
• Investor returns

Corporate Modeling Case Study

• Identification, exploration and control of sensitivities
• Advanced sensitivity techniques

Corporate Modeling Case Study

• Automating the model using controls and macros
• Controls
• Recording simple macros
• Writing simple macros
• Other VBA examples and exercises