Financial Modelling with Excel

FIE435 Financial Modelling with Excel

Autumn 2021

Spring 2022
  • Topics

    You just started your first job as a consultant or as a financial analyst for a large international firm.

    After the first few days with the firm you meet your first client together with your boss.

    During the meeting the client shows you a new project and wants to know from your company if the project is feasible and how much time it will take to recover the cost from the investment.

    You have to determine capital investment, forecast income statement and cash flow, calculate net present value, the internal rate of return and the payback period. Your boss is expecting you to come up with a model that answers these questions in order to make a recommendation to the client.

    In the next days your boss asks you to construct an optimal portfolio for a client. You’re going to have to construct the portfolio, explain the to your boss the results and assumptions you made. Based on your model your company is going to make a recommendation to the client.

    Spreadsheets are an essential tool used by banks, consulting firms and companies today.

     

    You, as the analyst have to be:

    1)the finance expert

    2)the spreadsheet specialist

    3)the graphic designer

     

    In this course the students will have to apply their finance knowledge and modeling skills by constructing a wide variety of spreadsheet models and communicating the output in a relevant, comprehensible way

    Main topics:

    Excel tables, matrices, functions

    Present Value, Net Present Value and Internal Rate of Return with Excel

    Data collection and preparation from different sources

    Bonds valuations, Immunizations and the term structure of interest rates

    Efficient portfolios and the CAPM

    Estimating Beta and the Security Market Line and Efficient portfolios with and without constraints in VBA

    Arbitrage Pricing Theory and multi-factor models in VBA

    Event studies

    Option valuations, the binomial model and the Black-Scholes model with VBA

    Monte Carlo Simulations to Simulate Asset Prices with Excel

  • Learning outcome

    Learning outcome:

    • In this course the students will have to apply their finance knowledge and modeling skills by constructing a wide variety of spreadsheet models and communicating the output in a relevant, comprehensible way.

     

    The student at the end of the course will be able to:

    Knowledge:

    • Understand the objective of financial modelling
    • Understand the principles of portfolio management

    Skills:

    • Download financial data from the internet
    • Undertake a range of financial and statistical calculation with Excel
    • Clearly communicate the results of the model
    • Use the results of the model to support decision making
    • Understand how different assumptions impact on the results
    • Better understand in practice the financial theory underlying the models

    General competence:

    • Being able to use Excel to solve practical financial problems

  • Teaching

    The course is taught using a combination of lectures and practical classes.

    Teams of 3 or 4 should be formed by the 6th class and communicated to the instructor.

    In each practical class the students will be given a problem that has to be solved within groups. The problems and the solutions adopted by the different groups will be discussed at the beginning of the next class.

    Students have to bring their own laptop to class with a working version of Excel.

    Due to COVID-19 the lectures will also be live streamed on zoom.

  • Required prerequisites

    None.

  • Requirements for course approval

    None

  • Assessment

    The grade is determined as follows:

    Group problem sets 45%.

    Peer review 5%. Each student will have a total of 100 point for each problem set to assign to his/her teammates with the restriction that no teammate can receive the same score. Points should be allocated proportionally based on the contribution to the team for that assignment. For example, if you think that person A contributed twice as much as person B, person A should get twice the points that person B gets. Scores will be confidential; students will know the total points awarded by their teammates but not the points awarded by a specific teammate.

    1-hour individual digital home exam in English 50%.

    All the three parts of the assessment must be completed in the same semester.

  • Grading Scale

    A-F

  • Computer tools

    None

  • Literature

    Simon Benninga, Financial Modeling (fourth edition), 2014. Additional materials will be provided for some topics.

Overview

ECTS Credits
7.5
Teaching language
English
Semester

Spring. Offered spring 2021.

Course responsible

Assistant Professor Roberto Ricco, Department of Finance, NHH.