Flevy Management Insights Q&A
How to create a discounted cash flow model in Excel?


This article provides a detailed response to: How to create a discounted cash flow model in Excel? For a comprehensive understanding of Cash Flow Management, we also include relevant case studies for further reading and links to Cash Flow Management best practice resources.

TLDR Creating a DCF model in Excel involves projecting free cash flows, determining WACC, calculating terminal value, and structuring inputs, calculations, and outputs for strategic financial decisions.

Reading time: 4 minutes

Before we begin, let's review some important management concepts, as they related to this question.

What does Financial Forecasting mean?
What does Weighted Average Cost of Capital (WACC) mean?
What does Sensitivity Analysis mean?


Creating a discounted cash flow (DCF) model in Excel is a critical skill for C-level executives looking to evaluate investment opportunities, assess business valuations, or make strategic financial decisions. This process involves forecasting the free cash flows of an organization and discounting them back to their present value. This guide provides a step-by-step approach to building a robust DCF model in Excel, leveraging industry best practices and consulting frameworks.

First, gather historical financial data and project future cash flows. This step involves analyzing income statements, balance sheets, and cash flow statements to identify trends and drivers of revenue and expenses. Consulting firms like McKinsey and Bain emphasize the importance of a granular approach to forecasting, recommending segmenting revenues and costs for more accurate projections. In Excel, create a five-year forecast (or more, depending on the business cycle and industry) of free cash flows (FCF), which are typically calculated as EBIT(1-Tax Rate) + Depreciation & Amortization - Capital Expenditures - Changes in Working Capital.

Next, determine the appropriate discount rate to apply to these cash flows. The Weighted Average Cost of Capital (WACC) is commonly used as the discount rate, reflecting the average rate an organization pays to finance its assets. Calculating WACC requires determining the cost of equity and the cost of debt, weighting them by their respective proportions in the organization's capital structure. Consulting firms such as EY and PwC provide frameworks and calculators that can assist in accurately determining an organization's WACC. Input this rate into your Excel model to discount the projected free cash flows back to their present value.

Finally, calculate the terminal value, which represents the value of the organization's cash flows beyond the forecast period into perpetuity. The Gordon Growth Model, which assumes a constant growth rate in perpetuity, is a common method for calculating terminal value. This involves taking the final year's projected FCF, multiplying it by (1 + perpetual growth rate), and dividing it by (WACC - perpetual growth rate). Add this terminal value to the sum of the discounted cash flows to arrive at the total enterprise value of the organization.

Building the Excel Framework

To structure your DCF model in Excel, start by setting up separate sections for inputs, calculations, and outputs. Inputs will include historical financial data, assumptions for future growth rates, WACC, and perpetual growth rates. The calculations section will house your FCF projections, discount factors, and terminal value calculations. Outputs will present the total discounted cash flows and terminal value, culminating in the enterprise value.

Use Excel functions such as NPV (Net Present Value) for discounting cash flows and XNPV for more precise calculations that account for irregular time periods. The PV (Present Value) function is useful for calculating the terminal value. Ensure your model is dynamic by using cell references for inputs, allowing you to easily adjust assumptions and instantly see the impact on the organization's valuation.

For accuracy and credibility, cross-verify your model's outputs with real-world examples and benchmarks from consulting and market research firms. Incorporating sensitivity analysis by using Excel's Data Tables feature can provide insights into how changes in key assumptions impact the valuation. This analysis is crucial for strategic decision-making, enabling executives to understand potential risks and returns under different scenarios.

Are you familiar with Flevy? We are you shortcut to immediate value.
Flevy provides business best practices—the same as those produced by top-tier consulting firms and used by Fortune 100 companies. Our best practice business frameworks, financial models, and templates are of the same caliber as those produced by top-tier management consulting firms, like McKinsey, BCG, Bain, Deloitte, and Accenture. Most were developed by seasoned executives and consultants with 20+ years of experience.

Trusted by over 10,000+ Client Organizations
Since 2012, we have provided best practices to over 10,000 businesses and organizations of all sizes, from startups and small businesses to the Fortune 100, in over 130 countries.
AT&T GE Cisco Intel IBM Coke Dell Toyota HP Nike Samsung Microsoft Astrazeneca JP Morgan KPMG Walgreens Walmart 3M Kaiser Oracle SAP Google E&Y Volvo Bosch Merck Fedex Shell Amgen Eli Lilly Roche AIG Abbott Amazon PwC T-Mobile Broadcom Bayer Pearson Titleist ConEd Pfizer NTT Data Schwab

Best Practices and Common Pitfalls

While building your DCF model, adhere to best practices such as clearly labeling assumptions, using consistent formulas, and organizing your spreadsheet for easy navigation. Consulting veterans recommend keeping models as simple and transparent as possible, avoiding overly complex calculations that can obscure understanding and review.

