Search
close menu
Advanced Seminar

Cash Flow Modelling II

This seminar will primarily focus on the methodologies and techniques for designing, implementing, and optimising planning and analysis models using advanced Excel functions.
Duration of seminar

2 days

Seminar location(s)

Frankfurt

Seminar format

In-class Teaching

Language(s)

German

Seminar Fees

from €1,700

Overview

Enhancing Excel Skills: Cash Flow Modelling II Seminar for Real-World Professional Applications

Building upon the foundations laid in the Cash Flow Modelling I seminar, Cash Flow Modelling II offers participants the opportunity to delve deeper and broaden their knowledge and skills in MS Excel modelling. The seminar's primary focus lies in the conception and hands-on implementation of flexible and efficient models tailored for real-world professional applications.

Upcoming Dates
Cash Flow Modelling II

08. 08. 2024 | Frankfurt

Need help with the seminar? Click on the preferred start date to get a detailed overview and don't hesitate to reach out to our study management. We're excited to assist you with any inquiries!

Telephone +49 (0) 6723 9950-30
Key Benefits

Discover the benefits of choosing our institution for your education

University-level Education

The IREBS Executive Education is part of the Faculty of Business, Economics, and Management at the University of Regensburg.

Renowned

You will engage in expert discussions with high-profile and renowned lecturers from academia and industry.

Pioneers

IREBS Executive Education has been in the market for over three decades and has successfully trained over 7,500 professionals.

Prospects

Achieving certification from IREBS is held in high regard by executives and HR managers in the real estate industry.

Seminar Plus

We provide our students with the necessary materials in digital form.

Optimal Learning Environment

With small group sizes, we ensure an optimal learning environment, leading to higher learning success. Therefore, the available seats are limited.

Seminar Structure

Advanced Cash Flow Modelling seminar:
2-day training

The seminar is scheduled for two days, divided into four blocks, and will run from 9:30 a.m. to 5:30 p.m. each day. Prior knowledge (Cash Flow Modelling I) is necessary. Participants will be provided with a laptop for the duration of the seminar.

Through the Cash Flow Modelling Seminar, you will acquire comprehensive knowledge of key financial mathematical concepts and investment evaluation methods. You will learn to apply these methods in Excel and implement practical solutions for your professional endeavours.

Content Overview

Excel in Cash Flow Modelling:
A brief Content Overview

In the cash flow modelling seminar, you will acquire a well founded knowledge of the key financial mathematical concepts and methods of investment calculation. You will learn how to model these methods in Excel and implement them in practical solutions for your everyday work. You are welcome to ask your own questions in advance or during the seminar in order to discuss them in the classroom.

Day 1 (Block 1) Day 1 (Block 2) Day 2 (Block 2) Day 2 (Block 3) Day 2 (Block 4)

Day 1 (Block 1)

Day 1 (Block 1)

Approach to Modelling and Deepening of Necessary Techniques in MS Excel

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

During this session, you will review and enhance your Excel proficiency while gaining a comprehensive insight into the capabilities and constraints of Excel-based approaches to Cash Flow Modelling. Key focuses include model flexibility, efficient implementation, and the performance of the developed solutions.


Excel Refresher and Advanced Techniques

  • Practical implementation of more complex financial mathematical concepts
  • Application of array formulas for multi-step calculations
  • Goal seek and optimization techniques
  • Efficient work in Excel through consistent utilization of available features
    • Enhancing efficiency with color schemes
    • Improving report layouts with standardized designs
    • Defining and setting up standardized color templates and schemes
    • Employing the established schemes
    • Strategies for easy updates
    • Creating and using a corporate template
  • Developing comprehensive and flexible analysis approaches
    • Utilizing the EVA principle in Excel (Input, Processing, Output)
    • Separation of variables, data, formulas, and results
    • Fundamentals of good user guidance
    • Streamlining data entry using controls
  • Identifying required functionalities in MS Excel and comparing their efficiency through practical examples
    • Identifying and making data systematically available
    • Using MTRANS for list transposition
    • Creating dynamic ranges using functions
    • Determining range size
    • Utilizing (dynamic) names
  • Understanding and using matrix formulas
    • Basic concepts and structure of matrix formulas
    • Working with matrices - developing, modifying, and expanding formulas
    • When is the use of matrices appropriate?
    • Applying matrices for comparisons, differences, sums, products, MMULT, and MTRANS
  • Approaches and functions for creating lists with unique entries without gaps
    • Using matrix functions in Excel 2003
    • Utilizing the aggregate function in 2010
  • Enhanced understanding through comments - annotating formulas using the N function


