Visual Basic Skills AssessmentTry our free on-line Assessment

Spreadsheet skills for finance [Day 2]


This course covers spreadsheet skills. During the course you will:

  • Review how financial statements can be modelled in spreadsheets.
  • Examine the principles of financial model design and apply those to design, develop, test and maintain financial models.
  • Learn how to use iteration, goal-seeking, the solver and optimisation to solve problems that are too complex to solve in a single step.
  • Apply Visual Basic macros to automate repetitive tasks.
  • Learn how Visual Basic functions can be written and accessed from spreadsheet formulae.

The course is preceded by an optional first day which covers other spreadsheet skills.

  • About
  • Facilitator
  • Outline
  • Content
  • Assessment
  • Register

About the course

Spreadsheets provide many functions, tools and features that can be used in finance. This course describes those functions, tools and features, illustrates how they work and shows how they can be used in a variety of practical finance settings. Some of the functions are powerful but often underutilized, and other financial functions are sometimes misunderstood or misinterpreted: Special emphasis is given to those functions.

A well-designed spreadsheet can pay significant dividends: The course shows how good design skills together with a mastery of spreadsheet functions leads to efficient, user-friendly, maintainable, robust, responsive and error-free financial spreadsheets.

About the facilitator

This course's facilitator is Alex Palfi. Before joining Tykoh, Alex had ten years of experience with Macquarie Group in quantitative finance and in presenting workshops internationally. He has presented courses in financial modelling, financial mathematics, financial programming, valuation, and option pricing and risk management in Europe, North and South America, Africa, Asia and in Australia & New Zealand.

Alex has worked in Investment banking, commercial banking and software development industries. Alex's university qualifications are in engineering. He obtained Bachelors and Masters degrees in Electrical Engineering from the University of Canterbury in New Zealand.

Audience

This course's intended audience is spreadsheet users in finance who wish to understand and apply the important functions, features, tools and techniques that spreadsheets provide.

Level

This is an intermediate through to advanced level course.

Duration

The course comprises one day. An optional one day course on the preceding day covers additional topics on spreadsheet skills.

Prerequisites

You will need a level of knowledge equivalent to that covered on the optional preceding day.

Format

The course has a "hands-on" format. Each participant works with a laptop for the major part of the course on practical financial spreadsheet topics.

Following is an overview of this course's content.

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. The following are covered:

  • Objectives of financial models
  • Types of financial models

  • Elements of good design
  • Key design stages

Applying the principles of financial model design

There are many applications and types of financial models. This section involves applying the principles of model design to constructing models. Models and applications covered include:

  • Financial statements
  • Enterprise models
  • Loan and amortisation schedules
  • Sinking fund schedules

  • Modelling the current and future states of an entity
  • Examining behaviour under various scenarios
  • Determining sensitivity to critical revenue and cost drivers
  • Valuing cash flows and determining overall value

Solving problems iteratively

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 such problems can be solved iteratively. These are the methods covered:

  • Iteration
  • Goal seeking

  • The solver
  • Optimisation

Using Visual Basic for financial applications

Visual Basic can be a very effective tool for extending the power of spreadsheets. This session gives an introduction to the process and outcomes of using Visual Basic in spreadsheet modelling. Topics covered are:

  • Recording and playing back macros
  • Defining and using new functions

  • Integrating Microsoft Office applications

Register

You can register your interest for any of our courses by selecting the "Register" menu item at the top of this page.

Self assessment

You can do an online self assessment of your spreadsheet skills. Click here.

Competition

We are hosting a spreadsheet skills quiz / competition. The live leader board is below.

LeaderPercentAt
test25.8%
alex0.5%
----------0.1%

To enter the competition click here.

Video

To see a video of how to do the quiz above click here.

Webinar

We provide this workshop in classroom and webinar formats. Click here for information about webinars.