case study financial modeling

SAMPLE CASES

Financial modeling world cup – body of knowledge.

Financial Modeling World Cup has regular stages every year, which allows us to provide a wide variety of cases to our participants. We have cases covering all the possible topics that a typical modeler might encounter in his daily work. Occasionally we also offer the so-called “Freestyle” questions where participants are required to model something unusual, solve a problem one can never expect to encounter at work. We expect that the share of Freestyle questions should be about 15-20% during the season while a wide variety of “Regular” cases should comprise the remaining 80-85%. Our plan is to make sure that during the course of a season, every participant gets a chance to expand their knowledge by creating models that they have never done before. At the same time, every participant will also score some easy points when a case is similar to what he/she does on a daily basis. Sample cases are published so that the participants could get a grip on the style of problems and questions. From time to time we will be publishing some of the cases used in the competition. Our participants have full access to the cases from the rounds they have completed. Our participants also get the full solutions models – our goal is to give each contestant a way to learn best modeling practices, especially in the fields they don’t specialize in. VBA knowledge is not needed to successfully participate in the Financial Modeling World Cup.

Trending Courses

Course Categories

Certification Programs

• Free Courses

Financial Modeling Examples

• Free Practice Tests
• On Demand Webinars

Published on :

21 Aug, 2024

Blog Author :

Edited by :

Aaron Crowe

Reviewed by :

Dheeraj Vaidya

Top 9 Examples of Financial Modeling

Financial modeling is used by the financial analysts for several purposes like cost and benefit analysis of a new proposed project or analyzing the impact of change in economic policy on stock’s performance or for the valuation of the business and its comparison with competitors in the market. Some of the examples of financial modeling include three statement modeling, DCF Modeling, Merger Modeling, IPO Modeling, LBO Modeling, Option Pricing Model etc.

Table of contents

#1 - three statement modeling, #2 - discounted cash flow (dcf) modeling, #3 - merger finance modeling, #4 - initial public offering (ipo) model, #5 - leveraged buyout (lbo) modeling, #6 - sum-of-the parts modeling, #7 - consolidation modeling, #8 - budgeting and forecasting modeling, #9 - option pricing model, recommended articles.

In this article, we discuss the top 9 financial modeling examples -

In the 3 statements model , the income statement, balance sheet, and cash flow statement all are linked together with the excel formulae, and this helps in the analysis of historical financial statements and forecasting future statements. Forecasting of financial statements will help financial analysts to understand how the company will perform after taking into consideration a variety of assumptions.

The DCF Valuation modeling is done taking three statement model as a base and is used to calculate the value of the business or the company’s net present value (NPV) of the future cash flow. The DCF modeling takes the projected cash flow from 3 statement model and discounts them to their present value after making necessary adjustments.

This type of modeling is used to analyze the merger and acquisition decision of two companies. A merger happens when two companies come together under mutual agreement, whereas an acquisition happens when one company overtakes others in return for some cash price.

This model looks into the market with the help of a comparable company’s analysis to understand how much the investors will be ready to pay for the company’s stock in an IPO.

A leveraged buyout is the acquisition of one company by another company with the help of borrowed funds. Leveraged buyout modeling helps in the determination of the value that the buyer would be ready to pay and the level of debt that would be needed to be raised. It also calculates the present and future cash flow of the company to be purchased.

This financial model is used to calculate the value of each division in the organization separately to determine its worth if any other company acquires it. The sum of the parts method is calculated through different types of analysis methods like discounted cash flow (DCF) model, asset-based valuation, market value, etc. Each part is then added together to derive the value of the sum of the part (SOTP).

Consolidated modeling is prepared by consolidating all 3 financial statements, i.e. income statement, balance sheet, and cash flow statement of different business units into one.

The financial analysts perform the budget modeling to determine the budget for the cost and income of the company for the upcoming time. Budgets are generally prepared on a monthly or quarterly basis. They depend upon historical income statement for input purposes. Whereas, the financial analyst does forecast modeling to prepare a forecast against the budget. Many times budget and forecasting modeling is done on the single tab together, and other times they are done separately.

The financial analysts use the option pricing model to determine the theoretical value of an option, i.e. the value of an option after taking all the known input under consideration. It helps financial analysts in deciding the fair value of an option. It is a mathematical model prepared based on mathematical formulas.

This has been a guide to top 9 examples of financial modeling including three statement modeling, DCF Modeling, Merger Modeling, LBO Modeling, IPO Modeling, Options Pricing Model etc. You can learn more about finance from the following articles –

• Financial Modeling Test
• Financial Modeling Benefits
• Coursera Financial Model
• Templates on Financial Modeling

Financial modeling case studies: How to learn from real world examples of financial modeling

1. introduction to financial modeling, 2. forecasting revenue for a tech startup, 3. valuation of a real estate investment, 4. analyzing financial statements for a manufacturing company, 5. building a budget for a non-profit organization, 6. creating a financial model for a retail business expansion, 7. evaluating investment opportunities in the stock market, 8. projecting cash flow for a small business, 9. conclusion and key takeaways from financial modeling case studies.

## The Essence of financial modeling

financial modeling is the art and science of creating mathematical representations of financial situations. These models help us analyze, forecast, and make decisions based on data. Here are some key insights from different perspectives:

1. Purpose and Scope :

- Financial models serve various purposes, such as valuation, budgeting, risk assessment, and scenario analysis.

- They can be simple or complex, depending on the problem at hand. For instance:

- A startup might create a basic revenue projection model to attract investors.

- An investment bank might build an intricate discounted cash flow (DCF) model to value a company for an acquisition.

2. components of a Financial model :

- Assumptions : Every model starts with assumptions. These can relate to growth rates, interest rates, inflation, market conditions, and more.

- Data Sources : Reliable data is crucial. Financial statements, historical data, market indices, and economic indicators feed into the model.

- Structure : Models can be spreadsheet-based (Excel, Google Sheets) or coded (Python, R). The structure defines how inputs flow to outputs.

- Formulas and Functions : Formulas represent relationships (e.g., revenue = price × quantity). Functions (e.g., NPV, IRR) perform calculations.

- Sensitivity Analysis : Varying assumptions helps assess model sensitivity and robustness.

3. types of Financial models :

- Valuation Models :

- DCF: Estimates the present value of future cash flows .

- comparable Company analysis (Comps): Compares a company's metrics to peers'.

- Precedent Transactions: Analyzes historical M&A deals.

- Forecasting Models :

- Sales Forecast: Predicts future sales based on historical trends.

- Expense Forecast: Estimates costs (operating, capital, etc.).

- working Capital model : projects short-term liquidity needs.

- Risk Models :

- monte Carlo simulation : Quantifies uncertainty by running thousands of scenarios.

- credit Risk models : assess creditworthiness of borrowers .

- Portfolio Models :

- Markowitz's Efficient Frontier: Optimizes asset allocation.

- black-Litterman model : Combines market views with historical data.

4. Example: Building a DCF Model :

- Imagine valuing a tech startup. Steps include:

1. forecasting Cash flows : project future revenues , expenses, and taxes.

2. Discounting Cash Flows : Apply a discount rate (usually WACC) to calculate present value.

3. Terminal Value : Estimate the value beyond the explicit forecast period.

4. Sum of Present Values : Add up all discounted cash flows .

5. Sensitivity Analysis : Test different growth rates, discount rates, and terminal values.

5. Challenges and Best Practices :

- Garbage In, Garbage Out : Models are only as good as the data and assumptions.

- Overfitting : Avoid making models too complex to fit historical data perfectly.

- Scenario Planning : Consider best-case, worst-case, and base-case scenarios.

- Documentation : Document assumptions, formulas, and methodology.

- Model Auditing : Regularly review and validate models.

In summary, financial modeling empowers decision-makers to navigate uncertainty, allocate resources wisely , and plan for the future. Whether you're valuing a company, managing investments, or optimizing a portfolio, understanding the nuances of financial modeling is a superpower in the financial world.

Remember, the devil is in the details, so let's dive deeper into the intricacies of financial modeling!

# Example Python code snippet for calculating NPV

Def calculate_npv(cash_flows, discount_rate):

Npv = sum([cf / (1 + discount_rate) t for t, cf in enumerate(cash_flows)])

Cash_flows = [100, 150, 200, 250] # Cash flows for four years

Discount_rate = 0.1 # Discount rate (10%)

Result = calculate_npv(cash_flows, discount_rate)

