Cash Flow Modelling II

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?

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?

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.

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

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.