I need this solved if possible, thanks a lot in advance! Right click, open image in new tab to be able to see proper zoomed in version. Thanks. Everything should be there.
NPV Assignment Version #3 Scenario: Trojan, LLC is contemplating a $250,000 investment of equipment that they feel can add $175,000 in revenues in Year #1, and increase by 2.5% each year thereafter. They estimate variable expenses at 10% of Sales and additional fixed costs would be approximately $50,000 per year. The equipment should last 5 years and have a salvage value of $35,000 at the end of its life. They've decided to use straight line depreciation over the equipment life NA Discount Rate Cost of Borrowing Cost of Equity Debt to Equity 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) 5.5% 15.0% NA NA 0 Year # o 1 2 3 4 5 Investment $ $ Incremental Revenue Variable expenses Fixed Expenses EBITDA Deductible Depr. Exp EBIT Tax rate #DIV/0! #DIV/0! #DIV/0! 40% #DIV/01 #DIV/0! 40% #DIV/0! #DIV/0! 40% #DIV/0! #DIV/01 40% #DIV/0! 40% #DIV/0! #DIV/0! #DIV/0! #DIV/0! Taxes Net Cash Flows #DIV/0! #DIV/0! $ #DIV/0! #DIV/0! #DIV/0! #DIV/0! PV of Cash Flows Cumulative PV of NCF Investment Measures: $ $ $ $ $ NPV IRR Approx Payback (In Years) 5.00 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? NPV Assignment Version #3 NA 0.055 Investment Information: Initial Investment Opportunity cost (if any)= Lifetime of the investment Salvage Value at end of proje Dep meth(1:St.line; 2:DDB) Tax Credit (if any) Other invest.(non-depreciab Discount Rate Cost of Borrowing Cost of Equity Debt to Equity 0.15 3 NA NA 0 Year # 0 1 2 3 4 5 Investment =-D4 =D7 Incremental Revenue Variable expenses Fixed Expenses EBITDA SUM(D15:019) =SUM(F15:F19) =SUM(G15:619) =SUM(H15:H19) Deductible Depr. Exp =E21 =F21 =G21 SUM(E15:E19) =D15/$D$6 =SUM(E20:521) 0.4 SUM(115:119) =H21 =SUM(120:121) 0.4 EBIT =SUM(D20:D21) =SUM(F20:F21) 0.4 =SUM(G20:521) 0.4 =SUM(H20:H21) 0.4 Tax rate Taxes --G22623 =-H22*H23 =-E22E23 =E22+E24 =-F22*F23 =F22+F24 =-122*123 =122+124 Net Cash Flows =D22+D24 =G22+G24 =H22+H24 PV of Cash Flows Cumulative PV of NCF =D25 =D27 =E27+D27 =F27+E28 =G27+F28 =H27+G28 =127+H28 Investment Measures: NPV IRR Approx Payback (In Years) =IF(D28>0,0,1F(E28>0,013+(-D28/E27),IF(F28>0, E13+(-E28/F27),IF(G28>0,F13+(-F28/G27),IF(H28>0,613#-G28/H27),IF(128>0, H13+(-H28/127),5)))) Instructions: Complete the above worksheet using the information provided in the Scenario at the top right. 1) 2) 3) A Yellow box means you need to inse An Orange box means you need to de A Green box already has formulas an A Grey box of any shade you need no 5) 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? NPV Assignment Version #3 Scenario: Trojan, LLC is contemplating a $250,000 investment of equipment that they feel can add $175,000 in revenues in Year #1, and increase by 2.5% each year thereafter. They estimate variable expenses at 10% of Sales and additional fixed costs would be approximately $50,000 per year. The equipment should last 5 years and have a salvage value of $35,000 at the end of its life. They've decided to use straight line depreciation over the equipment life NA Discount Rate Cost of Borrowing Cost of Equity Debt to Equity 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) 5.5% 15.0% NA NA 0 Year # o 1 2 3 4 5 Investment $ $ Incremental Revenue Variable expenses Fixed Expenses EBITDA Deductible Depr. Exp EBIT Tax rate #DIV/0! #DIV/0! #DIV/0! 40% #DIV/01 #DIV/0! 40% #DIV/0! #DIV/0! 40% #DIV/0! #DIV/01 40% #DIV/0! 40% #DIV/0! #DIV/0! #DIV/0! #DIV/0! Taxes Net Cash Flows #DIV/0! #DIV/0! $ #DIV/0! #DIV/0! #DIV/0! #DIV/0! PV of Cash Flows Cumulative PV of NCF Investment Measures: $ $ $ $ $ NPV IRR Approx Payback (In Years) 5.00 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? NPV Assignment Version #3 NA 0.055 Investment Information: Initial Investment Opportunity cost (if any)= Lifetime of the investment Salvage Value at end of proje Dep meth(1:St.line; 2:DDB) Tax Credit (if any) Other invest.(non-depreciab Discount Rate Cost of Borrowing Cost of Equity Debt to Equity 0.15 3 NA NA 0 Year # 0 1 2 3 4 5 Investment =-D4 =D7 Incremental Revenue Variable expenses Fixed Expenses EBITDA SUM(D15:019) =SUM(F15:F19) =SUM(G15:619) =SUM(H15:H19) Deductible Depr. Exp =E21 =F21 =G21 SUM(E15:E19) =D15/$D$6 =SUM(E20:521) 0.4 SUM(115:119) =H21 =SUM(120:121) 0.4 EBIT =SUM(D20:D21) =SUM(F20:F21) 0.4 =SUM(G20:521) 0.4 =SUM(H20:H21) 0.4 Tax rate Taxes --G22623 =-H22*H23 =-E22E23 =E22+E24 =-F22*F23 =F22+F24 =-122*123 =122+124 Net Cash Flows =D22+D24 =G22+G24 =H22+H24 PV of Cash Flows Cumulative PV of NCF =D25 =D27 =E27+D27 =F27+E28 =G27+F28 =H27+G28 =127+H28 Investment Measures: NPV IRR Approx Payback (In Years) =IF(D28>0,0,1F(E28>0,013+(-D28/E27),IF(F28>0, E13+(-E28/F27),IF(G28>0,F13+(-F28/G27),IF(H28>0,613#-G28/H27),IF(128>0, H13+(-H28/127),5)))) Instructions: Complete the above worksheet using the information provided in the Scenario at the top right. 1) 2) 3) A Yellow box means you need to inse An Orange box means you need to de A Green box already has formulas an A Grey box of any shade you need no 5) 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
