Certificate In Financial Modelling Using Excel

Fully revised and updated for 2014. Includes one full day on Essential Excel for finance

Certificate In Financial Modelling Using Excel Book and pay NOW and bring your colleague for FREE on this course See last page for details*

Top 5 Learning Objectives 1. Understand the essence of financial models and analyse their applications 2. Discover how to construct reliable and realistic financial models that work and are easy to review 3. Learn to measure, interpret and predict company performance using Excel modelling 4. Improve your decision making processes and save time on financial analysis 5. Proficiently use Excel as an analytical tool and enhance your financial abilities

19 – 23 January 2014 Dusit Thani Hotel, Dubai, UAE 4 – 8 May 2014 Dusit Thani Hotel, Dubai, UAE 19 – 23 October 2014 Radisson Royal Hotel, Dubai, UAE

Sign up for this course and receive a complimentary copy of Using Excel for Business Analysis written by your Course Director Follow us on

Organised by:

www.twitter.com/iirmiddleeast www.facebook.com/iirmiddleeast www.youtube.com/iirmiddleeast

www.iirme.com/excelmodelling

Meet Your Expert Course Director Danielle Stein Fairhurst is the Principal of Plum Solutions, a Sydney-based consultancy specialising in Excel modelling and analysis.

With over fourteen years’ experience as an analyst, she helps her clients create meaningful models in the form of business cases, pricing models and management reports. She has hands-on experience in a number of industry sectors, including telecoms, information systems, manufacturing and financial services.

Danielle is the author of “Using Excel for Business Analysis” which is part of the Wiley finance series. She has regular engagements around Australia and globally as a speaker, course facilitator, consultant and analyst. She holds a Master of Business Administration (MBA) from Macquarie Graduate School of Management (MGSM), and has taught management accounting subjects at Sydney University.

This is what delegates have said about Danielle Stein Fairhurst’s previous Certificate In Financial Modelling courses “Danielle has vast experience in financial modeling building, she shared practical experiences with us. I would like to attend another course with her.”

Muhammed Arif Masood, Aldar Properties, UAE

“Danielle is an expert in financial modeling.”

Dr. Ram Behin, Team Leader Projects, Universal Hospital, UAE

Would you like to run this course in-house?

The in-house training division of IIR Middle East Tel: +971 4 407 2624 • Email: CTS@iirme.com www.iirme.com/cts

+971 4 335 2437

+971 4 335 2438

Course Assessment Delegates are required to submit one of the models built during the course for assessment and to sit for a short exam at the completion of the course.

Course Materials Delegates will receive a complimentary copy of Using Excel for

Business Analysis: a Guide to Financial Modelling Fundamentals, by your trainer, Danielle Stein Fairhurst which contains instructions in Excel 2007 and 2010, and Excel for Mac.

Software Bring your own laptop to the session installed with any version of Microsoft Excel. Although the coursee includes an overview of the new features in later versions of Excel, delegates should bring their laptops loaded with whichever version they are comfortable using in their everyday work.

Course Prerequisites The course material includes extensive use of Excel and delegates will gain the maximum benefit from this course if they are already competent spreadsheets users. It is designed for users who do use (or will use) Excel on a regular basis, and are comfortable with using its tools and functions.

Who Should Attend?

This hands-on course is designed for middle and senior business and finance professionals as well as general managers who need to use financial models to measure business performance, including: • Finance Managers and Controllers • Strategy Directors and Managers • Budget, Corporate, Business and Financial Analysts • Project Managers and Risk Analysts • Investment and Management Accountants • Heads of Business Units and Business Planners • Financial Advisors and Corporate Analysts

www.iirme.com/excelmodelling

Certificate In Financial Modelling Using Excel

19 – 23 January 2014 • Dusit Thani Hotel, Dubai, UAE 4 – 8 May 2014 • Dusit Thani Hotel, Dubai, UAE 19 – 23 October 2014 • Radisson Royal Hotel, Dubai, UAE

Course Timings: Registration will be from 7:30 on Day One. Each day the course will commence promptly at 8:00 and conclude at 14:30 followed by lunch.

Refreshments will be served at approximately 10:30 and 12:30.

Course Outline

Charting • Creating and modifying a chart

Day One

• Making your charts look fabulous • Charting tips, tricks and shortcuts • New charting features of Excel 2013

Essential Excel For Finance Specifically designed for business professionals who need to take their Excel skills to the next level, this intensive full day course focusses

Essential Advanced Tools

on the technical Excel skills that will be required for the Financial

• Autofilters

Modelling course. This very practical day covers everything you need

• Goal seek

to perform your job.

• Macros • Pivot tables

For those who don’t have time to sit through extensive Excel “how

• Array formulas

to” training, this day will arm you with a range of tools, techniques and formulas essential for finance. Guaranteed to contain “no fluff”,

Other Useful Tools

this day will equip you with the skills to produce more efficient and

• Spin buttons and drop-down menus

accurate Excel models and increase productivity.

• Hiding • Working with dates

