Home  |  About Us   |   Be Our Speaker  |  Terms & Conditions  |  Connect With Us
Executive Education
Clariden Leadership Institute
Financial Modeling for Financial Analysis, Forecasting and Budgeting in Excel (NZ)
Alan Brooke
Aquipu Financial Consultants
Alan Brooke, Aquipu Financial Consultants

Alan Brooke has over 30 years’ experience in a wide range of roles in fi nance and has worked as an independent financial modeler, trainer and analyst for a wide range of blue-chip clients for the past 17 years.

 

He has built up a great deal of experience in financial modeling in numerous sectors, including gas production, electricity generation, energy retailing, waste processing and property development sectors, as well as building financial models in central government departments, retail enterprises and high-volume online environments. He has built, developed and used models to support commercial negotiations, analyze risks, test scenarios and forecast results.

 

Alan has also trained others in his specialism of financial modeling and runs public courses on behalf of major training companies in more than a dozen countries, such as UK, US, China, Hong Kong, Singapore, Germany, Netherlands, South Africa, Vietnam, Saudi Arabia and UAE. Clients for whom Alan has delivered in-house training for include Qatar Gas, Salalah Methanol Company, PepsiCo, Vietcom Bank, Vestas Wind, PwC, Deloitte and International Finance Corporation and many more from the banking industry.

 

Alan trained as a Chartered Accountant at KPMG in South Africa and New Zealand, before entering the industry with Ford Motor Company. He held various positions in the company, starting with financial analysis, budgeting and forecasting and finally as a Sales Planning Manager who is responsible for forecasting models, production planning and supply logistics. After which, he took on the role of General Manager Finance in a multinational private consultancy group in Australia. In this role, he guided the group through a period of major change and financial turnaround.

 

With an extensive accounting background, Alan brings accounting knowledge and analytical skills to transactions and financial modeling.

Program Summary

Financial modeling is often the centerpiece of an effective financial management strategy. With increasing economic volatility and uncertainty, it is becoming more crucial than ever for you to learn the necessary skills required to build robust financial models, forecast and budget that is accurate and aligned with your organization’s strategy.

 

Clariden is pleased to welcome you to this two-day comprehensive program on Financial Modeling for Financial Analysis, Forecasting and Budgeting in Excel. Led by Alan Brooke, a trusted advisor in financial modeling, this executive program is designed for financial leaders to help you discover the tools, techniques, and special considerations that will increase the accuracy of your fi nancial forecast and budget and develop a more robust analysis process that will improve your overall financial strategy. By utilizing the best practices in designing fi nancial models, you will learn how to build eff ective financial models using Excel, develop accurate financial projections by using customized forecasting and budgeting drivers. You will also discover how to build a comprehensive financial dashboard that includes key metrics and KPIs which will aid your senior management in effective decision making.

 

Through hands-on exercises and case studies, you will also discover how to model important risk factors and stress test your models with eff ective sensitivity and scenario analyses. At the end of the program, you will walk away with an enhanced understanding of forecasting and budgeting and solid fi nancial modeling skills.

 

This interactive two-day executive program is launched concurrently with Strategic Financial Planning: Financial Analysis, Forecasting and Budgeting for Improved Profitability. As both programs are designed to help you establish the right finance capabilities for your organization to pursue growth, you are encouraged to send in multiple participants to these two highly popular finance programs.

 

 



Programs, dates and locations are subject to change. In accordance with Clariden Global policy, we do not discriminate against any person on the basis of race, color, sex, religion, age, national or disability in admission to our programs.

Introduction

Accounting and tax advisory Weiser Mazars uncovered in 2011 that 87% of the CFOs they surveyed relied heavily on Excel spreadsheets in their financial closing and FP&A activities, as well as for budgeting and reporting. However, many identified challenges in building effective financial models which is the most widely used tool in the finance profession, to help advance business performance analysis and decision making.

 

Aimed at all finance professionals involved in the analysis and preparation of budgeting and forecasting, this two-day Financial Modeling for Financial Analysis, Forecasting and Budgeting in Excel executive program will show you how to use financial modeling to reduce risks in your decision-making and provide recommendations with proven practical quantitative tools and techniques.

 

Led by Alan Brooke, a trusted financial modeling consultant and trainer, this two-day Financial Modeling for Financial Analysis, Forecasting and Budgeting in Excel executive program is designed to be both engaging and vastly practical with emphasis placed on helping delegates gain solid, practical experience in designing and constructing financial models in MS Excel. By learning the best practices in designing financial model design, you will be able to prepare logical and easy-to-use financial models to support transactions, forecasts and planning for ongoing business streams. You will also learn how to identify forecasting and budgeting drivers that will help to increase revenue, reduce or reallocate costs and ratify Key Performance Indicators (KPIs). This will aid you in producing objective budgets and forecasts understood by senior management and bought into by operational and divisional heads.

 

Through hands-on exercises and case studies, you will also discover how to drill down on risk factors and stress test your models in sensitivity and scenario analyses. At the end of the program, you will walk away with enhanced understanding of forecasting and budgeting as well as more solid model-building skills.

