The ABCs of DCF Valuation & Modeling

A simple step by step guide to DCF valuation and modeling.

Table of Contents

Who is this book for?
How to use this book

Chapter 1: Key Concepts in DCF Modeling

1.1 Why Value a Business?
1.2 Different Approaches To Valuation
1.3 What Creates Value in a Business?
1.4 Key Principle Driving DCF Valuation
1.4.1 Cash flows
1.4.2 Free Cash Flow To The Firm
1.4.3 Free Cash Flow To Equity
1.5 Lifetime of a Business
1.5.1 Two-Stage Model
1.5.2 Three-Stage or Multi-Stage Models

Chapter 2: Building a DCF Model

2.1 Ingredients Required for a DCF Valuation Model
2.2 Components of a DCF Model
2.3 Planning & Structure
2.4 Build Order

Chapter 3: Assumptions that Drive the Model

3.1 Historical Financial Statements: Income Statements and Balance Sheets
3.2 Historical Financial Ratios and Performance Drivers
3.3 Assumptions that Drive the Projected Financial Statements
3.3.1 Income Statement Assumptions
3.3.2 Balance Sheet Assumptions

Chapter 4: Operating Cash Flows

4.1 Revenues
4.2 Expenses
4.2.1 Expenses Detailed (Option 1)
4.2.2 Operating Margin (Option 2)
4.3 Income taxes and Operating Income
4.4 Operating Cash Flow vs. Free Cash Flow

Chapter 5: Forecasting Free Cash Flow

5.1 Need for Reinvestments
5.2 Working Capital
5.3 Capital Expenses
5.4 Others
5.5 Free Cash Flow Projections

Chapter 6: Discount Rate / WACC

6.1 What are Discount Rates
6.2 The Appropriate Discount Rate
6.3 Weighted Average Cost of Capital
6.3.1 Market Values vs. Book Values
6.4 Value of Debt
6.5 Value of Equity
6.6 Weight of Debt and Equity
6.7 Cost of Debt
6.8 Cost of Equity
6.8.1 Capital Asset Pricing Model
6.9 Weighted Average Cost of Capital Formula

Chapter 7: Valuing the Firm

7.1 Value of a Firm
7.2 Present Value of Cash Flows of the Forecast Period
7.3 Present Value of Cash Flows of the Horizon Period
7.4 Present Value of a Perpetuity
7.5 Present Value of a Growing Perpetuity
7.6 Horizon value in the DCF Model
7.7 Firm Value
7.8 Value of Debt and Equity
7.9 Intrinsic Value of a Share

Chapter 8: Presentation & Formatting

8.1 Formatting Guidelines
8.2 Other Formatting Options

Chapter 9: Additional Features

Table of Contents

The ABCs of DCF Valuation & Modeling

Preface

Who is this book for?

This book, The ABCs of DCF Valuation & Modeling is for anyone interested in understanding and using the discounted cash-flow valuation method to value assets. Although primarily written for MBA and undergraduate business students, anyone interested in understanding discounted cash-flow valuation will find this book useful.

This book is an introductory level book. As the title indicates, with the word ‘ABCs’, this book is meant for beginners trying to understand and build a DCF valuation model in Microsoft Excel or Google Sheets. If you are looking for an advanced book on valuation, we recommend McKinsey & Company’s book ‘Valuation: Measuring and Managing the Value of Companies’ written by Tim Koller, Marc Goedhart and David Wessels or ‘Investment Valuation: Tools and Techniques for Determining the Value of Any Asset’ written by Professor Aswath Damodaran.

This book assumes that the reader is familiar with basic financial accounting and corporate finance concepts. For example, the reader is expected to know the structure of an income statement, the meaning of the term “working capital”, etc. We will cover some of these concepts only briefly as the focus is on the DCF valuation model. The reader is NOT expected to be an expert in Microsoft Excel or Google Sheets but must be reasonably familiar with these tools.

This book will teach you how to build a simple DCF model. Once you can confidently build a simple DCF model, you can add many bells and whistles to make your model more detailed. Our focus is on helping you understand the foundations of building a good DCF model.

The ABCs of DCF Valuation & Modeling is based on Senith Mathews’ experience tutoring students and executives in DCF modeling over 10 years and building models as a management consultant with Arthur Andersen and Mercer Management Consulting (now Oliver Wyman). The ABCs of DCF Valuation & Modeling narrowly focuses on teaching readers how to build a basic DCF model in a spreadsheet. It does not go in depth into the underlying finance and accounting concepts or rules.

Table of Contents

How to use this book

The ABCs of DCF Valuation & Modeling is built on our modeling boot camp experience. So it is best you adopt a hands-on boot camp attitude as you read this book. This will help you maximize your learning from this book. You should be confident of building a basic DCF model on your own in an hour after working through this book. You should read this book at least twice to really understand how to build a basic DCF model.

Your first read is meant to give you a quick overview of the entire process. You should follow the model spreadsheet as you progress through the book. Read each section one by one. Once you have read a section, review the corresponding region in the Microsoft Excel model. Change the relevant input assumptions to see how that section changes. Dig into each cell of the Microsoft Excel model row by row so you understand the formulae and relationships section by section.

Your second read is when you should open the blank workbook and build the exact DCF valuation model yourself. We recommend that you re-read the book from the beginning. As you re-read each section, re-create that section in a blank workbook. Begin by setting up the same input assumptions and build the projected financial statement section by section. Check each section of your model and proceed to the next section only after you are sure it reflects the sample model. Your final model should reflect the sample model when you are done. Change the relevant input assumptions in your model to check that the model returns reflect the changes throughout the entire model.

Building a DCF valuation model from scratch should give you the confidence to build a DCF valuation model on your own. Now you are ready to value a business or company using the DCF valuation method. Don’t stop there. Practice on real companies. We recommend that you work on these valuation exercises with a friend or a tutor so you get instant feedback. Good and timely feedback boosts your learning process tremendously.

Table of Contents

Chapter 1: Key Concepts in DCF Modeling

I could open a Microsoft Excel file or Google Sheet and start teaching you Discounted Cash Flow (‘DCF’) modeling.  But that would not be of much help! You cannot value a business or an asset without understanding the core principles underlying the DCF valuation. So I set the stage for DCF modeling in this chapter by briefly covering the key concepts underlying DCF valuation.

1.1 Why Value a Business?

The primary goal of a company’s management team is to increase the value of the company. The management team cannot know if they are going in the right direction without valuing the business!

There could be many other reasons to value a business. The shareholders and the board of directors need to know if the value of the business is increasing or decreasing to evaluate the performance of the management. Anyone looking to buy or sell a business or company needs to understand how to value a business so they can set or pay a fair price. You will also need to value a business to issue and account for employee stock options, to raise funds, to meet regulatory reporting requirements, etc. Therefore, we must know how to value a business.

1.2 Different Approaches To Valuation

There are three different approaches to valuation. The simplest and quickest method is the relative valuation method. This method is also referred to as valuation using multiples. The relative valuation method arrives at valuation by comparing the business to similar businesses. This method identifies a value driver and values the business as a multiple of the driver. We do not address the relative valuation method in this book.

The second method is the options approach to valuation.  Here the business’s equity and debt are valued as if they are put or call options and valued as options. This too is not the focus of this book and is covered in more advanced books and courses.

Multiple approaches to Valuation
There are three approaches to valuation!

The third approach to valuation is the DCF method of valuation. The value arrived at using the DCF method is also known as the intrinsic value because the valuation is based on the intrinsic properties of the business to generate cash flow.  This book focuses on the DCF method of valuation.

1.3 What Creates Value in a Business?

Think about this for a few minutes. What creates value in a business? This is important. Take a few minutes now to list out what you believe creates value in a business. Write them down on a sheet of paper or notepad.

.

.

.

.

.

.

.

.

.

.

I hope you have not zoomed to this sentence! But instead, come up with a list of factors that create value in a business.

Three factors create value in a business:

Cash flows

Cash generated by a business or an asset creates value for the asset’s owners. The cash produced allows the owner of the business or asset to live the life the owner wants to lead. Therefore, if an asset generates a lot of cash flow, investors will pay a lot of money to own the asset because they value it highly. On the contrary, if an asset gives only a little cash flow, investors will pay only a little money to own the asset because they do not value it highly.