Must-Know Tools And Techniques

• Hyperlinking

• Key points on upgrading from prior versions of Excel to the latest,

• Conditional formatting

2013

Day Two

• Absolute referencing • Named ranges

Introduction To Financial Modelling

• Time saving shortcuts

• What is financial modelling?

-

New Features In Excel 2010 and 2013

Typical examples and purposes of financial models

• Software selection

• An overview of power pivot

-

• Using sparklines to display trends on dashboards

Tool selection and advantages and disadvantages of Excel and other software for the purpose of financial modelling

• Applying a slicer to pivot tables

• Model design -

Formulas You Simply Can’t Live Without

Model planning and steps in building your model

• Skills needed for financial modelling

• LOOKUP, VLOOKUP, HLOOKUP

-

• Logical functions eg. IF, AND

The technical, design, business and industry knowledge required for financial modelling

• Aggregation functions eg. SUMIF, COUNTIF

• Model tools and functions

• Financial functions eg. NPV, IRR, PMT

-

• Formula nesting

Which formula or tool is most appropriate in which modelling situation?

• Building the business case: issues and logic surrounding the

Bullet-Proofing Your Model • Protect data by locking cells

calculation of customer acquisition

• Password protection

-

Creating the pool of potential customers

• Restricting incorrect data entry with data validations

-

Narrowing down the pool

• Using worksheet protection to prevent entry

-

Expected customer acquisition

• Protecting your file using “Read Only” and password protection

-

Lost customers/customer defection

-

Market penetration

+971 4 335 2437

+971 4 335 2438

register@iirme.com

www.iirme.com/excelmodelling

Day Four

Practical exercises: Modelling exercises using a range of financial functions and tools. Building a business case utilising financial modelling techniques

Day Three Financial Modelling Theory And Best Practice • Modelling techniques -

What makes a good model? Attributes of a good model such as user-friendly and structural features

-

Avoiding Excel errors – A wrong calculation in a model can have disastrous consequences! Strategies to reduce errors in financial modelling

-

Rebuilding an inherited model

-

Building error checks

-

Correcting errors

-

Create a more efficient model with links

-

Dealing with links and the potential errors they cause

• Bullet-proofing your model Retain model integrity and prevent misuse of your model by learning to restrict incorrect inputs -

Protection

-

Locking cells

-

Restriction of incorrect data entry with data validations

Financial Modelling For Budgeting And Forecasting • Practical application of modelling in Excel for the purpose of budgeting for organisations - Advanced forecasting functions; time series, exponential smoothing and regression analysis - Modelling for “stepped” costs when forecasts include both fixed and variable costs - Sales forecasting – predication of likely sales revenue based on drivers - Seasonality – simply and easily spread revenue or expenses throughout the budget period based on season variations - Building an expense budget – based on given assumptions, you will build a budget for 12 months - Contingency planning – calculating a contingency based on assumptions about probability and impact of unexpected events - Budgeting for capital expenditure – capital expenditure is budgeted for cashflow and then depreciated - Cash flow budgeting – translating your profit and loss statement into a cashflow forecast - Reporting against budget; variance analysis – ceating a template into which actual and budget can be compared and analysed

Practical Exercise: Create a model in Excel which will calculate volume-dependent costs

• Making a model user-friendly -

Formatting

-

Navigation

Day Five Practical Financial Modelling • Charting and graphing fundamentals – how to build charts • When to use a table or charts; combining tables and charts using chart data table and data bars • Choosing the correct chart to display your findings • Visual design - dos, don’ts and common mistakes in graphical presentation of data and reports • How to present easy to follow information concisely and clearly • Pivot Tables and their role in financial modelling

• Dealing with uncertainty and risk -

Economic inputs to model, and modelling fluctuations in

-

Use of stress testing to validate a model

-

What-if analysis

-

Importance of assumptions when assessing risk

external factors

• Create a best, base and worst case scenario on your model Technical tools for creating scenarios: -

Manual sensitivity analysis; drop-down scenarios

-

Scenario manager

-

Data tables

Practical exercise: Build your own flexible report including a dynamic chart

• Displaying final model results and findings Communicate the results of your model clearly and concisely whilst getting the key message across to the audience -

Documentation and source referencing

-

Writing assumptions

-

Writing operation instructions

Practical exercise: Building scenarios and perform sensitivity analysis on financial models. Build a property development scenario model. Build a cash flow funding model, including a P&L, cash flow and balance sheet including scenario analysis.

Case Studies Demonstration and overview of the following: 1. Tiering tables One of the more complex and widely used calculations in financial modelling. Discussion of two different methods of calculation; a simple and progressive table 2. Pricing models Calculating profitability and break-even points at various prices 3. Dynamic charting Create a user-friendly report with a dynamic chart based on drop-down selections 4. Modelling for risk assessment Assess project risk with a risk assessment model using conditional formatting Practical Exercise: Forecast a sales cycle based on historical data by calculating the seasonality index and adjusting the forecast accordingly

+971 4 335 2437

