Spreadsheet quality assessment
Assess your spreadsheets’ quality
Home Register Contact About Us

  Spreadsheet skills workshop


Links

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

About this 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.

Outline

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.

Content

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

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

Links


Sitemap | Terms of use | Privacy | Contact us
- 3.219.31.204 -