What You Can Expect

  • Discover the best practices in model structures and logic
  • Identify key forecasting and budget drivers as well as learn how to build forecasting and budget models around them
  • Build logical and easy-to-use fi nancial models to support transactions, forecasts and planning for ongoing business streams with Microsoft Excel
  • Grasp useful tools to highlight areas of risk, particularly in sensitivity analysis
  • Learn techniques to consolidate and report key model outputs

Who Will Benefit Most

This program is designed for mid to senior level professionals with a working knowledge of forecasting, budgeting and fi nancial analysis as well as basic Excel knowledge, whose responsibility involves:

 

  • Financial Planning / Finance Business Partners / Capital Planning
  • Management Accounting
  • Product Control
  • Planning & Budgeting
  • Costing
  • Forecasting & Financial Analysis
  • Statutory & Regulatory Reporting / Policy Planning
  • Business Planning / Strategic Planning / Business Development
  • Operations
  • Portfolio Management

 

and anyone with vested interests in forecasting trends at the strategic, tactical, operational and planning levels

Program Outline

08:30 - 18:00 | Day 1, 14 August 2017

 

Introduction and Course Objectives

  • Brief overview of objectives
  • Review of models and their objectives

 

Best Practice in Model Structure and Design

  • Best financial modeling practice
  • Model’s role in forecasts, budgets and financial analysis
  • Overall structure of the model
  • Logic flow within the model
  • Defining desired outputs
  • Separation of inputs, calculations and output
  • Consistency in the model
  • Use of flags to control timing factors
  • Set-up for flexibility
  • Using the assumptions sheets as a sign-off document
  • Restricting ranges of inputs and validation criteria
  • Protection of worksheets and portions of worksheets
  • Version control
  • Use of switches to allow option selection
  • Use of the corkscrew technique
  • Exercise – create a simple model with an assumption / input sheet with built-in flexibility

 

Forecasting and Budget Drivers

  • Activity-based cost drivers
  • Volume changes: fixed vs. variable costs and fixed with step-changes
  • Absorption of overheads into unit costs
  • Drivers of overhead costs
  • Cost allocation techniques for indirect and corporate overheads
  • Revenue drivers: customer and market segmentation
  • Controllable and non-controllable factors
  • Exercise – for a given case study, identify drivers for a number of costs and create a budget for them

 

08:30 - 18:00 | Day 2, 15 August 2017

 

Cash Flow Analysis

  • Brief revision of:
  1. Weighted cost of capital (WACC)
  2. Discounted cash flows
  3. NPV & IRR
  4. Capital Asset Pricing Model
  • Exercise – for a given set of assumptions and cash flows, calculate cost of debt, cost of equity, WACC, NPV and IRR

 

Comparing a Model to Previous Versions of the Model

  • Separate runs and variation of inputs
  • Comparison of actuals to forecast
  • Comparing results of different versions of same model
  • Reviewing future implications of variances
  • Example – from different versions of a modeled forecast, calculate variances and review future assumptions

 

Sensitivity and Scenario Analysis in a Model

  • Stress-testing the model
  • Varying inputs to assess effect on results
  • Use of built-in sensitivity inputs
  • Use of goal seek & solver
  • Version control to allow comparison of outputs
  • Use of Excel tools to support sensitivity analysis:
  1. Watch window function
  2. Data tables
  3. Scenario manager
  • Exercise – from a given model of cash flows, P&L and balance sheet, calculate effect of varying inputs to a given degree, and stress-test model to break-even

 

Reporting Outputs

  • Design techniques to enable optimization
  • Pivot tables
  • Consolidation techniques
  • Use of charts & graphics
  • Exercise – from a given set of outputs, create output tables, pivot tables and consolidations to enable flexible reporting

 

Wrap-Up

  • Overall review
  • Key points to re-iterate
  • Brief introduction to further exercises & reading
  • Final questions and issues to discuss

 

 

CFOs Leadership :
Experience Clariden
Discover how our leadership program has shaped the perspectives of CFOs across Asia
 
Venue: Stamford Plaza, Auckland
Date: 14 - 15 August 2017
Faculty: Alan Brooke
Early Bird 1: NZ$2,195 (by 19 June 2017)
Early Bird 2: NZ$2,395 (by 17 July 2017)
Regular Fee: NZ$2,495
Group Discount: 2nd participant get 10%, or register 3 participants and 4th participant get a complimentary seat
(1 discount scheme applies)
Contact: zachariah@claridenglobal.org
 
 
More Programs
Procurement Audit (SG)
- Ted Landgraf
Masters in International Project Management [MIPM] (SG)
- Matthew Harrison
Higher Education - Developing Scorecards, Dashboards and KPIs to Set Strategic Priorities and Drive Performance (SG)
- Alan Fell
Tax Efficient Transfer Pricing and Intra-group Financing (US)
- Matthew Andrew
Strategic HR: Moving From Operational To Strategic HR (Melbourne)
- Dr. Karl Larson, PhD
View All Programs >
 
 
   
Home | About Us | Be Our Speaker | Press Release | Terms & Conditions | Connect With Us
Copyright © 2011 Clariden Global, all rights reserved.