Financial Modeling For Finance or Leasing Company Using Excel and Xcelsius with Sensitivity and Scenario Analysis

DESKRIPSI

Tujuan Pelatihan:

Memperoleh pemahaman dan ketrampilan:

1. Dasar-dasar corporate budgeting.
2. Pengetahuan prinsip dasar financial modeling yang diterapkan pada budget model.
3. Membangun budget model secara bulanan.

Manfaat pelatihan ini adalah:

1. Pelatihan ini sangat berguna untuk membekali peserta agar dapat membangun financial model untuk berbagai produk dari suatu perusahaan pembiayaan (car financing, financial lease, housing loan, factoring, channeling), dengan cara yang jelas (in a transparent way),
2. Peserta akan dapat membuat model yang dapat dimengerti dengan mudah oleh orang lain yang berasal dari berbagai tingkat keahlian,
3. Menampilkan analisa ?what-if? dengan cara yang mudah untuk memperjelas permasalahan dan menyelesaikan masalah, dan
4. Membuat presentasi dan laporan yang menarik dengan mempelajari cara menggunakan SAP Dashboard Design (Xcelsius), menentukan komponen mana yang paling sesuai untuk me-representasikan bermacam-macam informasi yang berbeda, mem-finalisasi dashboard dan mengekspor menjadi file format yang berbeda.

Syllabus :

Day 1 :

Day 1a ? Financial Modeling Fundamental

1. Introduction and Overview
   1. Overview of the Course
   2. Definition of Financial Model
   3. Scope of financial model
   4. Objectives of financial model
2. Financial Modeling Design Principles
   1. Financial Statement Interrelationship
   2. Financial Modeling Design Principles
   3. Design Flow & Model Layout : Input, Working & Output
   4. Financial Model Flow Chart
3. Developing Excel Skills : Powerful Functions Used in Financial Models
   1. Statistical Functions: MAX, MIN
   2. Lookup and Reference Functions: OFFSET, INDEX, MATCH
   3. Date and Time Functions: DATE, DATEDIF
   4. Information Functions: IFERROR, ISNUMBER, ISTEXT
4. Developing Excel Skills : Powerful Functions Used in Financial Models (Continued)
   1. Math Functions : ROUND, ROUNDUP, ROUNDDOWN, MOD
   2. Range Names : Naming one cell, Naming Row, Naming relative reference
   3. Conditional Formatting
   4. Data Validation
   5. Using Form Control : Combo Box, List Box, Spinner, Button
5. What-if Analysis Techniques
   1. Using scenario manager & scenario buttons

Day 1b ? Modeling Mini Financial Model

1. Modeling Mini Financial Model
   1. Pre-Development ? Defining a financial model plan
      • Knowing financial model objective
      • Specification and planning, flowchart and information flow
      • Design and structure ? key steps
   2. Development process
      • Modeling mini balance sheet
      • Balancing the balance sheet
      • Modeling mini income statement
      • Testing and debug
2. Using Datatable
   1. Working with one-variable datatable
   2. Working with two-variable datatable
3. Introduction to VBA (Visual Basic for Application)
   1. Recording Macro for Button
   2. Activating and using Visual Basic Editor
   3. Using VBA codes for automation What-if analysis techniques
   4. Number Fomating Using VBA codes

Day 2 :

Day 2a ? Modeling Powerful and Easy to Use Financial Model

1. Modeling Powerful and Easy to Use Budget Model
   1. Model Layout, Design Principles & Overview of Corporate Budget Model
   2. Modeling Assumption module ? Revenue Drivers
      • Car Financing
      • Financial Lease
      • Installment Credit
      • Housing Loan
      • Factoring
   3. Modeling Sales & Collections module
      • Sales by Type (New or Used Cars)
      • Sales by Tenor (1 to 5 years)
      • Loan Amortization
   4. Modeling Operating Budget ? Financing module:
      • Disbursement
      • Loan Administration
   5. Modeling Operating Budget ? Operating Expenses module
      • Branch Addition
      • Head Count
      • Fixed Assets Management
2. Modeling Powerful and Easy to Use Budget Model (Continued)
   1. Modeling Operating Budget ? Income Statement module
   2. Modeling Financial Budget ? Capital Budget & Cash Budget module
   3. Modeling Financial Budget ? Balance Sheet module
   4. Modeling Cash Flow Statement module
   5. Modeling Free Cash Flow module
   6. Modeling Variance Analysis Chart
   7. Modeling Dynamic Chart
   8. Modeling Sensitivity & Scenario Analysis

Day 2b ? Valuation Modeling

1. Modeling Sensitivity & Scenario Analysis Tool
   1. Scenarios simulation using VBA
   2. Using Data Table & Scenarios for Sensitivity & Scenario Analysis
   3. Modeling Dynamic Data Table for versatile Sensitivity & Scenario Analysis
   4. Modeling Dynamic Chart to visualize the result of Sensitivity & Scenario Analysis.
2. Introduction to Dynamic Business Dashboards
   1. What is a SAP Dashboard Design (Xcelsius)? How does it work?
   2. Sample Crystal Xcelsius ? Different by application
   3. Relationship between Excel & Xcelsius
   4. Supported Excel Functions
   5. Principles of a good design ? best practice
   6. Crystal Xcelsius elements
      • The canvas
      • The component list & categories
      • The component properties (General, Behavior, Appearance)
3. Modeling Dynamic Business Dashboard
   1. Importing underlying spreadsheet
   2. Working with canvas, components and properties
   3. Creating dynamic what-if analysis dashboard (Creating dynamic dashboard for profitability analysis)
   4. Assessing the company profitability
   5. Setting Scenarios
   6. Export interactive business dashboard to Powerpoint, Word dan PDF, for easy distribution to other people

Waktu :

2 hari kerja, 09.00 am ? 05.00 pm, diselingi 2 kali coffee break dan 1 kali istirahat makan siang

Course Leader :

Irwin Ibrahim

Profile detail...