Lancers, Ltd. is contemplating a $350,000 investment of equipment that they feel can add $90,000 in revenues in Year #1 and Year #2, and $100,000 in revenue in Year 3, and $105,000 in Years 4 & 5. They estimate variable expenses at 7.5% of Sales and additional fixed costs would be approximately $9,500 per year. The equipment should last 5 years and have a salvage value of $15,000 at the end of its life. They've decided to use straight line depreciation over the equipment life. Instructions: 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? A C D F H 1 NPV Assignment Version #4 2 3 Investment Information: 4 Initial Investment= $0 Discount Rate 0.0% Opportunity cost (if any)= Lifetime of the investment NA Cost of Borrowing 7.5% 6. Cost of Equity 15.0% 7 Salvage Value at end of project= $0 Debt to Equity NA 11 12 Year# 13 1 2 4 14 15 Investment 16 17 Incremental Revenue 2$ 2$ 18 Variable expenses 19 Fixed Expenses 20 EBITDA 21 Deductible Depr. Exp #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 22 EBIT #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 23 Tax rate 40% 40% 40% 40% 40% 24 Taxes #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 25 Net Cash Flows 2$ #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! 26 27 PV of Cash Flows $4 2$ 2$ 24 24 28 Cumulative PV of NCF 2$ $4 2$ 2$ $4 29 Investment Measures: 30 NPV 31 IRR