Need help making sure IRR is correct, also with 6B and 6C. Please and thank you!

J K M N o R S T Scenario: ABC Company is contemplating a $10,000 investment of equipment that they feel can add $12,500 in revenues in Year #1 and Year #2, and then increase by $1,000 above those levels each year there after. They estimate variable expenses at 15% of Sales and additional fixed costs would be approximately $1,000 per year. The equipment should last 5 years and have a salvage value of $1,000 at the end of its life. They've decided to use straight line depreciation over the equipment life. A B E F 1 NPV Assignment Version #1 2 3 4 5 Discount Rate $10,000 NA 5 $1,000 Investment Information: Initial Investment= Opportunity cost (if any)= Lifetime of the investment Salvage Value at end of project= Dep meth(1:St.line; 2:DDB)= Tax Credit (if any)= Other invest. (non-depreciable)= 7.9% 5.5% 15.0% 6 Cost of Borrowing Cost of Equity Debt to Equity 7 3 8 NA 9 NA 10 0 11 12 Year # 13 0 1 2 3 4 5 14 $ (10,000) 1,000 15 Investment 16 $ 17 Incremental Revenue 18 Variable expenses 19 Fixed Expenses 20 EBITDA 12,500 $ 1,875 1,000 15,375 (2,000.00) 12,500 $ 1,875 1,000 15,375 (2,000) 13,375 40% 13,500 $ 2,025 1,000 16,525 (10,000) 14,500 $ 2,175 1,000 17,675 (2,000) 15,675 40% 15,500 2,325 1,000 19,825 (2,000) 21 Deductible Depr. Exp (2,000) (10,000) 13,375 22 EBIT 23 Tax rate 14,525 40% 17,825 40% 40% 24 Taxes (5,350) (5,810) (6,270) (7,130) (5,350) 8,025 $ $ (10,000) $ 8,025 $ 8,715 $ 9,405 $ 10,695 25 Net Cash Flows 26 27 PV of Cash Flows $ 6,945 $ (10,000) $ (10,000) $ 7,439 $ (2,561) $ 6,896 $ 4,335 $ 6,942 $ 11,278 $ 7,321 25,544 28 Cumulative PV of NCF $ 18,223 $ 29 Investment Measures: 30 NPV 35,544 -10,000 7,439 31 IRR 66.0% 32 33 Approx Payback (In Years) 1.37 6,896 6,942 6,945 7,321 Instructions: 34 35 28 Cumulative PV of NCF $ (10,000) $ (2,561) $ 4,335 $ 11,278 $ 18,223 $ 25,544 29 Investment Measures: 30 NPV $ 35,544 -10,000 31 IRR 66.0% 7,439 32 Approx Payback (In Years) 33 34 35 36 37 38 1.37 6,896 6,942 Instructions: 6,945 7,321 1) Complete the above worksheet using the information provided in the Scenario at the top right. 2) A Yellow box means you need to insert numbers either hard coded or via a simple formula. 3) An Orange box means you need to derive what goes here by use of an Excel formula. 4) A Green box already has formulas and will update automatically as you complete the other areas. 5) A Grey box of any shade you need not do anything. Some of the info is FYI. 6) Explain three things: a) How did you choose your discount rate? b) Should you make this investment? Why? c) What other considerations do you feel are relevant in assessing the results of your work? 39 40 41 42 43 44 45 E1F7F5]=FGXX3+1) TII 18 Variable 19 Fixed EX 20 EBITDA =SUMID 15.019) SUM(DZD-021) vir 20 Curule Investor =128-015 F1RR/K30:635) -1F/028>0,0,1F/E28-0,013+(-0.28 Approx Payback (in Years)