+971 4 335 2438

register@iirme.com

www.iirme.com/excelmodelling

Certificate In Financial Modelling Using Excel

19 – 23 January 2014 • Dusit Thani Hotel, Dubai, UAE 4 – 8 May 2014 • Dusit Thani Hotel, Dubai, UAE 19 – 23 October 2014 • Radisson Royal Hotel, Dubai, UAE

FIVE WAYS TO REGISTER IIR Holdings Ltd. P.O Box 9428 Dubai, UAE

+971 4 335 2437 +971 4 335 2438 register@iirme.com

www.iirme.com/excelmodelling

DISCOUNTS AVAILABLE FOR 2 OR MORE PEOPLE CALL – +971 4 335 2483 E-MAIL – a.watts@iirme.com WEB BC5204/BC5205/BC5206 Event

Course Fee Before 3 November 2013*

Course Fee Before 24 November 2013*

Final Fee*

Certificate In Financial Modelling Using Excel (BC5204) 19 – 23 January 2014

US$ 4,150

US$ 4,650

US$ 4,950

Event

Course Fee Before 16 February 2014

Course Fee Before 9 March 2014

Final Fee

Certificate In Financial Modelling Using Excel (BC5205) 4 – 8 May 2014

US$ 4,150

US$ 4,650

US$ 4,950

Event

Course Fee Before 3 August 2014

Course Fee Before 24 August 2014

Final Fee

Certificate In Financial Modelling Using Excel (BC5206) 19 – 23 October 2014

US$ 4,150

US$ 4,650

US$ 4,950

WOULD YOU LIKE TO RUN THIS COURSE INͳHOUSE?

All registrations are subject to our terms and conditions which are available at www.iirme.com/terms. Please read them as they include important information. By submitting your registration you agree to be bound by the terms and conditions in full.

Payments

Course fees include documentation, luncheon and refreshments. Delegates who attend all sessions will receive a Certificate of Attendance.

*Book and pay NOW and bring your colleague for FREE on this course. Only applicable for January 2014 course

DELEGATE DETAILS Name: ..............................................................................................................................................................................................................

Job Title: ......................................................................................................... Email: .....................................................................................

Tel: ..................................................... Fax: .................................................... Mobile: ..................................................................................

Name: ..............................................................................................................................................................................................................

A confirmation letter and invoice will be sent upon receipt of your registration. Please note that full payment must be received prior to the event. Only those delegates whose fees have been paid in full will be admitted to the event. You can pay by company cheques or bankers draft in Dirhams or US$. Please note that all US$ cheques and drafts should be drawn on a New York bank and an extra amount of US$ 6 per payment should be added to cover bank clearing charges. In any event payment must be received not later than 48 hours before the Event. Entry to the Event may be refused if payment in full is not received.

Credit card payment If you would like to pay by credit card, please tick here and a member of our team will contact you to take the details

Cancellation

Job Title: ......................................................................................................... Email: .....................................................................................

COMPANY DETAILS Company: ............................................................................................................................................................................................................

Address: ................................................................................................................................................................................................................

Postcode: ................................................................................. Country: ...........................................................................................................

If you are unable to attend, a substitute delegate will be welcome in your place. Registrations cancelled more than 7 days before the Event are subject to a $200 administration charge.

Registration fees for registrations cancelled 7 days or less before the Event must be paid in full. Substitutions are welcome at any time.

Avoid Visa Delays - Book Now

Tel: .............................................................................................. Fax: ..................................................................................................................

Delegates requiring visas should contact the hotel they wish to stay at directly, as soon as possible.

Visas for non-GCC nationals may take several weeks to process.

No. of employees on your site: 1000+ 500-999 250-499

All registrations are subject to acceptance by IIR which will be confirmed to you in writing.

50-249

0-49

Nature of your company's business: ..........................................

YES, I would like to receive information about future events & services via e-mail .................................................................

To assist us with future correspondence, please supply the following details: Name of the Department Head: .....................................................................................................................................................................

Department: ........................................................... Mobile: .......................................... Email: .......................................................................

Training Manager: .............................................................................................................................................................................................

Department: ........................................................... Mobile: .......................................... Email: ......................................................................

Booking Contact: ..............................................................................................................................................................................................

Due to unforeseen circumstances, the programme may change and IIR reserves the right to alter the venue and/or speakers.

Event Venue: Dusit Thani Hotel, Dubai, UAE Tel: +971 4 343 3333 Radisson Royal Hotel, Dubai, UAE Tel: +971 4 308 0000 Accommodation Details We highly recommend you secure your room reservation at the earliest to avoid last minute inconvenience. You can contact the IIR Hospitality Desk for assistance on: Tel: +971 4 407 2693 Fax: +971 4 407 2517 Email: hospitality@iirme.com

Department: ........................................................... Mobile: .......................................... Email: ...................................................................... © Copyright I.I.R. HOLDINGS B.V.

SN/ST FN Finance

LR

Tel: ..................................................... Fax: .................................................... Mobile: ..................................................................................