valuation

risk

portfolio

dcf

wacc

dcf

cfa

games

 

 

Online DCF model

 

Here is a simplifyed online DCF model, insert appropriate figures in cells and press calculate.

This model allows you to calculate the value of the company really fast but with a poor accuracy .

To construct a full scale DCF model for the appropriate valuation you need much more inputs and totally different forecasting mechanics.

This model may help you to understand the mechaincs of DCF.

Financial analysis

CFA formulas

Instructions

-Year 0 is the previous year from now. If now is 2011, then "Year 0" is 2010, "Year -1" is 2009 and "year -3" is 2008. So if it is 2011 please insert values for 2008, 2009 and 2010

- Please insert all the data in the cells below. A good source of financial information is Yahoo. You should insert stock's ticker in the box, choose your company and click on Income statement, Balance sheet and Cash flow in the left menu bar.

- After you inserted all the data in the cells press the button "Calculate DCF model". Below you will see the forecast of P&L statement, Balance sheet, Cash flows statement, WACC and DCF model with an estimated fair value for the stock.

- Note that the major share of company's value comes from the post forecast period (terminal value). When creating a model, try to focus on the last cash flow in the forecasting period ("Year 2") because it defines terminal value.

- Use debt (short term and long term) as a balancing row when the company shows negative cash at the end of the period.

- After you created a model for the first time you can make changes in inputs and calculate the value again. Thus you can analyse the sensitivity of your model to changes in wacc, growth rates, profit tax rate, etc.

 

Market data
Current stock price ($)
Number of shares
Growth rate in forecasting period (%)
Terminal growth rate (%)
P&L
  Year -2 Year -1 Year 0
Revenue ($mn)
EBITDA ($mn)
EBIT($mn)
Profit tax rate(%)
Balance sheet
Assets
Non current assets($mn)
Cash and cash equivalents($mn)
Other current assets($mn)
Liabilities
Short term debt($mn)
Other current liabilities($mn)
Long term debt($mn)
Other non current liabilities($mn)
Capex
Capital expenditures($mn)
Capex growth rate(%)
WACC inputs
Risk free rate(%)
Beta
Market return(%)
Cost of debt(%)
 
RESULTS
P&L
  Year -2 Year -1 Year 0 Year 1 Year 2 Year 3
Revenue ($mn)
EBITDA ($mn)
EBITDA margin(%)
EBIT($mn)
EBIT margin(%)
DDA($mn)
Balance sheet
Assets
Non current assets($mn)
Cash and cash equivalents($mn)
Other current assets($mn)
Total assets($mn)
Liabilities
Short term debt($mn)
Other current liabilities($mn)
Long term debt($mn)
Other non current liabilities($mn)
Total liabilities($mn)
Equity
Equity($mn)
Cash flows statement
Cash flows from operations($mn)
Cash flows from investing($mn)
Cash flows from financing($mn)
Change in cash($mn)
Cash at the beginning of the year($mn)
Cash at the end of the year($mn)
DCF model
WACC
Risk free rate(%)
Beta
Market return(%)
Cost of equity(%)
Equity share(%)
Cost of debt(%)
Debt share(%)
WACC(%)
VALUATION
Taxed EBIT($mn)
DD&A($mn)
Capital expenditures($mn)
Working capital($mn)
Free cash flows($mn)
Terminal value($mn)
Discounted terminal value ($mn)
Sum of discounted free cash flows($mn)
Net debt($mn)
Estimated fair value($mn)
Estimated fair value per share($)
Upside/Downside potential(%)

 

HTML Comment Box is loading comments...

DISCLAIMER. Neither this site, the available materials, nor its contents (including any information concerning any securities mentioned on this site) constitutes an advertisement of any securities or an offer to sell or a solicitation of an offer to purchase any securities.

 

If you have any questions, comments or suggestions please contact me