Cash Flow Modelling I

Time: 9:30 a.m. to 11:00 a.m. and 11:30 a.m. to 1:00 p.m.

You will refresh your knowledge of using MS Excel and gain an initial overview of the possibilities and limitations of MS Excel-based approaches for investment analysis. You will employ tools and functionalities of MS Excel for investment calculations and develop flexible analysis approaches. Additionally, you will learn how to avoid user input errors beforehand and simplify result analysis.

MS Excel Refresher Learning Steps:

1. Working with absolute, relative, and mixed references
2. Utilizing the Formula Wizard
3. Establishing universally applicable and flexible analysis approaches:
   • The "EVA" principle in MS Excel (Entry, Processing, Output)
   • Separating variables, data, formulas, and results
   • Fundamentals of good user guidance
   • Controls for simplifying and validating inputs
4. Theoretical and practical implementation of fundamental financial mathematics concepts:
   • Present Value Factor / Future Value Factor
   • Annuity Present Value Factor / Annuity Future Value Factor
   • Amortization Factor / Annuity Payment Factor
   • Accounting for compounding frequency in compound interest and annuity calculations
   • Accounting for prepayment in compound interest and annuity calculations
5. Considering prepayments and compounding frequency
6. Using named ranges as an alternative to references
7. Applying array formulas to calculate multiple operations
8. Goal Seek and optimization

Time: 2:00 p.m. to 3:30 p.m. and 4:00 p.m. to 5:30 p.m.

In this block, you will learn about the Capital Budgeting Model and gain an understanding of how the included parameters influence each other. Together, we will develop a Discounted Cashflow (DCF) model and then build upon it to calculate the yield using the Internal Rate of Return (IRR) in MS Excel.

During the session, we will start by creating a simple basic model, which will be progressively expanded to approach the "real" world. You will learn how a DCF model is converted into an interest rate or yield (Internal Rate of Return, IRR), and the significance of a yield calculated in this way. Moreover, you will understand the conditions under which these learned methods are appropriately applied and when they are not.

Learning Steps: Introduction to fundamental concepts of investment analysis

• Brief introduction to investment theory
• Capital Budgeting Method
  • Basic methodology
  • Purpose of the discount rate
  • Application for valuation or pricing
• Derivation of the Internal Rate of Return (IRR) method from the Capital Budgeting Method
  • Presentation of the basic methodology
  • Addressing the issues with classical methods

Classical Investment Appraisal Methods

• Presentation of the conceptual structure of a DCF model
• Capital Budgeting Method or DCF Method
  • Basic structure of simple models without considering tax effects
  • Building the basic model
  • Parameter control optimization and user guidance
• Transition from DCF to IRR
  • Presentation of relevant basic considerations
  • Deriving IRR from the presented DCF model
  • Advantages, disadvantages, and limitations of the IRR model
• Influence of various model parameters on calculations and results
• Application areas of the presented model approaches

Time: 9:00 am to 10:30 am

Continuation of Block 2 from the previous day.

Time: 11:00 am to 12:30 pm

Using practical examples, you will gain an overview of the methods and tools used in cash flow modeling. Based on this, concepts and methods for the analysis and evaluation of rental condition comparisons will be developed and presented.

Learning Steps:

• Approaches and instruments for modeling and representing lease agreements
• Working with indices
  • Data acquisition
  • Methods of trend analysis and extrapolation
• Approaches and models for the systematic consideration and evaluation of rental incentives
• Calculation of effective and reference rents
• Building of present value-oriented rental condition comparisons for comparing rental offers
• Cost forecasts

Classical Investment Appraisal Methods:

• Conceptual structure of a DCF model
• Net Present Value (NPV) method or DCF approach
  • Basic structure of simple models without considering tax effects
  • Building the base model
  • Optimization of parameter control and user guidance
• Transition from DCF to Internal Rate of Return (IRR)
  • Presentation of relevant basic considerations
  • Derivation of IRR from the presented DCF model
  • Advantages, disadvantages, and limitations of the IRR model
• Influence of different model parameters on calculations and results
• Application areas of the presented model approaches

Time: 1:30 pm to 3:00 pm and 3:30 pm to 5:00 pm

First, you will jointly develop a Complete Financial Plan (CFP) model using MS-Excel with other participants. Subsequently, you will deepen your understanding of investment calculations based on Complete Financial Plans through practical examples.

Based on one of the previously discussed methods (either DCF, IRR, or CFP), you will develop a model for evaluating investments in the existing portfolio, explicitly considering the specific objective of analyzing the relative advantages of investment, renovation, or conversion alternatives.

Additionally, you will receive a brief overview of the significance of risk consideration and its representation within investment appraisal. Building on one of the previously developed investment appraisal models (also either DCF, IRR, or CFP), you will explore various approaches to risk analysis and put them into practical use.

In this seminar, you will learn:

• How to determine a realistic return for a real estate investment using Complete Financial Plans.
• The impact of different forms of financing on the profitability of investments.
• How to assess investments in the existing portfolio and what peculiarities need to be considered for analysis models.
• The significance of risk assessment in investment appraisal, systematically representing and analyzing them.
• Which functions of MS Excel can be used for these purposes.

Learning Steps:

• CFP Method
  • Basic structure of simple models without considering tax effects
  • Building the base model
  • Modeling cash flows
  • Integration, representation, and analysis of financing effects
  • Optimization of parameter control and user guidance
• Analysis of investments in the existing portfolio
  • Concept and approach
  • Peculiarities compared to "conventional" investment appraisal
• Conceptualization and representation of fundamental instruments for risk analysis
  • Significance and representation of risks or uncertainties in investment appraisal
  • Overview of various methods and their implementation using MS Excel
• Realization of "What-if" analyses
• Working with scenarios