Timing

The timing of cash flow is important and significantly impacts value. Cash on hand now is worth a lot more than cash in the future. This difference in value is due to the impact of inflation, opportunity cost and risk and is captured by the term ‘time value of money’. This concept is a fundamental building block of finance theory. We expect that you are already familiar with this concept and do not deal with this topic in this book. You can read more about the time value of money here.

Certainty

The certainty or uncertainty of cash flow is a major driver of value. The essence of this driver of value is captured by the word ‘risk’ in finance. A stream of cash flow that is certain will be valued higher than a cash flow that is less certain, all other things being equal.

1.4 Key Principle Driving DCF Valuation

The above three factors that create value in a business lead to the key principle of valuation:

The value of any asset is equal to the sum of the present values of all future cash flows.

What drives the value of any asset? Future Free cash flows
What drives the value of any asset?

 This is the most important concept to grasp before building a DCF valuation model. Every word in this definition is important. So, we will discuss each of these concepts briefly here.

1.4.1 Cash flows

We have established that cash flows are a key driver for value creation. What exactly do we mean by cash flows in a DCF valuation?

Essentially cash flows refer to the cash generated in the business during a specific period after meeting ALL business obligations.  This cash flow is often referred to as ‘free cash flow’ indicating that the business has met all its obligations (operating payments, investments in working capital and capital expenditure). It also means that the business is free to do whatever it pleases with this cash flow including returning it to its owners.

The term cash flow confuses many students because this term is loosely used in a variety of settings in the business world. Understanding what free cash flow does not mean will help you understand what it does mean. So, here are a few settings where the terms cash and cash flow DO NOT represent what the DCF valuation principle considers cash flow in the DCF valuation context.

  • Free cash flow is NOT the cash left over at the end of a period. The cash at the end of a period is what is reflected as cash in the balance sheet. The cash reflected in the balance sheet could have been generated over many periods and/or may have been raised as debt or new equity and is not the cash flow generated by the business during a specified period.
  • Free cash flow is NOT the cash flow found in the Statement Of Cash Flows that are presented in a company’s financial statements. The Statement Of Cash Flows classifies cash flows for the year into three parts: cash flows from operating activities, cash flows from investing activities and cash flows from financing activities.
  • Free cash flow does NOT equal the operating cash flow. We must consider the reinvestment needs of the business before we consider operating cash flows to be free cash flows.

The above categories are not what we will use as cash flows in a DCF valuation context. The two types of free cash flow that are used in the context of valuation are:

  • Free cash flow to the firm; and
  • Free cash flow to equity.

Both these types of free cash flows are used in the context of valuation. However, there are situations where one may be appropriate and the other inappropriate. So, it is important to understand what each of these types of cash flows mean and when it is appropriate to use them.

1.4.2 Free Cash Flow To The Firm

The free cash flow to the firm is the cash flow available to the firm before any payments are made to the providers of capital (both debt and equity). This is the free cash flow to the firm assuming that the business is fully funded by equity. Equivalently, we can say that this is the free cash flow to the firm before any interest and debt repayments have been made even if the firm is funded by equity and debt.

The free cash flow to the firm is used when we want to value the entire firm – as opposed to valuing only the equity. It will be inappropriate to use the free cash flow to value equity when you are trying to value the entire firm. 

Firm value = value of Equity + value of Debt
Firm value = value of Equity + value of Debt

1.4.3 Free Cash Flow To Equity

Free cash flow to equity is the cash flow remaining after all obligations including any interest and debt repayments have been fulfilled. We take the free cash flow to the firm and subtract the debt repayments and interest payments to get the free cash flow to equity.

The free cash flow to equity is used when we want to value the equity of the firm only – as opposed to valuing the entire firm.

Please note that in this DCF model, we will be using the free cash flow to the firm and not the free cash flow to equity as we want to value the entire firm first.

1.4.4 All

The word ‘all’ in the DCF definition may be a small word but is a significant part of the concept. The word ‘all’ emphasizes that all cash flows must be considered and not just cash flows generated from its operations. These include one-time or recurring and direct or indirect or ancillary cash flows and benefits. Examples of what must be included in cash flows for DCF valuation are investments, maintenance expenses, cost savings, savings as a result of synergies, cannibalization of current cash flows, taxes, restoration or clean-up costs, sale of assets, etc.

1.4.5 Future

When valuing an asset or a firm, only future cash flows are considered. The cash flow generated in the past is irrelevant. This seems to be a simple point to understand but many professionals fail to apply this concept in practice. It is especially important to avoid considering sunk costs and irrelevant costs.

1.4.6 Present Value

