Need to submit one copy of your Capital Budgeting (see attached).
The deliverable will include the Completed MS Excel Worksheet CapitalProject Model and a narrative explaining how you arrived at your solutions and recommendations.The Capital Budgeting Project narrative should be4 to 6 pages(1200 to 1800 words) andwritten in APA format.APAformat includes a Cover sheet with Turnitin Originally Score, Summary, Table of Contents, and Reference Page (with at least three refernces.)
Capital Budgeting Basics, Build a Model Model 10/8/20 Build a Model Sunrise Nursery is considering two mutually exclusive investments. The projects' expected net cash flows are as follows: Expected net cash flows TimeProject AProject B 0($375)($575) 1($300)$190 2($200)$190 3($100)$190 4$600$190 5$600$190 6$926$190 7($200)$0 a. If you were told that each project's discount rate was 12 percent, which project should be selected? If the discount rate was 18 percent, what would be the proper choice? @ a 12% cost of capital@ a 18% cost of capital Discount rate =12%Discount rate18% NPV A =NPV A = NPV B =NPV B = b. Construct NPV profiles for Projects A and B. Before we can graph the NPV profiles for these projects, we must create a data table of project NPV relative to differing discount rates. Project AProject B 0% 2% 4% 6% 8% 10% 12% 14% 16% 18% 20% 22% 24% 26% 28% 30% c. What is each project's IRR? We find the internal rate of return with Excel's IRR function: IRR A =Note in the graph above that the X-axis intercepts are equal to the two projects' IRRs. IRR B = d. What is each project's MIRR at a rate of 12 percent? At r = 18%? (Hint: Consider Period 7 to be the end of Project B's life.) @ a 12% cost of capital@ a 18% cost of capital MIRR A =MIRR A = MIRR B =MIRR B = e. What is the crossover rate, and what is its significance? Cash flow TimedifferentialTake the cashflow differences of the two projects and calculate IRR to get the cross over rate 0 1 2Crossover rate = 3 4The crossover rate represents the cost of capital at which the two projects 5have the same net present value. In this scenario, that common net present 6value, at a cost of capital of 13.13%, is: 7 f. What is the regular payback period for these two projects? Project A Time period:01234567 Cash flow:(375)(300)(200)(100)600$600$926($200) Cumulative cash flow: Payback: Project B Time period:01234567 Cash flow:(575)190190190190$190$190$0 Cumulative cash flow: Payback: g. At a discount rate of 12%, what is the discounted payback period for these two projects? RATE =12% Project A Time period:01234567 Cash flow:(375)(300)(200)(100)600$600$926($200) Disc. cash flow: Disc. cum. cash flow: Discounted Payback: Project B Time period:01234567 Cash flow:(575)190190190190$190$190$0 Disc. cash flow: Disc. cum. cash flow: Discounted Payback: Net Present Value of "A" discounted at a WACC of 12% Net Present Value of "A" discounted at a WACC of 18% The IRR for the Cash Flow Differential The difference in cash flows between project "A" and project "B". Use Excel's MIRR function