Financial Modelling for Forecasting, Budgeting & Financial Statements in Excel

Course Objectives

The course learning outcomes are statistics, Excel and finance.

Breakdown of learning outcomes:

• Important Excel functions.
• Time series models, regression, confidence intervals.
• Forecasting and budgeting.
• 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 budget versions.
• Build your own dynamic scenarios with form controls.

Target Audience

This is a 2-day hands-on workshop in forecasting and budgeting delivered in Microsoft Excel. Participants learn how to create forecasting and budgeting financial models using Excel.

Being an Excel-intensive class, participants are expected to possess a mid-level working knowledge of MS-Excel. The session teaches participants the Excel and statistics know-how in building a robust budgeting and forecasting financial model.

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 2016 (or higher) with Power Query are required. This program is conducted with practical exercises and case studies.

Methodology

Instructor-led follow-along workshop. Datasets provided.

Course Modules

Module 1: Financial Models

• Introduction & types of financial models
• Program scope
• Financial model structure
• Budgeting & forecasting
• The budgeting & forecasting process

Module 2: 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

Module 3: 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
• FORECAST, CORREL
• Tracking forecasting accuracy as a KPI
• Using Excel’s Data Analysis tools- Exponential Smoothing, Moving Average and Regression Analysis

Module 4: Price setting for products & services

• Understanding price and demand curves
• Price elasticity of demand computation methods
• Decisions in price setting

Module 5: Cashflow

• Working capital components, ratios & cash conversion cycle
• Cash flow projection using the direct method
• Free Cash Flow (FCF)

Module 6: Form Controls

• Essential Form Controls & overview
• Incorporating Form Controls into financial models & sensitivity analysis

Module 7: 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
• The Analyze Data function

Module 8: 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-makin

Module 9: Important Excel knowledge

• Essential Excel functions in financial modeling- VLOOKUP, SUMIF, SUMIFS, IFERROR, INDIRECT, IF, IFS
• Utilize macros to automate simple tasks

Module 10: Managing multiple budget Version

• Version controlling your budget forecast spreadsheets
• Fitting in multiple budgets into your financial model

Module 11: Introduction to business intelligence tools in Excel

• Utilizing Power Query, Power Pivot and the OLAP Pivot Table to extract, transform and stratify historical data