Financial modeling is a key tool for understanding the numbers behind a financial situation. It is mostly used for projecting cash flows of a company or project. Financial modeling helps in assessing financial projects, credit analysis, and valuing companies. Many financial analysts use Microsoft Excel for modeling due to its flexibility and powerful functions. Models can be simple or very complex. For example, a basic model like the Gordon Growth Model can be built in a few minutes, while complex models for Project Finance may need dozens of spreadsheets and a lot of inputs. In this article, I will discuss financial modeling complexity, provide advice on the right level of complexity for different situations, and explain how to control complexity to avoid common pitfalls.
Financial Models Complexity
The complexity of a financial model depends on its purpose. Here are the main uses for financial models:
- FP&A (Financial Planning and Analysis): Used to forecast budgets for projects or operations within a company. These models often require a detailed breakdown of revenues, costs, and operational metrics to ensure accurate forecasts.
- Valuation: Used to find the value of companies and projects, which helps in making investment decisions. Valuation models typically include discounted cash flow (DCF) analysis, comparable company analysis, and precedent transactions to determine the fair value of an asset. For example, in real estate modeling, valuation might involve calculating the value of a property based on projected rental income and discounting those cash flows to present value.
- Credit Analysis: Used by banks and financial institutions to evaluate the risks of lending to a company or project. These models focus on downside risk, cash flow stability, and the company’s ability to meet debt obligations. In real estate, this might involve modeling the impact of different financing structures on a development project to assess the risk of default.
These uses come with different limitations that affect how complex a model should be:
| Limitation | FP&A | Valuation and Credit Analysis |
|---|---|---|
| Quantity of Data | Lots of granular data | Varies by the company being analyzed or public data sources |
| Quality of Data | High-quality, detailed data | High-quality but limited; projections depend on analyst expertise |
| Time | Usually enough time | Limited time due to covering multiple companies |
FP&A models are often more complex because they have fewer limitations compared to valuation and credit analysis models. The quantity and quality of data available also play a role in determining how detailed the model should be. A good financial model must balance accuracy and ease of use. It should be easy to understand, update, and make changes when needed. Remember, a model that is too complicated can become difficult to manage and prone to errors, which defeats the purpose of having a model in the first place.
Uncertainty and Complexity
The level of uncertainty also affects model complexity. When there is a lot of uncertainty, adding more inputs can actually make things worse. More inputs do not reduce uncertainty but may increase it by adding more potential errors and complications. As Prof. Damodaran from NYU Stern School of Business advises, “When faced with more uncertainty, simplify the model. Focus only on the key inputs. This aligns with Prof. Damodaran’s statement in his blog: “When faced with more uncertainty, strip the model down to only the basic inputs, minimize the complexity and build the simplest model you can. Take out all but the key variables and reduce detail.”
This is especially important when valuing startups or young companies, which often have high uncertainty and limited historical data. In another paper dealing with the valuation of young companies, Prof. Damodaran suggests using a relatively simple model to value startup companies—companies with no sales and a lot of uncertainty. For these companies, using a simpler model is more effective, as it minimizes the impact of uncertain inputs on the overall valuation. For instance, when valuing a young tech startup, it might be better to use revenue multiples rather than attempting to forecast every individual cost item, given the lack of reliable historical data.
Complexity should also be adjusted based on the purpose of the model. For example, a real estate modeling project for a well-established property may allow for detailed cash flow projections, while a new development project with uncertain future cash flows may require a more straightforward approach. The more uncertainty you face, the more you should focus on keeping the model lean and easy to adjust.
Modeling Duration
The time period covered by a model also impacts its complexity. Here are some factors to consider:
- FP&A: Models for financial planning and analysis often need to cover the full lifetime of a project, including operational phases and potential future investments. This helps companies understand the financial needs and expected returns over the long term.
- Credit Analysis: For credit analysis, the model must cover at least until the loan matures, often including projections beyond maturity if refinancing is likely. Lenders need to understand not only if a company can repay the loan but also if it can maintain financial health afterward.
- Valuation: Valuation models typically forecast cash flows for 3 to 10 years, depending on the growth stage of the company. A mature company might need only a few years of detailed projections, while a growth company might require a longer forecast to reach a stable stage. Terminal value calculations, which represent the value of the company beyond the forecast period, are critical in valuation models. Terminal value often makes up a large portion of the overall valuation, so the assumptions used must accurately reflect the company’s long-term potential.
Modeling in Complexity Layers
Building financial models in “complexity layers” is an effective way to handle data and time limitations. The idea is to start with a very simple base model and then gradually add complexity as more data becomes available or as time allows. This approach allows you to develop insights early and refine them over time.
For example, if you are modeling an apparel retail company, you might start with a very basic model that includes total revenues, cost of goods sold, and operating expenses. From there, you can add details, such as breaking down revenues by product category, modeling store openings and closings, and even including seasonal variations. The next layer might involve calculating revenue per square foot and modeling the impact of marketing campaigns on sales.
In real estate modeling, you could start by estimating rental income based on the average market rent for similar properties. The next layer could include details like occupancy rates, lease escalations, and maintenance costs. If the property is under development, you might add layers to model construction costs, financing structures, and potential changes in property value over time.
This step-by-step method provides flexibility and helps ensure the model remains useful throughout the process. Adding complexity is only beneficial when:
- The relevant data is available: If data on store sizes and sales per square foot is not available, adding those elements to the model will not improve accuracy.
- Reliable assumptions can be made: Adding assumptions that cannot be supported with reasonable estimates will only add uncertainty. For example, if you cannot confidently forecast new store performance, it is better to stick with higher-level revenue projections.
- The model remains manageable: A model that becomes too complex may be difficult to update or understand. If you are an equity analyst who needs to respond quickly to new earnings reports, a model that requires hours of updates is not practical, regardless of its theoretical accuracy.
Managing Complexity and Consistency
As models grow in complexity, the chances of mistakes also increase. Maintaining consistency and managing inputs effectively is crucial. Here are some best practices:
- Track inputs systematically: It is a good idea to create a separate spreadsheet that lists all inputs along with their sources and links to where they are used in the model. This makes it easier to update inputs when assumptions change and helps eliminate duplicate inputs that could cause inconsistencies. For example, in real estate modeling, tracking inputs such as rent levels, occupancy, and financing terms in a dedicated sheet ensures that changes in rental income or expenses are accurately reflected throughout the entire model.
- Limit complexity through parameter lists: Before you begin building a model, prepare a list of the key parameters that affect the outcome. Focus on parameters that can be forecasted with a high level of certainty. This will keep the model manageable and help you avoid adding unnecessary detail. For example, in real estate modeling, you could prepare a list of key parameters like rental income, occupancy rates, maintenance costs, and financing terms. Focusing on these core elements helps ensure the model remains useful and easy to manage.
- Use sensitivity analysis for uncertain parameters: For parameters with high uncertainty, like future interest rates or commodity prices, it is better to use sensitivity analysis rather than add many layers of complexity. This approach helps you understand how changes in key variables impact the outcome without making the model overly complicated. For example, in real estate modeling, you can use sensitivity analysis to see how changes in cap rates or interest on debt impact the overall returns on an investment property.
- Link related inputs: Sometimes different inputs are logically dependent on the same factor. For example, the risk-free rate is often linked to the growth rate in a valuation model. Linking these inputs helps ensure that changes in one input are consistently reflected throughout the model.
- Track financial ratios: Financial ratios like return on equity, gross profit margin, and debt coverage ratios can provide a quick check on the consistency of the model. If these ratios show unexpected trends, it is a signal to review the underlying assumptions and calculations.
Summary
Financial modeling can range from very simple to extremely complex. The right level of complexity depends on the data available, the time to build and maintain the model, and the purpose of the model. Increased complexity may lead to better accuracy but also makes the model harder to understand and maintain. In situations with high uncertainty, simpler models are more effective because they focus on the core elements that matter most. The “garbage in, garbage out” principle applies here—a model is only as good as its inputs.
Financial modeling in Excel is a powerful tool for many types of analysis, including real estate modeling and corporate finance. The key to effective financial modeling is to strike the right balance between complexity and simplicity, ensuring that the model is detailed enough to be useful but not so complex that it becomes unwieldy. By using a layered approach to building complexity and focusing on the most important variables, you can create models that provide valuable insights while remaining manageable and easy to use.
Disclaimer: The information provided in this post is for informational purposes only and reflects my personal opinions. It should not be considered as professional financial, legal, or investment advice. Please consult with a professional before making any investment decisions. I am not responsible for any actions taken based on this information. For more details, please refer to our full disclaimer.