Financial Modelling forBudgeting and Forecasting in Excel
Date:
Thursday – Friday, 14 – 15 December 2017
9 am – 5 pm (Registration starts at 8 am)
Venue
ASTON Priority Simatupang Jakarta Hotel & Conference Center
Jl. Let. Jend. T.B. SimatupangKav. 9 Kebagusan,
South Jakarta 12520 – Indonesia
Investment Fee
Rp. 8,500,000/ Person (Excluding VAT)
Including: Certificate, seminar materials, lunch and coffee breaks for two days
Get 10% Discount for register more than 1 participants
Prerequisites
This course covers the practical usage of Excel Modelling in a budgeting 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.This course builds on students’ advanced knowledge of Excel tools and functions and incorporates these into complex and dynamic financial models.
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.
Course Materials
At the workshop, each participant receives a revised edition of ‘Using Excel for Business Analysis‘ by your course presenter, Danielle Stein Fairhurst(Additional Fee).
Participants are also provided with over 50 Excel templates and examples with step-by-step instructions for case studies.
Course Format
This course is delivered in two parts; the first section covers the theory of budgeting and forecasting within organizations. Students are encouraged to share their own experiences with the class. In the second section, students will build their own budget and forecasting models to bring a practical application to the skills they have learned. The model will include a budget, rolling forecast, variance report as well as scenarios and sensitivity testing.
Course Content
Day One: 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 behavior
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 Two: Budget and Forecast Modelling
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
- Key factors and considerations for a reliable forecasting
- Modelling for “Stepped” costs
- Using goal-seek to perform break even analysis
Practical exercise: create a model in Excel which will calculate volume-dependent “step” costs. Calculate break-even point using goal-seek.
Determination of business trends, cycles & seasonality
Utilisation of trends, cycles of the current market situation in the forecasting process
- Understanding the inter-relationship among trends, cycles and seasonality
- Recognizing 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 adjustingthe forecast accordingly.
Scenarios, Sensitivities and 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
- Optimize results and achieve maximum efficiency within constraints
- Creating and displaying budget scenarios
About your Presenter
Danielle Stein Fairhurst is the Principal of Plum Solutions, a Sydney-based consultancy specializing in financial modelling and analysis. She is the author of “Using Excel for Business Analysis; a Fundamental Approach to Financial Modelling”, Revised Edition published by Wiley Finance 2015 and “Financial Modeling in Excel for Dummies”, April 2017. With over fourteen years’ experience as a financial analyst, she helps her clients create meaningful financial models in the form of business cases, pricing models and management reports. She has hands-on experience in a number of industry sectors, including telecoms, information systems, manufacturing and financial services.
Danielle has regular engagements around Australia and globally as a speaker, course facilitator, financial modelling consultant and analyst. She holds a Master of Business Administration (MBA) from Macquarie Graduate School of Management (MGSM), and has taught management accounting subjects at Sydney University.
Comments from past students
- “Well-presented and content was excellent”
- “Well organised and subject matter…. was relevant to my current and future job”
- “Excellent course – thanks very much”
- “An excellent combination of technical and practical applications to financial forecasting, control and budgeting.”
- “The instructor has years of experience and was able to solve any problems or questions we had in the course.”
- “I was highly impressed. This course should be a core requirement for anyone considering undertaking an analytical role. I feel the course has provided me with the confidence to address stakeholders in discussing forecasting and budgeting – especially the procedure and method of approach. I’ll be back for another course!”
