SPECIALISTS IN FINANCIAL MODELLING

Advanced Financial Modelling for Budgeting and Forecasting in Excel (Auckland)

Start
November 27, 2018 9:00 am
End
November 29, 2018 5:00 pm

In the ever-changing business environment, being able to accurately model and forecast the volatile economic inputs is a critical skill for business professionals. Build on your existing modelling and forecasting skills and apply new techniques to better analyse financial data, predict revenues and cost and assess risks to justify business decision in the most time-efficient and effective way.

If you prefer to be invoiced and have your accounts department make the payment please use this form.

Status

Unlimited tickets

Event Details

Program Overview

In the ever-changing business environment, being able to accurately model and forecast the volatile economic inputs is a critical skill for business professionals. Build on your existing modelling and forecasting skills and apply new techniques to better analyse financial data, predict revenues and cost and assess risks to justify business decision in the most time-efficient and effective way.

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

Training Methodology

This course covers the practical usage of Excel in a Financial Modelling and Forecasting context. It is designed for users who use Excel regularly and are comfortable with commonly used Excel tools and techniques. Whilst a brief overview of tools used in this course is provided, it is assumed that delegates are confident Excel users.

Course Materials

At the workshop, each participant receives a revised edition of ‘Using Excel for Business Analysis’ by your course presenter, Danielle Stein Fairhurst. Participants are also provided with over 50 Excel templates and examples with step-by-step instructions for case studies

Software used

Each delegate needs their own laptop or workstation with their preferred version of Excel installed. The course is demonstrated using Microsoft Excel 2016, but students can use Excel 2007, 2010 or 2013 if they prefer. No add-ins are required.

Objectives

  • Insights into defining the “uncertainties” that affect companies’ ability to produce accurate Financial Models.
  • Advanced techniques to develop and manage more robust, multi-layered models to ensure maximised efficiency of financial models through in-depth use of functions and tools.
  • Discover and leverage on the different types of forecasting techniques that can be used in Financial Modelling.
  • Understand how to build for the “real world”, allowing for flexibility and future change.
  • Streamline model building by applying best practice functions, tools and techniques.
  • Structure financial models to be more efficient and flexible for continuous maintenance and future expansion.
  • Application of effective simulation techniques to examine the risk of a project.
  • Create Forecast Models based on real-world Financial Modelling challenges.
  • Incorporate current uncertainties into the model in order to take more calculated risks and make more informed business decisions
  • Quantify uncertainty in forecasting models with stress-testing, what-if analysis and risk analysis techniques.

Day 1: Budgeting and Forecasting Theory

Cost Management Concepts
Overview of accounting theory critical for the budget manager

  • Financial Accounting vs. Management Accounting
  • General ledger accounting for budgeting
  • Budgeting for marginal, opportunity and sunk costs

Budgets and Strategic Planning
How budgeting fits into your organisation’s strategic planning process

  • Linking Budgets to business objectives
  • Budgeting to achieve organisational objectives
  • How to turn measurement into management
  • Budgeting vs forecasting

Using Budgets as Performance Management
What gets measured gets done!

  • The impact of budgets on organisational performance
  • Successfully integrating forecasting and budgeting as essential tools for measuring performance
  • Translating the organisation’s values into measures and targets

Financial Statements
Consolidating the detailed budget into financial statements

  • Linking financial statements to drivers
  • Measures such as NPAT, EBIT and EBITDA
  • Budgeting cashflow
  • Capital Expenditure and depreciation budgeting

Reporting
Using budget reporting for management and decision making

  • Using reporting to promote accountability
  • Variance analysis
  • Non-financial measures such as the balanced score-card
  • Budgeting and forecasting as a planning and management tool
  • Assessment and selection of appropriate systems

Budgeting Techniques
Discussion and overview of different budgeting systems and methods

  • Historical-based versus Zero-based budgeting
  • Top down vs bottom up
  • Activity Based Budgeting
  • Flexible budgets
  • Rolling forecasts
  • Differences between a static, flexible and rolling budget
  • Budgeting for capital expenditure vs. operational expenditure
  • Contingency planning

Limitations of budgets
What’s wrong with how we do budgets?

  • Critique of the traditional budget process
  • Estimations and rigidity
  • Staff perceptions and changing environments
  • Dysfunctional budgeting behaviour

Budgeting “Soft” Skills
Non-financial skills required for the budget manager

  • Achieving buy-in from stakeholders
  • Involving non-financial management of the organisation
  • Techniques in getting your budget approved
  • Managing conflicting priorities and demands
  • Setting timelines

