Discounted Cash Flow (DCF) Excel Model Template by Henry Sheykin - Financial Models https://finmodelslab.com/ Last version published: 30/04/2019
COVER PAGE
The cover page is the name of your business project, company or Food Truck name. This name is put in a yellow cell and appears on every file page afterward.
2
Enter Income Statement actual financial data as a basis for the Discounted Cash Flow (DCF) Excel Model
Go to the IS tab. Here you may enter the actual information of the company you wish to perform a Discounted Cash Flow (DCF) valuation. The historical information can be found in the company’s annual report or other sources such as: - management presentation, - information memorandum, - business reviews etc. In our example, our historical year’s are 2019 – 2022. Change your last actual Fiscal Year, find the yellow cell at line 7. Enter yellow values for each historical year: - Net sales;- Cost of Goods Sold “COGS”;- Operating Expenses “OPEX” - Depreciation and- Tax
3
Enter Cash Flow actual financial data as a basis for the Discounted Cash Flow (DCF) Excel Model
4
Enter Cash Flow actual financial data as a basis for the Discounted Cash Flow (DCF) Excel Model Go to the Valuation tab Enter Capex (Capital Expenditure) which is the annual investments for the company each year. This is normally specified in the Annual Report under Cash Flow Statement or in a specific note (in the annual report). Enter Net Working Capital (“NWC”) for each historical year. This is also normally specified in the annual report. If not, do the following: - Add Account receivables + Inventory and Prepaid expenses and other: This information will sum up to Total Current Assets - Subtract Account payable+ Accrued Liabilities and Current Liabilities: This will sum up Total Current Liabilities - The Net Working Capital is calculated as Total Current Assets – Total Current Liabilities
5
Make a Income Statement forecast for the next five years 6 The forecast in the Discounted Cash Flow model has a large impact on the valuation, therefore, this step is extremely important. Net sales – Make a forecast of net sales by changing the growth, % – If you have access to analyst reports of the company, use those estimates or enter yours. Gross profit – Make forecast of gross profit by changing margin, %. OPEX – Make forecast of the company’s operating expenses by changing the growth, % Depreciation – Make forecast of depreciation by changing % of net sales. – look at historical depreciation in relation to sales and use an average from these years to use in the projection period.
Make a Corporate income tax forecast for the next five years 7
Go to the WACC tab Corporate income tax – use either the historical implied tax level or the corporate business tax applied in your country. We have used 30% in this example.
Make a CAPEX and NWC forecast for the next five years
8
Go to the Valuation tab CAPEX – Make a CAPEX forecast by changing the % of net sales – Determine capital expenditures that you believe the company will have in the future. This is quite difficult to estimate, therefore you can use an average of the last five years in relation to sales. NWC – Make an NWC forecast by changing the % of net sales – Determine net working capital that you believe the company will have in the future. This is quite difficult to estimate, therefore you can use an average of the last five years in relation to
Target Capital Structure and Beta
Important: This section is for you who have access to a financial database such as Bloomberg, Reuters, FactSet or similar and know how to do Trading comps. If you do not have such access, you can type in 30% into Debt to Total Capitalization and 70% into Equity to Total Capitalization Cells D7 and D8 in the WACC assumptions sheet! This is the most frequently used assumption when determining capital structure in a Discounted Cash Flow model. Or simply use a fixed WACC! If you work with a fixed WACC, then you can jump to "Determine WACC". But remember to type the WACC into cell E23 in the WACC sheet. However, the below-described method is more accurate and preferred if you have all the
9
ASSUMPTIONS AND INPUT 1. Identify a couple of listed companies that are similar to the one that you perform the DCF valuation of (peer group) 2. Enter the listed companies beta which can be found in a financial database such as Bloomberg 3. Enter the Market Value of debt of these companies. The market value of debt is the same as the book value of debt 4. Enter the market cap for the traded peers (share price * outstanding shares) 5. Enter the marginal tax rate each company 6. All the above information can be sourced from a financial database provider. Now the model will calculate the Beta and unlevered and levered Beta and put as input in the valuation model.
Seasonality Setup
10
Determine WACC The capital structure is given from the previous step and works as a base for determining WACC in the calculation below. If you want to go fast-track here… Simply use a fixed WACC and ASSUMPTIONS enter this into FOR WACC 1. Enter risk-free rate. This is the same rate as the 10-year treasury bond and can most likely be found on your cell E23 the in the country's government’s website, or by google e.g. “10-year interest rate in US” WACC sheet. 2. Enter the market risk premium – this is used to adjust for specific company risks and should be 7.1% according to Ibbotson. This change yearly so google your source. 3. The Levered Beta is given from the previous Step 4. Now add a size premium according to Ibbotson as well. We have used 1.7% since our company is pretty small 5. Now enter the cost of debt, the rate which your company can borrow money at. 6. The corporate tax rate has been given by previous steps.
Present value of free cash flow
11
Next step is to calculate the present value of the generated cash flows in the projection period. Perpetuity growth rate is the rate at which the economy is expected to grow at, this is normally around 2-3% in most well-developed countries. It should be equal to the long term inflation rate target. Make sure the Implied Exit Multiple isn’t too high since that probably means your assumptions are too aggressive in the Terminal Year. Another sanity check is to see whether the terminal value is higher than 70% of the Enterprise Value, then it is likely that something is wrong with your assumptions (they can simply be too aggressive, or your company is in
Calculate Terminal Value
12
The Terminal Value has the largest impact on the valuation and it is extremely important that this input is correctly made. Most inputs are already given at prior step.
Enterprise Value
13
The DCF valuation is almost done, you have made all the inputs required and the Enterprise Value is already calculated. In the results (see screenshot) you can see the Enterprise Value. You should also enter debt, cash (=net debt) and outstanding shares to get additional information on your valuation and to get the equity value/market cap and price per share.
Combined graphs
Check EBITDA and Free Cash Flow actuals and forecast conbined And Present value of free cash flow and implied multiples by years for the forecast
14
Thank you very much for your interest in my tools! If you enjoyed using my model, please let people know about your experience! Give 5-star feedback here & share this page across social networks If you have any suggestions or ideas of how to make this model more useful, don't hesitate, just send me a PM - Henry Sheykin