Financial Modeling using Excel & VBA – From Zero To Master

Financial Modeling using Excel & VBA – From Zero To Master
J.W. Marriot Hotel, Mega Kuningan, Jakarta | 20-21 July 2011 |  Rp.4,950,000,./trainee

You will need to put The Golden Rule of Financial Modeling into practice. This will be achieved by hands-on training and guidance from your trainers who will combine both real life and simulated exercise, using Excel and xcelsius as a base for your spreadsheet.

1. Attend this two days intensive training course and enhance your financial modeling skills through:
2. Creating problem solver mindset using conventional and new approaches.
3. Understanding key sensitivity using advanced spreadsheet.
4. Designing a model to maximize flexibility of key variables.
5. Observing and reviewing other people models.
6. Simulation and optimization.

CUDOS approach

The course starts by focusing on the advance use of excel in Modeling. These newly gained skills will be applied in the construction of basic company valuation models, before the incorporation of risk assessment, scenarios, optimization and forecasting into the models. With the help of the course director and a proven step-by-step approach, delegates will then construct models of different types, delegates will then construct models of different types. Delegate will leave this intensive two-days course the ability to effectively apply modeling techniques in Excel in a wide range of practical techniques in Excel in a wide range of practical scenarios.

Workshop Leader

Yohan Setio, CFA

Yohan Setio works as an equity analyst, in a state-owned investment banking firm, covering consumer goods industry since 2008, after previously worked in Deutsche Bank. In addition to day-to-day research report writing, he gained in-depth experience in financial modeling through participation in IPO of one of the largest consumer goods companies and airline companies in Indonesia. He studied accounting at University of Indonesia and graduated cum laude on 2006. Following the completion of CFA exams on 2010, he will continue his study by pursuing MBA at the University of Cambridge this year.

Workshop Outline

Day One (The Golden Rule of Financial Modeling)

Session 1: Introduction to the Golden Rule

1. Philosophy of Financial Modeling
2. Software for financial modeling

• Excel 2007 vs 2003
• Excelcius (SAP Dashboard Design)

1. Data Tables and Sensitivity Analysis, Goal Seek, Scenario Manager, Solver, Regression Analysis
2. Random Number Generator (Monte Carlo)
3. Create Macros for repetitive task
4. Create Functions to help middle and higher level management.

Session 2: Time Value of Money for Decision Maker

1. Short term capital budgeting
2. Long term capital budgeting
3. Decision making criteria for project selection

• Independent vs. Mutually Exclusive
• ROI
• IRR project and IRR equity
• Payback period
• Accounting Rate of Return
• EVA

4. Case study:

• Create EVA function
• Comprehensive case study: project selection with time value of money.

Session 3: Interactive Executive Dashboard for Decision Maker

1. Philosophy of converting numbers (statistics) into visualization
2. The Golden Rule of interactive executive dashboard
3. Reviewing misleading information within dashboard: pie, bar, waterfall, marimekko, speedometer, cascade, box and whisker, etc.
4. Case study:
   1. Create interactive executive dashboard
   2. Comprehensive case study: decision making with executive dashboard

Day Two (Advanced Application of the Golden Rule)

Session 4: Forecasting

1. Forecast vs. projection; AICPA/SAARS definitions and requirements
2. Linear regression, moving average and seasonality
3. Identifying key business drivers & documenting assumptions and uncertainties
4. Income statement projections & projecting revenues based on key drivers
5. Regression analysis for Fixed and Variable Cost Projections–how to determine
6. Expense and revenue drivers
7. Projecting cash flows and borrowing requirements
8. Projecting breakeven with data tables and problem solver
9. Optimal pricing projections with data tables and problem solver
10. Comprehensive case study: pro-forma financial statements

Session 5: Business Valuation

1. Capitalization of earnings
2. Discounted cash flow
3. Sensitivity analysis for growth and discount rates
4. Gordon growth model
5. Option pricing models, including Black-Scholes
6. Price-Earnings multiple
7. Comprehensive case study: merger and acquisition

Bonus session: Tips and Tricks

1. Adding combo box, list box and other menu.
2. Adding input/ output to your model by using menu, validation and comment.
3. Pivot table.
4. Advanced data filter and data validation
5. Useful common functions: YTD, EOMONTH, EDATE, SUMPRODUCTS, SUMIF, VLOOKOUP, HLOOKUP.
6. Retrieving data from a database (local or web database)
7. Other tips and tricks

Financial Modeling Sample:

1. Leasing
2. Financial Statement Analysis
3. Investment Analysis
4. Business Valuation

Target Audience:

1. Analyst and Finance Manager
2. Strategic Planner and General Managers
3. Project Financiers and Lenders
4. Budget Analyst and Financial Planners
5. Engineer and System Managers
6. Risk Manager
7. Corporate Finance and M&A Professionals

Course materials

You receive full course notes, plus a CD full of practical templates, exercises & solutions, reference guides, add-ins and other useful tools that can be used to efficiently build effective and robust financial models.

Certificate of Completion

Upon completion of this course, you will receive a Certificate of Completion, a recognition of your accomplishment, skills and knowledge learned from this course.

Laptops required

You will be required to bring along a laptop computer with Microsoft Excel pre-installed for this course (Ms. Excel 2007 Recommended).

Recent Attendee

King Halim Jewelry – Mall Arthagading -J ICT – COKE – ICBC Bank – SIEMENS – Bank DKI – IDX – Ferrier Hodgson – Sari Husada – GMF AeroAsia – Tiara Marga – TNT – Citra Marga – Panorama Tour – Carlson Wagonlit – Indocater – etc.

Tanggal:

• 20-21 July 2011
• 09.00 – 17.00 WIB

Venue: J.W. Marriot Hotel, Mega Kuningan, Jakarta

Investment:

• Rp.4,950,000,./trainee
• Discounts 10% for more than 1 participants
• (Including: Lunch, Coffee Break, Module, Certificate, CD)

cforms contact form by delicious:days