50.1K

Verified Solution

Link Copied!

Need help with the blanks, please and thank you!

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. Year #1 NA Discount Rate Cost of Borrowing Cost of Equity Debt to Equity 3.3% 5.5% 15.0% 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) expens should deprec 3 NA NA 0 Year # 0 1 2 3 4 5 Investment $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ Incremental Revenue Variable expenses Fixed Expenses EBITDA Deductible Depr. Exp 12,500 $ 1,875 1,000 25,375 #DIV/0! 12,500 $ 1,875 1,000 25,375 13,500 $ 2,025 1,000 26,525 #DIV/01 #DIV/0! 40% 14,500 $ 2,175 1,000 27,675 #DIV/0! 15,500 2,325 1,000 28,825 10,000 #DIV/0! #DIV/0! EBIT 10,000 #DIV/01 #DIV/0! 40% #DIV/0! 40% #DIV/0! 40% Tax rate 40% Taxes #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Net Cash Flows $ 10,000 #DIV/0! #DIV/0! #DIV/0! #DIV/0! PV of Cash Flows $ $ 10,000 10,000 $ Cumulative PV of NCF 10,000 $ 10,000 $ 10,000 $ 10,000 $ 10,000 Investment Measures: NPV IRR Approx Payback (In Years) 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. 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. 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. Year #1 NA Discount Rate Cost of Borrowing Cost of Equity Debt to Equity 3.3% 5.5% 15.0% 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) expens should deprec 3 NA NA 0 Year # 0 1 2 3 4 5 Investment $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ 10,000 $ Incremental Revenue Variable expenses Fixed Expenses EBITDA Deductible Depr. Exp 12,500 $ 1,875 1,000 25,375 #DIV/0! 12,500 $ 1,875 1,000 25,375 13,500 $ 2,025 1,000 26,525 #DIV/01 #DIV/0! 40% 14,500 $ 2,175 1,000 27,675 #DIV/0! 15,500 2,325 1,000 28,825 10,000 #DIV/0! #DIV/0! EBIT 10,000 #DIV/01 #DIV/0! 40% #DIV/0! 40% #DIV/0! 40% Tax rate 40% Taxes #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! Net Cash Flows $ 10,000 #DIV/0! #DIV/0! #DIV/0! #DIV/0! PV of Cash Flows $ $ 10,000 10,000 $ Cumulative PV of NCF 10,000 $ 10,000 $ 10,000 $ 10,000 $ 10,000 Investment Measures: NPV IRR Approx Payback (In Years) 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. 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

Answer & Explanation Solved by verified expert

See Answer