Advanced Spreadsheet Skills for Finance

About

Essential for today’s leading financial decision-makers is the ability to effectively use and manage financial spreadsheets. Financial spreadsheets can be a highly effective tool for assisting in more informed decisions; however an in-depth knowledge must be acquired in order to truly unleash the power of these spreadsheets.

Advanced Excel Spreadsheet Skills for Finance is one of our premium series seminars, designed to give NZ’s financial executives all they need to know to more effectively apply spreadsheets in their organisation.

Participants will be exposed to a unique mix of lecture, discussion and hands-on practical work to full understand the ways in which they can use financial models to accurately model cash flow, forecast revenue and simulate a variety of different scenarios.

Each participant will be provided with a laptop to ensure to highest level of learning, and class sizes will be kept small to ensure maximum opportunity for on-on-one instruction.

•    As this course is highly practical, and much of the seminar will be based around practical sessions in Excel.

Participants will be provided with software including practical exercises for use throughout the seminar, with solutions provided at the end of the course.  Excel 2003 will be used for the course.  All functions, tools and techniques covered will also directly apply to Excel 2007. 

At this premium training seminar you will:
•    Examine the key features of Excel that are most useful in financial applications
•    Review and apply the core mathematical concepts of finance
•    Use Excel financial functions and apply them in decision making
•    Represent and model financial statements in Excel
•    Come to grips with the principles of model design
•    Apply the principles of model design for a range of common model types
•    Learn how to use iteration and optimisation on problems that are too complex to solve in a single step
•    Link form elements into spreadsheets and use data validation to make spreadsheets more user friendly
•    Be introduced to Visual Basic functions and macros for maximum efficiency in modelling

Who should attend?
This course has been developed specifically to meet the needs of financial executives and decision-makers from both the public and private sectors including:
•    CFOs
•    Finance Directors
•    Financial Controllers
•    Business Analysts, Managers and Administrators
•    Financial Planners
•    Management Accountants
•    CEOs, MDs & General Managers

It is assumed that participants will have intermediate levels of understanding of general financial concepts and an intermediate level of experience with Excel.

Training methodology
This course contains a high level of practical work, backed up by lecture and discussion, to provide the most valuable and intensive learning experience possible. All participants will be provided with their own laptop and software for use in practical exercises.

Outline

Day one

The main focus of day one is to study and apply individual Excel features and functions.  On the second day these features and functions are assembled to make complete models and applications.

Examining key function groups in Excel:
The seminar begins by examining key function groups within Excel and applying them to compact - yet sophisticated - sample applications.  These key functions can often be used in combination to multiply their power and usefulness.  Where appropriate, examples are given of this technique. The following function groups are examined:

• Aggregation functions
• Logical functions
• Referencing functions
• Arrays
• Conditional formatting functions
• String functions
• Date functions
• Data tables
• Error handling functions
• Query functions
• Pivot tables.

Applications considered include:
• Performing queries and lookups
• Filtering, especially those involving logical operators
• Highlighting the “top 10” entries in a list.  Highlighting duplicates.  Cleansing data
• Determining business days against various criteria
• Investigating the sensitivity of the enterprise value of a firm to factors such as cost of capital and cash flows.

Reviewing the mathematics behind Excel's financial functions:
Many useful financial functions are built into Excel.  This section describes the mathematics that lies behind some of these functions.  The benefits of knowing the underlying mathematics include:  1) Being able to solve problems from "first principles" if the problems are too complex to be solved by inbuilt financial functions.  2)  Ensuring that functions are used correctly (e.g. that inputs are of the form that Excel "expects").  The following mathematical concepts and functions are covered. 

• Present value and future value and converting between them
• PV, NPV and XNPV functions (present value & net present value)
• FV and FVSCHEDULE functions (future value)
• Effective and nominal interest rates (EFFECT and NOMINAL functions)
• Compounding conventions and converting between them
• Definition of internal rate of return
• IRR and  XIRR functions (internal rate of return)
• Annuity formulae - present value of a perpetual cash flow
• Loan repayment formulae
• PMT, IPMT and PPMT functions (payment & principal payment)
• NPER and RATE functions (number of periods  and interest rate)
• PRICE and YIELD functions

Studying applications of Excel's financial functions:
The functions and mathematics covered in the preceding section have wide application in finance.  These are some examples covered:
• Comparing and contrasting NPV and IRR measures of which is the better project of two alternatives
• Finding the equity payback period on a project
• Deriving the net present value of a series of future cash flows in a variety of contexts
• Determining principal and interest payments to pay back a loan within a given time
• Determining the interest component and principal component of a payment
• Finding the outstanding principal
• Bond valuation and yield to maturity.

Working with Excel's data validation and forms controls
Excel provides features to make it easy for users to enter inputs and to lessen the chance that incorrect or inappropriate values are inputted.  This section shows how the following features can be used.

• The forms toolbar
• Checkboxes
• Option buttons
• Combo-boxes and list boxes
• Data validation.

These are some data validation examples covered:
• Limiting an input to a valid date
• Allowing inputs only if their total is less than a specified amount
• Not allowing duplicate entries
• Not allowing a blank entry between two non-blank entries
• Switching validation criteria on and off by linking to checkboxes.

Day two

Reviewing the principles of financial model design:
As with any area of expertise, it is essential that the principles of financial modelling and model design are understood in order to use these models for more advanced decision-making.
• Objectives of financial models
• Types of financial models
• Key design stages
• Elements of good, and bad, model designs
• Sensitivity analysis - scenarios and data tables.

Applying the principles of model design:
There are many applications and types of financial models.  This section involves applying the principles of model design to constructing some models, for example:

• Loan schedules and savings schedules
• Enterprise models
• Amortisation schedules and sinking fund schedules
• Financial statement modeling.

Solving problems iteratively in Excel:
Some types of financial problems are too complex to solve in a single step. This section describes the mathematics behind iteration and explores the different ways that Excel can solve problems iteratively.

• Goal seeking
• Iteration
• Solver
• Optimisation

Using Visual Basic for financial applications
Visual Basic can be a very effective tool for making modelling in Excel much more efficient. This session will give an introduction to the process and outcomes of using Visual Basic in financial modelling.

• What are macros?
• Recording and creating macros
• Using macros
• What are functions?
• Creating and using functions

Facilitator

Alex Palfi, Principal, Tykoh Group Pty Limited

Alex Palfi is Principal of Tykoh Group Pty Limited - an Australian-based training provider specialising in tailored one and two day workshops on technical finance and business topics.

Prior to setting up Tykoh in January 2009 Alex was a Division Director at Macquarie Group in Sydney.  In that role he developed a range of finance workshops both for Macquarie and non-Macquarie people and presented those workshops internationally in London, New York, Tokyo, Los Angeles, Korea, Hong Kong, Singapore, Taipei, Johannesburg, Bangkok, Sao Paulo, Sydney, Melbourne, Auckland and Wellington.  Those workshops focused on Financial modelling, financial spreadsheets, valuation, derivatives, credit risk and Visual Basic programming.

Prior to joining Macquarie in 1994 Alex worked for a number of financial services organisations as a financial software developer and before that lectured at Technical Institutes in New Zealand in programming and electrical engineering.

Alex's university qualifications are in engineering.  He obtained Masters and Bachelors degrees in Electrical Engineering from the University of Canterbury in New Zealand.

Alex Palfi is also facilitating:

• Financial Modelling
• Visual Basic for Financial Applications

In-house Training

Prices and Registration

* Early bird price available when you register and pay before the dates listed.