Financial Modeling using Excelنمذجة القوائم المالية بإستخدام االكسل

Managerial and Financial Training Center

المركز االداري والمالي للتدريب

Program ObjectivesAnalyze financial data using the most efficient and powerful functions

Grab best practice techniques to build the most effective charts and tablesfor all your financial data - make an impact on your business andstakeholders

Learn how to quickly, efficiently and effectively evaluate financialdecisions when undertaking a project, making an investment, acquiring ordisposing of a business, etc.

Be able to prepare realistic and reliable financial forecasts with confidence

Become competent in using optimization tools for getting the best resultsin portfolio management, project evaluation and planning Work with ateam on the analysis of a business problem, design part of the model andthen learn how to integrate it into a whole business financial / operationalmodel.

Who should attend

All Excel Users particularly:

Financial Analysts

CEO’s / CFO’s

Portfolio managers/- Corporate accountants

Credit analysts/- Private equity managers

Venture capitalists/- Risk managers

Board members /- Investment bankers

Regulators /- Bank lending officers

Financial government officials

Strategic planners /- Trustees

Compliance officers/Internal Auditors

Management consultants

Corporate Finance lawyers

Financial Modeling

Users

Financial Analysts

Financial Modelers

Head of Finance

Project Managers Accountants

Financial Modeling

Areas

Portfolio Risk Management

& Analysis

Projects' Finance

Bonds

BanksValuation

Agro-Industerial

Projects

Financial Statements

Analysis

Day One

Best Practice Financial Modeling and Tools

Introduction to Financial Modeling

Define a financial model

Principle of Occam’s razor

Interrelationships within a model

Logical arrangement of the parts

Model design and structure

Characteristics of good financialmodels

Exploring Excel Functions:

Financial

Date and time

Statistical

Lookup & reference

Database

Text

Logical

Information

Exercise 1: Designing a simple model for loan amortization

Day Two

Modeling & Analyzing Forecasted

Financial StatementsUnderstanding Basic Financial

Statements

Forecasting guidelines

Modeling the connection between theincome statement and the BalanceSheet

Assumptions margin

Structuring the input sheet vs outputsheets

Modeling common size statements

Smoothing techniques in forecasting

Simple and multiple regressionanalysis

Sensitivity analysis in forecasting

Surplus funds and the necessity tofinance

Static vs. dynamic analysis

Effect of surplus income from surplusfunds

Types of cash flows (Operations,Investment, Financing)

Modeling payment of successive debttranches

Modeling Ratio Analysis and KeyPerformance Indicators:

◦ DuPont Analysis

◦ Profitability ratios

◦ Leverage ratios

◦ Coverage ratios

Exercise 2: Forecasting the financial statements of a company with full ratio analysis for five years in future

Day ThreeProject Appraisal and Evaluation

Modeling

Introduction to Capital Budgeting Techniques

Understanding net present value

Understanding internal rate of return

Going through a worked example of a project cash

flow

Problems with the IRR calculation

Multiple IRR

Shooting yourself in the foot with incorrect

assumptions

◦ NPV

◦ XNPV

◦ IRR

◦ MIRR

◦ XIRR

Exercise 3: Modeling the IRR and MIRR of a

poultry project

Pivot Tables: Introduction to Pivot tables

Creating a Pivot Table report

The Pivot Table wizard

Percentage of column, percentage

of raw, and percentage of previous

Top and bottom 10 feature

Creating formulas in pivot tables

Retrieving data from external

sources including access and

internet

Linking Pivot Tables to MySQL

Pivot charts manipulation

Building one variable Data Tables

Building two variables Data Tables

Exercise 4: building a Pivot Table for

portfolio data

Day FourIntroduction to VBA, StochasticModeling and Monte Carlo Simulation

Introduction to VBA

What is VBA

What is a macro?

Creating a simple macro

Changing multiple properties at once

Assigning a shortcut key to a macro

Looking inside a macro

Objects, properties and methods

Navigating the Visual Basic Auditor

Manipulating recorded properties

Eliminating repeated objects in a recorded macro

The Select…Selection structure

The With Selection structure

Making long statements more readable

Designate a trusted location for macros

Looping

Branching

Automating spreadsheets

Retrieving data from non-Excel sources

Recording a macro that runs other macros

Exercise 5: Recording a macro in Excel and navigating through the Basic Editor

Monte Carlo Simulation: Monte Carlo simulation versus "what if"

scenarios

Stochastic vs. static models

Incorporating decision rules into Monte Carlo simulations

Comparing simulation to reality

Step by step application of Monte Carlo Simulation

Exercise 6: Examples of Monte Carlo simulation

Day Five

Enhancing and reviewing your Financial Models

Building custom new functions in Excel using VBA

Using a custom function from a worksheet

Adding arguments to a custom function

Making a function volatile

Making arguments optional

Using a custom function from a macro

Developing and storing a new add-in

Exercise 7: Building a new custom Function in Excel and storing it in an add-in

Reviewing and updating financial models

Exercise 8: Comprehensive case study

What Delegates Said About MFTC Courses

MR: Imad EL Choueiri / Program: Certified Compliance Officer

Compliance Manager – Al-Khabeer Merchant Finance Co.

“The Course is well organized & thanks to MFTC for conducting & offering such

beneficial courses”Mr. Hani Fathi Rageb / Program: ACI Dealing Certificate

Treasury Chief – Emirates Bank

“The course is very useful. It quipped us with all necessary information and tools that can

be applied in our daily routine work”

Mr. Ahmed Mahayri / Program: Chartered Wealth Manager

Money Analyst – SEDCO

“Companies must be licensed by Financial Market Corporation and staff should be

certified wealth managers”

MR: Mehernosh Colsawala / Program: Certified Compliance Officer

Operation & Control Manager – Watheeqah Holding Group Kuwait

“There is a positive interaction between the instructor and trainees which we rarely found in

other training centers. I wish you all the success”

MFTC Training Sessions

MFTC Clients

For Registration and More Information

Managerial and Financial Training Center

Al Sitten St. Ibn Al Alem Commercial Center – Floor No. 01

Telephone: 00966(12)6529126 – 6529275 Ext:122

Fax: 00966(12) 6531275

Mobile: 00966(5)35500097 / 00966(5)35530307

Website: www.fin.com.sa

Email: support.01@fin.com.sa