The term present value indicates that the valuation must be in today’s terms. We understood from the previous paragraph that it is the future cash flows that contribute to the value of an asset. However, since the value of money received in the future is different from the value of money today, we must adjust the value of future cash flows to arrive at the value in today’s terms, which is also referred to as present value. If you are reading a book on discounted cash flow valuation, I assume and believe that you would have covered the concept of present value in one or more basic finance courses or books. So, we do not discuss this concept in detail.  If you need to refresh your understanding of this topic, you can review the ‘present value’ concepts here (https://www.graduatetutor.com/corporate-finance-tutoring/time-value-of-money/).

1.4.7 Sum

The final step in a DCF model is to sum up the present values of all future cash flows. This has at least two components -the present value of the cash flow during the initial few years which is called the forecast period and the present value of the cash flows of the period after the forecast period which is also referred to as the terminal period. We will discuss this more in the relevant section in Chapter 7.

1.5 Lifetime of a Business

A company is incorporated as per the corporate laws of the land. When incorporated, it becomes a separate legal entity by itself and continues to function as a separate legal entity unless it is intentionally shut down in accordance with the corporate laws of the land. Since the company is a separate entity and lives on until it is shut down, it theoretically has an infinite lifespan. The owners and managers of the company may come and go but the company theoretically can live forever. 

This poses a problem for us: Since we agree that the value of a business is the sum of the present values of all future cash flows, the fact that a company can live on forever presents a problem. How many years of cash flow can we project with reasonable accuracy? Even predicting 3 to 5 years of cash flow is a challenge. Predicting anything beyond 3 to 5 years is difficult. We overcome this problem by breaking up the cash flow projections into two or three stages. This is why DCF models are sometimes referred to as two-stage or three-stage models.

1.5.1 Two-Stage Model

In a two-stage model, the first stage is the near term and often referred to as the forecast period. This can be the next three, five or ten years. We will forecast the operating performance of the business in detail and arrive at the cash flows of the business for each year of this period.

In a two-stage model, the second stage is the entire period after the first stage. This stage is theoretically never-ending because the life of the company can go on forever or up to infinity! Finding the present value of the cash flows of this period would have posed a challenge if it weren’t for brilliant mathematicians we are fortunate to have. These mathematicians have provided us formulae to compute the present values of perpetual streams of cash flows.  We assume that cash flows in the second stage, also called the terminal or horizon stage, are constant or grow at a low constant rate. We will address how to compute the present values of this stage in Chapter 7.

1.5.2 Three-Stage or Multi-Stage Models

In today’s technology and internet-enabled world, some rapidly growing companies grow at unearthly rates of 50%, 100% and even 200% a year during the first few years.  These tremendous growth rates are not sustainable for long periods. These high growth rates usually drop off in a few years and reach more earthly rates of 10%, 15%, 20% or even 30% which are still fantastic growth rates for most companies. Eventually, even these companies become mature, face competition and encounter obstacles and over time slow down to grow at historical rates.

Therefore, if you are trying to value a rapidly growing company, a three-stage or even a multistage model is the way to go. The first stage may have the companies grow at unearthly speeds of 50%, 100% or 200% a year. The second stage could have the companies grow at more earthly rates of 15%, 20% or even 30%. The third stage could have the growth rates decline steadily to terminal or horizon stage growth rates. We assume that cash flows in the final and last stage, also called the terminal or horizon stage, are constant or grow at a small but constant rate. We will address how to compute the present values of this stage in Chapter 7.

If you have got this far, you have the basic concepts that are required to build a DCF valuation model. We will move on to building the DCF model in the next chapter.

Table of Contents

Chapter 2: Building a DCF Model

Chapter 1 covered foundational concepts on which the DCF valuation model is built. You are now ready to start building your DCF valuation model. In this chapter, we discuss the ingredients required for a good DCF valuation model, the different components needed, the structure and the build order when building a DCF valuation model.

2.1 Ingredients Required for a DCF Valuation Model

The key ingredients required to build a DCF valuation model are:

  • Historical income statements;
  • Historical balance sheets;
  • A good understanding of the business’s operating characteristics; and
  • Management plans for the immediate future.

Historical income statements and balance sheets provide a good base on which future performance drivers can be assumed or predicted. A good understanding of the business model and its operating characteristics such as the levels of inventory required, credit policies, etc. will help better estimate future performance drivers. Management plans for the immediate future are very important especially if expansion, new investments or changes in strategy or financial policy are expected because these impact projected cash flows.

Most of the ingredients required to project cash flows required for a DCF valuation can be found in the company’s annual reports or in the company’s Form 10Ks. The section titled Management Discussion and Analysis (MD&A) in a company’s Form 10K is a good starting point.  Information about management plans can be found in industry reports, earnings calls and media reports.

2.2 Components of a DCF Model

A DCF valuation model must have the following components. This is a basic model. Your model could have many more components, but it should include the components listed below.

  • Historical performance figures
  • Projected performance drivers (assumptions)
  • Cash flow projections
  • Valuation
  • Supporting schedules
    • Working capital projections
    • Cost of capital computations

We have omitted many components to keep our model simple. Chapter 9 lists out some additional components you can add if needed. You should add them if your situation or case requires it.

What are the components of a DCF model?
What are the components of a DCF model?

2.3 Planning & Structure

You can structure your DCF model in many ways. What is important is that you should have all the important components in the model and that the model must be intuitive to follow. An intuitive model is especially important if it is going to be reviewed by others.

You can have a separate tab for each of the above components or you can have the entire model in one single tab. The best way to structure the model will depend on the complexity of the model, the specifics of your situation and personal preferences. If you are modeling a simple DCF valuation model with few supporting schedules, a one-page model may be enough. However, if you want to model many nuances and need many schedules, a model with multiple tabs may be a better way to proceed. We use a one-page model template because we are modeling a simple DCF model. You are welcome to structure your model any way you prefer.

A DCF Valuation model full picture
A DCF Valuation model’s full picture

We have laid out the prior year’s financial statements on the left and the forecasted years’ figures on the right. We have laid out the input assumptions on the top where they are most visible. The supporting schedules are at the bottom.

We will discuss each component of the model in detail in the next chapters.

2.4 Build Order

While you can build the DCF valuation model in any order, we recommend that you build the DCF model in a sequence that makes sense. Here is the sequence we recommend:

  1. Gather historical income statements and balance sheets
  2. Compute historical performance ratios to understand the business
  3. Forecast performance drivers/assumptions based on your understanding of the business and management plans
  4. Build out projected operating statements structure
  5. Forecast working capital requirements & Capex if required
  6. Forecast free cash flow to the firm
  7. Estimate the weighted average cost of capital (WACC)
  8. Value the firm, equity and/or a share, as required.
Build order for a DCF model
What is the sequence in which you build the components of a DCF model?

We now jump into building a DCF valuation model in the next chapter with this foundation in place. Please download the model using the link provided in the next chapter. You are going to start building the model.

Table of Contents

Chapter 3: Assumptions that Drive the Model

We will now start building a simple DCF model in Microsoft Excel or Google spreadsheets. In this chapter, we will compile and analyze the historical performance of the company. We will then use the historical performance and our understanding of management plans to make the assumptions required to estimate the cash flows over the next few years.

Please download the sample model using this Microsoft link or Google Sheet here.  Please email us at care@graduatetutor.com if you have any issues downloading the model.

Follow the model spreadsheet as you progress through the book. Read each section one by one. Once you have read a section, review the corresponding region in the Microsoft Excel model. Change the relevant input assumptions to see how that section changes and what impact it has on the final output figures. Dig into each cell of the Microsoft Excel model row by row so you understand the formulae and relationships section by section. Make notes if required. During your second review, please open a blank workbook and build this exact model yourself from scratch. This will help you spot errors quickly because your output should be the same as the one in our model and book. Once you have replicated this financial model, you will be ready to pick a company that you would like to study and value it using the DCF valuation method by following these steps.

3.1 Historical Financial Statements: Income Statements and Balance Sheets

The very first step in valuing a company using the DCF method is to understand the business. We analyze the historical financial statements of the company or organization – primarily the income statement and balance sheet – to understand the business. We need the financial statements of at least the last 3 years to get an understanding of the business. It would be great to have the historical financial statements for the last 5 or 10 years to get a better understanding of the business. The income statement and balance sheet will tell us what the business model is like, what are the major items of expenditure and their relative sizes, etc.

We compile or export the historical income statement and balance sheet into Microsoft Excel or Google Sheets. For our model, we have used the last 3 years of historical financial data to understand the business. Please note that we indicate the historical figures in green-colored font and assumptions that drive the projected financial statements in blue-colored font to easily distinguish them. All figures computed in the model based on historical figures or assumptions are in black-colored font.

Historical values, forecasted values and assumptions
Historical values, forecasted values and assumptions.

3.2 Historical Financial Ratios and Performance Drivers

Once we have the prior year’s income statement and balance sheet in a spreadsheet, we compute select operating and financial ratios to understand the business model. This is the foundation on which we will make assumptions that drive the forecasted cash flows.

We first study year over year sales or revenue growth over the last few years. The sales or revenue growth assumption is, arguably, the single most important assumption of the DCF valuation model because almost all the other numbers in the model are based on the sales figures for the year. Therefore, it is very important to understand the historical trends and the factors that drive future revenue growth and arrive at a good estimate. This assumption is a significant driver of the DCF value or intrinsic value of a business. The growth in revenues during the forecasted years is driven not only by the historical trend but also by many other factors including management plans, current products’ life cycle stages, industry developments, etc. Management plans could include announcements on geographical expansion or new product development, new capacity investments, etc. But the historical trend is the starting point for this decision and has a significant influence on the value of the business.

The operating expenses are studied to understand what drives the operating expenses. For a simple financial model, the operating expenses can be considered a percentage of sales. Expenses can also be a function of headcount, number of locations, number of customers, etc.

The historical balance sheet is similarly analyzed to understand what drives the various operational metrics such as receivables, inventory, payables, long-term assets and other investments.

3.3 Assumptions that Drive the Projected Financial Statements

The most important inputs in any financial model are the various assumptions made regarding future performance. These assumptions drive the future operating and financial figures. These in turn drive the future cash flows and therefore the intrinsic value of the business. The accuracy of any model’s output will depend on the quality of the input assumptions. The garbage in, garbage out (‘GIGO’) concept in computer science applies here. If your input assumptions are poor, the output and valuation will also be poor. The assumptions that drive the next few years’ performance are best derived from the prior year’s income statements and adjusted for new information, policy changes, etc.

It is best to have all the inputs and assumptions laid out in one clearly indicated section. All the input variables must be clearly indicated by using blue for the font color. We will address this in more detail in the formatting chapter. I also recommend adding comments in cells and/or footnotes, so the source of the information is documented where applicable.

The input assumptions can be categorized into two sections: 1) income statement assumptions and 2) balance sheet assumptions. Note that we need pieces of information from the balance sheet to project the cash flows from the business even though we do not need to build the entire balance sheet. These two sections are discussed in detail below.

3.3.1 Income Statement Assumptions

Revenue Growth Assumption: The first and the most important assumption is the one about the revenue growth over the next few years. The revenue growth assumption should be based on a mix of factors. These factors include recent historical growth of the firm, industry expectations and specific plans or strategies of the buyer group. The drivers of revenue will be different for different firms and industries. Some firms have only one line of business whereas some firms have multiple lines of businesses with different growth rates. Your financial model must reflect the different sources of revenue of the appropriate firm.

The revenue growth rate can be constant over the entire forecast period or can be modified year by year based on the buyer’s plans. Management’s plans could include the introduction of new products or services or the elimination of divisions, etc. For example, if the firm is planning to introduce a slew of new products in the second year, the growth will be higher in the second year as compared to the first year and if they plan to drop a service line in the fourth year, revenue growth will be slower that year.