Objectives:

  • What are the fundamental principles of "good" modeling that need to be considered?
  • How can a comprehensive, integrated, and comprehensive model be best constructed?
    • How should it be structured?
    • Which structuring requires specific modeling rules?
    • What restrictions should be taken into account, and how can they be overcome if necessary?
  • What basic techniques are used to structure models based on different levels of consideration?
  • How can the diverse instruments and functionalities of Excel be effectively utilized in cash flow modeling?
  • How can Excel functions and formulas support the development of flexible forecasting and analysis approaches? When and how should matrix formulas be used?
  • How can user input errors be avoided and result analyses simplified?

Day 1 (Block 2)

Day 1 (Block 2)

Cash Flow Modelling for Real Estate: Designing and Building High-Performance Models for Modelling

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

This session will provide participants with a comprehensive understanding of the methods and tools used in Cash Flow Modelling for lease agreements, using practical examples. Building on this knowledge, we will explore concepts and methods for analyzing and evaluating lease comparison conditions. Gradually, the basic lease model will be expanded to address real-world challenges systematically. Additionally, we will delve into perspectives from the landlord's point of view, considering contract-induced and/or tenant turnover-induced costs.

  • Conceptualization and Structure of Forecast Models

  • Practical Construction of a Model for Calculating an Escalation Lease Contract

  • Practical Construction of a Model for Calculating an Index Lease Contract

    • Design as Point or Percentage Variant

    • Determining Adjustment Dates based on Previously Forecasted Index Development

    • Calculation of Passed-On Costs to the Tenant

    • Consideration of Advance Notice Periods


Objective:

Through practical examples, participants will develop a solid understanding of the concepts and functionalities required for modelling a lease agreement in MS Excel. They will then apply these concepts directly to create corresponding models and assess their accuracy and practical applicability using the previously used examples.

Advancement of models for representing the terms of lease agreements

  • Systematic consideration of options:

    • Utilizing the forecast of market rent development as a starting point
    • Implementing the market rent forecast as an index series
    • Modeling market cycles
    • Incorporating rules to account for options
  • Approach for considering subsequent leases:

    • Representing vacancy periods and associated costs
    • Systematically accounting for leasing expenses
  • Methods and tools for modeling and depicting lease agreements:

  • Integration into previously developed models for representing different contract types:
    • Escalation leases
    • Index leases
    • Potentially turnover leases
    • Potentially combined leases
  • Depicting rent adjustment rules

  • Importance of and approach for considering options

  • Representing variable rent ceiling limits (e.g., 20% rule for residential properties)

  • Subsequent leases

  • Implementing "what-if" analyses for rent forecasts

  • Working with scenarios - constructing and implementing the necessary structures.


Objectives

  • How can lease agreements and contract conditions be described in a way that allows for modeling and representation in Excel?
  • What contract conditions are needed to depict the economic consequences effectively?
  • What support do Excel functions and formulas provide in building flexible analysis approaches?
  • What is the significance of risks in rent forecast calculations, and how can they be systematically represented and analyzed?
  • What functionalities does Excel offer in this context, and how can they be strategically utilized?

Day 2 (Block 2)

Day 2 (Block 2)

Project Development and Urban Planning

Time: 9:00 a.m. to 10:30 a.m.

In this session, together we will develop a model for analysing and forecasting data series, such as the CPI (Consumer Price Index), and practically implement it in MS Excel. We will first explore the essential tools for modelling uniform and/or cyclical developments, such as market trends, and then proceed to create a flexible and user-friendly model.


Addition:

  • Fundamentals and construction of forecasting models – trend analysis and extrapolation
  • Regression analysis as the basis for forecasting the development of index series
  • Explanation of the approach and tools for conducting regression analysis:
    • Linear regression and trend function
    • Exponential regression
    • Polynomial regression
  • Presentation of the procedure for forecasting the future development of indices
  • Data sources and acquisition
  • Data handling and processing


Objectives:

  • How can the tools and functionalities of Excel be most effectively utilized in the context of regression analysis?
  • What additional tools are required?

Expanding model functionality through systematic use of User-Defined Functions.

 

Day 2 (Block 3)

Day 2 (Block 3)

Enhancing Model Functionality through Systematic Utilization of User-Defined Functions

