Centre for Continuing Education

Microsoft Excel Financial Modelling Course

Microsoft Excel. Unlock insights and tell the story in your data.

Learn Microsoft Excel the smart way with Microsoft Excel courses at the University of Sydney.

This Microsoft Excel Financial Modelling Course is designed for those of all backgrounds as an introduction to building a basic spreadsheet model in Excel. It covers spreadsheet functions including lookup, conditional sum and if formulas, together with financial functions IRR (Internal Rate of Return) and NPV (Net Present Value). Spreadsheet techniques including autofilters and conditional formatting will be explored, and recording macros and writing basic code are covered in an introduction to macros and the Visual Basic editor.

Aims

This Microsoft Excel Financial Modelling Course aims to introduce participants to functionality available in Excel to support them as they build their financial model. This course is not designed to teach participants financial modelling techniques. It is designed to offer participants a greater understanding of the functions and automation available within Excel when preparing a financial model.

Outcomes

Upon completion of this short course, you should be able to:

  1. Create a financial model in Microsoft Excel.
  2. Automate some of the most common functions available in Excel, to help a model adapt and be relevant as situations and assumptions change.
  3. Open and follow workbook formulas, ranges and simple recorded macros.
  4. Ensure basic protection of a model to avoid data loss and errors.

Content

This Microsoft Excel Financial Modelling Course covers the following topics:

Spreadsheet modelling techniques

  • Tips for basic structure and design.
  • Considering the separation of input data from processing and output data and breaking it down into simple parts.
  • Using Absolute and Partial cell referencing to master avoiding hard coding.
  • Effective design tips and tools.
  • Minimising the risk of error and using validation tools.
  • Tips for effective reporting.

Microsoft Excel functions and methods useful for modeling

  • Named cells and ranges.
  • Lookup functions using VLOOKUP.
  • Conditional functions (IF, COUNTIF, SUMIF).
  • Financial functions (IRR, NPV).
  • Formula nesting.
  • Autofilters.
  • Advanced filters.
  • What if analysis using Goal Seeking.
  • Conditional formatting.
  • Navigation buttons.

Automating your model with macros

  • Pros and cons.
  • Macros security level.
  • Recording and running simple macros.
  • The Visual Basic editor.
  • Modifying a recorded macro.

Protecting your model from undesired changes

  • Model structure and locking cells.
  • Worksheet and workbook protection.
  • Data validation.

Delivery Style

This Microsoft Excel course is delivered as presenter-taught computer-based training in a Microsoft Windows environment.

Intended Audience

This Microsoft Excel training course is suitable for those who currently use Excel to perform simple tasks and want to become familiar with more advanced Excel tools, formulas and functions necessary for building effective models for business analysis. The content covers intermediate Excel techniques in constructing a basic model, with some of the content covered in Microsoft Excel Course: Level 2 and Microsoft Excel Course: Level 3 covered in this course. Participants should be familiar with, and understand basic Excel navigation including creating basic formula calculations such as Sum and Average.

Features

  • Free repeat class - Conditions apply
  • Free USB flash drive
  • Expert trainer
  • Dedicated computer for every student
  • Small class size
  • Student notes – yours to keep
  • Statement of completion

What others say.

  • The course was informative and well presented by a well-informed presenter.