Since we are building a simple financial model, we assume that the target firm has only one line of business. We assume that the revenues grow at a rate of 10% over the previous year in the first year (cell G4). We also assume that the firm will have the same revenue growth rate for the first five years of the forecast period (cells H4 to K4). We then drop the growth rate by one percent between year 6 to year 10 so it tapers down to 5% by year 10 (cells L4 to P4). You can have different growth rates for each year in your model.

This is a three-stage model since we have three stages – 1) a steady growth of 10%, 2) a tapering off of growth from 10% to 5% and 3) a terminal period or horizon period growth of 3%.

What are the assumptions that drive a DCF model?
What are the assumptions that drive a DCF valuation model?

Operating Expenses: We estimate the various costs of operating the business after the revenue growth assumptions are made. Costs are broadly classified into the cost of goods and services (CoGS), sales and general administration expenses (SG&A), research and development costs (R&D), depreciation and amortization expenses (D&A) and taxes. Each of these costs is driven by different factors in different industries. A good financial model considers the different cost drivers and models each cost as a function of its cost drivers.

We have assumed for simplicity that all our operating expenses are driven by the size of our revenues and so our operating costs are modeled as a percentage of revenues. The exception is income tax expense which is a function of the earnings before taxes and the tax rate. Please note that we do not include interest expense as we are valuing the entire firm in our model.  We have laid out these assumptions in rows five to nine. Please note that these expenses as a percentage of revenue assumption (blue font color) are often the average of the historical figures (green font color) or very close to it.

You have the assumptions to model the income statement if you have the assumptions for revenue growth and operating expenses in place. We now move on to the assumptions required to compute the few balance sheet ingredients required for a DCF valuation model.

3.3.2 Balance Sheet Assumptions

Working Capital Assumptions: The cash required to run the business on a day to day basis is referred to as its working capital. A business may not get paid for its products and services when an order is placed. But the business must pay cash to buy inventory and pay expenses such as rents, wages, etc. to provide its products and services after which it gets paid cash. Therefore, cash is required on a day to day basis for the smooth functioning of a business and this cash is referred to as its working capital. Working capital is estimated to be equal to the business’s current assets minus current liabilities. Current assets include assets like inventory and accounts receivables that are converted into cash in a few weeks or months. Current liabilities are liabilities like accounts payables which need to be paid in 12 months or sooner.

We need to estimate the amount of current assets and current liabilities carried in the balance sheet to arrive at the working capital required to run the business. Current assets like inventory and accounts receivables and current liabilities like accounts payables are a function of the size of the operations of the firm. (The larger the revenues, the larger the amount of inventory, accounts receivables and accounts payables). We therefore link accounts receivables to the revenues in terms of the days outstanding. We link inventory and accounts payables to the cost of goods sold in terms of the days outstanding. We make assumptions about days sales outstanding (DSO), days inventory outstanding (DIO) and days payable outstanding (DPO) to estimate the working capital required to run the business. We therefore add these variables to the operating assumptions section in rows 10-12. We have also assumed that the number of days in a year is 360 in cell E14.

The days sales outstanding indicates how many days of sales is yet to be collected as cash from customers. The days inventory outstanding indicates how many days worth of costs is unfinished and in inventory at the end of the year. The days payable outstanding indicates how many days worth of costs are yet to be paid to vendors and suppliers. These assumptions are driven by management plans and financial policies. Historical figures (black font color in columns C, D and E) also provide an estimate of the figures as a guide to make these assumptions.

Balance sheet assumptions when doing a DCF valuation model
Balance sheet assumptions when building a DCF valuation model.

Property Plant & Equipment and Depreciation: Every business will need to invest in maintaining and upgrading its capital equipment. These investments are referred to as capital expenses (CAPEX). We need to account for the capital expenses required to maintain and grow the business in our financial projections. Capital expenses can be estimated based on specific plans or investments that will be undertaken by the firm. Capital expenses may or may not be even and may occur in cycles depending on the firm/industry. Capital expenses are driven by different factors in different companies/industries including revenue growth, capacity installed, geographies/locations planned (bottling plants), number of employees planned (services), new stores planned (retail), etc. Capital expenses can also be estimated as a percentage of revenue or cost of goods sold to keep the model simple. We choose revenue as the key driver for capital expenses and estimate CAPEX as a percentage of revenues in row 13 in this simple model.

Depreciation and amortization also can be modeled in a variety of ways. We can have a separate supporting schedule to compute depreciation using any one of the many depreciation and amortization methods for different blocks of assets (straight line, double declining balance, MACRS, etc.) In this simple financial model, we decide to model depreciation also as a percentage of revenues and have laid out the depreciation assumptions in row 8.

You now have all the assumptions required to forecast your cash flows for the forecast period. We will see how these assumptions are used in the next few chapters as we start to build out the DCF valuation model.

Table of Contents

Chapter 4: Operating Cash Flows

We have agreed that cash flows create value in a business. It is the day to day operations of a business that create the cash flows for the business. Therefore, we start by forecasting the operating cash flows of the business to build the DCF model.

Projecting the operating cash flows is relatively straightforward once you have the income statement structure and assumptions that drive the next few years’ performance. Note that the assumptions that drive the next few years’ performance, made in the previous chapter, were based on the prior year’s operating income statement figures and our expectations of the future.

4.1 Revenues

We start by projecting revenues of the firm for the forecast period. As indicated earlier, the assumptions about revenue growth over the next few years are very important and drive the entire valuation model. The revenue growth assumption should be based on a mix of factors. These factors include recent historical growth of the firm, industry expectations and specific management plans and strategies. The drivers of revenue will be different for different firms and industries. Some firms have only one line of business whereas some firms have multiple lines of businesses with different growth rates. Your financial model must reflect the different sources of revenue of the appropriate firm.

We assumed that revenues grow at 10% per year for the 5 year forecast period (cell G4 to K4). We therefore grow the most recent year’s revenue (cell E26) by the growth rate assumption for the first year (G4) to arrive at the revenue for the first year in cell G26. Each subsequent year’s revenue is arrived at by growing the previous year’s revenue by the corresponding growth rate. The second year’s revenues for example is modeled by multiplying the prior year (first year) by the corresponding growth rate factor: G26*(1+H4). Once the second year’s revenues are modeled this way, we can drag the formula down to the 10th year. This would give us the revenues for the entire forecast period of ten years.

If we had multiple streams of revenue, we would start with the most recent year’s revenues and grow each stream at the assumed growth rates and add them up to get the total revenues of the firm.

4.2 Expenses

The next step is to work out the expenses incurred to generate the revenues. There are two ways to approach this. One approach is to model the major expense categories individually. The other method is to estimate a total cost or operating margin directly. Both should reach the same figures approximately if done correctly. We will discuss both these methods here.

4.2.1 Expenses Detailed (Option 1)

Four types of operating expenses were estimated as a percentage of revenues. These are the cost of goods and services sold (CoGS), sales and general administration expenses (SG&A), research and development costs (R&D) and depreciation and amortization expenses (D&A). We multiply each expense item’s percentage estimate from the assumptions section by the appropriate year’s revenue to arrive at the operating expenses for each projected year. Please review rows 27, 29, 30 & 31.

We build the income statement structure by first subtracting CoGS from revenues to arrive at the gross profits in row 28. We then subtract SG&A and R&D from the gross margin to arrive at the earnings before interest, tax, depreciation and amortization (EBITDA) in row 31. From here we subtract D&A to arrive at earnings before interest and tax (EBIT) in row 33.

Modeling Expenses option 1
There are many ways to model expenses when projecting cash flows.

4.2.2 Operating Margin (Option 2)

We mentioned that there are two ways to arrive at the operating income. In the previous section, we estimated the main expenses categories (CoGS, SG&A, R&D, D&A) which were then subtracted from the revenues to arrive at the operating income. There is another way to arrive at the operating income.

Here, we estimate an operating margin (percentage of revenues) based on the historical figures and trends. This operating margin is multiplied by the revenue figures directly to get the operating income. This is the earnings before interest and taxes (EBIT).

Operating income (EBIT) = Revenues * operating margin

If this is done correctly, it provides as reliable an operating cash flow figure as the more detailed method. We skip the entire shaded region in the second option to arrive at the EBIT or operating income before taxes in row 33.