Time: 11:00 a.m. to 12:30 p.m.

  • Analysis and optimization of model efficiency:

    • Fundamental rules for constructing efficient models
    • Recognizing, avoiding, and resolving performance bottlenecks
    • Utilizing high-performance formulas
    • Identifying conceptual and/or structural issues through performance analysis -> Measuring performance through macro solutions
  • Basic knowledge and usage of macros/VBA:

    • General structure and utilization
    • Fundamentals of building and using VBA
    • Practical examples of useful macros

 

Day 2 (Block 4)

Day 2 (Block 4)

Applying the Seminar Concepts in Practice

Time: 1:30 p.m. to 3:00 p.m. and 3:30 p.m. to 5:00 p.m.

During the last block, participants will collaboratively implement the seminar contents into a comprehensive, flexible, and user-friendly model. They can either choose from a question and task provided by the facilitator or formulate their own to serve as the basis for the implementation.


Potential Case Study Assignment

The objective is to develop a straightforward, flexible, and reusable calculation model from the tenant's standpoint for evaluating a potential relocation in a major German city. After conducting an initial market analysis and evaluating a longlist, three properties have been shortlisted for further consideration. The primary focus is to compare these options based on their economic feasibility. Participants are required to create a decision proposal along with a brief rationale, while thoroughly documenting the results and their derivation.

In addition to the tenant's perspective, the model should also encompass the landlord's viewpoint to identify any potential negotiation possibilities and critically assess the calculation methods utilized for the tenant's perspective. Furthermore, the analysis should encompass the impact of different financing approaches (leverage) on the landlord's side and be incorporated into the model to ensure a comprehensive evaluation.

To account for the dynamic nature of such evaluations, participants will be introduced to and, to the extent feasible, model various approaches that systematically represent and compare alternative scenarios (e.g., variations in renovation costs, financing terms, and scope of renovations).


Summary

By the end of the seminar, participants will have acquired in-depth knowledge and practical skills in implementing Cash Flow Models. They will be able to design and build their own relevant models, as well as analyze and evaluate existing ones. Additionally, they will proficiently utilize tools and models in this domain, understanding the possibilities and limitations of each approach and explaining them effectively.

Furthermore, participants will develop the ability to identify questions that cannot be adequately addressed through standardized methods. This will enable them to provide clients with optimal advice and, if needed, conceptualize or even implement necessary modifications to existing models as required.

Lecturers

Expertise from academia and industry

Sven-Eric Ropeter-Ahlers
Dr. Sven-Eric Ropeter-Ahlers
Owner of Ropeter-Ahlers Real Estate Consulting & Analysis

Dr. Sven-Eric Ropeter-Ahlers, who holds a degree in business administration, has more than 25 years of experience as a management consultant in the field of real estate investment analysis. Within this framework, he advises companies from the insurance and banking sectors, industry, real estate investors and asset managers, as well as real estate companies organised as medium-sized enterprises.

Dr. Sven-Eric Ropeter-Ahlers has published numerous professional articles and book contributions on the topics of investment appraisal and risk analysis.

Registration

Everything you need to know

Structure & Study Formats
  • Duration: 2 full days (from 9:30 AM to 5:00 PM each day)
  • In-class Teaching at two locations: Frankfurt or Munich
  • Seminar materials in digital form, refreshments during breaks, and lunch snacks
  • A laptop with Microsoft Windows operating system and MS Office 365 will be provided.
Tuition Fees
Seminar fees: €1,850
Reduced seminar fees: €1,700*


* For alumni, members of IMMOEBS e.V., or IREBS Core e.V. and participants of Cash Flow Modelling I

All prices are subject to the legally applicable VAT. Discounts cannot be combined.

Target Group & Admission Requirements

To participate in this seminar, a solid grasp of Cash Flow Modelling is essential. Thus, we highly recommend attending the foundational seminar, Cash Flow Modelling I, beforehand.

The is open to following individuals:

  • Real estate professionals and experts in the real estate industry
  • Project developers and real estate developers
  • Asset managers and property managers
  • Financial experts and investment managers
  • Architects and engineers with a focus on real estate
  • Consultants and analysts in the real estate sector
  • Real estate investors and financiers
  • Students and graduates with an interest in real estate economics and management
Certificate of Completion

Upon completing our seminar successfully, you will be awarded an official certificate of participation, acknowledging your successful attendance and the knowledge and skills you have gained. Our seminars are highly regarded in the real estate industry, earning recognition from industry experts and professionals. Our practical and high-quality training provides you with valuable career opportunities.

Student advisory services
close call +49 6723 9950-30E-mail to irebs@irebs.academy