SPECIALISTS IN FINANCIAL MODELLING
0 item(s) $0.00 AUD + GST

Financial Modelling using Excel

Date: Sunday, 12 February 2017 - 9:00am - Thursday, 16 February 2017 - 5:00pm

Location: Dubai

Duration: 5 days

Financial Modelling Using Excel and Essential Excel for Finance

This five-day intensive public course is being hosted by a third party training provider.  Contact us for more information on attending this event in Dubai.  To run a course like this at your organisation, contact us for quote.

Course Pre-Requisite

The course material includes extensive use of Excel and participants will gain the maximum benefit from this course if they are already competent spreadsheets users. It is designed for users who do use (or will use) Excel on a regular basis, and are comfortable with using its tools and functions.

The course will be taught using the latest Excel 2016 but the models used will also function with any version of Excel.  Excel 2003 users will have a small amount of limitation on functionality.  The text book also contains instructions for Mac users.

Due to the pragmatic nature of this course, you will need to have access to a computer and therefore be able to practice and discuss with examples.  It is imperative that delegates bring their own laptop computers for the duration of the course.  Sharing of laptops is not advised.

Course Outline

 

Day One (optional)

Essential Excel for Finance

Specifically designed for business professionals who need to take their Excel skills to the next level, this intensive full day course focusses on the technical Excel skills that will be required for the Financial Modelling course.  This very practical course covers everything you need to perform your job and nothing you don’t!

For those who don’t have time to sit through extensive Excel “how to” training, this course will arm you with a range of tools, techniques and formulas essential for Finance.  Guaranteed to contain “no fluff”, this optional day will equip you with the skills to produce more efficient and accurate Excel models and increase productivity. 

Must-know Tools and Techniques

  • Key points on upgrading from prior versions of Excel to the latest, 2013
  • Absolute referencing
  • Named Ranges
  • Time saving shortcuts

New Features in Excel 2016

  • An overview of Power Pivot
  • Using sparklines to display trends on dashboards
  • Applying a slicer to pivot tables

Formulas you simply can’t live without

  • LOOKUP, VLOOKUP, HLOOKUP
  • Logical functions eg. IF, AND
  • Aggregation functions eg. SUMIF, COUNTIF
  • Financial Functions eg. NPV, IRR, PMT
  • Formula Nesting

Bullet-proofing your Model

  • Protect data by locking cells
  • Password Protection
  • Restricting incorrect data entry with data validations
  • Using worksheet protection to prevent entry
  • Protecting your file using “Read Only” and password protection

Charting

  • Creating and modifying a chart
  • Making your charts look fabulous
  • Charting tips, tricks and shortcuts
  • New charting features of Excel 2016

Essential Advanced Tools

  • Autofilters
  • Goal Seek
  • Macros
  • Pivot Tables
  • Array formulas

Other Useful Tools

  • Spin buttons & drop-down menus
  • Hiding
  • Working with dates
  • Hyperlinking
  • Conditional formatting

Day 2

Introduction to Financial Modelling

1.       What is Financial Modelling?
Typical examples and purposes of financial models 

2.       Excel or Access?
Tool selection and advantages and disadvantages of Excel and other software for the purpose of Financial Modelling

3.       Model Design
Model planning and steps in building your model

4.       Skills needed for Financial Modelling
The technical, design, business and industry knowledge required for financial modelling

5.       Model Tools & Functions
Which formula or tool is most appropriate in which modelling situation?

6.       Building the business case: issues and logic surrounding the calculation of customer acquisition

  • Creating the pool of potential customers
  • Narrowing down the pool 
  • Expected customer acquisition 
  • Lost customers / customer defection 
  • Market penetration 

Practical exercises: Modelling exercises using a range of financial functions and tools.  Building a business case utilising financial modelling techniques

Day Three

Financial Modelling Theory & Best Practice 

1.       Modelling Techniques 

  • What makes a good model?
    Attributes of a good model such as user-friendly and structural feature
  • Avoiding Excel errors
    A wrong calculation in a model can have disastrous consequences!  Strategies to reduce errors in financial modelling
  • Rebuilding an inherited model
  • Building error checks
  • Correcting errors
  • Create a more efficient model with links
  • Dealing with links and the potential errors they cause

2.       Bullet-proofing your model
Retain model integrity and prevent misuse of your model by learning to restrict incorrect inputs

  • Protection
  • Locking cells
  • Restriction incorrect data entry with data validations

3.       Making a model user-friendly

  • Formatting
  • Navigation

4.       Dealing with uncertainty and risk

  • Economic inputs to model, and modelling fluctuations in external factors
  • Use of stress testing to validate a model
  • What-if analysis
  • Importance of assumptions when assessing risk

5.       Create a best, base and worst case scenario on your model.  Technical tools for creating scenarios:

  • Manual sensitivity analysis; drop-down scenarios
  • Scenario manager
  • Data tables

6.       Displaying final model results and findings
Communicate the results of your model clearly and concisely whilst getting the key message across to the audience

  • Documentation and source referencing
  • Writing assumptions
  • Writing operation instructions

Practical exercise: Building scenarios and perform sensitivity analysis on financial models.  Build a property development scenario model. Build a cash flow funding model, including a P&L, Cash flow and Balance Sheet including scenario analysis.

Day Four

Financial Modelling for Budgeting & Forecasting 

Practical application of modelling in Excel for the purpose of budgeting for organisations 

1.       Advanced forecasting functions; Time series, exponential smoothing and regression analysis.

2.       Modelling for “Stepped” costs when forecasts include both fixed and variable costs.

3.       Sales forecasting
Predication of likely sales revenue based on drivers

4.       Seasonality
Simply and easily spread revenue or expenses throughout the budget period based on season variations

5.       Building an expense budget
Based on given assumptions, we will build a budget for 12 months

6.       Contingency Planning
Calculating a contingency based on assumptions about probability and impact of unexpected events

7.       Budgeting for capital expenditure
Capital expenditure is budgeted for cashflow and then depreciated

8.       Cash flow budgeting
Translating our profit and loss statement into a cashflow forecast

9.       Reporting against budget; variance analysis
Creating a template into which actual and budget can be compared and analysed

Practical Exercise:  Create a model in Excel which will calculate volume-dependent “stepped” costs.

Day Five 

Practical Financial Modelling

1.       Charting and graphing fundamentals – how to build charts

2.       When to use a table or charts; combining tables and charts using chart data table and data bars 

3.       Choosing the correct chart to display your findings

4.       Visual Design - dos, don’ts and common mistakes in graphical presentation of data and reports

5.       How to present easy to follow information concisely and clearly

6.       Pivot Tables and their role in financial modelling

Practical exercise: Build your own flexible report including a dynamic chart 

Case Studies:

Demonstration and overview of the following:

1.       Tiering tables
One of the more complex and widely used calculations in financial modelling. Discussion of two different methods of calculation; a simple and progressive table

2.       Pricing models
Calculating profitability and break even points at various prices

3.       Dynamic charting
Create a user-friendly report with a dynamic chart based on drop-down selections

4.       Modelling for risk assessment
Assess project risk with a  risk assessment model using conditional formatting

Practical Exercise:   Forecast a sales cycle based on historical data by calculating the seasonality index and adjusting the forecast accordingly.