B F GH K 1 Appendix 2 2 Sample Valuation Using the Net Present Value Method...

Transcribed Image Text:

B F GH K 1 Appendix 2 2 Sample Valuation Using the Net Present Value Method 3 Fill in the colored cells with your own calculation (cell should contain formula) For certain special numbers, such as "0" or "365" you can enter them manually without showing formula; Also note that the order of finishing these cells are not necessarily from top to bottom. 4 5 Year 1 Year 2 Year Year Year Year Year Year Year 9 6 7 Revenues 100 140 210 250 8 Costs 230 240 260 275 290 380 290 310 500 650 900 350 400 470 9 EBIT -130 -100 -50 -25 0 70 150 250 430 10 11 12 WACC Calculation 13 Tax Rate 40% 14 Rm - Rf 7.5% 15 EN 100% 16 Bu 1.2 17 10 Year Treasury 6.0% 18 WACC 19 20 Cash Flows 21 22 Terminal Growth Rate 23 24 Year 25 Revenues 26 Less: Costs 27 EBIT 28 Less: Tax 29 EBIAT 30 Less: Ch. NWC 31 Free Cash Flow 32 33 Discount Factor 34 PV (Cash Flow) 35 36 PV (Cash Flows) 37 Terminal Value 38 PV (Terminal Value) 3.0% 0 1 2 3 4 5 6 7 8 9 100 140 210 250 290 380 500 650 900 230 240 260 275 290 310 350 400 470 -130 -100 -50 -25 0 70 150 250 430 Net Present Value and 40 Sensitivity Analysis 41 42 PV (Cash Flows) 43 PV (Terminal Value) 44 Net Present Value 45 46 Tax Calculation 47 EBIT 48 NOLs Used 49 NOLS Added 50 Tax 51 52 Beginning NOLS WACC 13% 15% 17% Terminal 2% 0 0 0 Growth 3% 0 0 0 Rate 4% 0 0 0 -130 -100 -50 -25 0 70 150 250 430 100 53 Ending NOLS 54 55 Net Working Capital (10% sales) 56 Beg NWC 0 10 14 57 End NWC 10 14 21 22 21 25 225 29 38 29 38 50 5559 50 65 65 90 58 Ch. NWC 59 I already created formulas for this data table. As long as you finish the rest of the cells, this table should be generated automatically. Of course you are always welcome to delete what I did and practice creating this table in your own way. For these three rows, I will tolerate manual entrance for certain cells. Alternatively, you can creately use the MIN and MAX function to generate appropriate formulas. Also, don't forget the tax rate is given in cell B13 143 40 40 G H K L M N P R Xfx {TABLE(B18,B22)} J 41 WACC 42 0 13% 15% 17% 43 2% 0 0 0 44 3% 0 0 0 45 4% 0 0 0 I already created formulas for this data table. As long as you finish the rest of the cells, this table should be generated automatically. Of course you are always welcome to delete what I did and practice creating this table in your own way. Year Revenues 0 1 2 3 4 5 100 140 210 250 290 380 Less: Costs 230 240 260 275 290 310 EBIT -130 -100 -50 -25 0 70 Less: Tax 0 0 0 0 0 60000 7 8 500 650 900 350 400 470 150 250 430 0 0 26 172 EBIAT -130 -100 -50 -25 0 70 150 224 258 Less: Ch. NWC 10 4 7 4 4 9 12 15 25 Free Cash Flow -140 -104 -57 -29 -4 61 138 209 233 Discount Factor 0.870 0.756 0.658 0.572 0.497 0.432 0.376 0.327 0.284 PV (Cash Flow) -122 -79 -37 -17 -2 26 52 68 66 PV (Cash Flows) (44) Terminal Value PV (Terminal 2000 569 Value) Net Present Value and Sensitivity PV (Cash Flows) (44) PV (Terminal 569 Matue desent Value 525 Tax Calculation EBIT NOLS Used NOLS Added Tax WACC 525 13% 15% 17% Terminal 2% Growth 3% Rate 4% -130 -100 -50 -25 0 0 130 100 50 0 0 0 20 25 0000 70 70 2200 150 250 430 150 185 0 0 0 0 26 172 Beginning NOLs Ending NOLs 100 230 330 380 405 405 335 185 0 230 330 380 405 405 335 185 0 0 Net Working Capital (10% sales) Beg NWC End NWC Ch. NWC 0 10 14 21 25 29 10 14 21 25 29 38 10 4 4 4 29 38 50 12 525 50 65 65 90 15 25 This assignment on valuation is based on the Appendix 1 and 2 of "Note on Valuation in Private Equity Settings". You are asked to replicate the results in the two appendices by filling in the missing information (colored cells) with your own calculation results. That is, your results should be the same as those shown in the Appendicies but your cells should contain formulas. B F GH K 1 Appendix 2 2 Sample Valuation Using the Net Present Value Method 3 Fill in the colored cells with your own calculation (cell should contain formula) For certain special numbers, such as "0" or "365" you can enter them manually without showing formula; Also note that the order of finishing these cells are not necessarily from top to bottom. 4 5 Year 1 Year 2 Year Year Year Year Year Year Year 9 6 7 Revenues 100 140 210 250 8 Costs 230 240 260 275 290 380 290 310 500 650 900 350 400 470 9 EBIT -130 -100 -50 -25 0 70 150 250 430 10 11 12 WACC Calculation 13 Tax Rate 40% 14 Rm - Rf 7.5% 15 EN 100% 16 Bu 1.2 17 10 Year Treasury 6.0% 18 WACC 19 20 Cash Flows 21 22 Terminal Growth Rate 23 24 Year 25 Revenues 26 Less: Costs 27 EBIT 28 Less: Tax 29 EBIAT 30 Less: Ch. NWC 31 Free Cash Flow 32 33 Discount Factor 34 PV (Cash Flow) 35 36 PV (Cash Flows) 37 Terminal Value 38 PV (Terminal Value) 3.0% 0 1 2 3 4 5 6 7 8 9 100 140 210 250 290 380 500 650 900 230 240 260 275 290 310 350 400 470 -130 -100 -50 -25 0 70 150 250 430 Net Present Value and 40 Sensitivity Analysis 41 42 PV (Cash Flows) 43 PV (Terminal Value) 44 Net Present Value 45 46 Tax Calculation 47 EBIT 48 NOLs Used 49 NOLS Added 50 Tax 51 52 Beginning NOLS WACC 13% 15% 17% Terminal 2% 0 0 0 Growth 3% 0 0 0 Rate 4% 0 0 0 -130 -100 -50 -25 0 70 150 250 430 100 53 Ending NOLS 54 55 Net Working Capital (10% sales) 56 Beg NWC 0 10 14 57 End NWC 10 14 21 22 21 25 225 29 38 29 38 50 5559 50 65 65 90 58 Ch. NWC 59 I already created formulas for this data table. As long as you finish the rest of the cells, this table should be generated automatically. Of course you are always welcome to delete what I did and practice creating this table in your own way. For these three rows, I will tolerate manual entrance for certain cells. Alternatively, you can creately use the MIN and MAX function to generate appropriate formulas. Also, don't forget the tax rate is given in cell B13 143 40 40 G H K L M N P R Xfx {TABLE(B18,B22)} J 41 WACC 42 0 13% 15% 17% 43 2% 0 0 0 44 3% 0 0 0 45 4% 0 0 0 I already created formulas for this data table. As long as you finish the rest of the cells, this table should be generated automatically. Of course you are always welcome to delete what I did and practice creating this table in your own way. Year Revenues 0 1 2 3 4 5 100 140 210 250 290 380 Less: Costs 230 240 260 275 290 310 EBIT -130 -100 -50 -25 0 70 Less: Tax 0 0 0 0 0 60000 7 8 500 650 900 350 400 470 150 250 430 0 0 26 172 EBIAT -130 -100 -50 -25 0 70 150 224 258 Less: Ch. NWC 10 4 7 4 4 9 12 15 25 Free Cash Flow -140 -104 -57 -29 -4 61 138 209 233 Discount Factor 0.870 0.756 0.658 0.572 0.497 0.432 0.376 0.327 0.284 PV (Cash Flow) -122 -79 -37 -17 -2 26 52 68 66 PV (Cash Flows) (44) Terminal Value PV (Terminal 2000 569 Value) Net Present Value and Sensitivity PV (Cash Flows) (44) PV (Terminal 569 Matue desent Value 525 Tax Calculation EBIT NOLS Used NOLS Added Tax WACC 525 13% 15% 17% Terminal 2% Growth 3% Rate 4% -130 -100 -50 -25 0 0 130 100 50 0 0 0 20 25 0000 70 70 2200 150 250 430 150 185 0 0 0 0 26 172 Beginning NOLs Ending NOLs 100 230 330 380 405 405 335 185 0 230 330 380 405 405 335 185 0 0 Net Working Capital (10% sales) Beg NWC End NWC Ch. NWC 0 10 14 21 25 29 10 14 21 25 29 38 10 4 4 4 29 38 50 12 525 50 65 65 90 15 25 This assignment on valuation is based on the Appendix 1 and 2 of "Note on Valuation in Private Equity Settings". You are asked to replicate the results in the two appendices by filling in the missing information (colored cells) with your own calculation results. That is, your results should be the same as those shown in the Appendicies but your cells should contain formulas.