Print(f"NPV: ${result:.

Introduction to Financial Modeling - Financial modeling case studies: How to learn from real world examples of financial modeling

1. The Founder's Optimism: balancing Ambition and realism

- Insight : Founders are often fueled by boundless optimism. They envision exponential growth, rapid user adoption, and hockey-stick revenue curves. While this enthusiasm is essential for entrepreneurial success , it must be tempered with realism.

- Example : Imagine a SaaS startup offering project management software . The founder believes that within a year, they'll have 10,000 paying customers. However, a more grounded approach involves analyzing market size, competitive landscape, and customer acquisition costs . A conservative estimate might project 2,000 customers in the first year.

2. Investor Expectations: Balancing Aggressiveness and Prudence

- Insight : Investors seek high returns and are willing to take calculated risks. They scrutinize revenue forecasts to assess growth potential and valuation.

- Example : A venture capitalist evaluating the same SaaS startup will consider market trends, competitive advantages, and scalability. If the founder's projection aligns with industry norms, it signals alignment. However, overly aggressive forecasts may raise eyebrows.

3. The Analyst's Rigor: Data-Driven Modeling

- Insight : Financial analysts employ data-driven models to forecast revenue. They consider historical performance, seasonality, customer acquisition channels , and pricing strategies.

- Example : The analyst builds a cohort-based model, tracking customer cohorts over time. By analyzing churn rates , expansion revenue, and customer lifetime value, they create a dynamic revenue projection. sensitivity analysis helps account for uncertainties.

4. Challenges and Mitigations: Navigating the Unknown

- Insight : Startups face uncertainties—market shifts, product pivots, and unforeseen events. Revenue forecasts must be adaptable.

- Example : The SaaS startup experiences unexpected churn due to a competitor's aggressive pricing. To mitigate, they diversify customer acquisition channels, invest in customer success, and adjust pricing tiers.

5. Scenario Planning: Preparing for the Unexpected

- Insight : scenario-based forecasting allows for flexibility. Founders and investors explore best-case, worst-case, and base-case scenarios.

- Example : In the worst-case scenario , the startup faces slower adoption and higher churn. They reduce burn rate , extend runway, and explore strategic partnerships . In the best-case scenario, they accelerate growth and raise additional capital .

6. Feedback Loop: Iterative Refinement

- Insight : Revenue forecasts evolve. Regular reviews, actual vs. Projected analyses, and feedback from sales teams refine the model.

- Example : The startup's actual revenue falls short in the first quarter. The founder revisits assumptions, recalibrates growth rates, and adjusts the forecast.

Remember, revenue forecasting isn't a crystal ball; it's a dynamic process. By combining optimism, data, and adaptability, tech startups can chart a course toward sustainable growth. Whether you're a founder dreaming big, an investor assessing risk, or an analyst crunching numbers, revenue forecasting remains at the heart of financial modeling.

Forecasting Revenue for a Tech Startup - Financial modeling case studies: How to learn from real world examples of financial modeling

## The importance of Real estate Valuation

valuing a real estate investment involves estimating its fair market value, which serves as the foundation for investment decisions. Here are some key points to consider:

1. Market Dynamics and Property-Specific Factors:

- real estate markets are influenced by macroeconomic trends, local supply and demand dynamics , interest rates, and investor sentiment.

- Property-specific factors include location, property type (residential, commercial, industrial), condition, and potential for future development.

2. Valuation Approaches:

- Income Approach: This method estimates value based on the property's income-generating potential. It's commonly used for income-producing properties like rental apartments, office buildings, and shopping centers.

- Example: calculating the net operating income (NOI) by subtracting operating expenses from rental income, and then applying a capitalization rate.

- sales Comparison approach : This approach compares the subject property to recently sold comparable properties (comps). It's useful for residential properties.

- Example: If similar houses in the neighborhood sold for $500,000, the subject property's value might be estimated around that range.

- Cost Approach: This method calculates the cost to replace the property with a similar one. It's relevant for unique properties or when comps are scarce.

- Example: Assessing the cost of constructing a similar building, adjusting for depreciation.

3. Discounted Cash Flow (DCF) Analysis:

- Widely used for commercial properties, DCF estimates the present value of future cash flows (rental income, resale proceeds) by discounting them to today's dollars.

- Example: Projecting rental income, expenses, and terminal value over a holding period and discounting them using an appropriate discount rate .

4. Challenges and Risks:

- Illiquidity: real estate investments are less liquid than stocks or bonds.

- Market Volatility: Property values can fluctuate due to economic cycles or unforeseen events.

- Property-Specific Risks: Environmental issues, tenant vacancies, and regulatory changes impact value.

5. Case Study Example: Residential Apartment Building

- Imagine you're evaluating a 10-unit apartment building in a growing neighborhood.

- Gather data on rental income, operating expenses, and recent sales of similar properties.

- Calculate NOI, estimate market cap rate , and apply it to the NOI to get an initial value.

- Adjust for property-specific factors (e.g., deferred maintenance, location).

- Consider potential rental growth and exit strategy (selling after 5 years).

- perform sensitivity analysis to account for different scenarios.

6. Conclusion:

- valuation is both art and science . It requires judgment, research, and a deep understanding of the market .

- Always consider multiple valuation methods and triangulate your estimate.

- Remember that real estate is a long-term investment; focus on fundamentals.

Remember, successful real estate investors combine quantitative analysis with intuition. Whether you're buying your first home or managing a large portfolio, mastering valuation techniques is essential.

Valuation of a Real Estate Investment - Financial modeling case studies: How to learn from real world examples of financial modeling

1. Introduction to Manufacturing Companies:

Manufacturing companies operate in diverse sectors, producing goods ranging from automobiles and electronics to textiles and machinery. Their financial health is influenced by production volumes, supply chain efficiency , raw material costs, and market demand. understanding their financial statements is crucial for investors, creditors, and management.

2. income Statement analysis :

- revenue and Cost of Goods sold (COGS): Manufacturing firms generate revenue from selling products . The top line (revenue) reflects sales, while COGS represents the direct costs incurred to produce those goods (raw materials, labor, and manufacturing overhead).

- gross Profit margin : Calculated as `(Revenue - COGS) / Revenue`, this metric indicates how efficiently the company produces goods. A higher margin suggests better cost control.

- Operating Expenses: These include selling, general, and administrative expenses (SG&A). Analyzing SG&A as a percentage of revenue helps assess operational efficiency .

3. balance Sheet insights :

- Inventory Management: Manufacturing companies often have substantial inventory. A high inventory turnover ratio (Cost of Goods Sold / Average Inventory) indicates efficient inventory management.

- Accounts Receivable (AR) and accounts payable (AP): Timely collection of AR and extended payment terms for AP impact cash flow . A low AR turnover or high days payable outstanding (DPO) may signal issues.

- Fixed Assets: Machinery, plants, and equipment are critical for manufacturing. Analyze the asset turnover ratio (Revenue / Average Fixed Assets) to assess asset utilization.

4. cash Flow statement Considerations:

- Operating Activities: Cash flow from operations reflects the company's ability to generate cash from core business activities. Positive operating cash flow is essential.

- Investing Activities: Capital expenditures (CAPEX) for machinery upgrades or expansion impact cash flow. Evaluate CAPEX relative to revenue.

- Financing Activities: Debt repayments, dividends, and equity issuance affect cash flow . A stable capital structure is desirable.

5. key Ratios and metrics :

- Return on Assets (ROA): ROA measures how efficiently the company uses its assets to generate profits. It's calculated as `Net Income / Average Total Assets`.

- debt-to-Equity ratio : Manufacturing firms often rely on debt financing. A high debt-to-equity ratio may indicate risk.

- Current Ratio: Assess short-term liquidity using `Current Assets / Current Liabilities`. A ratio above 1 indicates a healthy liquidity position.

6. Example Scenario: XYZ Manufacturing Co.

- XYZ produces industrial machinery. Its gross profit margin has improved due to cost-cutting measures .

- However, high inventory levels raise concerns. The company should optimize inventory turnover .

- XYZ's CAPEX for a new production line impacted cash flow but promises future growth.

analyzing financial statements for manufacturing companies requires a holistic approach. investors should consider industry-specific challenges, competitive dynamics, and macroeconomic factors. By combining quantitative analysis with qualitative insights, we gain a comprehensive understanding of a manufacturing firm's financial health. Remember, each case study provides unique lessons, and real-world examples enhance our financial modeling skills .

Analyzing Financial Statements for a Manufacturing Company - Financial modeling case studies: How to learn from real world examples of financial modeling

1. Understanding the Non-Profit Landscape:

Non-profit organizations exist to serve a social or humanitarian purpose rather than generate profits. Their financial health directly impacts their ability to fulfill their mission. Here are some key insights:

- Mission-Driven Goals: Non-profits prioritize their mission above all else. Their budgets should align with achieving that mission.

- diverse Funding sources : Non-profits rely on donations, grants, sponsorships, and program fees. Each funding source has its own requirements and restrictions.

- Transparency and Accountability: Non-profits must be transparent about their financials to maintain trust with donors and stakeholders.

2. Budget Components:

- Program Expenses: These include costs related to the organization's core programs or services. For example, if a non-profit runs an after-school tutoring program, expenses might include salaries for tutors, educational materials, and facility costs.

- Administrative Expenses: These cover overhead costs like rent, utilities, office supplies, and salaries of administrative staff.

- Fundraising Expenses: Non-profits need funds to sustain their operations. Fundraising expenses include marketing, events, and donor management.

- Capital Expenses: These are one-time investments (e.g., purchasing equipment or renovating facilities).

3. Budgeting Process:

- Start with the Mission: Understand the non-profit's mission and strategic goals. How will the budget support these objectives?

- Historical Data: Analyze past financials to identify trends and patterns . Consider seasonality and any major changes.

- Zero-Based Budgeting: Unlike for-profit businesses, non-profits often use zero-based budgeting. Each expense must be justified from scratch.

- Involving Stakeholders: Engage board members, staff, and volunteers in the budgeting process. Their insights are invaluable.

4. budgeting Challenges and solutions :

- Volatility of Funding: Non-profits face uncertainty due to fluctuating donations and grants. Solution: diversify funding sources and build reserves.

- Restricted vs. Unrestricted Funds: Some donations come with restrictions (e.g., for a specific program). Balancing restricted and unrestricted funds is crucial.

- Overhead Myth: Donors sometimes expect non-profits to have minimal overhead. Educate them about the importance of investing in infrastructure.

5. Example Scenario: XYZ Education Foundation:

- Mission: providing quality education to underprivileged children.

- Budget Highlights:

- Program Expenses: $300,000 (teacher salaries, textbooks, classroom materials)

- Administrative Expenses: $50,000 (office rent, utilities, admin staff)

- Fundraising Expenses: $20,000 (annual gala, donor outreach)

- Capital Expenses: $40,000 (new computers for classrooms)

- Challenges: Seasonal fluctuations in donations; need to balance program impact with administrative efficiency.

Remember, building a budget for a non-profit involves empathy, creativity, and adaptability. It's not just about numbers; it's about making a difference in the lives of others.

Building a Budget for a Non Profit Organization - Financial modeling case studies: How to learn from real world examples of financial modeling

1. The Decision to Expand: A Strategic Perspective

Expanding a retail business is a significant decision that requires careful consideration from multiple angles. Here are insights from different viewpoints:

- Business Strategy : Before diving into financial modeling, the retail management team must evaluate the strategic rationale for expansion. Questions to ponder include:

- Market Opportunity : Is there unmet demand in the target market? What are the growth prospects?

- Competitive Landscape : How intense is the competition? Are there barriers to entry?

- Brand Positioning : Will the expansion align with the existing brand image?

- Risk Assessment : What are the risks associated with expansion (e.g., cannibalization of existing stores, operational challenges)?

- Financial Perspective : Financial modeling comes into play once the strategic decision is made. Here's how it unfolds:

2. building the Financial model : Key Components

A well-constructed financial model for retail expansion includes the following components:

- Sales Forecasting :

- Historical Data : Analyze historical sales data to identify trends and seasonality.

- Market Research : Estimate future sales based on market size, demographics, and consumer behavior.

- Assumptions : Incorporate assumptions about foot traffic, conversion rates, and average transaction value.

- Example : Suppose a clothing retailer plans to open a new store in a bustling shopping mall. The model predicts sales based on foot traffic patterns and the store's product mix.

- Operating Expenses :

- Fixed Costs : Include rent, salaries, utilities, and other fixed expenses.

- Variable Costs : Consider inventory costs, marketing expenses, and commissions.

- Growth Factors : Adjust expenses for the expansion (e.g., additional staff, marketing campaigns).

- Example : The model accounts for increased rent and additional staffing costs for the new store.

- Capital Expenditures (CapEx) :

- Store Build-Out : Estimate costs for leasehold improvements, fixtures, and signage.

- Equipment : Include expenses for cash registers, shelving, and display units.

- Example : The model allocates funds for renovating the new store space and purchasing necessary equipment.

- cash Flow and Working capital :

- Cash Inflows : Consider sales receipts, loans, and equity injections.

- Cash Outflows : Account for expenses, loan repayments, and inventory purchases.

- Working Capital : Monitor inventory turnover, receivables, and payables.

- Example : The model ensures sufficient working capital to manage day-to-day operations .

3. sensitivity Analysis and Scenario planning

Financial models are inherently uncertain. Therefore, it's crucial to perform sensitivity analysis and scenario planning :

- Best Case : Optimistic assumptions (e.g., higher sales growth).

- Worst Case : Pessimistic scenarios (e.g., lower foot traffic).

- Base Case : Realistic projections.

- Example : The model assesses the impact of varying sales growth rates on profitability.

4. Decision metrics and Investment appraisal

Finally, evaluate the expansion using relevant metrics:

- Net Present Value (NPV) : Is the project financially viable?

- internal Rate of return (IRR) : What return does the investment offer?

- Payback Period : How quickly will the initial investment be recovered?

- Example : The model calculates NPV and IRR to guide the decision-makers .

In summary, creating a robust financial model for retail expansion involves strategic alignment, meticulous data analysis, and thoughtful assumptions. By considering various perspectives and using real-world examples, businesses can make informed decisions that drive growth.

*(Note: The above content is and creativity. For specific case studies or real-world examples , additional research would be necessary.

Get your startup off the ground with startup loans

FasterCapital helps you secure different types of loan funding that fit your early-stage startup's needs and connects you with lenders!

## Understanding the Context

Before we dive into the specifics, let's set the stage. Imagine you're an individual investor looking to allocate your capital wisely in the stock market. You've done your research, identified potential investment opportunities , and now you need to make informed decisions. Here's where financial modeling comes into play.

### Insights from Different Perspectives

1. Fundamental Analysis vs. Technical Analysis:

- Fundamental analysis focuses on understanding a company's intrinsic value by examining its financial statements , industry position, management quality, and growth prospects. It involves metrics like price-to-earnings (P/E) ratio, earnings per share (EPS), and book value.

- Technical analysis , on the other hand, relies on historical price and volume data . Traders use charts, patterns, and indicators to predict future price movements . While fundamental analysis looks at the "what" (company performance), technical analysis focuses on the "when" (timing of trades).

2. Risk and Return Trade-off:

- Investors face a perpetual trade-off between risk and return . high-risk investments (e.g., small-cap stocks , emerging markets) tend to offer higher potential returns, but they also come with greater volatility.

- low-risk investments (e.g., blue-chip stocks , government bonds) provide stability but may yield lower returns. balancing risk and return is crucial.

3. Valuation Methods:

- Discounted Cash Flow (DCF) : This method estimates the present value of a company's future cash flows. It considers factors like growth rate, discount rate, and terminal value.

- Comparable Company Analysis (Comps) : Compare a company's valuation metrics (P/E, EV/EBITDA) to those of similar publicly traded companies .

- precedent Transactions analysis : Analyze historical M&A deals to assess valuation multiples.

4. Qualitative Factors:

- Beyond numbers, consider qualitative aspects. Is the company in a growing industry? Does it have a competitive advantage (e.g., patents, brand loyalty)?

- Management quality, corporate governance, and industry trends matter. For example, a tech company with visionary leadership may outperform its peers.

### In-Depth Exploration

Let's break down the evaluation process further:

1. Company Research and Industry Analysis :

- Understand the company's business model, revenue sources, and competitive landscape.

- Investigate industry trends, regulatory changes, and disruptive technologies.

2. financial Statement analysis :

- Scrutinize the income statement, balance sheet, and cash flow statement.

- calculate financial ratios (e.g., debt-to-equity, current ratio) to assess financial health.

3. Forecasting Future Performance :

- Use historical data to project future revenue , expenses, and earnings.

- Sensitivity analysis helps account for uncertainties.

4. Valuation Techniques :

- Apply DCF by estimating future cash flows and discounting them.

- Compare valuation multiples (P/E, P/B) with industry peers.

5. Risk Assessment :

- Identify risks (market risk, business risk, liquidity risk).

- Diversify your portfolio to mitigate specific risks.

### Examples

- Company X : A tech startup with disruptive AI technology. high growth potential but lacks profitability. Valuation based on expected future cash flows.

- Company Y : A stable utility company. Low growth but consistent dividends. Valuation using P/E ratio compared to industry average.

Remember, investing is both science and art. Quantitative models provide a framework, but intuition and judgment play a crucial role . evaluate investment opportunities holistically, considering all available information.

Happy investing!

Evaluating Investment Opportunities in the Stock Market - Financial modeling case studies: How to learn from real world examples of financial modeling

## understanding the Importance of Cash flow Projections

cash flow projections provide a forward-looking view of how money moves in and out of a business. Here are some key points to consider:

1. Liquidity Assessment : cash flow projections allow business owners and managers to assess the company's liquidity. By forecasting cash inflows (such as sales revenue, loans, or investments) and outflows (such as operating expenses, loan repayments, and capital expenditures), they can identify potential cash shortages or surpluses.

2. decision-Making tool : Entrepreneurs and financial analysts use cash flow projections to make critical decisions. For instance:

- When should the company invest in new equipment or expand its operations?

- Is the business generating enough cash to cover its obligations?

- Should the company seek external financing or rely on internal resources?

3. Scenario Analysis : Cash flow projections allow for scenario analysis. By adjusting assumptions (e.g., sales growth rate , payment terms, or inventory turnover), businesses can explore different outcomes. For example:

- What if sales increase by 20%? How does that impact cash flow?

- What if a major customer delays payment? How does it affect liquidity?

## Components of Cash Flow Projections

Let's break down the components of a typical cash flow projection :

1. Operating Activities :

- Cash Inflows : These include cash sales, collections from customers, and interest received.

- Cash Outflows : Operating expenses (salaries, rent, utilities), payments to suppliers, and taxes fall under this category.

2. Investing Activities :

- Cash Inflows : proceeds from asset sales (e.g., selling equipment or property).

- Cash Outflows : Capital expenditures (e.g., purchasing new machinery or renovating facilities).

3. Financing Activities :

- Cash Inflows : Borrowings (loans, lines of credit) and equity financing (issuing shares).

- Cash Outflows : Repaying debt and paying dividends to shareholders.

## Example Scenario

Consider a small retail business, "GreenLeaf Boutique," which sells organic skincare products. Here's a simplified cash flow projection for the next quarter:

- Cash Inflows :

- Sales revenue from in-store and online sales: $50,000

- Collections from credit sales: $10,000

- Cash Outflows :

- Rent: $5,000

- Salaries and wages: $15,000

- Inventory purchases: $20,000

- None (no asset sales)

- Purchasing new display shelves: $2,000

- None (no new loans or equity financing)

- Repaying a short-term loan : $3,000

## Conclusion

Cash flow projections are dynamic tools that evolve as the business environment changes. Regularly updating these projections helps businesses stay agile and make informed decisions. Whether you're a startup founder, a financial analyst, or an investor, understanding cash flow is crucial for sustainable growth. Remember that accurate projections require thoughtful assumptions and continuous monitoring.

Projecting Cash Flow for a Small Business - Financial modeling case studies: How to learn from real world examples of financial modeling

Financial modeling is both an art and a science . It involves constructing mathematical representations of financial situations to aid decision-making , risk assessment, and strategic planning. As we wrap up our exploration of financial modeling case studies, several critical takeaways emerge:

1. Holistic Approach to Modeling:

- effective financial models consider multiple dimensions, including revenue, costs, capital structure, and market dynamics. A holistic approach ensures that no critical aspect is overlooked.

- Example: When building a valuation model for a tech startup , we must account for growth rates, customer acquisition costs, churn rates, and competitive pressures.

2. sensitivity Analysis matters :

- Financial models are inherently uncertain due to variables like interest rates, exchange rates, and market volatility. Sensitivity analysis helps quantify the impact of these uncertainties.

- Example: In a project finance model for a renewable energy plant, sensitivity analysis reveals how changes in electricity prices or construction costs affect project viability.

3. Scenario Planning and Stress Testing:

- real-world scenarios rarely unfold as predicted. Financial models should incorporate scenario planning and stress testing to assess resilience .

- Example: A bank's credit risk model should simulate adverse economic scenarios (e.g., recession) to evaluate loan portfolio performance .

4. Assumptions Drive Outcomes:

- Every financial model relies on assumptions. Scrutinize these assumptions rigorously, as they significantly influence results.

- Example: In a discounted cash flow (DCF) model, the assumed discount rate profoundly impacts the calculated present value of future cash flows.

5. Model Simplicity vs. Complexity:

- Striking the right balance between simplicity and complexity is crucial. Overly complex models may be unwieldy, while overly simplistic ones may miss essential nuances.

- Example: A startup's revenue projection model can start with straightforward assumptions (e.g., linear growth) and gradually incorporate more sophisticated factors (seasonality, customer cohorts).

6. Model Validation and Backtesting:

- validate financial models against historical data or benchmark against industry standards . Backtesting ensures that models perform well in practice.

- Example: A quantitative trading model should be backtested using historical stock price data to assess its predictive power.

7. Communication and Visualization:

- Clear communication of model results is essential. Visualizations (charts, graphs) enhance understanding.

- Example: When presenting a budget model to stakeholders , use visual aids to highlight key drivers (revenue, expenses, margins).

8. Ethics and Assumptions:

- Models can inadvertently perpetuate biases or unethical practices. Be aware of assumptions related to social impact, environmental sustainability, and fairness.

- Example: A climate change risk model should consider ethical implications and potential harm caused by certain investments.

9. Iterative Refinement:

- Financial models are not static; they evolve with new data and insights. Regularly revisit and refine your models.

- Example: A real estate development model should adapt to changing market conditions, interest rates, and construction costs.

10. Learning from Failures:

- Even flawed models provide valuable lessons. Analyze failures, understand their root causes, and improve.

- Example: A bankruptcy prediction model that failed to predict a company's collapse teaches us about hidden risks and data limitations.

financial modeling is a continuous learning process. By studying real-world case studies , we gain practical wisdom that transcends textbooks. Remember that no model is perfect, but a well-constructed one equips decision-makers with better tools for navigating complex financial landscapes.

Conclusion and Key Takeaways from Financial Modeling Case Studies - Financial modeling case studies: How to learn from real world examples of financial modeling

Read Other Blogs

Female entrepreneurs face many challenges and barriers in their entrepreneurial journey, such as...

Dancing is not only a form of art and expression, but also a powerful skill that can benefit...

Email marketing has become an integral part of B2B advertising campaigns, revolutionizing the way...

Mobile marketing has become an essential strategy for businesses of all sizes and industries. As...

Autism, a complex neurodevelopmental disorder, has garnered increased attention in recent years. As...

In the dynamic landscape of entrepreneurship, the ability to anticipate and adapt to market trends...

Art is not only a form of expression, but also a source of innovation, inspiration, and value...

Conversion segmentation is the process of dividing your website visitors into different groups...

Debt is a financial instrument that can be both a boon and a bane. It allows individuals and...

• Recently Active
• Top Discussions
• Best Content

By Industry

• Investment Banking
• Private Equity
• Hedge Funds
• Real Estate
• Venture Capital
• Asset Management
• Equity Research
• Investing, Markets Forum
• Business School
• Fashion Advice
• Financial Modeling Resources

3 Statement Model

A dynamically integrated financial model developed by linking together a company’s three primary statements.

Matthew started his finance career working as an investment banking analyst for Falcon Capital Partners, a healthcare IT boutique, before moving on to  work for Raymond James  Financial, Inc in their specialty finance coverage group in Atlanta. Matthew then started in a role in corporate development at Babcock & Wilcox before moving to a corporate development associate role with Caesars Entertainment Corporation where he currently is. Matthew provides support to Caesars'  M&A  processes including evaluating inbound teasers/ CIMs  to identify possible acquisition targets, due diligence, constructing  financial models , corporate valuation, and interacting with potential acquisition targets.

Matthew has a Bachelor of Science in Accounting and Business Administration and a Bachelor of Arts in German from University of North Carolina.

Prior to becoming our CEO & Founder at Wall Street Oasis, Patrick spent three years as a Private Equity  Associate for Tailwind Capital  in New York and two years as an Investment Banking Analyst at Rothschild.

Patrick has an  MBA  in Entrepreneurial Management from The Wharton School and a BA in Economics from Williams College.

• What Is A Three-Statement Model?

3 Core Elements Of A Three-Statement Finance Model

• Understanding A Three-Statement Model
• How To Build A Three-Statement Model?

Free WSO Financial Modeling Templates

What Is a Three-Statement Model?

A three-statement model is a dynamically integrated financial model developed by linking together a company’s three primary statements. This is one of the most important models as it serves as a base for other complex models, such as the Leveraged Buyout (LBO) Model or the Discounted Cash Flow (DCF) Model .

It uses the numbers from the company’s historical financial statements as base inputs, makes some assumptions about the company’s future operations, and forecasts the figures in the three statements based on these inputs and assumptions.

This model projects the company’s financial health if certain decisions are made or if certain assumptions materialize in the future.

In this article, we will explain the three statements, what a three-statement model is, and how to build one.

Key Takeaways

• A three-statement model is a comprehensive financial modeling tool that integrates a company's three key financial statements: the income statement, balance sheet, and cash flow statement.
• The primary purpose of a three-statement model is to provide a dynamic and interconnected financial forecast that helps in decision-making, strategic planning, and valuation. It is crucial for investment analysis, budgeting, and corporate finance activities.
• The model relies on various assumptions and inputs, such as revenue growth rates, expense margins, working capital changes, capital expenditures, and financing activities.
• Changes in one statement automatically reflect in the other statements. For example, net income from the income statement affects the equity section of the balance sheet and the starting point for the cash flow statement.

The three financial statements are the income statement , the balance sheet , and the cash flow statement . The information in each is linked to the information in the other two statements.

These three statements are interconnected, and changes in one can affect the others. Together, they provide a comprehensive view of a company's financial health and performance.

The three statements are vital to gaining a complete understanding of a company’s performance. First, the income statement provides insight into income and expenses. The balance sheet focuses on managing capital. Finally, the cash flow statement illustrates how cash is generated and invested.

The top-performing companies are efficient in all three components of the three statements—their operations, capital allocation, and cash management. Efficiency in each of these areas is significant due to the interconnectedness of the three statements. 

Data from the financial statements is used to conduct further analyses and create forecasts to help make decisions for the future. Users may also create pro forma financial statements based on the analyses to see how various choices can affect the financial statements.

Income Statement

The income statement presents a company's earnings and profitability for a given period, such as a year or a quarter. It is also known as a “statement of operations,” “profit and loss account,” or “P&L account.”

It is generally prepared using the accrual system of accounting. It starts with revenue in the first line and, after deducting various direct and indirect expenses, arrives at the company’s net income. It is often the first place an analyst or investor looks to gauge a business's performance. 

Balance Sheet

Also known as the “statement of financial position,” the company’s balance sheet illustrates its financial position at a particular point in time. Unlike the income statement, it is not drawn for a period.

Instead, it shows the company’s sources of funds and its utilization of those funds at a particular point in time. The total funds raised from various sources (i.e., capital and liabilities) must always match the utilization of those funds (i.e., assets).

It displays the account balances of capital, liabilities, and assets at the end of a period so users can assess the changes in those balances against previous periods. The net earnings (loss) from the income statement are added in (subtracted from) retained earnings , which is part of equity/capital.

Cash Flow Statement

It is also known as a “statement of cash flows” or “funds flow statement.” Like the income statement, the cash flow statement is prepared for a period. However, unlike the income statement, it is prepared using the cash system of accounting .

Since the income statement is prepared using the accrual system , it does not tell us how the cash moves in or out of the business.

The purpose of the cash flow statement is to tell us about the inflows and outflows of cash over a period. It breaks down the inflows and outflows into three categories: cash flow from operating activities , investing activities , and financing activities .

The net cash flows for the period must equal the difference in the opening and the closing cash balances.

Understanding a three-statement model

A three-statement model takes a company’s financial statements – the balance sheet, the income statement, and the cash flow statement – and combines them into a single dynamically linked financial model .

Its purpose is to project what the financial statements may look like if the company makes certain decisions, given certain assumptions. Since the statements are dynamically linked in a 3-statement model, changes in one statement are automatically reflected in the other two statements.

It is the base on which other more complex financial models are constructed, such as discounted cash flow (DCF) models, leveraged buyout models, and merger models, among numerous others.

Most of what might seem complicated in a three-statement model is basic mathematics. Building and understanding the model becomes easier if the model developers and users are familiar with the fundamental relationships among the three statements.

Moreover, while simpler financial models use only one of these statements (the income statement or the cash flow statement), they often fail to show the entire picture.

The main benefit of using as comprehensive a financial model as the three-statement model is that users can use tools like what-if analysis and scenario analysis and get a bird’s eye view of how various decisions can affect the financial statements. This is crucial to making informed decisions.

The following video gives a brief overview of why the three statements are so important and why building financial models is a core skill in any high-paying job in finance.

How to build a three-statement model?

To put together a three-statement financial model, we begin with the income statement and the balance sheet. First, we start with the actual numbers from the previous period. Then, we move on to building forecasts based on some calculated model drivers (assumptions).

After building a forecasted income statement and a balance sheet, we create the cash flow statement. After these steps, we work towards linking the three statements. 

The steps to build a three-statement model are listed in detail below. Although you will be all set to construct a three-statement model with these steps, we firmly believe that a more hands-on approach will help you better understand the topic.

Hence, our finance experts have created a three-statement model template for you to experiment with.

Download WSO’s free three-statement model template below, along with other financial modeling templates! This template allows you to create your own 3 statement model for a company – specifically, the balance sheet, income statement, and statement of cash flows.

The template is plug-and-play, and you can enter your numbers or formulas to auto-populate output numbers. The template also includes other tabs for other elements of a financial model.

Sign up to receive a FREE swipe file containing a collection of quality financial modeling templates to help your finance skills and prepare for interviews.

Here, we explain the steps to building a three-statement model. Before starting, please ensure that the Iterations setting is disabled in Excel. This is to deal with the unavoidable circularity (when the output of a computation is also an input for it) in the model.

On the Windows version of Excel, users can go to File > Options > Formulas and deselect the “Enable iterative calculation” checkbox while Mac users can go to Preferences > Calculation and then disable the “Use iterative calculation” option.

Please check out this article by Microsoft on removing or allowing a circular reference .

1. Input Historical Data

First and foremost, input the actual numbers for the statement of operations and the statement of financial position. This task becomes easier if the data can be downloaded or copied from another source. Please note the tips below, which can make the process easier.

• Some formatting is highly recommended to ensure that you follow the best practices for financial modeling. It makes the data easy to follow for our eyes.
• In WSO’s guide to the best practices, we recommend following the widely used color palette: blue for hard-coded inputs and assumptions, black for formulas and referencing in the same sheet, green for formulas and referencing to other sheets, and red for external links to other files. 
• Use comments where necessary. They are generally inserted in the cell to the side of the relevant line item and are italicized.
• Use shortcuts if possible. One of the most used functions is Sum. Users can press “Alt + =” right under a list of numbers to calculate their sum.
• Ensure that the input is correctly entered. Users can run “balance checks” to verify the net income figure and whether the balance sheet tallies. Remember the fundamental equation: Equity (Capital) + Liabilities = Assets.

With the advent of Python and other programming languages in financial modeling, the use of Stocks APIs to retrieve data quickly is becoming increasingly common.  

2. Analyze Historical Data

Historical data is analyzed by evaluating trends, computing ratios, and statistical information. Model drivers are based on the results of these analyses. Generally used metrics include:

• Year-on-year (YoY) growth rates for revenue, gross profit , operating profit or earnings before interest and taxes (EBIT) , and net profit.
• Margins and ratios that affect them, like direct costs, financing costs, and non-cash expenses ( depreciation and amortization ).
• Balance sheet ratios like current ratio , receivables outstanding, inventory turnover, accounts payables days , operating cycle, and cash conversion cycle . In addition, users may also calculate ratios related to the company’s capital structure .

3. Determining Model Drivers

Model drivers (assumptions) guide the forecasts further. They are established on the results of the analyses of the historical data.

For example, users may use the ratios calculated in the previous step or assume how they might change in the coming years. Here are a few examples of critical assumptions that are generally used:

• Revenue growth rate: If a company’s revenue has been growing at a high rate in recent years, it may grow at declining rates going forward due to increased competition.
• Costs and margins:  It is reasonable to assume that a company will achieve economies of scale as it grows, so direct costs might decline, leading to improved gross margins. In addition, with increased revenues and reduced direct costs, the company may be able to allocate an increasing sum for research and development (R&D) expenses each year.
• Other indirect expenses may be taken as a percentage of sales. Although the capital expenditure ( CapEx ) requirements are usually forecasted using complex calculations, users may assume a fixed percentage of sales as a simple estimate. Unless changes in tax law are anticipated, historical effective tax rates are typically expected to apply in the future.

4. Projecting Income Statement

Once the inputs are in place and assumptions are determined, users have all they need to begin forecasting. The entire forecasting exercise starts with the income statement , starting from the sales and down to the EBITDA .

Although we have the inputs and the assumptions at this stage, building the income statement still requires supporting schedules for line items such as depreciation, taxes, and interest expense .

However, net financing costs (interest expense) are not linked to the income statement at this stage but rather at the end. It is because financing costs are connected to the other two statements, so incorporating them in the income statement at this stage is bound to produce circularity in the model. 

5. Forecasting Capital Assets and Financing Activity

Next, users must build supporting schedules to forecast metrics related to capital assets.

The closing balance for capital assets can be calculated by a simple formula:

Opening Balance + Capital Expenditures—Depreciation

There are multiple ways to account for depreciation, i.e., straight-line method, declining balance method, etc.

Users must also build the debt schedule to determine financing cost payments and principal repayments.

We can use this simple formula to arrive at the closing figures:

Opening balance + (-) increases (decreases) in the principal.

The interest may be calculated in various ways, i.e., based on the closing balance of debt, the opening balance, or an average of the two. Using an average is ideal, as there might be principal repayments throughout the year.

For a more intensive calculation, users may build a separate supporting schedule for financing costs altogether.

6. Projecting Balance Sheet

The balance sheet is slightly tougher to build. It can be built without cash, equity, and debt at this stage. These line items will be derived from the cash flow statement later.

For other items, average ratios from recent years are used (with or without adjustments based on assumptions) to determine closing balances during forecasting. These ratios may include days of accounts payable , inventory days, accounts receivable days , etc.

For instance, days payable may be computed for each of the recent years as average accounts payable ÷ cost of goods sold ( COGS ), which can be used to calculate the average of days accounts payable for the recent years. This number may be adjusted before it is used in the forecasted statements if necessary.

For example, to predict accounts payables for the coming years, use this formula: (adjusted) days accounts payable x expected COGS ÷ 365 days. Here are a few tips to help users at this stage.

• Unless users are expecting M&A transactions, goodwill should be unchanged going forward.
• Determining the assumptions first makes building the model and the error-checking process easy.
• The Trace Precedents tool in Excel can be beneficial in checking whether the formulas have been entered using the correct dependencies.

7. Building Cash Flow Statement

With the partially completed balance sheet and income statement, users can move on to creating the cash flow statement. A cash flow statement has three sections, each of which must be completed by linking the line items to the ones already calculated in the model.

We start with the period's net earnings and make all the adjustments necessary to convert them from the accrual system to the cash system of accounting to determine the cash flows from operating activities. We also adjust for items that might belong to investing or financing activities.

Here are some critical adjustments required to net earnings to ascertain the cash flows from operating, investing, and financing activities.

• Depreciation and amortization are non-cash expenses deducted from income to calculate net earnings. Therefore, these and other non-cash costs must be added back to the net income.
• An increase in current assets implies cash utilization, which needs to be deducted from operating activities. Conversely, a decrease in current assets means an increase in liquidity as the cash is no longer tied up in those assets. The opposite applies to current liabilities – an increase is due to not using up cash to pay them off, while a decrease is due to utilizing cash to pay them.
• Debt-related items like issuances and repayments can be derived from the debt schedule . While preparing the balance sheet, expected stock issuances and buybacks must also have been factored in beforehand. These are financing activities and will not affect cash flows from operating and investing activities.
• The net cash flows for the period are calculated as the sum of cash flows from operating, investing, and financing activities.

At this stage, the users will have completed projecting the three financial statements .

8. Linking The Statements

This is the concluding stage of building the three-statement model. A few items in the balance sheet were intentionally left out. Users can now build on them and link the three statements.

• Debt: Prior long-term debt is adjusted for expected issuances and repayments. It is worth noting that long-term debt becomes short-term debt as it nears its expected repayment.
• Equity: Shareholders’ equity is computed as - Opening balance + Expected issuances or buybacks (from the cash flow statement) + Net income (from the income statement) – Dividends (from the cash flow statement). 
• Cash: The closing balance for cash is calculated as - Opening balance + Net cash flow for the period.

Once these items are calculated, they will be plugged into the balance sheet. Please ensure that the balance checks do not indicate any errors.

9. Net Interest Expense

The puzzle is almost complete. The interest expense (or income) is the single item left to be plugged. Plugging in the net finance costs in the income statement will change the net earnings, further impacting the balance sheet through retained earnings and the cash flow statement through cash flow from operating activities.

Thus, financing costs affect all three statements, producing circularity. This is also known as iterative calculation. Excel (or other spreadsheet software) runs different numbers through the calculations to find the values that satisfy each of such analyses.

As the final piece of the puzzle, Iterations must be enabled in Excel. On the Windows version of Excel, users can go to File > Options > Formulas and select the “Enable iterative calculation” checkbox.

Users with the Mac version of Excel can go to Preferences > Calculation and enable “Use iterative calculation.” The net earnings figure should get updated in the income statement now, which should flow into the other two statements.

After the above steps, your three-statement model should be ready to help you make more informed decisions.

Check this page out for another  3 Statement model example .

Everything You Need To Master Financial Statement Modeling

To Help you Thrive in the Most Prestigious Jobs on Wall Street.

More about Financial Modeling

To continue learning and advancing your career, check out these additional helpful WSO resources:

• What is Financial Modeling?
• Free Financial Modeling Guide
• What Makes a Good Financial Model?
• Top Financial Modeling Courses
• Valuation Modeling in Excel

Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.

or Want to Sign up with your social account?

• Financial Modelers
• Corporate Finance Consultants
• Interim CFOs
• Financial Forecasting Experts
• Fractional CFOs
• Interim Finance Directors
• Financial Reporting Experts
• Due Diligence Consultants

Financial Modeling Case Study: OOVA

Amy Divaraniya, CEO and co-founder of OOVA, created a product she knew the market needed. Yet, while she was pitching investors trying to close her seed round, she found that the financial projections needed a fresh pair of eyes.

By Elizabeth J. Howell Hanano, CFA

Elizabeth was an equity research analyst on both the buyside and sellside before transitioning to freelancing where she specializes in market research and valuation.

PREVIOUSLY AT

The following is a Toptal Case Study that details how one founder worked with a Toptal financial modeling consultant to bring confidence and certainty to her projections.

Problem: a founder trying to close a seed round needs financial projections based on research presented in a way investors understand..

Amy Divaraniya, CEO and co-founder of OOVA, created a product she knew the market needed - a fertility diagnostic kit that brought the accuracy of a clinic into your own home. Yet, while she was pitching investors trying to close her seed round, she found that the financial projections needed a fresh pair of eyes. Divaraniya recalls, “When financial projections came up in conversation, I felt they were weak. They weren’t backed up by data. Further, it was in a format I could understand, but it was something VCs were not used to seeing.”

While Divaraniya had great relationships with other startups, she couldn’t get her hands on a good template. “That’s the one thing nobody wants to share.” Even if she did, Divaraniya felt she didn’t have the background to make fact-based, reasonable assumptions. “What’s the going growth rate I should assume for legal fees? Taxes? What is a typical CAC for a company like ours? How are we different?”

Solution: A financial model with significant flexibility and logic, with every assumption based on research.

How divaraniya found the right consultant.

Divaraniya had been working with a Toptal developer since 2018 and saw that Toptal also offered finance consulting. She decided it was time to pull the trigger so she began interviewing candidates.

The Toptal matching team selected two consultants with appropriate backgrounds, and Divaraniya chose Jeffrey Fidelman during the interview. “With Jeffrey, he understood what I needed. He’d ask, ‘Have you thought about these things?’ Everything he mentioned, someone had asked me about and I didn’t have well-fleshed out.”

He really understood what my need was, not what my ask was.

Quick Kickoff Process

Fidelman plugged himself into the workflow immediately. “The interview was toward the end of the week and over the weekend, he sent a sample of what a financial projection would look like. He responded to everything over the weekend, within a few hours.” They had a kickoff call on Monday and then communicated at least twice per week for the next few weeks. In about two weeks, Divaraniya had a working model.

Understanding the Bigger Picture, Not Just the “Ask”

The project could have ended there, but Fidelman kept asking questions that unveiled more holes that Divaraniya wanted to fill in her fundraising efforts. Further, Divaraniya realized she wanted to add flexibility to her model so she could see the impact of different strategies (e.g., various distribution models). While Divaraniya thought she just wanted a basic financial model , she ended up working with Fidelman for about 100 hours. In the end, she not only had a very flexible financial model, she had six reports of about 25 pages each backing up every assumption.

Snippet from Market Research Report

A Flexible Financial Model

At the time Fidelman was building out the financial model, both Fidelman and Divaraniya were still working out the optimal sales channel model for OOVA. “At the same time, we were still doing product development. The sales model kept changing. Should we have affiliate fees? One-time fees? Jeffrey was super flexible. No matter what I decided, we could still make it work with the model he created.” Fidelman ended up creating an assumptions tab that included the various sales methods Divaraniya was considering so she could simply plug and play when she decided on a model.

Market Research to Create Fact-based Financial Model Inputs

Your model is only as good as its inputs. Fidelman aimed to create a model that resembled the company as closely as possible. While the expense side was relatively straightforward, the topline figures took some research. Fidelman tapped his network to research questions like: What’s the average markup? What’s the size of the US fertility market? “Yes, it’s health technology, but it’s also consumer goods. So a lot of the research I did was relative to things like heart rate monitors, baby monitors, and contraceptives,” noted Fidelman.

Fidelman researched the unique market position of OOVA. “It’s tech-enabled, yes. But they’re still ovulation sticks so you’ll likely need to replenish them. Where would you find this OTC? Would it be in the Personal Care/ Family Planning section? Will it be behind the counter at the pharmacy? We had to really think about who we wanted to sit with.”

Results: A flexible financial model based on deep market research that aided OOVA in closing its seed round.

With a flexible financial model and market research in hand, Divaraniya successfully closed her seed financing round. “Conversations with investors were so much easier. During fundraising, I felt like I could firmly stand on my own two feet.”

About OOVA: OOVA is a fertility diagnostic company that spun out from Mount Sinai Hospital in New York City. Using patent-pending biochemistry and artificial intelligence, OOVA learns a woman’s fertility profile by accurately measuring key hormones over time through daily urine samples. By monitoring this critical balance of hormones, OOVA can help a woman get pregnant, identify reasons she is having trouble getting pregnant, and even expose reproductive health issues like PCOS.

About Jeffrey Fidelman: Jeffrey has been a trusted advisor for several years, focused on working with entrepreneurs and helping them build and grow their business, often from the ground up. He's successfully helped entrepreneurs raise more than $1 billion. His ability to work across a wide variety of industries is the result of a diverse investment background. Freelancing enables him to work directly with leadership to achieve efficient and positive results.

Understanding the basics

Why is financial modeling important.

An accurate, research-based financial model can help companies demonstrate their value to investors, making it easier to attract funding and support future growth. The financial projections provided by the model can also enable smarter, more informed decision-making and make it easier for the company to pivot and respond to challenges when they arise. Without a sound model in place, a company may not fully understand the financial implications of its choices.

How long does it take to build a financial model?

An extremely detailed financial model with assumption drivers for many variables could take 30-60 hours to build. Additional research to provide a basis for assumptions could add additional time as well. A basic financial model that projects annuals (not weeks) and uses high-level drivers without significant logic may take as little as one hour.

What do financial projections include?

At a high level, financial projections include money coming in and money going out. Generally, the most important big picture financial projections include revenue, expenses, cash, debt, and cash flow. At a detailed level, financial projections should take all the major drivers of historical three-statement results and forecast them going forward: income statement, cash flow statement, and balance sheet.

• Forecasting
• Fundraising

Elizabeth J. Howell Hanano, CFA

Annapolis, MD, United States

Member since August 21, 2017

About the author

World-class articles, delivered weekly.

By entering your email, you are agreeing to our privacy policy .

Toptal Finance Experts

• Blockchain Consultants
• Business Management Consultants
• Business Plan Consultants
• Business Process Optimization Consultants
• Certified Public Accountants
• Economic Development Consultants
• Equity Research Analysts
• Excel Experts
• Financial Benchmarking Consultants
• Financial Writers
• Fintech Consultants
• FP&A Consultants
• Fundraising Consultants
• FX Consultants
• Growth Strategy Consultants
• Investment Managers
• Investment Thesis Consultants
• Investor Relations Consultants
• M&A Consultants
• Market Sizing Experts
• Pitch Deck Consultants
• Private Equity Consultants
• Procurement Consultants
• Profitability Analysis Experts
• Real Estate Experts
• Restructuring Consultants
• Risk Management Consultants
• Small Business Consultants
• Supply Chain Management Consultants
• Valuation Specialists
• Venture Capital Consultants
• Virtual CFOs
• Xero Experts
• View More Freelance Finance Experts

Join the Toptal ® community.

The Amazon Case Study (New Edition)

• What You'll Learn
• Career Programs
• What Students Say

The Amazon Case Study

Welcome to CFI’s advanced financial modeling course – a case study on how to value Amazon.com, Inc (AMZN).  This course is designed for professionals working in investment banking, corporate development, private equity, and other areas of corporate finance that deal with valuing companies and applying various methods of valuation.

Advanced Financial Modeling Course Objectives

This advanced financial modeling course has several objectives including:

• Use Amazon’s financial statements to build an integrated 3-statement financial forecast
• Learn how to structure an advanced valuation model effectively
• Set up all the assumptions and drivers required to build out the financial forecast and DCF model
• Create a 10-year forecast for Amazon’s business, including an income statement, balance sheet, cash flow statement, supporting schedules, and free cash flow to the firm (FCFF)
• Learn how to deal with advanced topics like segmented revenue, capital additions, finance leases, operating leases, and more
• Perform comparable company analysis (Comps) utilizing publicly available information
• Perform a Sum-Of-The-Parts (SOTP) valuation of Amazon, as well as consider precedent transactions, equity research price targets, and Amazon’s 52-week trading range
• Generate multiple operating scenarios to explore a range of outcomes and values for the business
• Perform detailed sensitivity analysis on key assumptions and assess the overall impact on equity value per share

Amazon (AMZN) Case Study

This course is built on a case study of Amazon, where students are tasked with building a financial modeling and performing comparable company analysis to value AMZN shares and make an investment recommendation.

Through the course of the transaction, students will learn:

• How to build a detailed financial forecast of Amazon
• How to apply various valuation methodologies to derive an implied value for Amazon
• How to develop an investment recommendation on the shares of Amazon
• How to create a dashboard and summary output that highlights the most important information from the model

Why Take CFI’s Advanced Financial Modeling Course?

This course is perfect for anyone who wants to learn how to build a detailed financial model for a public company, from the bottom up. The video-based lessons will teach you all the formulas and functions to calculate things like segmented revenue, marketable securities, accrued expenses, unearned revenue, stock-based compensation, long-term debt, finance and operating leases, and much more.

In addition to learning the detailed mechanics of how to build the financial model for Amazon, students will also learn how to think about intrinsic value, and develop an investment recommendation.

What’s Included in the Advanced Modeling Course?

This advanced financial modeling and valuation course include all of the following:

• Blank Amazon model template
• Completed Amazon model template (dashboard, DCF model, Comps model, WACC analysis, scenarios, etc.)
• 4+ hours of detailed video instruction
• Certificate of completion

Recommended Preparatory Courses

We recommend you complete the following courses or possess the equivalent knowledge before taking this course:

• Excel Fundamentals – Formulas for Finance
• DCF Valuation Modeling
• Comparable Valuation Analysis
• 3-Statement Modeling

This course is intended solely for educational and training purposes. The information contained herein does not constitute investment advice, or an offer to sell, or the solicitation of any offer to buy any securities of Amazon.com, Inc. (NasdaqGS: AMZN) or any other security.

This content in this course has not been approved or disapproved by (a) Amazon.com, Inc., (b) S&P Global Market Intelligence Inc., (c) any equity research analyst that covers Amazon.com, Inc., or (d) any securities regulator in any province or territory of Canada, the United States Securities and Exchange Commission or any other United States federal or state regulatory authority, and no such commission or authority has passed upon the merits, accuracy or adequacy of this content, nor is it intended that any will.

The information in this course does not constitute the provision of investment, tax, legal or other professional advice. As with all investments, there are associated risks and you could lose money investing – including, potentially, your entire investment. Prior to making any investment, a prospective investor should consult with its own investment, accounting, legal and tax advisers to evaluate independently the risks, consequences and suitability of that investment.

No reliance may be placed for any purpose on the information and opinions contained herein or their accuracy or completeness, and nothing contained herein may be relied upon in making any investment decision.

Approx 12.5h to complete

100% online and self-paced

What you'll learn

Introduction, building a financial forecast, implied value analysis, investment recommendation, course conclusion, qualified assessment, this course is part of the following programs.

Why stop here? Expand your skills and show your expertise with the professional certifications, specializations, and CPE credits you’re already on your way to earning.

Financial Modeling & Valuation Analyst (FMVA®) Certification

• Skills Learned Financial modeling and valuation, sensitivity analysis, strategy
• Career Prep Investment banking and equity research, FP&A, corporate development

What Our Members Say

Eugene uwizeyimana, muhammad ahsan khan, juan pablo mancera sosa, anvarjon abdulkhamidov, roman ruzymov, diana alves, ryan garousi, shuopeng cui, marc- aurel hountondji, ayush mishra, joshua mwendabai sepiso, fardeen soudagar, theodorus emil, marco atsushi, okoth tebby, joseph mensah, dennis graham, atinafu asefa, michelle eisenberg, aaron garrido, valentina popovska, beth pitzer, frequently asked questions.

Create a free account to unlock this Template

Access and download collection of free Templates to help power your productivity and performance.

Already have an account? Log in

Supercharge your skills with Premium Templates

Take your learning and productivity to the next level with our Premium Templates.

Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.

Already have a Self-Study or Full-Immersion membership? Log in

Access Exclusive Templates

Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.

Already have a Full-Immersion membership? Log in

INR 1,463Market Capitalization (In Crores)INR 1,13,98752 Week HighINR 1,69452 Week LowINR 1,329Book ValueINR 807P/E Ratio (x)12.8

Financial Statements of IndusInd Bank Ltd. Case Study

Income statement.

	45,748	36,367	30,822
	55,143	44,540	38,230
	39,279	30,121	25,132
	8,977	7,443	4,805

Balance Sheet

	3,84,585	3,36,120	2,93,349
	3,43,298	2,89,923	2,39,051
	1,06,486	83,075	70,929
	62,030	53,882	46,928

Cash Flow Statement

	-16,924	-12,442	16,672
	-711	-560	-406
	-2,241	1,114	-4,302

Key Performance Indicators (KPIs)

	4	3.84	3.73
	19.62	20.46	15.58
	14.26	13.60	10.06
	3.20	3.27	3.36
	37.87	40.08	42.72

SWOT Analysis of IndusInd Bank Ltd.

• Brand Image – Indusind Bank has established a strong brand image and is seen as a dependable bank.
• Diversified Product Portfolio – The bank provides a range of products to its clientele, including loans, investment services, and several kinds of savings accounts. 
• Loan Book – An indication of the company’s strong financial standing is the expansion of its loan book on an annual basis. 
• Market Share – The bank’s market share is comparatively smaller than that of the other participants in the industry.
• Limited Geographical Reach – The bank’s limited geographical presence limits its potential for growth because it has concentrated on specific regions of the nation. 
• Wholesale Borrowing – Most of the bank’s borrowing comes from wholesale borrowing, which raises borrowing costs. 

Opportunities

• Cross-Selling – The bank can sell other financial products to exploit cross-selling opportunities, which will boost their earnings. 
• Digital Inclusion – The bank may take advantage of this growth in internet usage by adding additional digital products. 
• Rural Areas – The bank can increase its market share by reaching out to untapped semi-urban and rural areas. 
• Competition – IndusInd Bank faces fierce competition from well-established government and private banks. 
• Cyber Security Risk – Since banks rely on digital technologies to store their data, any type of cyberattack will compromise the bank’s integrity.
• Government Policies – Since the Indian banking sector is so heavily regulated, any changes to the laws could have an impact on the bank’s operations. 

Within the Indian banking sector, the IndusInd Bank has made a big name for itself. The bank responds to the evolving digital technology landscape by providing its clients with innovative financial products. IndusInd Bank can increase its market share by expanding its operations in rural and semi-urban areas. However, one should speak to a financial advisor before making investment decisions. 

Frequently Asked Questions (FAQs)

Who is the current md and ceo of indusind bank.

Mr Sumant Kathpalia is the current MD and CEO of IndusInd Bank as of 13 September 2024.

Is Indusind Bank a profitable bank?

IndusInd Bank is profitable as it has declared net profits of 8,977 crores in FY 2024 and 7,443 crores in FY 2023. 

Where is the headquarters of Indusind Bank situated?

The headquarters of IndusInd Bank is situated in Mumbai.

IndusInd Bank is a large-cap or mid-cap company?

IndusInd Bank is classified as a large-cap firm by market capitalization as of 13 September 2024.

When was Indusind Bank Limited established?

Mr S.P. Hinduja established the IndusInd Bank in 1994. 

Related Posts

BPCL Case Study: Business Model, Product Portfolio and SWOT Analysis

Divi’s Laboratories Case Study: Business Model, Product Portfolio, And SWOT Analysis

Hindalco Industries Case Study: Business Model and SWOT Analysis

Pocketful is an advanced trading platform that empowers traders with cutting-edge technology. we provide innovative tools and resources to make trading more accessible and practical., quick links.

• Open an Account
• Pocketful Web
• Pocketful App
• Investment Tool
• Trading Tool
• Support Portal
• Referral Program
• Calculators
• Stocks Pages
• Government Schemes
• Index Heat Map
• Stock Screener
• Mutual Funds
• Terms & Conditions
• Policies & Procedures
• Privacy Policy
• Press & Media

We are a concern of PACE Group. Pocketful is an investing platform that helps people be better investors. Pocketful unlocks the discoverability of new investment and trading ideas.

Open free demat account.

Join Pocketful Now

You have successfully subscribed to the newsletter

There was an error while trying to send your request. Please try again.

• Video Tutorials
• Knowledge Base
• Group Licenses
• Why Choose Us?
• Certificates

• 3-Statement Model Tutorials

Knowledge Base: 3-Statement Model Tutorials

A 3-statement model links the Income Statement, Balance Sheet, and Cash Flow Statement of a company into a single, dynamic spreadsheet.

Companies may use 3-statement models for a wide variety of purposes, from internal budgeting (e.g., in the FP&A division within corporate finance) to planning for major acquisitions or the issuances of debt and equity to fund growth and expansion.

The model itself may be very simple (e.g., 100 rows or less in Excel) or very complicated, with multiple spreadsheets and supporting schedules to back up every single detail.

We prefer to build 3-statement models in the “mid-complexity range,” with enough detail to be useful but not enough to be overwhelming.

For a detailed example, please see our full 3-statement modeling tutorial and walkthrough .

IB Case Studies: 3-Statement Modeling Test (26:05)

Subscription Revenue Model (Netflix) (17:37)

• All Self-Study Programs
• Premium Package
• Basic Package
• Private Equity Masterclass
• VC Term Sheets & Cap Tables
• Sell-Side Equity Research (ERC © )
• Buy-Side Financial Modeling
• Real Estate Financial Modeling
• REIT Modeling
• FP&A Modeling (CFPAM ™ )
• Project Finance Modeling
• Bank & FIG Modeling
• Oil & Gas Modeling
• Biotech Sum of the Parts Valuation
• The Impact of Tax Reform on Financial Modeling
• Corporate Restructuring
• The 13-Week Cash Flow Model
• Accounting Crash Course
• Advanced Accounting
• Crash Course in Bonds
• Analyzing Financial Reports
• Interpreting Non-GAAP Reports
• Fixed Income Markets (FIMC © )
• Equities Markets Certification (EMC © )
• ESG Investing
• Excel Crash Course
• PowerPoint Crash Course
• Ultimate Excel VBA Course
• Investment Banking "Soft Skills"
• Networking & Behavioral Interview
• 1000 Investment Banking Interview Questions
• Virtual Boot Camps
• 1:1 Coaching
• Corporate Training
• University Training
• Free Content
• Support/Contact Us
• About Wall Street Prep
• Financial Modeling

3-Statement Model

Step-by-Step Guide to Understanding How to Build an 3-Statement Financial Model

View modeling courses

What is a 3-Statement Model?

The 3-Statement Model is an integrated model used to forecast the income statement, balance sheet, and cash flow statement of a company for purposes of projecting its forward-looking financial performance.

Table of Contents

How to Build a 3-Statement Model

How to format the 3-statement financial model, what is periodicity in 3-statement modeling, what is the structure of a 3-statement model, basic elements of an integrated 3-statement model, sec edgar: how to gather data for financial modeling, how to forecast the income statement, how to forecast the balance sheet, how to forecast the cash flow statement (cfs), how to create model plugs (cash and revolver), how to handle a circularity in excel, how to calculate shares outstanding and earnings per share (eps), how to perform scenario analysis in excel, how to conduct sensitivity analysis in excel, what skills are required in financial modeling, why does the 3-statement model matter.

While accounting enables us to understand a company’s historical financial statements, forecasting those financial statements enables us to explore how a company will perform under various assumptions, and visualize how a company’s decisions interact to impact the bottom line in the future.

• Operating Decisions → i.e. “Let’s reduce prices”
• Investing Decisions → i.e. “Let’s buy an additional machine”.
• Financing Decisions → i.e. “Let’s borrow a bit more”.

A well-built 3-statement financial model helps insiders (corporate development professionals, FP&A professionals ) and outsiders (institutional investors, sell side equity research , investment bankers and private equity ) see how the various activities of a firm work together, making it easier to see how decisions impact the overall performance of a business.

It is critical that a complex financial model like the 3-statement model adheres to consistent best practices. This makes both the task of modeling and auditing other people’s models far more transparent and useful.

We have written an Ultimate Guide to Financial Modeling Best Practices , but we’ll summarize some key takeaways here.

The most basic formatting rules are:

Color-code your model so that inputs are blue and formulas are black. The table below shows other color-coding best practices:

Type of cells	Color
Hard-coded numbers (inputs)	Blue
Formulas (calculations)	Black
Links to other worksheets	Green
Links to other files	Red
Links to data providers (i.e. CIQ, FactSet)	Dark Red

Format data consistently (for example, keep consistent unit scale, use 1 decimal place for numbers, 2 for per share data, 3 for share count ).

Avoid partial inputs that commingle cell references with hard numbers.

Maintain standard column widths and consistent header labels.

One of the first decisions in building a 3-statement financial model concerns the periodicity of the model.

Namely, what are the shortest periods the model will be partitioned into annual, quarterly, monthly, or weekly?

This will typically be determined by the purpose of the 3-statement financial model.

Below we’ve outlined some general rules of thumb:

• Annual Models →  Common when using the model to drive a DCF model valuation. This is because a DCF model needs at least 5 years of explicit forecasts before making terminal value. LBO models are often also annual models, as the investment horizon is around 5 years. An interesting wrinkle with annual models is the handling of the “stub period,” which captures the latest 3-, 6-, or 9-months of historical data).
• Quarterly Financial Models →  Common in equity research, credit, financial planning and analysis , mergers and acquisitions (accretion/dilution) models where near-term issues are a catalyst. These models often roll up into an annual buildup.
• Monthly Financial Models →  Common in restructurings and project finance where month-to-month liquidity tracking is critical. One thing to note is that the data required for a monthly buildup is usually unavailable to outside investors unless it is privately provided by management (companies typically don’t report monthly data). These models often roll up into a quarterly buildup.
• Weekly Financial Models →  Common in bankruptcies. The most common weekly model is called the thirteen-week cash flow model (TWCF). The TWCF is a required submission in a bankruptcy process to track cash and liquidity.

When models get large, adhering to a strict structure is critical.

The key rules of thumb to follow include the following:

• Use roll-forward schedules when forecasting balance sheet items.
• Aggregate inputs in one worksheet or one section of the model and separate them from calculations and outputs.
• Avoid linking files together.

Build a 3-Statement Financial Model | Free Course

By submitting this form, you consent to receive email from Wall Street Prep and agree to our terms of use and privacy policy.

An integrated 3-statement model

3-statement models include a variety of schedules and outputs, but the core elements of a 3-statement model are, as you may have guessed, the income statement , balance sheet, and cash flow statement .

A key feature of an effective model is that it is “integrated,” which simply means that the 3-statement models are modeled in a way that accurately captures the relationship and linkages between the various line items across the financial statements.

An integrated model is powerful because it enables the user to change an assumption in one part of the model to see how it impacts all other parts of the model consistently and accurately.

Before firing up Excel to begin building the model, analysts need to gather the relevant reports and disclosures.

At a minimum, they will need to gather the company’s latest SEC filings, press releases and possibly equity research reports.

Data is much harder to find for private companies than for public companies, and reporting requirements vary across countries. We have compiled a guide on gathering historical data needed for financial modeling here .

The income statement illustrates a company’s profitability . All three statements are presented from left to right, with at least 3 years of historical results present to provide historical rations and growth rates on which forecasts are based.

Inputting the historical income statement data is the first step in building a 3-statement financial model.

The process involves either manual data entry from the given company’s 10K or press release or the use of an Excel plugin such as FactSet or Capital IQ to drop historical data directly into Excel.

Forecasting typically begins with a revenue forecast followed by the forecasting of various expenses. The net result is a forecast of the company’s income and earnings per share. The income statement covers a specified period such as a quarter or year.

For more on this, check out the complete income statement forecasting guide .

Income Statement Screenshot from the Wall Street Prep Premium Package Training Program

Unlike the income statement, which shows operating results over a period of time (a year or a quarter), the balance sheet is a snapshot of the company at the end of the reporting period. The balance sheet shows the company’s resources ( assets ) and funding for those resources ( liabilities and shareholder’s equity). Inputting historical balance sheet data is similar to inputting data in the income statement. The data is inputted either manually or through an Excel plugin.

In large part, the balance sheet is driven by the operating assumptions we make on the income statement. Revenues drive the operating assumptions in the income statement, and this continues to hold true in the balance sheet: Revenue and operating forecasts drive working capital items, capital expenditures , and a variety of other items. Think of the income statement as the horse and the balance sheet as the carriage. The income statement assumptions are driving the balance sheet forecasts.

Click here for a complete guide to forecasting the balance sheet

Balance Sheet Screenshot from the Wall Street Prep Premium Package Training Program

The final core element of the 3-statement model is the cash flow statement. Unlike on the income statement or the balance sheet, you aren’t actually forecasting anything explicitly on the cash flow statement and it isn’t necessary to input historical cash flow statement results before forecasting. That’s because the cash flow statement is a pure reconciliation of the year-over-year changes in the balance sheet.

Every individual line item on the cash flow statement should be referenced from elsewhere in the model (it should not be hardcoded) as it is a reconciliation. Constructing the cash flow statement correctly is critical to getting the balance sheet to balance.

Cash Flow Statement Screenshot from the Wall Street Prep Premium Package Training Program

A universal feature of a 3-statement model is that cash and a revolving credit line serve as model “plugs.” This simply means that a 3-statement model has an automatic way of ensuring that when the model projects a cash shortfall after all the line items are forecast, additional debt via a “revolver” account will automatically increase to finance the shortfall. Conversely, if the model projects a cash surplus, cash will accumulate by the amount of the surplus. While this seems fairly logical, modeling this can be tricky. Click here for a guide to forecasting the revolver and cash balance with a free excel template

Many financial models have to deal with a problem in Excel called circularity. A circularity in Excel occurs when one calculation either directly or indirectly depends on itself to arrive at an output. In the 3-statement model, a circularity can occur because of the model plugs described above. This makes Excel unstable and can create a variety of problems for those using the model. There are several elegant ways to deal with this issue. To learn more about how to deal with circularity, go to the “Circularity” section of our guide on financial modeling best practices.

For public companies, projecting earnings per share is key. Forecasting the numerator of EPS is described in detail in our income statement forecasting guide , but forecasting shares outstanding can be done in a variety of ways, ranging from simply keeping the historical share count constant to a more sophisticated analysis that takes into account forecasts for share repurchases and issuances. Click here for a guide to forecasting EPS .

The purpose of building a 3-statement financial model is to observe how various operating, financing and investing assumptions impact a company’s forecasts. Once the initial case is built, it is useful to see — using either equity research, management guidance, or other assumptions — how the forecasts change given changes in a variety of key model assumptions. To this end, financial models often have a drop-down list that provides the user with the option to select either the original case (often called “base case”) or a variety of other scenarios (“strong case,” “weak case,” “management case,” etc.), which is referred to as scenario analysis .

A close cousin of scenario analysis is sensitivity analysis . Any good 3-statement financial model (or a DCF model , LBO model , or M&A model, for that matter) will include the ability to toggle between various scenarios to see how the model’s outputs change, as well as something called sensitivity analysis. Sensitivity analysis is the process of isolating one (usually critical) model output to see how changes impact one or two key inputs.

For example, how would Apple’s 2020 EPS forecast change at various assumptions for 2020 revenue growth and gross profit margins ? Click here to learn how to build a sensitivity analysis into a 3-statement model .

Building a 3-statement financial model requires the combination of the following skills:

• Excel: Getting strong in Excel may seem daunting, but it’s actually the easiest skill on this list to develop. A general rule of thumb in finance is to avoid using the mouse and memorize some keyboard shortcuts. Accounting: This is the single most important (and least glamorous) part of getting strong in modeling. Understanding how the three financial statements are tied together and what each line item on the income statement, balance sheet and cash flow statement represents is the key to the conceptual understanding of how a 3-statement financial model works.
• Reading Financial Reports: Even though 3-statement financial models are designed to illuminate a firm’s future performance, setting up the model depends on a thorough understanding of what happened to the company in the past. For that, investment bankers and investors gather historical financial data . Whether you’re looking through SEC filings or quarterly press releases, or modeling a private company where you’re only provided piecemeal disclosures, finding the data you need will feel like a scavenger hunt. Your ability to navigate those reports and to find the exact data you are seeking can make a difference when building a model.
• Company and industry knowledge: One of the realities for new investment bankers is that they are often tasked with building a lot of models for industries and companies they don’t really know and don’t have time to learn. A 3-statement financial model’s assumptions about things like revenue growth and profit margins are critical to making a good forecast, so knowing the resources available to collect company and industry insights is very important. Quite often, investment bankers rely on sell side equity research to quickly get smart on the company and industry. Meanwhile, institutional investors (who, unlike investment bankers, have skin in the game) spend even more time getting to know the company, often through a lot of due diligence such as speaking with management and customers, going on-site visits, and trying out products themselves.
• Attention to detail: One wrong decimal place is all it takes to completely screw up a model. In investment banking, corporate finance, and equity research, the stakes are high and attention to detail is often the difference between getting promoted and getting fired.

At their core, all M&A, DCF, and LBO models depend on forecasts produced in the 3-statement model.

The output of a 3-statement model serves as the foundation for several types of financial models :

• Discounted Cash Flow (DCF) Modeling: In investment banking , private equity , and on the investment management side, practitioners value companies using a methodology called the DCF approach. This approach looks at a company’s future expected cash flows and discounts those cash flows to the present. While analysts sometimes rely on a “back of the envelope” approach when building the DCF, a rigorous DCF analysis requires a full 3-statement model to feed the cash flow forecasts.
• Mergers & Acquisitions (M&A) Modeling: To analyze the impact of an acquisition on a variety of key considerations for buyers and sellers, such as the acquirer’s profitability, accretion/dilution, capital structure, synergies post-acquisition, and the seller’s tax implications, 3-statement financial models for both companies need to be constructed and fused together.
• Leveraged Buyout (LBO) Modeling The only way to truly understand how a leveraged buyout (or a management buyout) or a corporate bankruptcy or restructuring will impact a company’s performance (and thus ultimately determine the potential returns to the financial sponsors and lenders involved in the buyout), is to construct a 3-statement financial model for the buyout candidate, and it must be flexible enough to handle the new leveraged capital structure.

Everything You Need To Master Financial Modeling

Enroll in The Premium Package: Learn Financial Statement Modeling, DCF, M&A, LBO and Comps . The same training program used at top investment banks.

• 100+ Excel Financial Modeling Shortcuts You Need to Know
• The Ultimate Guide to Financial Modeling Best Practices and Conventions
• What is Investment Banking?
• Essential Reading for your Investment Banking Interview

We're sending the requested files to your email now. If you don't receive the email, be sure to check your spam folder before requesting the files again.

Everything you need to master financial and valuation modeling: 3-Statement Modeling, DCF, Comps, M&A and LBO.

The Wall Street Prep Quicklesson Series

7 Free Financial Modeling Lessons

Get instant access to video lessons taught by experienced investment bankers. Learn financial statement modeling, DCF, M&A, LBO, Comps and Excel shortcuts.

Digital business transformation in Silicon Savannah: How M-Pesa changed Safaricom (Kenya)

This case study investigates the phenomenal development of M-PESA in Kenya, the world’s most successful mobile phone-based financial service, and the way it transformed its parent company, Safaricom from being an incumbent telecommunications operator to also becoming a digital platform working across different industries and sectors through an extensive business ecosystem. Safaricom, Kenya’s incumbent telecom operator, developed M-PESA in partnership with Vodafone. The service was launched in March 2007 and has since been used by more than 23 million people in Kenya! It started off as a mere person-to-person remittance service and now provides a wide array of financial services for retail customers, businesses and government. Nearly a decade after its launch, M-PESA has drastically transformed the daily life of its users as well as Kenya’s economic landscape, where it impacted the banking and telecom landscape, boosted the development of e-commerce and facilitated operations for thousands of small businesses, online and offline. Internationally, M-PESA has become a role model for mobile financial services and payment platforms as well as inclusive business practices.

• Comprehend the why, what, and how of DBT; Assess the different dimensions of DBT
• Appreciate the crucial role in DBT of leadership, lean management, talent development and organizational alignment
• Understand the business opportunities enabled by leveraging breakthrough innovation, digital platforms, and big data

The Case Centre

Cranfield University

Wharley End Beds MK43 0JR, UK Tel +44 (0)1234 750903 Email  [email protected]

Harvard Business School Publishing

60 Harvard Way, Boston MA 02163, USA Tel (800) 545-7685 Tel (617)-783-7600 Fax (617) 783-7666 Email  [email protected]

Asia Pacific Case Center

NUCB Business School

1-3-1 Nishiki Naka Nagoya Aichi, Japan 460-0003 Tel +81 52 20 38 111 Email  [email protected]

IMD retains all proprietary interests in its case studies and notes. Without prior written permission, IMD cases and notes may not be reproduced, used, translated, included in books or other publications, distributed in any form or by any means, stored in a database or in other retrieval systems. For additional copyright information related to case studies, please contact Case Services .

Research Information & Knowledge Hub  for additional information on IMD publications

As the EU enforces the Digital Markets Act, tech giants face strict new rules aimed at curbing their dominance – but critics warn the move could st...

Design thinking is an experience-based and user-centric method for solving problems. This human-centered approach engages end-users and employees i...

Digital product innovation (DPI) is critical for the survival of firms, especially those operating in traditional industrial-age industries. While ...

The global race for dominance in generative AI is intensifying, with tech giants vying for supremacy in this transformative arena. While U.S. compa...

FUCHS SE's journey in China shows how its FUCHS2025 strategy balances global ambitions with local agility, leveraging local expertise for global in...

Companies that excel in both digital and sustainable transformation attract a stock market premium, according to research. So, how do you tap into ...

The increasing datafication of the workplace is often cast as a means of imposing organisational and managerial control on workers. This reflection...

This paper introduces the New Industrial Policy Observatory (NIPO) data set and documents emergent patterns of policy intervention during 2023 asso...

Too many organizations assume late-career workers can't keep up with new technology. In doing so, they are ignoring a hugely important labor pool.

Since the dawn of video games, there have been gaming competitions. But for decades, such contests were regarded as beneath the hallowed realm of '...

Join 307,012+ Monthly Readers

Get Free and Instant Access To The Banker Blueprint : 57 Pages Of Career Boosting Advice Already Downloaded By 115,341+ Industry Peers.

• Break Into Investment Banking
• Write A Resume or Cover Letter
• Win Investment Banking Interviews
• Ace Your Investment Banking Interviews
• Win Investment Banking Internships
• Master Financial Modeling
• Get Into Private Equity
• Get A Job At A Hedge Fund
• Recent Posts
• Articles By Category

Real Estate Financial Modeling (REFM): The Ultimate Guide, With Templates & Examples

If you're new here, please click here to get my FREE 57-page investment banking recruiting guide - plus, get weekly updates so that you can break into investment banking . Thanks for visiting!

Step 6: Make an Investment Decision

When it comes to real estate financial modeling (REFM) , there’s a ton of information out there…

…which, ironically, makes it harder to understand.

It can be nearly impossible to find one source that clearly explains the key points from start to finish.

We go into great depth in our full Real Estate Financial Modeling course , so I’m not going to attempt to replicate everything here.

However, I will summarize the most important parts, give you a few sample Excel (XLS) models to download, and explain the step-by-step process for modeling the three most common deal types:

• Acquisition
• Development

So let’s get started…

Real Estate Financial Modeling: Sample Excel (XLS) Files

Here are the sample Excel files (both simplified real estate models). Below, we’ll move on to the explanation and tutorials.

• Multifamily Acquisition Model – Arcadia Gardens (XLSX)
• Industrial Development Model – 4216 61 Ave SE (XLSX)

What’s the Point of Real Estate Financial Modeling?

First, some definitions: we define “real estate” as land and buildings that generate revenue or have the potential to do so .

We focus on commercial real estate (CRE) that is purchased and then rented out to individuals or businesses, as opposed to residential real estate, such as single-family homes, that is owner-occupied and not rented out to others.

In CRE, individuals or businesses, i.e., tenants, pay rent to property owners to use their space.

The owners earn income from this rent, and they use part of it to pay for expenses such as utilities, property taxes, and insurance; in some cases, tenants are responsible for portions of these expenses as well.

All of this allows us to come up with the following definition of Real Estate Financial Modeling (aka REFM):

In real estate financial modeling (REFM), you analyze a property from the perspective of an Equity Investor (owner) or Debt Investor (lender) in the property and determine whether or not the Equity or Debt Investor should invest, based on the risks and potential returns.

For example, if you acquire a “multifamily” property (i.e., an apartment building) for $50 million and hold it for 5 years, could you earn a 12% annualized return on your investment?

Or, if you develop a new office building by spending $100 million on the land and construction, and then you find tenants, lease out the property, and sell it, could you earn a 20% annualized return?

If you identify the most important assumptions and set up your analysis correctly, real estate financial modeling helps you answer these types of questions.

All investing is probabilistic, so a simple model cannot tell you if a property will generate an 11.2% or 13.5% annualized return.

But a decent analysis can tell you whether or not that range of returns – 10% to 15% – is plausible.

These are the questions that real estate private equity firms think about all day, and they spend significant time doing the analysis before making investment decisions.

Types of Real Estate Financial Modeling

Real estate combines elements of equities and fixed income and can offer a risk / potential return profile that is somewhere in between them.

For example, a Core real estate deal where a firm acquires a stabilized property, changes very little, and then re-sells it, might offer risk and potential returns closer to those of an investment-grade corporate bond.

On the other hand, a “Value-Added” deal where a firm acquires a property with a low occupancy rate, make significant renovations to improve it, and aims to sell the property for a significantly higher price might offer risk and potential returns closer to those of stocks.

And an “Opportunistic” deal where a firm develops a new property from the ground up (“development”) or completely converts or re-builds an existing one (“redevelopment”) might offer even higher risk than stocks, but also higher potential returns.

These descriptions highlight the three main strategies and the three main types of real estate financial modeling:

• Real Estate Acquisition Modeling: Acquire an Existing Property, Change Little to Nothing, and Sell It.
• Real Estate Renovation Modeling: Acquire an Existing Property, Change It Significantly, and Sell It.
• Real Estate Development Modeling: Buy Land, Pay to Build a New Property, Find Tenants, and Sell It Upon Stabilization.

There is a fourth strategy as well: develop a new property, but pre-sell units before completion rather than leasing it out and selling the entire property at the end.

This type is just a subset of real estate development modeling, and it mostly applies to condominiums (residential real estate), so it’s not our focus.

In addition to these strategies, there are also different property types:

The lease types explain the key differences here.

Office, retail, and industrial properties tend to use more granular financial modeling because lease terms vary significantly, and there are fewer tenants or guests than in multifamily or hotel properties.

By contrast, hotels use assumptions and drivers that you’d see for many normal companies, and multifamily properties (apartment buildings) are somewhere in between.

You can think of the property spectrum like this:

For more about individual properties and how the differences translate into revenue and expenses, please see our detailed article on the real estate pro-forma .

Real Estate Modeling

Master financial modeling for real estate development and private equity with 6 short case studies and 5 in-depth ones based on real properties from around the world.

The Step-by-Step Process to Real Estate Financial Modeling

The exact steps vary based on the type of financial model, but they’ll always be something like this:

• Step 1: Set up the Transaction Assumptions , including those for the size of the property, the purchase price or development costs, and the exit (i.e., how much you might sell the property for at the end).
• Step 2: For a development model, project the Construction Period , usually on a monthly basis, and draw on Debt and Equity over time – not all upfront – to fund the construction.
• Step 3: Build the Operating Assumptions for the property, which could be very high-level (e.g., Average Rent per Unit * # Units) or very granular (revenue, expenses, and concessions for individual tenants) depending on the property type.
• Step 4: Build the Pro-Forma , including revenue and expenses down to the Net Operating Income (NOI) line, capital costs below that to calculate Adjusted NOI, and Debt Service (interest and principal repayments) below that to calculate Cash Flows to Equity.
• Step 5: Make the Returns Calculations , including the initial investment and any additional investments over time, the Cash Flows to Equity each year, and the exit proceeds, including repayment of Debt and transaction fees. You focus on the Internal Rate of Return (IRR) and Cash-on-Cash or Money-on-Money multiples here.
• Step 6: Make an Investment Decision based on your criteria and the output of the model in different cases.

Deal Type #1: Real Estate Acquisition Modeling

Let’s begin by looking at a very simple example of a real estate acquisition model for a multifamily property in Arizona. You can download the Excel file here:

This is a 76-unit property that we plan to acquire for just under $10 million USD.

We will change it a little bit because we plan to boost rents up to market rates by lightly renovating the units.

You can determine what these “market rates” might be with some commercial real estate market analysis .

We will also be less generous with concessions, bargain for higher utility expense reimbursements, and accept a higher vacancy rate in exchange for those.

These changes are minor compared with a true renovation or redevelopment, so this deal falls under the “Core” or “Core-Plus” category.

NOTE: In all the screenshots below, you can always click the screenshot to view a larger, higher-resolution version .

Step 1: Set Up the Transaction Assumptions

First, we need to determine the size of the property, which is usually based on the Units times the Average Square Feet per Unit in the multifamily sector:

A portion of the building will not be rentable because it corresponds to hallways, elevators, lobbies, etc., so we also distinguish between gross and rentable square feet:

We then make assumptions for the Acquisition Price , Exit Price , and the Loan-to-Value (LTV) Ratio :

We’ve hard-coded the acquisition price here, but it’s based on a Cap Rate of 5.80%.

That means that we take the Year 1 Net Operating Income (NOI) of the property ($567K) and divide by the 5.80% to determine the price.

NOI is a bit like EBITDA for normal companies: it includes operating revenue and expenses, but not Debt Service – and it may include the capital cost reserves as well, which makes it different from EBITDA.

In real estate financial modeling, property valuation is almost always based on the NOI divided by a Cap Rate or range of Cap Rates.

Cap Rates represent the property’s location, quality, and overall desirability, and lower Cap Rates mean the property is more expensive, while higher Cap Rates mean the opposite.

A multifamily property in Manhattan might sell for a Cap Rate of 3-4%, while a similar one in Columbus, Ohio might sell for a Cap Rate in the 6-8% range.

If the property does not change significantly, it’s best to make a conservative assumption that the Cap Rates rise over time.

We assume here that the Cap Rate rises from 5.80% upon purchase to 6.00% upon exit.

The LTV is 65%, which means that ~$6.4 million of this ~$9.8 million purchase price will be funded with a Senior Loan.

This amount of leverage is very high, but it’s common for properties because:

• Property margins tend to be much higher than “normal company” margins, which gives properties more cash flow to service their Debt.
• Permanent real estate loans tend to amortize over long periods, such as 30 years, which means only ~3.3% of the principal must be repaid each year. Interest-only periods, in the beginning, are common as well.

Using these assumptions, we create a Sources & Uses schedule that shows where the money is coming from and where it’s going to:

Step 2: Project the Construction Period

This step is not applicable here since there is no construction.

Step 3: Build the Operating Assumptions

The key assumptions for a multifamily property include the rent per unit or rent per square foot, the parking income, the utility reimbursements and concessions, and the expenses: insurance, utilities, sales & marketing, property taxes, maintenance, and other operating costs.

The growth rates for all of those, especially the income sources, are also important.

For more, please see the real estate pro-forma for full explanations of these categories:

We use a simple approach here and make the rent and expenses grow at specific annual rates.

Some items are projected on a per-unit or per-square-foot basis, while others are linked to Effective Gross Income (EGI), which is similar to Revenue.

Step 4: Build the Pro-Forma

With all these assumptions, we can create the real estate pro-forma:

We use the IPMT and PPMT Excel functions to calculate the Interest Expense and Principal Repayments:

• =IPMT(Loan Interest Rate, Year #, Loan Amortization Period, Loan Amount)
• =PPMT(Loan Interest Rate, Year #, Loan Amortization Period, Loan Amount)

Using these functions ensures that the total amount of Debt Service will be the same each year.

That’s fine here because the terms of the Debt are simple: there’s a fixed annual interest rate, fixed amortization period, no accrued interest (also known as PIK Interest ), and no interest-only period.

The Debt Yield equals NOI / Initial Debt Amount, the Interest Coverage Ratio equals NOI / Interest, and the Debt Service Coverage Ratio (DSCR) equals NOI / (Interest + Principal Repayments).

In real estate financial modeling, these metrics are important for both lenders (they indicate downside risk) and owners (they indicate Debt capacity).

Step 5: Make the Returns Calculations

The calculations are straightforward when there’s a fixed exit date in Year 5.

We calculate the returns on an unleveraged basis (as if no Debt were used, meaning no Debt Service and no principal repayment at the end, but a higher upfront purchase price) and a leveraged basis (the traditional method):

We calculate both types of returns to assess how dependent the deal is on leverage.

High dependency could be a red flag since leverage also hurts us if the deal goes poorly.

As always, the answer depends on the IRR our firm is targeting.

For “Core” deals, an Equity IRR of 8-10% is often the target, so by that metric, this deal is a clear “Yes.”

However, to answer this question in real life, we’d have to build Excel sensitivity tables or scenarios and examine other outcomes.

If the IRR drops to 0% with slightly more negative assumptions, then it might be a “No” decision since that indicates too much risk.

It’s also worth evaluating the operating assumptions to see whether or not they’re plausible – if not, then this could also be a “No” decision.

The Cap Rate assumptions seem OK since the Exit Cap Rate rises slightly, and the NOI increases at an annualized rate of around 5%, which is not crazy for a stabilized property.

One potential red flag, though, is that there are no assumptions for Leasing Commissions (LCs) or Tenant Improvements (TIs), even though there will be significant tenant turnover.

Multifamily properties tend to have low LCs and TIs because individual tenants have little bargaining power, but there’s usually at least something in these categories.

Deal Type #2: Real Estate Renovation Modeling

Real Estate Renovation Modeling is quite similar to Acquisition Modeling, and the basic steps in the process are the same.

The key difference is that something significant about the property changes during the holding period, and the owners spend something to enact this change.

For example, maybe they complete a major renovation that boosts a property from Class B to Class A, or they boost the Occupancy Rate from 70% to 90%, or they modify the ground floor of an office building and add retail units.

These differences translate into the following real estate financial modeling additions:

• Renovation Costs – These will reduce Cash Flow to Equity; you might also assume a higher upfront purchase price to cover these costs, depending on the timing.
• Penalty During the Renovation Period – For example, a hotel’s occupancy rate might drop as rooms become unavailable due to the renovation.
• Benefit Following the Renovation – For example, the occupancy rate or average rent might increase once the renovation is done.
• Permanent Loan Refinancing – There is often a loan refinancing as the renovation finishes and the property stabilizes, both to boost returns for the Equity Investors and to bring in a different set of lenders.
• Exit Assumptions – It is reasonable to assume a lower Cap Rate upon exit because the property should become more valuable as a result of the renovation.

We’re not going to cover a full renovation example because it’s not much different from acquisition modeling, and this article is already very long.

However, you can get a sense of how the cash flows differ by reviewing the Returns Calculations in a hotel renovation deal:

You can also see why the owners choose to refinance here: doing so at a higher LTV, based on higher NOI, generates around $50 million of Cash Flow to Equity in Year 2, boosting the IRR and Cash-on-Cash Multiple.

Deal Type #3: Real Estate Development Modeling

Real estate Development Modeling could be described as “startup meets leveraged buyout.”

It’s a bit like modeling a tech or biotech startup because you assume an asset gets created from the ground up , but you use both Debt and Equity to fund it – similar to a leveraged buyout.

That setup works because Equity must be contributed first .

Lenders will only sign onto deals once the investors/owners/developers have contributed sufficient Equity to pay for the initial costs – similar to how venture lenders operate .

The last steps in a real estate development model, such as the operating assumptions, pro-forma, and returns calculations, are similar to the ones in the acquisition model above.

The major differences occur in the first few steps because the “purchase price” is based on land and construction costs (not Cap Rates and NOI), Debt and Equity are drawn on over time (not all upfront), and the construction could take years to complete.

We’ll look at a simplified industrial development model here for a plot of land in Alberta, Canada:

We plan to purchase 18 acres of land, build a warehouse-like facility, and lease it out to two major tenants.

It will cost $12.6 million for the land, $16.9 million for the construction, and $600K for the Replacement Reserves just before tenant move-in, for a total of ~$30 million.

Once the tenants have moved in and the property has stabilized, we’ll sell the excess land and eventually sell the property itself.

First, we make assumptions about the construction start date, the plot of land, and the construction costs per gross square foot or gross square meter:

Next, we assume that a Construction Loan is used to fund part of the development costs.

As discussed in our coverage of commercial real estate lending , Construction Loans have higher interest rates than Permanent Loans, and interest is capitalized when the property is under construction:

The IRR hurdles here create a “ waterfall model ” because the deal’s overall performance changes the percentage that goes to the Developers vs. Investors.

In real estate development deals like this one, Developers often earn higher percentages when the deal’s IRR increases; the waterfall structure incentivizes them to perform well.

Finally, we set up a Sources & Uses schedule:

In the next step, we project the Construction Costs over the year required to build this warehouse (the cost distributions here come from an “instructions” document):

Initially, we draw on Equity to pay for the construction, but we switch to the Construction Loan once the maximum amount of Equity has been drawn ($15 million, roughly half the total costs):

The Debt balance must include both capitalized interest and capitalized loan fees – but we base the interest on the beginning balance each month to avoid circular references (see our tutorial on how to remove circular references in Excel ).

After setting this up, we extend these projections until the end of the Construction Period to get the ending Debt and Equity balances.

They do not represent an exact 50/50 split due to the capitalized loan fees and interest.

In more complex real estate financial modeling exercises, we fix this issue by making the entire calculation circular, but it’s not worth the time/hassle/headache in a quick model like this one.

The tenant-by-tenant projections are similar to the setup in the real estate pro-forma article: we assume a rent-per-square-foot figure for each tenant, an annual growth rate, and we use that to calculate the Base Rental Income.

Then, we calculate the Absorption & Turnover Vacancy in periods when tenants might cancel and leave space vacant, calculate Free Rent when new tenants move in, and factor in Expense Reimbursements.

We also calculate the TIs and LCs for each tenant based on the lease start dates and renewal dates.

With the tenant assumptions in place, we create the pro-forma:

The Debt Service is a bit trickier because we assume that the Construction Loan is refinanced with a Permanent Loan.

To determine the Permanent Loan amount, we need to estimate the property’s value when the refinancing takes place and then multiply its value by an LTV.

But the property is not yet stabilized when the refinancing takes places, so we retrieve its value one year after the refinancing and discount it back one year:

Next, we project the interest and principal repayments for this Permanent Loan.

There is only one tranche of Debt here, the interest rate is fixed, there’s no interest-only period, and there’s no capitalized interest, so we use the built-in IPMT and PPMT functions in Excel:

We can now calculate the IRR to Equity Investors based on their initial contributions, the refinancing, the annual cash flows, and the eventual sale of the property.

We’ll also factor in the fees associated with the refinancing, the sale of excess land, the costs associated with the sale of the property, the repayment of Debt upon exit, and prepayment penalties associated with that.

Here’s the calculation with an assumed exit in the final year:

We are simplifying this setup by pretending that the Equity Draws occur all at the end of the first year, not over the first several months of that year.

Also, this model does not support variable exit dates.

Past this point, we create a waterfall schedule to split up the cash flows to the Developers and Investors based on the overall Equity IRR.

We’re not going to cover that here, but please see our real estate waterfall model tutorial for a video walk-through of the rest of this same model.

It’s difficult to give a clear answer here because we have not examined the outcomes in different scenarios, such as longer/shorter construction periods, higher/lower construction costs, and Base/Upside/Downside market environments.

But if we’re targeting a 20% IRR, this deal seems like a “No” since the IRR to Investors is only 19% and the overall Equity IRR is just barely above 20%.

The excess land purchased in the beginning hurts us because it only appreciates by ~3% per year, and the waterfall structure also works against us because a 10% IRR hurdle for Tier 1 is low for a new development.

Some of the credit stats and ratios are also “iffy” in the first year following construction.

So, we would not recommend this deal as it is presented here, but we might be open to it if some of the terms changed and we could analyze the outcome in different scenarios.

Real Estate Financial Analysis: To Buy, or Not to Buy?

Real Estate Financial Modeling is simpler than normal financial modeling… in most cases.

That’s because the purpose is more limited: we don’t need 3-statement models, credit models, valuation, DCF models , merger models, or LBO models.

Also, revenue and expense projections do not differ as dramatically as they do for companies in different industries.

Most real estate financial models can be summarized by a slight variation on Shakespeare’s most famous quote:

“To buy, or not to buy?”

Should you acquire or develop a property at the stated terms?

Is it plausible to achieve the returns you are seeking, or would that require completely unrealistic assumptions?

In the worst-case scenario, would you lose money, or would you survive, even if the returns disappoint?

Real estate financial modeling gives you simple but effective methods for answering these questions and making investment decisions.

You might be interested in:

• Opportunistic Real Estate: An Undiscovered Gem, or Another WeWork Waiting to Happen?
• Value-Add Real Estate: What Makes It Different, and Why You Should Invest – Maybe
• Commercial Real Estate Lending : The Best Backdoor into the Finance Industry? 

About the Author

Brian DeChesare is the Founder of Mergers & Inquisitions and Breaking Into Wall Street . In his spare time, he enjoys lifting weights, running, traveling, obsessively watching TV shows, and defeating Sauron.

Free Exclusive Report: 57-page guide with the action plan you need to break into investment banking - how to tell your story, network, craft a winning resume, and dominate your interviews

Read below or Add a comment

11 thoughts on “ Real Estate Financial Modeling (REFM): The Ultimate Guide, With Templates & Examples ”

Hello Brian, My name is Ignacio and I’m writing this from Argentina. I enjoyed very much your article about Real Estate financial modeling. You see, I studied Economics, although I am currently shifting into Real Estate. I am looking for a course in REFM, perhaps a “certification” which is recognized in the industry. In this sense, I was wondering if you could recommend one, based on your knowledge and experience. I see there are lots of online platforms offering these type of courses and I’m trying to find one that is well acknowledged by employers and the industry, in general. I look fwd to hearing from you. Thank you for your attention and I appreciate your help. Best regards, Ignacio Antonio

We have our own Real Estate Financial Modeling course: https://breakingintowallstreet.com/real-estate-modeling/

Is it not the case that most loans are quoted with an interest rate assuming a 30/360 interest rate, as opposed to the annual effective interest rate you are using here? Interested as to why you are using (1+Interest rate)^(1/N)-1, when it seems that most loans in the industry would be quoted assuming (interest rate)*(30/360) or (/12).

Yes, some loans are quoted like that, but this is a minor point, and this is a simplified model where 30/360 vs. the calculation here does not matter.

I have a question on the IRR calculations in these models. I see this repeatedly.

IRR in excel assumes all equal periods, right? yet, every-time i see it, i see the acquisition equity as one cell, incoming rents for the 1st year in the 2nd cell. But these aren’t equal periods.

Since the 1st year is an equal period, shouldn’t the acquisition costs then be offset by the year 1 net income?

With the IRR calculation, all that matters is the *interval* between cash flows. The standard IRR function assumes 1 year between each cell. So as long as the cash flows arrive one year after the acquisition, on average, then it’s fine.

If your point is that the cash flows do not all arrive at the end of year 1, yes, that is true, but adjusting the cash flows to arrive midway through the year makes a very small difference and isn’t usually worth the effort. We do sometimes use the mid-year convention in DCF analyses, but usually not when calculating returns.

hey , what would have an example for the 4th strategy ? buying building and pre sell ??

No, but there is a development/opportunistic example here: https://mergersandinquisitions.com/opportunistic-real-estate/

Nice job Brian!

Wonderful write up! May Almighty always enlighten your journey called life. Stay Blessed

Just wanted to say I love this platform. To everyone who contributes to this, thank you for your existence!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Master Real Estate Modeling

Master financial modeling for real estate development and private equity with 11 global case studies based on property acquisitions, developments, and renovations.