Another Option to model Expenses
Jump straight to EBIT is another Option to model Expenses in a DCF valuation model.

The operating income is earnings before interest and taxes. We need to estimate interest expense for each year before we subtract it from the EBIT to arrive at the earnings before tax (EBT). Only then can we estimate the tax expense and arrive at the net income. However, because we are valuing the entire firm for this model, we ignore the interest expense and set it equal to zero. We assume that there is no interest in this firm and account for the value generated by debt using the tax shield impact in the WACC. (If you intend to value only the equity, please build a debt schedule to compute the interest expense and factor the interest expenses in row 34.) In this model, we set interest expenses to zero and continue to build out the rest of the income statement. The EBT is the EBIT minus the interest expense. We now need to consider the impact of taxes reducing free cash flow further.

We apply the tax rate (assumption made in row 9) to the earnings before tax (EBT) to arrive at the income tax expense in row 36. We then deduct the income taxes expense from the EBT to arrive at the net income for each year in row 37.

4.3 Income taxes and Operating Income

We estimated tax rates directly based on the tax codes and historical rates in the assumptions section in row 9. All the other expenses have been estimated as a percentage of revenues. The tax rate, however is applied to the EBIT. In this model, we have laid out taxes in row 36 by multiplying EBT in row 35 with the estimated tax rate in row 9. The operating income is arrived at by subtracting the taxes from the EBT.

Alternatively, we can arrive at the operating income directly from the EBIT multiplying the EBIT with tax factor as follows:

Operating income after taxes = Operating income * (1 – Tax rate)

The formulae used in each row are highlighted below. Please look at this in tandem with the model or images provided in the appendix.

Formulas use to project income statement figures.
Formulas use to project income statement figures.

4.4 Operating Cash Flow vs. Free Cash Flow

We have just worked out the operating income from the business. The operating income does not represent cash flow. The income statement and operating income follow the accounting convention of accruals and does not reflect cash flows. Free cash flow as defined in a DCF valuation model is more specific as we saw in Chapter 1. We will now move on to computing the free cash flow from the operating income in the next chapter.

Table of Contents

Chapter 5: Forecasting Free Cash Flow

We estimated the operating cash flow that the business generates during the forecast period in the previous chapter. The operating cash flow, however, is not the same as the free cash flows of the business.

5.1 Need for Reinvestments

Every business needs to reinvest some of this operating cash flow back into the business to keep the business growing or even to maintain its operating capacity and financial health. If the required investments to maintain the business’s assets and operating capacity or to stay competitive in its industry are not made, the business will eventually die. While this reinvestment may not be a mandatory payment like taxes, it is not only essential but also critical and therefore not considered free cash flow. Therefore, the money needed for this reinvestment needs to be set aside from the operating cash flows to estimate the free cash flows of the business.  The cash required for this reinvestment can be classified into three broad categories:

  1. Working Capital;
  2. Capital Expenses; and
  3. Other needs.

The estimates for both working capital and capital expenses can be derived from the forecasted financial statements. The balance sheet reflects the values of the assets and liabilities as on a specific date. We take different approaches to forecasting the current and non-current items in a balance sheet. We link the working capital figures – current assets and current liabilities – with the size of the operations because the size of the operations determines the quantum of current assets and current liabilities required to operate smoothly. Capital expenses can be forecast based on changes driven by operational performance and management plans. We discuss these in the next sections.

5.2 Working Capital

Working capital is the cash required to run the business on a day to day basis. It consists of current assets like accounts receivables and inventories and current liabilities like accounts payables. Working capital figures in a balance sheet such as accounts receivables, inventories and accounts payables will depend on the size of the operations. We therefore linked accounts receivables to the revenues in terms of the days outstanding. We linked inventory and accounts payables to the cost of goods sold in terms of the days outstanding in the assumptions section. So, we use days receivables outstanding, days inventory outstanding, days payable outstanding assumptions (from rows 10-12) to estimate each type of current asset and current liability to arrive at the working capital required to run the business. We estimate the components of working capital as follows:

Accounts Receivables: We project accounts receivables figures in row 61. We start with our assumption about the number of days sales outstanding (DSO) in row 10. This is also referred to as days receivables outstanding and indicates how many days of sales are yet to be collected as cash from customers. This assumption, which is made in row 11, is based on historical trends and management policy.  We divide the annual revenue by the number of days in the year (G14) to give us the average daily sales. We then multiply the average daily sales by the number of days sales outstanding (our assumption from row 10) to get the accounts receivables outstanding at the end of the year in row 61.

Inventory: We project inventory figures in row 62. To arrive at the inventory levels at the end of each year, we start with our assumption about the number of days inventory outstanding (DIO) in row 11. The days inventory outstanding indicates how many days worth of costs is unfinished and in inventory at the end of the year. So, we divide the CoGS by the number of days in the year which gives us the average daily CoGS used. We then multiply the average daily CoGS by our assumption about the days inventory outstanding (row 11) to get the inventory held at the end of each year in row 62.

Accounts Payable: We project accounts payables figures in row 66. Here we start with our assumption about the number of days payables outstanding (DPO) in row 12. The days payable outstanding indicates how many days’ worth of costs are yet to be paid to vendors and suppliers. We divide the CoGS by the number of days in the year which gives us a proxy for the average daily purchases. We multiply the average daily purchases by our assumption about the number of days payables outstanding to get the accounts payables outstanding at the end of the year in row 66.

Forecasting Working Capital Components
Forecasting working capital components to estimate investments in working capital.

Working Capital: The working capital a business needs can be estimated in row 69 by subtracting the current assets total in row 63 from current liabilities total in row 67. We assume that the current assets consist of accounts receivables and inventory only. We also assume that the current liabilities consist of just accounts payables to keep our financial model simple. If you have other current assets or current liabilities, please feel free to add them to arrive at the working capital used each year.

Please note that we do not use the working capital figure directly in computing the free cash flow. We will see how this figure is used to arrive at the cash needed each year in section 4.5.

5.3 Capital Expenses and Depreciation & Amortization

We made assumptions about the amount of capital expenses required each year in the assumptions section. We chose revenue as the key driver for capital expenses and so a percentage of revenues assumption was made in row 13. We multiply the percentage of revenues estimate with the corresponding year’s revenue to arrive at the capital expense for the current year. We simply add the capital expense for the current year to the prior year’s gross property, plant and equipment figures to arrive at the gross property, plant and equipment figures for the current year in row 44.

Similarly, we made assumptions about the depreciation expenses for each year in the assumptions section. We chose revenue as the key driver for depreciation expenses and so a percentage of revenues assumption was made in row 8. We multiply the percentage of revenues estimate with the corresponding year’s revenue to arrive at the depreciation expense for the current year.

5.4 Others

If there is any other need for cash that you are aware of, please add it to the model. This includes both cash inflows and outflows. Potential events that will impact cash flow include the sale of assets, a planned acquisition, potential liabilities from pending litigation, compensation for employee termination or other restructuring charges. These must be considered when estimating projected free cash flows.

5.5 Free Cash Flow Projections

We are ready to compute free cash flows once we have the operating income, the working capital figures, depreciation and amortization and the capital expenses estimates. We will use the following formula to compute free cash flows:

Free cash flows = Operating income + depreciation and amortization – investment in working capital – investment in capital expenditure

This formula arrives at the free cash flow from the operations of the business by first adding depreciation and amortization expenses to operating income because depreciation and amortization is a non-cash item deducted from revenues in the income statements. Operating income and depreciation have been computed in rows 41 and 32 respectively. The formula then subtracts the cash required to run the business (investment in working capital and capital expenses) but not already deducted to arrive at the operating income in the income statement.

Investments in working capital: To arrive at the next component of the free cash flow formula – the investments required in working capital each year, we first estimate the working capital required in a business each year. The working capital required to support the operations is estimated in row 69 by subtracting the current liabilities from the current assets.

Working Capital = Current Assets – Current Liabilities

We assume that the current assets consist of accounts receivables and inventory and current liabilities consist of accounts payables to keep our financial model simple. Please note that the cash required to support a growing business is NOT the working capital required each year. The cash required to support the growing business is ONLY the increase in working capital required each year. We compute the cash investment required for working capital in row 43 as follows:

Investments in Working Capital = Working Capital (t) – Working Capital (t-1)

This would also mean that we can extract cash if there is a decrease in the working capital required. Such situations would be depicted with a negative figure in row 43.

Capital expenditure: Capital expenditure required each year as a percentage of revenues was estimated in row 13. We therefore multiply the estimated percentage required for CAPEX with the revenues each year in row 26 to arrive at the cash required for capital expenses in row 44.

Computing Free Cash Flow
Basic components of Free Cash Flow in a DCF model.

To finally arrive at the free cash flow from the operations of the business in row 45, we add depreciation and amortization expenses to net income and subtract investments in working capital and capital expenses.

Free cash flows = Operating income + depreciation and amortization – investment in working capital – investment in capital expenditure

This free cash flow in row 44 is the free cash flow generated by the business after meeting all its operating and capital needs. This free cash flow is the primary driver of value creation in finance. We will discount the free cash flow using the Weighted Average Cost of Capital (WACC) to value the business. We will estimate the discount rate or WACC in the next chapter. We will then value the business in Chapter 7.

Table of Contents

Chapter 6: Discount Rate / WACC

You built the assumptions that drive the DCF model in Chapter 3, the operating forecast in Chapter 4 and generated the forecast free cash flows to the firm in Chapter 5. Now you are ready to discount the future cash flows into today’s value to arrive at the value of the firm.

We need to discount the future cash flows because the value of a dollar received in the future is not the same as the value of a dollar today. We cannot add all the future cash flows to arrive at the value of the business. We first need to discount the future cash flow into today’s value. And for that, we need to know the discount rate.

6.1 What are Discount Rates?

The discount rate is the rate at which the value of money declines. This loss of value over time is a central concept in finance and is captured by the term ‘time value of money’. We do not delve into it deeply in this book, but you can read more about the time value of money here.

The discount rate is the cost of capital when valuing assets. There are various types and sources of capital. On one extreme, we have debt which provides a fixed rate of interest but no ownership. And on the other extreme, we have equity which does not promise a fixed rate of return but provides ownership in the firm. In between these two extremes, there are several hybrid types of capital that combine some combination of ownership and promised return (convertible debt, preferred shares, etc.). We assume that the firm is funded by debt and equity only to keep things simple in this model. 

The cost of debt is the cost of borrowing money or the interest cost. The cost of equity is not specified in the equity contract and we will learn to estimate it later in this chapter. Since a firm uses both debt and equity capital, we must use the cost of debt and cost of equity to arrive at the total cost of capital. The quantity used and the cost of the two types of capital will be different. Therefore, we weigh the cost of capital in the proportion of debt and equity used by the company to arrive at the weighted average cost of capital. This weighted average cost of capital is the discount rate we refer to as the WACC.

6.2 The Appropriate Discount Rate

Apples to apples and oranges to oranges. Different types of cash flows (discussed in Chapter 1) need to be discounted with different types of discount rates. When valuing the equity component of a firm using the free cash flow to equity, we discount the free cash flow to equity by the cost of equity. Whereas if we are valuing the firm using the free cash flow to the firm, we discount the free cash flow to the firm by the weighted average cost of capital as the firm is funded by debt and equity.

Appropriate Cash Flows and Discount Rates
Care must be taken to use the appropriate cash flows and discount rates.

6.3 Weighted Average Cost of Capital

You will need the following ingredients to arrive at the weighted average cost of capital:

  • Value of debt
  • Value of equity
  • Cost of debt
  • Cost of equity
  • Tax rate

We will discuss each of these ingredients in the next few paragraphs.

6.3.1 Market Values vs. Book Values

Please note that we ideally want the market values of debt and equity and not the book values. The values of debt and equity in the balance sheet are book values which are usually at historical costs. We prefer to go with market values as it reflects current costs which are more realistic as opposed to historical costs.

6.4 Value of Debt

The market value of debt is the present value of the future cash flows to the debt holder. If you are given the market value of debt, please use the given market value. In most cases, the book value of debt is a good approximation of the market value of debt. This is because the debt holder cannot expect to get anything more than what was agreed to at the time of signing the debt agreement. In our model too, we use the book value of debt as the market value of debt as this is an introductory level book.

The exception to using the book value of debt as its market value occurs when the company is in a financial crisis and is in or near bankruptcy. In these cases, the debt holders may not receive the entire amount of debt due to them. They will only receive what is left over after other liabilities and more senior debt is paid back. In these scenarios, the value of debt is lower than the book values reflected in the balance sheet and often only pennies on a dollar. If the company you are valuing is in this situation, you must compute the value of debt. The value of debt is the present value of the future cash flows to the debt holder. We do not go into valuing debt in this book as this is an introductory level book.

6.5 Value of Equity

The market value of equity needs to be estimated if it is not given to you. Theoretically, stock prices accurately reflect the current market value of each share. Therefore, we multiply the stock price by the shares outstanding to arrive at the market value of equity.

If the company is a private company, we can skip arriving at the value of equity and debt separately and use the target debt to equity ratios.

6.6 Weight of Debt and Equity

Once we have the market value of equity (E) and the market value of debt (D), it is easy to get the weight of debt and equity. The weight of equity and debt is as follows:

Weight of Equity = E / (E+D)

Weight of Debt = D/(E+D)

6.7 Cost of Debt

The cost of debt is the cost of borrowing money or the interest cost. This is usually indicated in the debt or loan contract. However, like the book value of debt, this may be outdated. We want the current cost of debt and so want to use the current yield or IRR. This is out of scope for this book. We assume that you have been given the cost of debt.

6.8 Cost of Equity

Unlike the cost of debt, the cost of equity will not be found in the equity contract or term sheets. In fact, unlike in the case of debt, there is no promise to pay the equity investor anything back on a periodic basis or ever! The equity investor only gets a portion of the ownership of the firm and no guaranteed return.

So, what causes anyone to give their money to a firm or company? Equity investors give money to a firm or company in exchange for ownership because they expect a return on their investment. This expectation of a return is referred to as the expected return on equity and is considered the cost of equity. The cost of equity will be different for different investors as each investor will have different expectations. There are several ways to arrive at the expected return including the Capital Asset Pricing Model, Fama French, APT, etc. The most commonly used method is the Capital Asset Pricing Model (CAPM).

6.8.1 Capital Asset Pricing Model

The Capital Asset Pricing Model (CAPM) states that the expected return on an investment is a function of 1) the prevailing risk-free rates, 2) the market risk premium and 3) the risk profile of that asset. The equity beta is what we will use to gauge the risk profile of that investment as we are interested in an equity investment.

Expected return = risk free rate + Beta * Market Risk Premium

6.8.1.1 Risk Free Rate

The risk free rate is the rate that an investor would receive on a risk free asset. Although there is, theoretically, no perfectly risk free asset, we use the US treasury as a proxy for a risk free asset because we assume that it is the closest to a risk free asset.  We believe that it is the closest to a risk free asset because it presides over the largest economy in the world and so hopefully, will not default on its obligations.

The risk free rate is the minimum return an investor would need on an investment. Given that investments carry risk, he would not invest in a risky asset unless he gets a return higher than the risk free asset. So the risk free asset becomes the starting point to estimate his expected return.

6.8.1.2 Market Risk Premium

You are taking a risk when you invest in the market. This risk is often referred to as ‘market risk’. The market risk premium is the premium expected as a reward for taking on the ‘market risk’ in an equity investment. This is the excess return offered by the market over the risk free rate. Market Risk Premium can be computed as follows:

Market Risk Premium = Market return – risk free rate

6.8.1.3 Beta (Equity)

The beta captures the risk profile of an investment in relation to the market. It reflects how closely an investment’s returns move with that of the market returns. The equity beta of a stock reflects how closely that equity stock’s returns move with that of the market returns. An equity beta of one indicates that the stock return will move exactly in line with the market. For example, if that market provides a 10% return, this stock will also have a 10% return. An equity beta of 1.5 indicates that if the market moves up by 10%, this stock will move up by 15% (10% x 1.5). A negative beta indicates that the stock moves in the opposite direction of the market. If the market goes up, a negative beta stock will move down. For example, equity beta of -0.5 indicates that if the market moves up by 10%, this stock will move down by -5% (10% x -0.5).