A common pitfall in DCF modeling is over-optimism in forecasting future cash flows and growth rates. It's essential to base your projections on realistic, defendable assumptions and to consider industry and economic cycles. Consulting giants like McKinsey often highlight the importance of conservative estimates and the inclusion of scenario analysis to test the robustness of your model.

Another challenge is accurately determining the discount rate. A slight variation in WACC can significantly impact the valuation, making it critical to use current market data and industry benchmarks. Regularly updating your model to reflect changes in the financial landscape ensures that your valuation remains relevant and reliable. In conclusion, mastering how to make a discounted cash flow model in Excel is indispensable for C-level executives involved in strategic planning, investment analysis, and financial management. By following a structured framework, leveraging consulting insights, and adhering to best practices, you can develop a powerful tool for informed decision-making.

Best Practices in Cash Flow Management

Here are best practices relevant to Cash Flow Management from the Flevy Marketplace. View all our Cash Flow Management materials here.

Did you know?
The average daily rate of a McKinsey consultant is $6,625 (not including expenses). The average price of a Flevy document is $65.

Explore all of our best practices in: Cash Flow Management

Cash Flow Management Case Studies

For a practical understanding of Cash Flow Management, take a look at these case studies.

Cash Flow Management for Boutique Hospitality Firm

Scenario: The organization is a boutique hospitality chain with a footprint in urban and exotic locales, facing liquidity constraints amidst post-pandemic recovery.

Read Full Case Study

Sustainable Growth Strategy for Textile Mills in Southeast Asia

Scenario: A prominent textile mill based in Southeast Asia is grappling with challenges in cash flow management due to increased raw material costs and fluctuating demand.

Read Full Case Study

Cash Flow Enhancement in Renewable Energy Sector

Scenario: The organization is a mid-sized player in the renewable energy sector, grappling with the challenge of managing cash flow amidst fluctuating government incentives and subsidies.

Read Full Case Study

Comprehensive Cash Flow Management Reform for Retailer

Scenario: A multinational retail organization has experienced significant profit reduction due to challenges in Cash Flow Management.

Read Full Case Study

Operational Excellence Strategy for SMB Wellness Centers in North America

Scenario: An established SMB wellness center chain across North America is facing challenges in Cash Flow Management due to a 20% decline in customer retention and a 15% increase in operational costs over the past two years.

Read Full Case Study

Strategic Growth Plan for Professional Services Firm in Digital Transformation

Scenario: A mid-sized professional services firm specializing in digital transformation solutions faces significant challenges in cash flow management, directly impacting its ability to scale and innovate.

Read Full Case Study

Explore all Flevy Management Case Studies

Related Questions

Here are our additional questions you may be interested in.

What role does corporate culture play in enhancing cash flow management across all levels of the organization?
Corporate culture significantly enhances Cash Flow Management by fostering financial discipline, encouraging collaboration and cross-functional synergy, and embracing technology and innovation for operational efficiency and strategic investment. [Read full explanation]
How can the alignment between sales and finance departments be improved to enhance cash flow?
Improving Sales and Finance alignment involves Strategic Alignment, leveraging Integrated Systems and Processes, and adopting a Unified Approach to Customer Management to drive cash flow improvement. [Read full explanation]
In what ways can companies better leverage data analytics for predictive cash flow management?
Companies can enhance predictive cash flow management by integrating Advanced Analytics and AI into forecasting, leveraging Predictive Analytics for decision-making, and linking Performance Management to operational activities for improved financial health. [Read full explanation]
How to manage petty cash book using Excel?
Use Excel to create a well-designed petty cash template, automate calculations, implement data validation, and restrict access for efficient financial management. [Read full explanation]
How can executives integrate cash flow management into the company's strategic planning process more effectively?
Integrating Cash Flow Management into Strategic Planning ensures financial stability and operational resilience, involving cross-functional teams, technology, and alignment with strategic objectives for informed decision-making and risk mitigation. [Read full explanation]
What impact do emerging payment technologies have on traditional cash flow management strategies?
Emerging payment technologies are reshaping Cash Flow Management, necessitating the adoption of advanced digital solutions, dynamic forecasting models, and robust Risk Management practices to enhance Operational Efficiency, reduce costs, and drive Innovation for Competitive Advantage. [Read full explanation]

Source: Executive Q&A: Cash Flow Management Questions, Flevy Management Insights, 2024


Flevy is the world's largest knowledge base of best practices.


Leverage the Experience of Experts.

Find documents of the same caliber as those used by top-tier consulting firms, like McKinsey, BCG, Bain, Deloitte, Accenture.

Download Immediately and Use.

Our PowerPoint presentations, Excel workbooks, and Word documents are completely customizable, including rebrandable.

Save Time, Effort, and Money.

Save yourself and your employees countless hours. Use that time to work on more value-added and fulfilling activities.




Read Customer Testimonials



Download our FREE Strategy & Transformation Framework Templates

Download our free compilation of 50+ Strategy & Transformation slides and templates. Frameworks include McKinsey 7-S Strategy Model, Balanced Scorecard, Disruptive Innovation, BCG Experience Curve, and many more.