Visual Basic Skills Assessment Try our free on-line Assessment

Spreadsheet skills for finance


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

  • Use a wide range of Excel functions and practice applying them in finance settings.
  • Learn how to choose the best function to solve a problem when several functions are available.
  • Cover techniques to perform complex calculations in a single cell that would otherwise take many cells to do.
  • Be shown the errors that spreadsheets can generate and techniques for eliminating or managing those errors.
  • Review the finance principles and assumptions underlying the main spreadsheet financial functions.
  • Apply the tools, utilities and features available in spreadsheets to make them more effective and user friendly and to speed their development time.

The course is followed by an optional second day which covers additional advanced 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 second day follows this course and on day that further spreadsheet skills are covered.

Prerequisites

You will need spreadsheet skills at an intermediate level. You should be able to use formulae, and understand how cells are referenced (e.g. $A1:$B5).

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.

Key spreadsheet functions, tools and features

This section reviews the most useful spreadsheet functions, tools and features and gives examples of their applications. These are covered:

  • Arithmetic functions
  • Logical functions
  • Referencing functions
  • Aggregation functions
  • Date functions
  • Ranking functions
  • Error handling functions

  • Text functions
  • Query functions
  • Data tables
  • Pivot tables
  • Arrays
  • Conditional formatting

Financial functions

This section explains the mathematics underlying spreadsheet financial 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 functions are covered:

  • PMT, PV, FV, RATE & NPER
  • PPMT & IPMT
  • NOMINAL & EFFECT
  • NPV & XNPV

  • IRR & XIRR
  • PRICE & YIELD
  • FVSCHEDULE

Data validation and forms controls

Spreadsheets provide features to make it easy for users to enter inputs and to lessen the chance that incorrect or inappropriate values are inputted. The following are covered:

  • Data validation
  • Checkboxes
  • Listboxes

  • Option buttons
  • Comboboxes

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.