Pleaes provide excel functions to fill missing cells ( ??? ) : Flower Engineerin
ID: 2644881 • Letter: P
Question
Pleaes provide excel functions to fill missing cells ( ??? ):
Flower Engineering is considering two mutually exclusive investments. The projects' expected net cash flows are as follows: Expected Net Cash Flows Time Project A Project B 0 ($375) ($575) 1 ($300) $200 2 ($200) $200 3 ($100) $200 4 $600 $200 5 $600 $200 6 $975 $200 7 ($200) $0 Part 1: A) Net Present Value Cost of capital = 12% Cost of capital = 18% NPV A = $224.81 NPV A =$30.84 (See example 2 at http://office.microsoft.com/en-us/excel-help/npv-HP005209199.aspx) NPV B = $220.79 NPV B =$105.53 B) NPV Profiles and Graph WACC Project A Project B 0% ??? ??? 2% ??? ??? 4% ??? ??? 6% ??? ??? 8% ??? ??? 10% ??? ??? 12% ??? ??? 14% ??? ??? 16% ??? ??? 18% ??? ??? 20% ??? ??? 22% ??? ??? 24% ??? ??? 26% ??? ??? 28% ??? ??? 30% ??? ???Explanation / Answer
In excel, often the initial outlay is not considered at time 0. So overcome this problem, the initial outlay is deducted from NPV core function as shown below (assuming Outlay is made at end of period 0).
For Project A:
NPV Function is given as
= NPV (Disc_Rate, -300, -200, -100, 600, 600, 975, -200) - 375
In place of discount rate, write the appropriate WACC.
when WACC = 0:
=NPV(0%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 2%
=NPV(2%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 4%:
=NPV(4%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 6%:
=NPV(6%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 8%:
=NPV(8%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 10%:
=NPV(10%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 12%:
=NPV(12%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 14%:
=NPV(14%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 16%:
=NPV(16%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 18%:
=NPV(18%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 20%:
=NPV(20%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 22%:
=NPV(22%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 24%:
=NPV(24%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 26%:
=NPV(26%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 28%:
=NPV(28%, -300, -200, -100, 600, 600, 975, -200) - 375
when WACC = 30%:
=NPV(30%, -300, -200, -100, 600, 600, 975, -200) - 375
PROJECT B
NPV Function is given as
= NPV (Disc_Rate, 200, 200, 200, 200, 200, 200, 0) - 575
In place of discount rate, write the appropriate WACC.
when WACC = 0%:
=NPV(0%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 2%:
=NPV(2%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 4%:
=NPV(4%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 6%:
=NPV(6%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 8%:
=NPV(8%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 10%:
=NPV(10%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 12%:
=NPV(12%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 14%:
=NPV(14%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 18%:
=NPV(18%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 20%:
=NPV(20%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 22%:
=NPV(22%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 24%:
=NPV(24%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 26%:
=NPV(26%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 28%:
=NPV(28%, 200, 200, 200, 200, 200, 200, 0) - 575
when WACC = 30%:
=NPV(30%, 200, 200, 200, 200, 200, 200, 0) - 575