Financial Modeling

BUILDING FINANCIAL MODEL IN EXCEL

Jakarta, 10 – 11 Maret 2020

Day(s)

:

Hour(s)

:

Minute(s)

:

Second(s)

SIGN UP NOW

A sound financial model is essential to understand the drivers and implications of business decision. This two day Building Financial Modelling workshop covers the fundamental skills needed to build a financial model from scratch in Excel sheet.

This workshop is aimed at both intermediate and experienced staff and includes the construction of an integrated set of simplified financial statements.

You will learn key tools of Excel which are useful for business analysis and how to construct a reliable financial model encompassing three financial statements (balance sheet, profit and loss statement and cash flow). This workshop also covers how to build the debt and financing sections including interest calculations and the capital structure of the business and has a pre-populated DCF built model.

OUR FACILITATOR

Timotius Ikin Solihin, ST, MBA, CMA, FRM, CTP, CSCP, FP&A

A well-seasoned Executive with 20 years’ Experience in directing, managing and transforming business. Have a broad range of skill and expertise including 7 years of experience as CFO/COO, 8 years of experience with SEC compliance, 4 years of experience in internal auditing. He got his Master of Business Administration in Finance & Accounting (MBA) from Monash University, Australia. Awarded as one of the Best CFO from SWA magazine in 2013 & 2014.

COURSE DESCRIPTION

Financial Modeling

Analisis Skenario

Analisis Sensitivites

Discounted Cash Flow Valuation

Apply a structured approach to financial modelling in Excel.
Outline the hallmarks of good financial models.
Explain the importance of planning and prep your model structure.
Building form iperating revenues down to operating profit.
Prinsip – prinsip financial modeling
Forecasting laba rugi
Forecasting balance sheet
Debt & financing schedule
Forecasting free cash flow

Analisis Skenario

Forecast property, plant and equipment and operating assets.
Forecast the various components of working capital
- Accounts receivable
- Inventories
- Accounts payable
Scenario Analysis
- Model layout & overview
- Designing the structure for scenarios
- Creating switch for live case
- Using CHOOSE function
- Testing and analysis the scearios

Analisis Sensitivites

Forecast debt financing and related interest costs
Forecast equity financing and dividends
Use an iterative analytic approach to managing circular references
Sensitivity Analysis
- Direct method vs Indirect method
- Setting up the data table
- Linking the data table
- Gravity sort
- Tornado chart

Discounted Cash Flow Valuation

Understand the importance of error checking
Derive a forecast cash flow statement based on a forecast income statement and balance sheet
Derive a free cash flow statement that can be used for equity valuation
Equity value & enterprise value
DCF & Valuation multiple
DCF analysis overview
Free cash flow to the firm
Terminal value calculation
XNPV calculation
XIRR calculation
Weighted avarege cost of capital
Building football field chart

Course Curriculum

Unit	Credit Units*
1	Introduction
	Instructor biography
	Key Learning Objective
	Keyboard over the mouse
2	A structured approach to financial modelling
	Key structure for model building
	Modeling best practice
	Inherent tensions in model building
	Model inputs
	Model processing
	Model outputs
	The four step approach
3	Forecasting Income Statement
	Forecasting operating revenues and profits
	Forecasting revenues
	Forecasting gross margin and SG&A expenses
	Income forecast demonstration
4	Forecasting Balance Sheet items
	Our financial forecast modelling framework
	Forecasting financeial statements
	Forecasing PPE
	Capital asset (PP&E) turnover ratio
	Forecasing PP&E acquisions and disposals
	Forecasting working capital
5	Debt and Financing Schedules
	A financial forecast modelling framework
	Forecasting financial statements
	Forecasing the financing structure
	The practicalities of forecasting finance
6	Building a free cash flow forecast
	Forecasting financial statements
	Cash flow from operating activities
	Cash flow from investing activitites
	Cash flow from financing activities
	Forecasting free cash flow to the firm
	Forecasting free cash flow to equity
	Reconciling free cash flow
7	Case Study
	Step for building a financial model
	Raw data
	Format and link historical subtotals
	Build supporting schedules
	Calculate historical ratios and metrics
	Fill in the assumptions
	Build the income statement
	Build the balance sheet
	Complete supporting schedules
	Complete the cash flow statements
	Complete the I/S and B/S
	Build charts and graphs

Keuntungan mengikuti workshop ini

Memiliki pengetahuan dan keterampilan untuk membangun finansial model dengan pendekatan terstruktur, membuat asumsi dan formula yang diperlukan untuk peramalan bisnis dan menganalisis dampak pada laporan laba rugi, neraca, dan arus kas.
Memiliki pengetahuan dan keterampilan untuk melakukan valuasi bisnis dengan menggunakan teknik DCF, menghitung free cash flow to the firm dan free cash flow to equity, menghitung biaya ekuitas, biaya hutang dan biaya rata-rata modal tertimbang, dan menghitung beta dengan menggunakan excel.
Memiliki pengetahuan dan keterampilan untuk menerapkan analisis skenario dan sensitivitas pada finansial model dan menyajikan hasilnya dengan menggunakan grafik.
Excel template untuk memfasilitasi pembuatan finansial model, valuasi bisnis, analisis skenario dan sensitivitas.