Articles

Financial Modeling – DCF Model

Pinterest LinkedIn Tumblr

DCF stands for Discounted Cash Flow, a DCF model is used to estimate the value of an investment based on predicted cash flows. In other terms, it estimates the value of an investment on today’s date with projections of how much money it will generate in the future. It is one of the most widely used methods in financial modeling.

DCF

Even though the concept seems simple, there is a bit of technical background which is required for each of the components mentioned above. The fundamental building block of a DCF model is a 3 statement financial model (income statement, balance sheet, and cash flow statement). Following is an example of DCF model that will guide you to understand and enable you to create one yourself.

DCF Model

What is Unlevered Cash Flow?

Unlevered Free Cash Flow also known as Free Cash Flow is a cash flow available to all equity holders and debtholders after deducting operating expenses, capital expenditures, and investments in working capital. It is used in financial modeling to determine the enterprise value of a firm. In simple terms, it is cash available to equity holders and debt holders from business operations.

Why is cash flow discounted?

The cash flow that is generated by the business is discounted back to a particular point of time (thus the name Discounted Cash Flow ), usually to the current date. The reason why cash flow is discounted is due to opportunity cost and risk according to the theory of the time value of money. Time value of money is states that the value of money in the present is worth more compared to the same amount of money in future, as the money in the present can be invested, thus earning more money.

A company’s Weighted Average Cost of Capital (WACC) depicts the required rate of return looked forward by its investors. Consequently, it can be the thought of as a firm’s opportunity cost, which means if they cannot find a higher rate of return elsewhere, they must buy back their own shares.

In general, investors required rate relates to the risk of the investment (using the Capital Asset Pricing Model). Therefore, riskier the investment higher the required rate of return and the higher the cost of capital. The farther the cash flows are, the riskier they get, hence there arises a need for discounting them.

DCF model

How to build cash flow forecast in a DCF model

The idea behind this is the forecasting of business performance. In general, the forecast period for a DCF model would be 5 years excepting long life industries like mining, oil and gas, and infrastructure, where engineering reports can be used to build a long-term forecast.

Forecast the revenue

There are numerous ways to make a revenue forecast but are predominantly categorized into two main streams: growth-based and driver-based.

A growth-based forecast is easier to comprehend and appt for stable businesses, where a basic year-over-year growth rate can be used. This is sufficient for many DCF models

A driver-based forecast is detailed and difficult to develop. It requires bifurcation of revenue into numerous drivers, like price, volume, products, customers, market share, and external factors. Regression analysis is frequently used to ascertain the relationship between underlying drivers and top-line revenue growth.

Forecast the expense

Building an expense forecast could be a comprehensive and fine process, else it can be a simple as year-over-year comparison.

The most elaborated approach is called a Zero-Based Budget. It requires building up the expenses from scratch, ignoring what was spent last year. In general, each department within the company is asked to justify the expense they have, based on activity.

This approach is common in a cost-cutting environment, or in case of financial controls being imposed. It can be performed internally by the company and not by third parties like investment bankers or equity research analysts.

Financial Modeling - DCF Model 1

Forecast the capital assets

Once the income statement is partially complete, then it’s time to forecast the capital assets. Forecasting the capital assets commonly include balance sheet items like property plant and equipment (PP&E), technology, research and development (R&D), and working capital.

PP&E is one of the largest balance sheet item, and capital expenditures, as well as depreciation, need to be built in a separate schedule.  A thorough approach is to have a separate schedule in the DCF model for each of the key capital assets, and then compile them into a total schedule.

Forecast the Capital Structure

The way this is built will majorly depend on what type of DCF model you are building. The most common approach is to keep the company’s current capital structure in place, assuming no major changes other than things that are known, such as debt maturity.

Terminal Value

The terminal value is a way important part of a DCF model. It makes up more than 50% of the net present value (NPV) of the business, typically if the forecast period is five years or less.  There are two ways to calculate terminal value:- exit multiple approach and perpetual growth rate approach.

The perpetual growth rate approach assumes that the cash flow generated at the end of the forecast period grows at a steady rate forever. Example, the cash flow of the business is $ 10 million and grows at 2% perpetually, with a cost of capital of 15%.  The terminal value equals $10 million / (15% – 2%) = 77 million.

With the exit multiple approach, the business is assumed to be sold to a reasonable buyer who would pay for it. This means an EV/EBITDA multiple at or near current trading values for comparable companies.  For instance, if the business has 6.3 million of EBITDA and similar companies are trading at 8x then the terminal value is 6.3 million x 8 = 50 million. That value is then discounted back to the present to get the NPV of terminal value.

Financial Modeling - DCF Model 2

Timing of cash flow

It is vital to pay close attention to the timing of cash flows in a DCF model, as not all the time periods are necessarily equal. There is often a “stub period” at the beginning of the model, where only a fraction of the year’s cash flow is received. XNPV and XIRR are easy ways to be specific with the timing of cash flows while building a DCF model. The best practice is to use these over NPV and IRR function in Excel.

Sensitivity analysis in a DCF model

Once you are done with DCF model, It is time to apply sensitivity analysis to predict the outcome of the decision with a certain range of variables

To know more on Financial modeling click on the link as follows https://excelinexcel.in/ms-excel/articles/financial-modeling-basics/

Gain access to a free session on financial modeling, use the link: https://www.youtube.com/watch?v=G1YmhAbc40s

Related Posts

Write A Comment