Financial Modelling For Forecasting, Budgeting & Financial Statements in Excel 

DURATION:  2 DAYS (16 HOURS)

DIFFICULTY LEVEL: ADVANCED

OUTLINE

1.      1.       Financial Models

Introduction & types of financial models

Program scope

Financial model structure

Budgeting & forecasting

The budgeting & forecasting process

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

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

4.       Price setting for products & services

Understanding price and demand curves

Price elasticity of demand computation methods

Decisions in price setting

5.       Cashflow

Working capital components, ratios & cash conversion cycle

Cash flow projection using the direct method

Free Cash Flow (FCF)

6.       Form Controls

Essential Form Controls & overview

Incorporating Form Controls into financial models & sensitivity analysis

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

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-making

9.       Important Excel knowledge

Essential Excel functions in financial modeling- VLOOKUP, SUMIF, SUMIFS, IFERROR, INDIRECT, IF, IFS

Utilize macros to automate simple tasks

10.   Managing multiple budget versions

Version controlling your budget forecast spreadsheets

Fitting in multiple budgets into your financial model

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.