FIN 3320-Excel Project Use Excel to evaluate the following capital budgeting pro

Question

FIN 3320-Excel Project Use Excel to evaluate the following capital budgeting project for the best, base, and worst cases. You will calculate the following values for each case: Net present value Internal rate of return Profitability Index .Payback . Discounted payback Average Accounting Return s below. Note: You must do your calculations in Excel. Do not do them with a calculator and type them in. Example: For the base case, unit sales is 100,000 in year 1. The assumptions state that unit sales will decline in the second year by 1,000. Assuming that the year 1 unit sales is in cell B46, year 2 unit sales would be in C46 and the formula in that cell would be B46-1,000 Here is information about the project. . Initial Fixed Asset investment- $9,000,000 Net Working Cap Requirement- $800,000 Time frame 5 years Marginal tax rate-35% .5-year straight line depreciation with assumption of zero salvage value For negative net pre-tax incomes, you must include negative taxes, i.e., if the project loses money, it will reduce the company's taxable income, thus lowering its tax bil. EACH CASE MUST BE ON A SEPARATE PAGE AND FORMATTED TO BE ON ONE PAGE, OR MINUS 20 POINTS.

Explanation / Answer

Answer : Step 1:Let me first summarize the formulas used in the calculations

Present value formula : cash flow/( 1 + interest rate/100) ^ ( no of years)

for calculating NPV sum all the PV values

IRR use excel function to calculate. IRR is the value where NPV becomes 0

Payback is the time period in years where net investment comes back and we are at break even

Discounted payback cash flow used in this are discounted for the time value of money

profitability Index : PV of cash flows / investment in the project. In PV of cash flows do not subtract investment amount

Average accounting return is average net income / average investment

average investment is ( initial year investment + salvage value)/2

After tax salvage value calculation : Actual salvage value * (1 - tax rate)

Now the calculation part

Step 2: Base case table:

Worst case scenario table:

Best case scenario table:

0 1 2 3 4 5 Initial fixed asset investment 9000000 net working capital requirement 800000 Unit sales 100000 99000 98000 97000 96000 price/unit 200 195 190 185 180 varcost/unit 150 151 152 153 154 Revenue 20000000 19305000 18620000 17945000 17280000 Variable cost 15000000 14949000 14896000 14841000 14784000 Fixed costs year 30000 30000 30000 30000 30000 Depreciation 1800000 1800000 1800000 1800000 1800000 EBIT 3170000 2526000 1894000 1274000 666000 Tax(35%) 1109500 884100 662900 445900 233100 Net income 2060500 1641900 1231100 828100 432900 Depreciation 1800000 1800000 1800000 1800000 1800000 After tax salvage 0 0 0 0 65000 Return of working capital 0 0 0 0 800000 Cash flow -9800000 3860500 3441900 3031100 2628100 3097900 Accumulted Cash flow -5939500 -2497600 533500 3161600 6259500 Discounted cash flow -9800000 3509545 2844545 2277310 1795028 1923552 Net present value 2549981 IRR 9% PI 1.260202 Payback 2.823991 Discounted payback 3.65102 Average Accounting return 27%

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.