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.
ASSUMPTIONS FOR AN INITIAL VALUATION OF THE COMPANY (WITHOUT DEBT).
https://www.financial-simulation.com
1) For calculation of Profit and Loss statements:
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
a/ Annual increase in sales
b/ Cost of goods sold: % of sales
c/ General and administrative expenses: % of sales
d/ Depreciation: % of previous year net fixed assets
e/ Income tax rate
2) For calculation of Balance Sheet:
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
3) For calculation of market value of the company
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
a/ Yield requested by the market without debt (Ku) (1)
b/ Cost of debt before tax
d/ Proportion of own financing over total permanent financing (2)
e/ Proportion of debt over total permanent financing
f/ Growth rate of Free Cash Flow (FCF) starting from last year (3)
From these assumptions, we have as a result the following yields requested by the market for the different leverage levels
Yield requested by the market for this leverage (Ke) (4)
Weighted cost of debt after tax (5)
Weighted cost of own financing (6)
Weighted average cost of capital (wacc) (7)
COMPANY
PROJECTIONS OF BALANCE SHEET, PROFIT & LOSS AND FREE CASH FLOW (WITHOUT DEBT)
BALANCE SHEET (8)
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Cash needed for operations
Receivables
Stocks
Fixed assets (gross)
 - Accumulated depreciation
Fixed assets (net)
TOTAL ASSETS
Payables
Debt
Would be filled up
Net worth
according to leverage
TOTAL LIABILITIES AND NET WORTH
Working capital needs (WCN) (9)
PROFIT AND LOSS (10)
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Income tax
Net Profit
Increase in net profit
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
 + Depreciation
 - Increase in WCN
 - Investment in fixed assets
Free cash flow (FCF)
Increase in FCF
COMPANY
INITIAL VALUATION ACCORDING TO SELECTED LEVERAGE LEVEL.-
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
FCF
Residual value according to a growth rate of:
with wacc:
Total cash flows to be discounted, including residual value
Discounting rate = wacc
Discounting factor
Discounted cash flows
Addition of discounted cash flows (Present value of the company)
Discounted cash flows without residual value
Addition of discounted cash flows without residual value (PV) (11)
Initial debt according to selected leverage
Value of own resources (discounting debt)
Value of own resources without residual value
CALCULATION OF DEBT AND INTEREST, ACCORDING TO SELECTED INITIAL AMOUNT OF DEBT.-
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Initial level of debt, according to selected leverage
Initial debt for year 1 (based on a more reasonable amount) (12)
Repayment term (years) (13)
Principal amount to be repaid each year
0   
Resulting debt after repayment
Average amount of debt for each year
Reference interest rate (Euribor or other)
Spread
Interest rate of debt
Amount of interest to be paid each year
Annual debt service (principal + interest)
ASSUMPTIONS FOR COMPANY VALUATION, INCLUDING DEBT AND INTEREST.
1) For calculation of Profit and Loss statements: Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
a/ Annual increase in sales
b/ Cost of goods sold: % of sales
c/ General and administrative expenses: % of sales
d/ Depreciation: % of previous year net fixed assets
e/ Cost of debt (14)
f/ Income tax rate
2) For calculation of Balance Sheet:
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
f/ Outstanding debt: According to expected repayment schedulle
3) For calculation of market value of the company Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
a/ Yield requested by the market without debt (Ku)
b/ Cost of debt before tax
d/ Growth rate of payable dividends starting from last year
COMPANY
PROJECTIONS OF BALANCE SHEET, PROFIT & LOSS AND FREE CASH FLOW (AFTER ADDING DEBT)
BALANCE SHEET 
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Cash needed for operations
Additional cash (to balance / if negative: additional debt)
Receivables
Stocks
Fixed assets (gross)
 - Accumulated depreciation
Fixed assets (net)
TOTAL ASSETS
Payables
Debt
Accumulated reserves
Capital
TOTAL LIABILITIES AND NET WORTH
Working capital needs (WCN) 
PROFIT AND LOSS
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Profit before tax
Income tax
Net Profit
Increase in net profit
FREE CASH FLOW
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Gross operating margin
 - Income tax
 - Increase in WCN
 - Investment in fixed assets
Free cash flow (FCF)
 - Annual debt service (principal + interest)
Casf flow available for dividend payment (FCD)
Increase in FCD
Reserves in year 0
Extraordinary payment of reserves starting year 1 (15)
Accumulated FCD
Profit of the year
Accumulated reserves as a limit for dividend payment
Accumulated dividend payable
Annual dividend payable
Financing structure resulting for each year
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Net worth (own resources)
Debt (third party resources)
COMPANY
VALUATION OF THE COMPANY BY DISCOUNTING ANNUAL DIVIDEND PAYABLE 
(and considering the market value of net worth in the formula to calculate the Ke)
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Cost of own resources without debt (Ku)
Cost of debt before tax
Income tax rate
Market value of debt (= book value)
Annual dividend payable
Market value of own resources
(16)
Cost of own resources with debt (Ke)
Growth rate of dividend payable starting in year 10
Market value of own resources without residual value
Market value of residual value
Debt / Total permanent resources (market value)
Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Year 8 Year 9 Year 10
Market value of own resources for each year
 % of annual increase
 % of accumulated annual increase
Market value needed for an accumulated annual increase of (17)