Capital budget techniques
dimles
DCF-Model_Team_1.xlsxCover Page
| DCF Model | Strictly Confidential | ||||||||||||
| Table of Contents | |||||||||||||
| DCF Model | |||||||||||||
| Notes | |||||||||||||
| This Excel model is for educational purposes only and should not be used for any other reason. | |||||||||||||
| All content is Copyright material of CFI Education Inc. | |||||||||||||
| https://corporatefinanceinstitute.com/ | |||||||||||||
| © 2019 CFI Education Inc. | |||||||||||||
| All rights reserved. The contents of this publication, including but not limited to all written material, content layout, images, formulas, and code, are protected under international copyright and trademark laws. | |||||||||||||
| No part of this publication may be modified, manipulated, reproduced, distributed, or transmitted in any form by any means, including photocopying, recording, or other electronic or mechanical methods, | |||||||||||||
| without prior written permission of the publisher, except in the case of certain noncommercial uses permitted by copyright law. | |||||||||||||
DCF Model
| © Corporate Finance Institute®. All rights reserved. | |||||||||||||
| DCF Model | |||||||||||||
| Assumptions | |||||||||||||
| Tax Rate | 25% | ||||||||||||
| Discount Rate | 12% | ||||||||||||
| Perpetural Growth Rate | 3% | ||||||||||||
| EV/EBITDA Mulltiple | 7.0x | ||||||||||||
| Transaction Date | 12/31/17 | ||||||||||||
| Fiscal Year End | 6/30/18 | ||||||||||||
| Current Price | 25.00 | ||||||||||||
| Shares Outstanding | 20,000 | ||||||||||||
| Debt | 30,000 | ||||||||||||
| Cash | 239,550 | ||||||||||||
| Capex | 15,000 | ||||||||||||
| Discounted Cash Flow | Entry | 2018 | 2019 | 2020 | 2021 | 2022 | Exit | Terminal Value | |||||
| Date | 12/31/17 | 6/30/18 | 6/30/19 | 6/30/20 | 6/30/21 | 6/30/22 | 6/30/22 | Perpetural Growth | 537,981 | ||||
| Time Periods | 0 | 1 | 2 | 3 | 4 | EV/EBITDA | 546,278 | ||||||
| Year Fraction | 0.50 | 1.00 | 1.00 | 1.00 | 1.00 | Average | 542,129 | ||||||
| EBIT | 47,814 | 51,095 | 55,861 | 58,693 | 63,039 | ||||||||
| Less: Cash Taxes | 11,954 | 12,774 | 13,965 | 14,673 | 15,760 | ||||||||
| Plus: D&A | 15,008 | 15,005 | 15,003 | 15,002 | 15,001 | ||||||||
| Less: Capex | 15,000 | 15,000 | 15,000 | 15,000 | 15,000 | ||||||||
| Less: Changes in NWC | 375 | 611 | 398 | 511 | 272 | ||||||||
| Unlevered FCF | 35,494 | 37,715 | 41,501 | 43,510 | 47,008 | ||||||||
| (Entry)/Exit | (290,450) | 542,129 | |||||||||||
| Transaction CF | - | 17,747 | 37,715 | 41,501 | 43,510 | 47,008 | 542,129 | ||||||
| Transaction CF | (290,450) | 17,747 | 37,715 | 41,501 | 43,510 | 47,008 | 542,129 | ||||||
| Intrinsic Value | Market Value | Rate of Return | |||||||||||
| Enterprise Value | 462,983 | Market Cap | 500,000 | Target Price Upside | 35% | ||||||||
| Plus: Cash | 239,550 | Plus: Debt | 30,000 | Internal Rate of Return (IRR) | 26% | ||||||||
| Less: Debt | 30,000 | Less: Cash | 239,550 | ||||||||||
| Equity Value | 672,532 | Enterprise Value | 290,450 | Market Value vs Intrinsic Value | |||||||||
| Market Value | 25.00 | ||||||||||||
| Equity Value/Share | 33.63 | Equity Value/Share | 25.00 | Upside | 8.63 | ||||||||
| Intrinsic Value | 33.63 | ||||||||||||
| Instructions | |||||||||||||
| Step 1: Get EBIT and D&A from the income statement | This file is for educational purposes only. E&OE | ||||||||||||
| Step 2: Calculate the net working capital | |||||||||||||
| There are two ways to find NWC: | |||||||||||||
| 1. NWC = Current Assets (less cash) - Current Liabilities (less debt) | |||||||||||||
| 2. NWC = Accounts Receivable + Inventory - Accounts Payable | |||||||||||||
| Corporate Finance Institute® | |||||||||||||
| https://corporatefinanceinstitute.com/ | |||||||||||||
Cash Flow
43281 43646 44012 44377 44742 17747.131000000001 37715.192064999996 41501.07372819999 43510.461665686991 47008.029943582238