Day 2: Financial Modelling Best Practice

Theory and Best Practice Projection & Forecast Financial Models

  • Best practices in Forecast modelling.
  • Comparison and evaluation of types of Forecast modelling.
  • Model planning structure and steps in building a Forecast model.
  • Learn the most effective structure for designing spreadsheet models to save time.
  • The technical, design, business and industry knowledge required for Financial Modelling.
  • Selecting the formula or tool which is most appropriate for each modelling situation.
  • Identify and emphasise the key inputs and assumptions that drive the model outputs and business decisions
  • Incorporate feedback and build organisational consensus on inputs and analyses.
  • Attributes and anatomy of a good Financial Model.

Case Study: What makes a poor Financial Model? Critique of a poorly built model.

Financial Modelling Tools to handle Uncertainty

  • How does uncertainty impact today’s Financial Models?
  • Assess and quantify uncertainty with scenarios and risk analysis tools and Financial Modelling techniques.
  • Using best practices to forecast despite volatility and uncertainty in contributing variables.
  • Thought process in deriving assumptions and documentation of variables for uncertainty.
  • Drivers of uncertainty in forecast modelling; e.g. industry forecasts, growth rates, market share.

Incorporating Risk Analysis and Uncertainty into a Forecast Model
Determination of business trends, cycles & seasonality
Utilisation of trends, cycles of the current market situation in the forecasting process

  • Utilise statistics and regression analysis tools in Excel such as TREND, FORECAST and GROWTH formulas in a model to predict and estimate future results.
  • Understanding the inter-relationship among trends, cycles and seasonality
  • Recognising patterns in financial data
  • Using Regression Analysis as a predictor and estimator
  • Identifying and calculating business trends, business cycles and seasonality in historical data
  • Using the seasonality factor in interpreting the forecast variances

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

Advanced Financial Modelling Tools & Techniques

  • Maintaining model integrity and avoiding Excel errors.
  • Building self-balancing error checks.
  • Correcting and suppressing errors.
  • Use advanced techniques to increase the accuracy of information being input by model users.
  • Create a more efficient model with internal links.
  • Best practice for dealing with external links and the potential errors they cause.
  • Pros and cons of using array functions.
  • Using the SUMIF/SUMIFS formula and array functions to perform complex conditional calculations.
  • Using advanced Excel functions to construct a Financial Model in a shorter amount of time.
  • Best practices on tools used to evaluate the  outcomes of models: NPV, XNPV, IRR, XIRR and other measures.

Case Study: An assessment of the use of NPV & IRR as accurate methods of project evaluation.

Testing the Financial Model under different risk scenarios

  • Develop and sharpen techniques required to increase accuracy in models through scenario and risk analysis.
  • Learn practical methods of what-if and scenario analysis in Excel:
  • Show multiple scenario outcomes simultaneously with one and two-dimensional Data Tables

Day 3: Budget and Forecast Modelling

Data Analysis and Management Reporting

  • Learn efficient reporting methodologies that will help in making complex business decisions.
  • Creating a Rolling Forecast in a Financial Model.
  • Variance Analysis in Management Reporting.
  • Advanced Excel data analysis tools to analyse historical data.
  • Management Summary & Reporting.
  • Consolidating multiple models & reports in the least amount of time.
  • The use of pivot tables for reporting.
  • Learn to write formulas that automatically rebalance monthly, quarterly and annually.
  • How to create robust reports which link to several files.
  • Develop presentations to persuade Executives of the strategic implications of the model outputs.

Practical Exercise: Creating a variance analysis forecasting reporting model: a dynamic template into which actual and budget can be compared and analysed quickly and efficiently. We will create a concise report that shows only the current month depending on the month selected by the user and forecast for the full financial year.

Developing the Budget Model
Turn your budget theory into a practical and dynamic financial model

  • Types of budget model (short and long-term models)
  • Fixed vs. variable costs
  • Drivers in budget modeling; e.g. industry forecasts, growth rates, market share

Stress-testing
How sensitive is your business to changes in budget drivers?

  • The difference between scenarios and sensitivities
  • Multi-currency budgeting
  • Stress-testing and validating your budget model integrity
  • Performing a “what-if” and sensitivity analysis
  • Optimise results and achieve maximum efficiency within constraints
  • Creating and displaying budget scenarios

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

Reviews

There are no reviews yet.

Only logged in customers who have purchased this product may leave a review.