6.8.1.3 Cost of Equity Formula

Once you have all the ingredients required, the cost of equity can be obtained by applying the formula.

Expected return = risk free rate + Beta * Market Risk Premium

In Microsoft Excel or Google Sheets, it is a simple formula linking the right cells. We have done that in cell J77.

Cost of Equity using the CAPM method
Computing the cost of equity using the CAPM method.

6.9 Weighted Average Cost of Capital Formula

After you have computed the cost of equity, you have all the ingredients required to compute the weighted average cost of capital (WACC). Simply apply the WACC formula.

WACC = Weight of Equity * Cost of Equity + Weight of Debt * Cost of Debt * (1-tax rate)

In Microsoft Excel or Google Sheets, it is a simple formula linking the right cells. We have done that in cell E77.

Weighted Average Cost of Capital or WACC
Components of Weighted Average Cost of Capital or WACC.

You are now ready to value the firm, its equity and arrive at the share price. We will do that in the next chapter.

Table of Contents

Chapter 7: Valuing the Firm

You built the assumptions that drive the DCF model in Chapter 3, the operating forecast in Chapter 4, estimated free cash flow to the firm in Chapter 5 and estimated the discount rate using the WACC approach in Chapter 6. You are now ready to value the firm.

7.1 Value of a Firm

The key concept in DCF valuation is that the value of any asset is the sum of the present values of all future cash flows.

Value of any asset is the sum of the present values of all future cash flows.
The intrinsic valuation concept.

We have now projected the future cash flows for the forecast period. In our model, the forecasted period is 10 years. We agreed in Chapter 1 that a company is a separate legal entity with, theoretically, an infinite life. Since an asset’s value is the sum of the present values of all future cash flows, we need to incorporate the values of this infinite life and not just the first 10 years which form the forecast period. So, we will find the present values of the forecast period and the horizon period and sum up the present values to find the present value of all future cash flows. This will be the value for the firm.

Value of the Firm = PV of Forecast Period + PV of Horizon Period

7.2 Present Value of Cash Flows of the Forecast Period

We can compute the present value of cash flows of the forecast period in two ways: using the present value formula or using the Microsoft Excel function for net present value (NPV). The present value formula is as follows:

Present Value = Future Value / (1+discount rate)^time period

Remember that the present value shows you what an amount received in the future is worth in today’s value. We compute the present values using the present value formula in row 49. You can see the free cash flows in row 45 and see that the free cash flows received in the future do not have the same worth in today’s values.

Present Value of the forecasted cash flows
Arriving at the Present Value of the forecasted cash flows.

We sum up the present values in row 45 to arrive at the present value of all the cash flows received during the forecast period – the first 10 years in this model.

Alternatively, we can use the NPV function in Microsoft Excel to compute the present value of cash flows of the forecast period for our DCF model. The ingredients required for the NPV function in Microsoft Excel are 1) the discount rate and 2) the cash flows. We have already computed both of these in the earlier sections of the model. We can simply apply the formula into the model in cell E50. It would look like this:

NPV formula for arriving at the Net Present Values
NPV formula for arriving at the Net Present Values

Note that cell E77 contains the WACC which is used as the discount rate and G45 to P45 contains the actual future cash flow from row 45. Also, note that the first period’s cash flows should be reflected as value 1.

7.3 Present Value of Cash Flows of the Horizon Period

We agreed in chapter 2 that a company is a separate legal entity with, theoretically, an infinite life. Since an asset’s value is the sum of the present values of all future cash flows, we need to incorporate the values of this infinite life and not just the first 10 years which form the forecast period. The challenge will be to compute the present values of future cash flows during an infinite time horizon. Thankfully, the mathematicians have figured this out for us!

We classify the cash flows out into the horizon into two types:

  1. A perpetuity: A periodic constant payment that continues forever.
  2. A growing perpetuity: A periodic payment that increases at a steady rate forever.

7.4 Present Value of a Perpetuity

A perpetuity is a constant payment at fixed intervals that continues forever. This is also called a perpetual cash flow. Thankfully, the mathematicians have provided us a formula to compute the present value of a perpetuity. The present value of a perpetuity is the periodic cash flows divided by the discount rate.

The Present Value of a Perpetuity
The Present Value of a Perpetuity Formula.

The cash flow is the value of cash received during each period. Since it is not growing, it will be the same value each period. The discount rate is the cost of capital and will be the WACC in a DCF model. In more advanced models, there can be reasons to use a different discount rate for each period. You will find those discussions in more advanced finance textbooks.

7.5 Present Value of a Growing Perpetuity

The primary job of the management of the company is to grow the business and so we assume that they will be able to grow the cash flows at least at the rate of inflation or at a slightly higher rate. We call this rate the terminal or horizontal period growth rate. Thankfully, again, the mathematicians figured out what the present value of a perpetually growing cash flow looks like. They have told us that the present value of a growing perpetuity is the cash flow in year one divided by the difference between the discount rate and the growth rate.

The Present Value of a Growing Perpetuity
The Present Value of a Growing Perpetuity

Please note that the numerator in the formula in the case of the present value of a growing perpetuity is next year’s cash flow indicated by CF1 or ‘Cash flow in year 1’. The denominator has the growth rate subtracted from the discount rate or cost of capital. This formula gives the present value of all future cash flows in today’s terms or in time zero.

7.6 Horizon value in the DCF Model

In our model, we have the forecast period ending in year 10 and therefore the horizon period starts in year 11. We have grown the last forecasted cash flow in year 10 by the terminal growth rate to get the next year’s cash flow for the numerator (year 10 cash flow x (1+terminal growth rate).  We have estimated growth rates in cash flow for the period after the forecast period or year 11 onwards to infinity in cell E15.

Computing Terminal Value using the Growing Perpetuity Formula
Computing Terminal Value using the Growing Perpetuity Formula.

The numerator, therefore, is:

CF t=11 = CF t=10 * (1 + growth rate)

You will notice that we have computed the present value of the growing perpetuity in P51 in our model using the formula for the present value of a growing perpetuity. This is the present value of the horizon cash flows as at the end of year 10 since we use year 11 cash flows in the numerator. We need the present value in today’s terms and therefore have to discount the year 10’s value by an additional 10 years. 

We discount the value in year 10 by an additional 10 years in cell E52. This is the present value of the cash flows in the terminal or horizon period (years 11 up till infinity) in today’s value.

7.7 Firm Value

An asset’s value is the sum of the present values of all future cash flows when using the DCF valuation approach. Therefore, we need to add the present values of the forecast period cash flows (E50) to the present value of the horizon period cash flows (E52) to arrive at the DCF value of the business or firm.

Value of the firm = Present Value of forecast period cash flows + Present Value of horizon period cash flows

This is the value of the firm as a whole.  More specifically, this is the value of the operating assets of the firm. If the firm has non-operating assets or assets not used in the process of generating cash flows, those assets can be valued separately and added to this value to arrive at the value of the firm.

7.8 Value of Debt and Equity

What we just computed is the value of the firm as a whole.  This value of the firm as a whole belongs to the providers of debt and equity because the firm is funded by debt and equity.  Therefore, from this firm value, we subtract the value of debt to get the value of all equity.

Value of equity = Value of the firm – Value of debt

This is the value of all the equity outstanding (E55). 

7.9 Intrinsic Value of a Share

We divide the total value of equity by the number of shares outstanding (E56) to arrive at the value of each share.

Value per Share = Value of equity/Number of shares outstanding

Remember this is the intrinsic value per share arrived at using the DCF valuation method. This may not be equal to the share price in the market today. If you have made the right judgments about the inputs used, the intrinsic value of each share gives you an indication of whether the stock is undervalued or overvalued by the market today.

You have just built out your first DCF valuation model! You can give yourself a pat on the back. In the next chapter, we give you some general guidelines on formatting your model well. In the last chapter, we discuss several additional features you can add to your model once you are comfortable building the basic DCF valuation model.

Table of Contents

Chapter 8: Presentation & Formatting

People do judge a book by its cover. A well-structured, formatted and presented model will communicate accuracy, thoroughness and professionalism. In this section, we discuss the formatting guidelines you should consider when building a financial model.

Professional financial models have some rules unlike other types of models such as inventory tracking models, expense analysis models, scheduling models, etc. These are not really ‘rules’ but more convention, etiquette or tradition. Following convention, etiquette and tradition makes it easier for a reader to follow the model faster. A professional-looking model will impress the reader more than an equally good but unprofessional-looking model. So, pay attention to the way you format your model, especially if you intend to share it with others or it is being evaluated by anyone else.

8.1 Formatting Guidelines

Here are a few guidelines you should adhere to:

Use blue as the font color for input cells: This will enable your reader to quickly identify the input cells and distinguish them from the computed cells.

Use black as the font color for computed cells and text: All the computed cells must be in black ink so the reader knows that these are not to be altered. Black is also used as the font color for text because it is not an assumption or input to be altered.

Use green as the font color for information from other files/workbooks: All cells with data from external sources must be in green font so the reader knows that this data has come from external sources.

Set up ‘Print Areas’: Your reader may want to print out the model. Set up ‘Print Areas’ from the ‘Page Layout’ ribbon in the model so your readers can print out the model neatly if the need arises.

Setting print area in a projected financial statements model
Setting print area in a projected financial statements model.

Specify units & currency measures: Define metrics, units and currency as appropriate at the beginning of the model and on every page. It is also common to indicate the currency symbol in the first and last rows of each section, but it is best avoided in every cell to improve readability.

Colors: Different firms or groups within larger companies choose different formatting colors for headings, tabs, etc. either by chance or to match corporate colors. You can choose the preferred colors if you know your readers’ preferences. Please ensure that the numbers are easy to read. We chose a light grey to increase the readability of the model.

Protect the sheet & lock cells: Lock the entire worksheet except the assumption input cells in the input section. This allows the reader to make changes in the input & assumption sections only and avoids unintentional changes happening in the LBO model.

8.2 Other Formatting Options

The above guidelines will give your DCF valuation model a professional look and feel. There are many additional options that you can choose from based on your preferences. We reiterate here that different corporates, firms and institutions have preferred formatting guidelines and so if you are interviewing or building a model for a specific target audience, use their preferred formatting guidelines and colors. Formatting options that you can choose include:

  • Font style: Theme, bold, italic, size, etc.
  • Conditional formatting
  • Borders
  • Totals
  • Number of decimals
  • Alignment
  • Percentages
  • Dates

Whatever you choose, use it consistently throughout the model. Keep in mind the golden rule: formatting must make the model easy to read, understand and follow.

Table of Contents

Chapter 9: Additional Features

We promised you that we would teach you how to build a simple DCF valuation model for two reasons: 1) once you can confidently build a simple model, you can add any additional features your situation warrants and 2) you may have  limited time to build a model in many situations like in an interview setting and only a simple model will be possible.

