|
|
|
|
|
|
|
FINANCIAL MODEL FOR VALUATION OF AN EXISTING
COMPANY, USING THE DISCOUNTED CASH FLOW METHOD, BEFORE AND AFTER DEBT
ADDITION
|
|
|
|
|
|
|
|
Explanation for a correct understanding and utilization of the
model:
|
|
|
|
|
|
1) Blue colour cells must be filled up by
the user.
|
|
|
|
|
|
2) In order to fill up the spreadsheet,
it must be followed the order of the sheets:
Starting in the second
|
|
|
one and ending in number 8. We consider this procedure more
easy to use than putting all the assumptions
|
|
|
in just one initial page (however, almost all the assumptions
are in sheet number 2).
|
|
|
|
|
|
3) Explanations according to the figures
which appear in the sheets:
|
|
|
1- Yield which should be achieved by the investor, in the
event that no debt would be used in the financing.
|
|
|
2- Proportion of own financing over total permanent financing
(debt + net worth), which is considered by
|
|
|
the investor to be reasonable to keep in a company like that.
|
|
|
3- Assumption of increase in perpetual free cash flow starting
in last year, in order to obtain a residual
|
|
|
value of the company.
|
|
|
4- Logically, with a higher level of debt, there is more risk
for the company, and a higher yield should
|
|
|
be requested by the investor. A financial formula helps us to
calculate the new requested yield.
|
|
|
5- After tax debt cost, in proportion to its percentage over
total permanent financing.
|
|
|
6- Cost of own financing in proportion over total permanent
financing.
|
|
|
7- Interest rate that will be used to discount to present
value the free cash flow that will be generated
|
|
|
by the company.
|
|
|
8- Information of year 0 should be filled up with the actual
balance sheet figures of last year.
|
|
|
9- WCN = Cash needed for operations + Receivables + Stocks -
Payables.
|
|
|
10- Information of year 0 should be filled up with the actual
P & L figures of last year.
|
|
|
11- The reason to point out this figure is to figure out the
importance of residual value out of the total
|
|
|
value of the company.
|
|
|
12- We substitute the automatic value of debt that we get from
the formula by another higher figure
|
|
|
which reflects a more reasonable amount of debt to be
requested to a financial institution.
|
|
|
13- In order to avoid a higher complication of the model, we
assume a fixed term for the debt of 10
|
|
|
years, which is the term of the projections.
|
|
|
14- Debt cost which results from sheet number 5 (debt
calculation).
|
|
|
15- This extraordinary payment of reserves is established to
adapt the balance sheet to the new structure
|
|
|
of debt over total permanent financing.
|
|
|
16- Last year market value is established by discounting a
perpetuity of last year dividend. Market
|
|
|
value of other years are calculated by discounting at the rate
Ke the amount of company's next year
|
|
|
market value plus next year dividend.
|
|
|
17- This figure will give us, for comparison purposes, the
market price which should be achieved by
|
|
|
the company for each year in order for the investor to achieve
the specified accumulated yield.
|
|
|
|
|
|
|
|