Course overview

This intermediate level course will focus on the tools and techniques available in Microsoft Excel which will enable you to develop, run and review financial models across multiple scenarios. You will gain a sound and practical insight in financial modelling building, comparison of financial values and evaluation of financial risks.

Throughout this course, you are encouraged to work on and develop solutions to your work based financial modelling challenges,

Who should attend?

• Financial directors and managers
• Financial controllers
• Risk managers
• Credit manages
• Financial and management accountants
• Financial project administrators
• Project finance professionals
• Financial planners
• Anyone responsible for or involved in financial modelling

Course Objectives

By the end of this course, you will be able to:

• Understanding and use key functions in Excel
• Analyse financial statements using modelling
• Create a Budget and to make changes to different scenarios
• Prepare a cash flow budget/forecast
• Carry out a variance and breakeven analysis for decision making
• Develop a model for investment decision
• Create a sensitivity analysis model
• Develop Excel models independently for specific tasks
• Explore and evaluate capital budgeting situations
• Manage existing financial proposals to deliver the best returns

Methodology:

Practical and interactive by using questions, exercises and case studies so the participants can put the learning objectives into practice.

Participants will go through examples and case studies for them to build their own models and to put into practice the learning objectives

Course Content

Review of major concepts and practicalities

• Financial, Mathematical, Statistical and Logical Functions
• Database functions
• Excel Tables
• Pivot Tables
• Lookup and Reference functions
• Array Functions, Formulas and Matrix Calculations
• Goal Seek and SOLVER
• Introduction to Power Pivot and Get & Transform (Power Query)

Working with Financial Statements

• Income Statement

• Balance Sheet
• Statement of Cash Flow

- Terminology and Formats

- Reading a financial report

Financial Statement Analysis

• Analysing
• Profitability
• Return
• Liquidity
• Asset Usage
• Cash Flow
• Investor
• Common size statements
• Growth ratios

Business Intelligence 1

• Introduction to
• Power Query
• Power Pivot
• Power BI

Management Accounting, Budgeting and Planning Cycle

Budget Model

• Preparing the Functional Budgets:
• Revenue
• Expenses
• Inventory and
• Preparing a Capital Budget.
• Forecasting and Projections models: historic, trend lines.
• Key drivers in forecasting financials

Variance analysis

• Budget variances
• Cash flow budgets
• Monthly cash model

Business Intelligence 2

- Enhancing your Power Pivot reports by using

• DAX: Data Analytic eXpressions
• Cube Functions

Leasing, Capital Budgeting, Risk Assessment and Simulations

Financial Analysis of Leasing

• Leasing and Firm Financing
• The Equivalent Loan Method
• The Lessor's Problem Calculating the highest acceptable lease rental
• Asset residual value and other considerations

Investment Decisions

• Calculating the NPV, XNPV, IRR, MIRR, XIRR, Profitability Indices and payback period
• Sensitivity Analysis for a Business Case
• Developing and Managing Scenarios

Risk Modelling

• The risk modelling process
• Defining risk and risk analysis
• Building a Risk Model

Monte Carlo Simulations

• Continuous and discrete distributions
• Density and Cumulative Curves
• Measures of the central point
• Measures of Spread and of Risk

The nature and examples of Monte Carlo Simulations

Interpreting Basic Monte Carlo Simulation outputs

Business Intelligence 3

• Comprehensive illustration of the application of business intelligence
• Importing data
• Cleaning and manipulating data
• Creating Power Pivot tables
• Enhancing the pivot tables and resulting reports with
• DAX
• Cube functions
• Power BI

Discover Sophisticated Techniques to Improve Usability, Reliability and Your Efficiency as you Work with your Financial Models

Workshop prerequisites

• Delegates should have a good working knowledge of Microsoft Excel: you don’t need to be an advanced user, just aware of and be confident in the use of basic Excel

• Delegates should also be in possession of their laptops loaded with Office365/Excel 2016

• You will also need to ensure that Power Query and Power Pivot are fully operational: you will receive a delegates’ pack in advance of the course and full instructions will be given then

About the trainer:

Mr. Duncan Williamson

Distinguished Trainer & International Consultant

Highlights: Duncan combines his extensive academic and training experience with significant manufacturing and commercial work. In addition to working as a teacher and trainer, Duncan has experience of working in a wide variety of settings, ranging from working as a management accountant in process industries in the UK to product cost consulting in several industries in Eastern Europe and the oil and gas industry in the Middle East. Duncan has worked in and around a number of oil and gas-based companies over recent years.

He has published books, articles and case studies. His latest books Introduction to Power Pivot and Introduction to Power BI for Desktop to be published by www.bookboon.com

Countries of Work Experience: Bosnia and Herzegovina, Kyrgyzstan, Kazakhstan, Uzbekistan, Romania, United Kingdom, Afghanistan, Dubai, Abu Dhabi, Kuwait, Oman, Saudi Arabia, Switzerland, Hungary, Thailand, Singapore, Vietnam, Philippines, Malaysia, Pakistan, Holland, Sudan, Albania, Qatar, Mexico, Angola

Significant Areas of Expertise: cost and management accounting, financial accounting and bookkeeping, financial reporting under IFRS, financial modelling, spread sheet modelling, and product cost modelling.

Disclaimer, Terms & Conditions:

• Location is subject to change

• Minimum number of participants has to be reached prior to confirmation of event

• Please note that trainers and topics were confirmed at time of publishing, however, CTS may necessitate substitution, alterations or cancellations. CTS reserves the right to change or cancel any part of its published programmed due to unforeseen circumstances.

• Registrations should be made 2 weeks prior to training date

• Full payment for the course should be made 3-4 weeks prior to training date

• Cancellation 3 weeks prior to event date or no shows are liable to pay the full course with no refund, however you may substitute delegates at any time with notice

• Participants are required to attend at least 80% of the course program prior receipt of Certificate

• Rates are exclusive of taxes unless otherwise indicated in the invoice

• Receipt of this email to participate and/or register in any of the learning activities organized by CTS should not in any way be taken as a promise for employment or contract of employment of any kind.

• Force Majeure: Should for any reason outside the control of CTS, the trainer changes his dates or event cancels due to the act of terrorism, extreme weather conditions, industrial action or epidemic, CTS shall reserve the rights to replace the trainer or reschedule the event but the client hereby indemnifies and holds CTS harmless from and against any and all costs, damages and expenses, which are incurred by the client.