Financial Modelling Using MS Excel and VBA - JEM128

• Anglický název: Financial Modelling Using MS Excel and VBA
• Zajišťuje: Institut ekonomických studií (23-IES)
• Fakulta: Fakulta sociálních věd
• Platnost: od 2020
• Semestr: letní
• E-Kredity: 4
• Způsob provedení zkoušky: letní s.:kombinovaná
• Rozsah, examinace: letní s.:1/1, Zk [HT]
• Počet míst: neurčen / 40 (80)
• Minimální obsazenost: neomezen
• Virtuální mobilita / počet míst: ne
• Stav předmětu: vyučován
• Jazyk výuky: angličtina
• Způsob výuky: prezenční
• Další informace: http://ies.fsv.cuni.cz/cs/syllab/JEM128
• Poznámka: předmět je možno zapsat mimo plán; povolen pro zápis po webu; při zápisu přednost, je-li ve stud. plánu
• Garant: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D.
• Vyučující: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D.; Ing. Saida Teleu, Ph.D.
• Třída: Courses for incoming students
• Neslučitelnost : JEM189
• Je neslučitelnost pro: JEM189

Anotace

čeština

Poslední úprava: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D. (05.11.2019)

This course is aimed to present some financial and business models and to show how they can be developed, solved or simulated using Microsoft Excel and its embedded Visual Basic for Applications (VBA) programming language. After a general short repetition of Excel and basic teaching in VBA, the course covers standard financial models (portfolio models, option pricing, simulation, corporate finance etc.). The aim of the course is to be a hands-on course with little theory where the students implement what they have studied on previous courses. The course should serve to bridge the gap between financial theory and its implementation.

angličtina

Poslední úprava: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D. (20.02.2017)

This course is aimed to present some financial and business models and to show how they can be developed, solved or simulated using Microsoft Excel and its embedded Visual Basic for Applications (VBA) programming language. After a general short repetition of Excel and basic teaching in VBA, the course covers standard financial models (portfolio models, option pricing, simulation, corporate finance etc.). The aim of the course is to be a hands-on course with little theory where the students implement what they have studied on previous courses. The course should serve to bridge the gap between financial theory and its implementation.

Cíl předmětu

čeština

Poslední úprava: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D. (05.11.2019)

This course is aimed to present some financial and business models and to show how they can be developed, solved or simulated using Microsoft Excel and its embedded Visual Basic for Applications (VBA) programming language. 

angličtina

Poslední úprava: SCHNELLEROVA (25.10.2019)

This course is aimed to present some financial and business models and to show how they can be developed, solved or simulated using Microsoft Excel and its embedded Visual Basic for Applications (VBA) programming language. 

Literatura

čeština

Poslední úprava: PhDr. Petr Bednařík, Ph.D. (15.05.2020)

BENNINGA S:. Financial Modeling. 2nd Edition. Cambridge (MA): MIT Press, 2000.
JACKSON M., STAUNTON M.: Advanced modeling in finance using Excel and VBA, New Jersey: Wiley & Sons Ltd, 2001.
WALKENBACH J: Excel 2003 Power Programming with VBA. New Jersey: Wiley Publishing, 2004.
WALKENBACH J: Microsoft Office Excel 2003 Programování ve VBA. Brno: Computer Press, 2006.

angličtina

Poslední úprava: PhDr. Petr Bednařík, Ph.D. (06.06.2020)

BENNINGA S:. Financial Modeling. 2nd Edition. Cambridge (MA): MIT Press, 2000.
JACKSON M., STAUNTON M.: Advanced modeling in finance using Excel and VBA, New Jersey: Wiley & Sons Ltd, 2001.
WALKENBACH J: Excel 2003 Power Programming with VBA. New Jersey: Wiley Publishing, 2004.
WALKENBACH J: Microsoft Office Excel 2003 Programování ve VBA. Brno: Computer Press, 2006.

Požadavky ke zkoušce

čeština

Poslední úprava: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D. (15.03.2021)

Home exercises I - excel based (after 4 weeks of classes, at least 2 weeks DL) - weight 20%

Home exercises II – VBA based (after finishing all classes, at least 2 weeks DL) - weight 30%

Final project (after finishing all classes, DL end-July) - weight 50%

All three requirements can be a teamwork, group can contain up to three members.

Sylabus

angličtina

Poslední úprava: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D. (14.02.2022)

MS Excel
 
 Introduction
 Functions
 Random numbers
 Data tables
 Advanced functions
 Goal Seek/ Solver
 Data Analysis

VBA

 Introduction
 VBA fundamentals
 Sub procedures
 Function procedures
 VBA techniques
 User Form
 Advanced User Form

Basic financial application which will be solved by MS Excel and VBA

Basics finance calculation
Net present Value, compounding interest

Corporate Finance
Cost of Capital
Financial Statement modeling
Valuation

Portfolio Models
Variance-covariance matrix
Betas and SML
Optimalization
Value at Risk

Option Pricing
Binomial option pricing
Black&Sholes

The course will be run fully online in 2021/2022. Please note that only one session will be conducted at 15:30 (no other session at 17:00 will be held). All sessions will be recorded for those who will not be able to attend the course in this time slot.

The sessions will be accessible under the link https://eiopa.webex.com/eiopa/j.php?MTID=mbb51ccd0debcb568e7fd47c48caf4552

český znakový jazyk

Poslední úprava: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D. (14.02.2022)

Lectures / Seminars:

MS Excel
 
    Introduction
    Functions
    Random numbers
    Data tables
    Advanced functions
    Goal Seek/ Solver
    Data Analysis

VBA

     Introduction
     VBA fundamentals
     Sub procedures
     Function procedures
     VBA techniques
     User Form
     Advanced User Form

Basic financial application which will be solved by MS Excel and VBA

Basics finance calculation
Net present Value, compounding interest

Corporate Finance
Cost of Capital
Financial Statement modeling
Valuation

Portfolio Models
Variance-covariance matrix
Betas and SML
Optimalization
Value at Risk

Option Pricing
Binomial option pricing
Black&Sholes

The course will be run fully online in 2021/2022. Please note that only one session will be conducted at 15:30 (no other session at 17:00 will be held). All sessions will be recorded for those who will not be able to attend the course in this time slot.

The sessions will be accessible under the link https://eiopa.webex.com/eiopa/j.php?MTID=mbb51ccd0debcb568e7fd47c48caf4552

Vstupní požadavky

čeština

Poslední úprava: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D. (05.11.2019)

Pre-requisites:
Basic understanding of Finance Theory, especially in the areas of Corporate Finance and Pricing of Financial Securities and Derivatives

angličtina

Poslední úprava: doc. PhDr. Ing. et Ing. Petr Jakubík, Ph.D., Ph.D. (20.02.2017)

Pre-requisites:
Basic understanding of Finance Theory, especially in the areas of Corporate Finance and Pricing of Financial Securities and Derivatives