41 E. Bonus Question: Given all the uncertainties of the economy and the business, the company try to conduct a sensitivity analysis base on different annal growth rate of earnings. What would be the NPV for the listed growth rate below? 42 Hint: use data table 5 pts Possibl e Annual Earning NPY Growth Rate 44 45 2% 47 48 50 51 52 53 54 55 6% 8% 1074 12% 14% 16% 18% 20% REY A43 B D E H A 3 Fill out the cells and answer the questions. 44 0 5. A firm plan to purchase a machine cost $35,000 6 Tax Rate 21% 7 1st Year Sale $8,000 8 Annual Earning Growth 10% 9 Useable Life 5 Years 10 Salvage Value 11 Depreciation Method Straight line 12 13 14 15 16 17 a. Fill out the cells for calculating Cash Flow (Straight line depreciation) 10 pts 18 Year 0 2 3 19 Depreciation Rate 20.00% 20.00% 20.00% 20.00% 20 Earnings before depreciation and taxes (EBITDA) 8,000 8,800 9,680 10,648 21 Depreciation 22 Earnings before taxes 23 Tax 24 Net operating profit after tax Problem Problem 4 Problem 5 5 20.00% 11,713 B E G D a. Fill out the cells for calculating Cash Flow (Straight line depreciation) 10 pts Year 0 2 Depreciation Rate 20.00% 20.00% Earnings before depreciation and taxes (EBITDA) 8,000 8,800 Depreciation Earnings before taxes 3 20.00% 9,680 4 20.00% 10,648 5 20.00% 11,713 Tax Net operating profit after tax Capital investment (no salvage value) Add back depreciation Free cash flow (835,000) -35,000 Discount rate 10.00% b. NPV 5pts C. IRR 5pts d. Should the firm purchase this machine? Why? Instruction Problem 2 Problem 1-must answer first Problem 3 Problem 4 Problem 5 C D E E F G H 0 5 1 2 3 4 B a) Year Depreciation rate EBITDA Depreciation Earnings before taxes Tax Net operating profit after tax Capital investment Add back depreciation Free cash flow 1 20% 8000 7000 1000 210 790 2 20% 8800 7000 1800 378 1422 3 20% 9680 7000 2680 563 2117 4 20% 10648 7000 3648 766 2882 20% 11713 7000 4712.8 990 3723 -35000 7000 7790 7000 8422 7000 9117 7000 9882 7000 10723 -35000 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Discount rate 10% b) NPV S-700.27 c) IRR 9.26% d) The firm should not purchase this machine. Because, the NPV is negative and the IRR is less than the cost of capital. EXCEL FORMULA: D E H 0 0.2 4 5 6 7 8 9 a) Year Depreciation rate EBITDA Depreciation Earnings before taxes Tax Net Operating profit after tax Capital investment Add back depreciation Free cash flow 1 02 8000 - 35000) -04-05 -06 21% -06-07 2 3 4 5 0.2 0.2 02 -D4F1-108-E49(1.10%)-F4(I-10%)-G41:10%) -3500003 - 35000"F3 - 35000*63 - 35000 H3 -E4-ES -G4-65 --5 E6213 -F6921% --G6213 6215 -E6-7 -G6-G7 -35000 -15 - -DS -08010 -GS -681610 EB4E10 -F8F10 -10 10 11 12 13 14 15 16 Discount rate 0.1 b) NPV c) IRR NPV(C13,011:11)+C11 RRC11:21) 41 E. Bonus Question: Given all the uncertainties of the economy and the business, the company try to conduct a sensitivity analysis base on different annal growth rate of earnings. What would be the NPV for the listed growth rate below? 42 Hint: use data table 5 pts Possibl e Annual Earning NPY Growth Rate 44 45 2% 47 48 50 51 52 53 54 55 6% 8% 1074 12% 14% 16% 18% 20% REY A43 B D E H A 3 Fill out the cells and answer the questions. 44 0 5. A firm plan to purchase a machine cost $35,000 6 Tax Rate 21% 7 1st Year Sale $8,000 8 Annual Earning Growth 10% 9 Useable Life 5 Years 10 Salvage Value 11 Depreciation Method Straight line 12 13 14 15 16 17 a. Fill out the cells for calculating Cash Flow (Straight line depreciation) 10 pts 18 Year 0 2 3 19 Depreciation Rate 20.00% 20.00% 20.00% 20.00% 20 Earnings before depreciation and taxes (EBITDA) 8,000 8,800 9,680 10,648 21 Depreciation 22 Earnings before taxes 23 Tax 24 Net operating profit after tax Problem Problem 4 Problem 5 5 20.00% 11,713 B E G D a. Fill out the cells for calculating Cash Flow (Straight line depreciation) 10 pts Year 0 2 Depreciation Rate 20.00% 20.00% Earnings before depreciation and taxes (EBITDA) 8,000 8,800 Depreciation Earnings before taxes 3 20.00% 9,680 4 20.00% 10,648 5 20.00% 11,713 Tax Net operating profit after tax Capital investment (no salvage value) Add back depreciation Free cash flow (835,000) -35,000 Discount rate 10.00% b. NPV 5pts C. IRR 5pts d. Should the firm purchase this machine? Why? Instruction Problem 2 Problem 1-must answer first Problem 3 Problem 4 Problem 5 C D E E F G H 0 5 1 2 3 4 B a) Year Depreciation rate EBITDA Depreciation Earnings before taxes Tax Net operating profit after tax Capital investment Add back depreciation Free cash flow 1 20% 8000 7000 1000 210 790 2 20% 8800 7000 1800 378 1422 3 20% 9680 7000 2680 563 2117 4 20% 10648 7000 3648 766 2882 20% 11713 7000 4712.8 990 3723 -35000 7000 7790 7000 8422 7000 9117 7000 9882 7000 10723 -35000 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Discount rate 10% b) NPV S-700.27 c) IRR 9.26% d) The firm should not purchase this machine. Because, the NPV is negative and the IRR is less than the cost of capital. EXCEL FORMULA: D E H 0 0.2 4 5 6 7 8 9 a) Year Depreciation rate EBITDA Depreciation Earnings before taxes Tax Net Operating profit after tax Capital investment Add back depreciation Free cash flow 1 02 8000 - 35000) -04-05 -06 21% -06-07 2 3 4 5 0.2 0.2 02 -D4F1-108-E49(1.10%)-F4(I-10%)-G41:10%) -3500003 - 35000"F3 - 35000*63 - 35000 H3 -E4-ES -G4-65 --5 E6213 -F6921% --G6213 6215 -E6-7 -G6-G7 -35000 -15 - -DS -08010 -GS -681610 EB4E10 -F8F10 -10 10 11 12 13 14 15 16 Discount rate 0.1 b) NPV c) IRR NPV(C13,011:11)+C11 RRC11:21)