<< Return to Calendar

Introduction to Financial Modelling Using Excel & VBA

Overview

Financial modelling is one of the most highly valued but less understood skills in finance.  The objective is to combine accounting, finance, and other business metrics to create an abstract representation of a company in Excel, forecasted into the future.

There are many types of financial models with a wide range of uses including: Budgeting, project appraisal, financial statements, or more intricate applications such as making business decisions at a company, making investments in a private or public company, pricing securities, or undergoing a corporate transaction such as a merger, acquisition, divestiture, or raising of capital.

Forecasting a company’s operations into the future can be very complex.  Each business is unique and requires a very specific set of assumptions and calculations.  Excel is used because it is the most flexible and customizable tool available. Customised software, as an alternative, can be too rigid and won’t let you have the flexibility to adapt to ever changing business needs.

Why Should an Individual Attend?

Running a profitable, successful business entails forecasting of budgets, income, expenses and ultimately profitability. Creating a financial model suited to the specifics of the entity will allow timeous responses to the quick changing environment of today’s business needs.

Here is some background information on the modelling concept:

  • Create a comprehensive model that will simulate different scenarios at a glance and will empower individuals to understand the full impact of event changes and the impact of these changes on the profitability of the business.
  • Scope - creep is an ever-present ogre in the engineering world. A comprehensive model will immediately highlight the effect of any changes on the scope of projects at the time as well as the impact thereof.
  • Modelling techniques not only apply to financial forecasts, but expand to encompass the entire gamut of projects, costing, quotations and tenders, ongoing changes as the projects progress et al. Structuring models that can highlight tipping points in the life cycle of the projects can forewarn engineers of potential dangers.
  • Modelling is a skill that all engineers use continuously, where a structures modelling is not. This course highlights the difference.
  • Skills gained through these techniques could assist Engineers in future effective applications.

Outcomes

At the end of this two-day course, delegates will be able to:

  • Understand the 6-step process to build a successful model.
  • Know what is and what is not a Financial/Engineering Model
  • Have reviewed the essential Excel skills to enable them to apply the required logic in the model.
  • Have reviewed financial concepts to enable successful project evaluation.
  • Have built and developed various models.
  • Understand the separation of input, process, and output, using extraneous variables to generate scenarios for different results.

Program Outline

DAY ONE

Concepts and skills

  • Constants
  • Extraneous variables
  • No “hard coding”
  • Links
  • Principles

Excel skills review

  • Functions
  • Logical functions
  • Referencing functions
  • Financial functions
  • Error handling functions
  • Arithmetic functions
  • Date functions
  • Ranking functions
  • Text functions

VBA

  • Introduction
  • Creations of Macros
  • Debugging of Macros
  • The VBA Editor

DAY TWO

Tools and features

  • Formula evaluation / tracing and diagnosis
  • Customising the Quick access toolbar
  • Customising Excel (Options, the back-stage view)
  • Form elements and controls
  • Data tables
  • Conditional formatting
  • Custom formats
  • Charts and templates
  • Arrays
  • Worksheet and workbook protection
  • Function Wizard
  • Auditing tools
  • Keyboard shortcuts
  • Basic VBA

Review of Relevant financial concepts and financial functions

  • Discount rate
  • Present and future value
  • Internal rate of return
  • Cost of debt and cost of equity
  • Weighted average cost of capital
  • Free cash flow to equity and to the firm
  • FX rate
  • PMT, RATE, PV, FV & NPER functions
  • Inflation rate, real and nominal values
  • IRR, rolling IRR, hurdle rate, The cost of capital (WACC)
  • NPV, XNPV, and XIRR functions

Concept case studies and problems to be done as teams during the 2-day period.

Who Should Attend?

  • Engineers
  • Architects
  • Project / Construction Managers
  • Quantity Surveyors
  • Construction Health and Safety
  • Executives / Senior Management             
  • Middle Management
Dates and Times:
  • Mon 27 May 2024  08:30 to 16:30
  • Tue 28 May 2024  08:30 to 16:30
Type: Online
Venue: Virtual Classroom Training, Online
Fee: R 5 170.00 excl. VAT
R 5 945.50 incl. VAT
Validation Number/s: CESA-2146-11/2025
CPD Points: 2
Coordinator: