Duration
2 days
TARGET PARTICIPANTS
This 2-day training session is targeted audience whose work function involves crunching forecasts and budgets. Participants will learn qualitative & quantitative techniques, sensitivity analysis, file consolidation, dynamic version control, form controls and visualization. Quantitative methods include multiple regression analysis and exponential smoothing.
Being an Excel-intensive class, participants are expected to possess a mid-level working knowledge baseline of MS- Excel. The session deep-dives into certain useful Excel functions & tools and breaking them to show their application and null-application.
The class also welcomes persons who would like to understand the forecasting and forecasting process, understand quantitative and qualitative forecasting and acquire the know-how in building robust financial models.
Financial Models covered are the Budgeting Model, Forecasting Model and the 3-Statement Model. The use of MS- Excel is extensive and laptops affixed with Excel 2013 (or higher) with Power Query are required. This program is conducted with practical exercises and case studies.
COURSE OBJECTIVES
- Learn to apply statistical and judgmental forecasting techniques.
- Reduce turnaround-time in managing your forecast structure and worksheet process.
- Learn how to version-control multiple forecast worksets.
- Build your own dynamic scenarios with form controls.
SETUP REQUIREMENTS
Required software: Excel 2013 or above.
OUTLINE
- Financial Models
- Introduction & types of financial models
- Program scope
- Financial model structure
- Budgeting & forecasting
- The budgeting & forecasting process
- Building the financial model
- Building revenue & expense drivers
- Revenue & cost projection
- Modelling the Income Statement & supporting cost centre sheets
- Building the headcount & payroll model
- Projecting the balance sheet
- Forecasting methods
- Qualitative and Quantitative forecasting methods
- Naïve method, Simple Moving Average, Simple Weighted Moving Average, Exponential Smoothing, Correlation, Regression Analysis
- Forecast Sheet function (applies to Excel 2016 & above)
- Multiple Regression Analysis, Coefficient Correlation, Coefficient of Determination
- CORREL, TREND and new iterations of the FORECAST functions
- Tracking forecasting accuracy as a KPI
- Using Excel’s Data Analysis tools- Exponential Smoothing, Moving Average and Regression
- Price setting for products & services
- Understanding price and demand curves
- Price elasticity of demand computation methods
- Decisions in price setting
- Cashflow
- Working capital components, ratios & cash conversion cycle
- Cash flow projection using the direct method
- Free Cash Flow (FCF)
- Form Controls
- Essential Form Controls & overview
- Incorporating Form Controls into financial models & sensitivity analysis
- Extracting and consolidating historical accounting data
- Using Power Query to extract & consolidate historical data
- Building forecasting working templates from historical data
- Building sensitivity analysis into forecast templates
- NPV & IRR
- Time value of money & discounted cashflows
- Compounding and Discounting- what’s the difference?
- NPV vs IRR introduction
- Implications of NPV vs IRR in decision-making
Programme scope, delivery arrangements and suitability are confirmed during enquiry.