We built a simple DCF valuation model. There are many more features you can add to your model, if you have the time and need. Here is a list of features or sections you can add to your DCF valuation model if you need to.

Property, Plant & Equipment & Depreciation Schedules: We made the simplifying assumption that the capital expenditure and depreciation are a percentage of revenues. We could build one or more schedules to compute the capital expenditure and depreciation for different blocks of assets with different useful life. Capital expenditure can be based on other factors such as geographic expansion, age of machines, capacity utilization, projected growth, etc. Depreciation schedules can be built on MACRS depreciation schedules or other methods like the double declining methods in different asset blocks if this level of detail is required.

Sales to Capital Ratio: We modeled capital expenses as a percentage of revenues. Another way to model capital expenses is to study the historical sales to capital ratios of the firm or other companies in the industry and use the sales to capital ratio to model CAPEX investments.

Research & Development Expenses: We have modeled R&D expenses as a percentage of revenue and have expensed this. R&D expenses is a capital investment for the future and can be capitalized for some companies. We can model the impact of this capitalization if appropriate for your company.

Operating Leases vs. Capital Leases: The accounting standards in each country govern what is permitted to be expensed as an operating lease and what lease expenses must be capitalized. Valuation does not have any such constraints. The focus must be on cash flow.

Cost of Capital: We have assumed a single rate as the cost of capital. If you believe that the cost of capital will change over time, you can discount each year’s cash flows with the appropriate discount rate for that year. The discount rate may change over time due to many reasons such as a decline in risk, increasing competition, the maturing of the business, etc.

Many models also use a separate discount rate for the horizon period cash flows.

Employee Stock Options: Some companies hand out employee stock options (ESOP) generously to employees. This impacts the valuation of the equity and cash flow. It impacts equity value as the owners’ share of the business is reduced by the amount granted to the employees. Accounting regulations specify rules for dealing with ESOP. We need to model the cash flows from ESOP in the DCF valuation model if it is significant.

Time Periods/Fractional years: We have assumed that we are modeling the financial statements for whole years. You can model performance by months, quarters or half years if a more detailed view is required. Your years also need not start with a 12-month period. It could be more or less if your situation requires it.

Deferred Taxes, Tax Assets and Tax Liabilities: We have assumed a tax rate of 40% on pre-tax income in this simple model. You can model taxes separately featuring deferred taxes, tax assets, tax liabilities, loss carryforward, federal and state taxes, etc. if you have access to the appropriate details.  

Investments: Companies could have investment securities or investments in other firms including subsidiaries. These must be modeled separately and added into the final value if needed.

Minority Interest: Revenues and expenses of subsidiary companies are consolidated into a parent company’s financials according to the accounting regulations if certain conditions are met. This is true even if the parent company does not own 100% of the subsidiary. Minority interests must be valued and considered when valuing the entire business.

Equity Beta: We have assumed an equity beta. We can model the equity beta based on the historical stock prices and market returns if the company is a publicly listed company. If the company is not a publicly listed company, we can model the equity beta from the industry beta by studying comparable firms that are publicly listed.

Table of Contents

Table of Contents

Who is this book for?
How to use this book

Chapter 1: Key Concepts in DCF Modeling

1.1 Why Value a Business?
1.2 Different Approaches To Valuation
1.3 What Creates Value in a Business?
1.4 Key Principle Driving DCF Valuation
1.4.1 Cash flows
1.4.2 Free Cash Flow To The Firm
1.4.3 Free Cash Flow To Equity
1.5 Lifetime of a Business
1.5.1 Two-Stage Model
1.5.2 Three-Stage or Multi-Stage Models

Chapter 2: Building a DCF Model

2.1 Ingredients Required for a DCF Valuation Model
2.2 Components of a DCF Model
2.3 Planning & Structure
2.4 Build Order

Chapter 3: Assumptions that Drive the Model

3.1 Historical Financial Statements: Income Statements and Balance Sheets
3.2 Historical Financial Ratios and Performance Drivers
3.3 Assumptions that Drive the Projected Financial Statements
3.3.1 Income Statement Assumptions
3.3.2 Balance Sheet Assumptions

Chapter 4: Operating Cash Flows

4.1 Revenues
4.2 Expenses
4.2.1 Expenses Detailed (Option 1)
4.2.2 Operating Margin (Option 2)
4.3 Income taxes and Operating Income
4.4 Operating Cash Flow vs. Free Cash Flow

Chapter 5: Forecasting Free Cash Flow

5.1 Need for Reinvestments
5.2 Working Capital
5.3 Capital Expenses
5.4 Others
5.5 Free Cash Flow Projections

Chapter 6: Discount Rate / WACC

6.1 What are Discount Rates
6.2 The Appropriate Discount Rate
6.3 Weighted Average Cost of Capital
6.3.1 Market Values vs. Book Values
6.4 Value of Debt
6.5 Value of Equity
6.6 Weight of Debt and Equity
6.7 Cost of Debt
6.8 Cost of Equity
6.8.1 Capital Asset Pricing Model
6.9 Weighted Average Cost of Capital Formula

Chapter 7: Valuing the Firm

7.1 Value of a Firm
7.2 Present Value of Cash Flows of the Forecast Period
7.3 Present Value of Cash Flows of the Horizon Period
7.4 Present Value of a Perpetuity
7.5 Present Value of a Growing Perpetuity
7.6 Horizon value in the DCF Model
7.7 Firm Value
7.8 Value of Debt and Equity
7.9 Intrinsic Value of a Share

Chapter 8: Presentation & Formatting

8.1 Formatting Guidelines
8.2 Other Formatting Options

Chapter 9: Additional Features

Back to the Top