这次算是发上去了，抱歉

谢谢楼主分享

我这还有Macquarie的3个练习题，看看投行是怎么做的，考考自己的财务知识和估值能力吧。

现金流折现模型、 财务报表、红利折现

只要填对了空，左边的红色框就会变成绿色。

310172.xls
大小:(219.5 KB)

 马上下载

310174.xls
大小:(170.5 KB)

 马上下载

310274.xls
大小:(174 KB)

 马上下载

310287.xls
大小:(174 KB)

 马上下载

花了一个下午。财务报表的那个红了两个，但是结果应该是对的。不知道怎么回事，高手帮忙看看。

第二个估值的，花了好长时间。纠结于0.01的误差和0.0001的误差之间，两种估值结果它都承认了，但是0.01的误差导致不一致（他要求的是小于0.00001的误差）。
实在受不了了，交作业了。

第三个改天再交上来

By: Lijun Deng (SID: 308029348)

Discounted cash flow (DCF) analysis is a widely used model in firm valuation. It values a firm by discounting all estimated future cash flows to obtain its net present value. The model is expressed as following:

The key inputs in the model are free cash flows (FCF) and discount rate(r), both of which can be decomposed by way of estimation and their own determinants.

Free cash flow is the cash generated by firm’s assets after deducting capital expenditure and is free to distribute to all security holders. To obtain a firm’s prospective FCF’s, pro forma financial statements need to be constructed, using the classical percent-of-sales forecasting method and some reasonable adjustments. This model starts from the leading item, sales growth projection. Through macroeconomic environment and competitive strategy analysis, analyst can project the industrial sales growth and the firm’s market share, which are two key factors of sales growth. Other accounts in financial statements are projected based on their relationships with sales, which needs analysts make decisions about whether to use historical ratios or make some discretional adjustments. After pro forma financial statements are projected, FCF stream to the firm can be obtained by the following calculation.

FCF = Net Profit After Tax +Depreciation – Increase In Working Captial + After Tax Interest Expense – Capital Expenditure

After accomplished the first task, the other important variable, discount rate r, is to be determined. The discount rate is the rate of company cost of capital, which is defined as the expected return on a portfolio of all the company’s existing securities1. The mostly adopted one is a firm’s weighted average cost of capital (WACC), as follows, assuming no preferred shares:

Cost of equity can be estimated by models such as capital asset pricing model (CAPM), the dividend discount model or the Fama-French three-factor model.

As interest payments are tax deductible, cost of debt is calculated on an after-tax basis, as show in the WACC equation. A company has a specific cost of debt dependent on its credit rating. Typical blue chip corporate debt generally commands a risk premium above the risk-free rate of between 0.5 and 1.25 per cent above LIBOR (London Inter Bank Offer Rate)2.

The weighting used to derive WACC is the ratios of market values of equity and debt. They can be valued separately by discounting cash flows to each of them, but using proper discount rates. Constant dividend growth model is generally used to value equity and discounting principle and interest payments by cost of debt to value debt.

Weight of preferred shares is added to get WACC similarly if applicable.

So far we have ignored the time frame of cash flow projection. In corporate valuation practice, the forecast of FCF is normally divided into yearly free cash flows and a terminal value. The terminal value is generally obtained by invoking the discounted cash flow approach with simple assumptions concerning the expected growth in free cash flows from terminal point.

The reason to break up them is that it is only reasonable to make FCF projections for a few years ahead based on the recent financial performance. The company and the industry it is in will go mature or enter a steady state after a few years. As shows in above figure, sales growths tend to converge to a normal average level as demand saturates and intra-industry competition becomes stable3. Therefore, FCF is expected to grow in similar pattern.

Reference

1.    Brealey-Meyers, 2003 Principles of Corporate Finance. 7th Edition, John Wiley & Amp; Son

2.      E.V. Lilford, 2006 The Corporate Cost of Capital. The Journal of The South African Institute of Mining and Metallurgy

3.    Palepu, K., and P. Healy, 2007 Business Analysis and Valuation: Using Financial Statements, 4th Edition, Thomson South-Western 4-2