Hello I reallyneed some help with a case for Finance.

Please find attach a copy of the Mini Case at the bottom of the first page.

Thanks!

Problem (10-23) Expected Net Cash Flows Present values of cash flows Present values of Cash Year Project A Project B Discounting factor) Project A Project B Discounting factor (18% Project A Project B 0 -375 -575 1.0000 -375 -575 1.0000 -375 -575 1 -300 190 0.8929 -267.87 169.651 0.8475 -254.25 161.025 2 -200 190 0.7972 -159.44 151.468 0.7182 -143.64 136.458 3 -100 190 0.7118 -71.18 135.242 0.6086 -60.86 115.634 4 600 190 0.6355 381.3 120.745 0.5158 309.48 98.002 5 600 190 0.5674 340.44 107.806 0.4371 262.26 83.049 6 925 190 0.5066 468.605 96.254 0.37043 342.6478 70.3817 7 -200 0 0.4523 -90.46 0 0.3139 -62.78 0 NPV 226.395 206.166 17.85775 89.5497 Note that part (a) nd (b) of the question are combined for to choose between two project, NPV must be calculated, part (a) and (b) of the question are better combined. NPV is calculated the tradational way, that is, manually. To make decision between two mutually exclusive projects, NPV is calculated and the project with the hihgest NPV is selected. At a discounting rate of 12%, the project that should be chosen is project A as it has the highest NPV. At a dcsounting rate of 18%, project B should be selected. Part C (IRR) Year IRR Expected cash flows Cash Flow Difference Project A Project B A-B 0 -375 -575 200 1 -300 190 -490 2 -200 190 -390 3 -100 190 -290 4 600 190 410 5 600 190 410 6 925 190 735 7 -200 0 17.4503550589058% 18.623350262964% 23.9171058690276% IRR is calculated by clicking on "Insert function", then select IRR, from there, click the arrow on the dialogu box reading "Values" and feed cash flows for each project separately and IRR be automatically calculated. Part D (Crossover rate) Cross over rate is calculated by obatinaing the difference between cashflows of the two projects and then calculating the IRR of the calculated cash flows as done in the table above. Crossover rate is important for it tells a point in which a previsouly viable project becomes invalid when comapred to another. Crossover rate imporatnce can be better expalined using the diagram below. The crossover rate is 8.56%, at IRR below 8.56%, the project with the blue line is selected. At IRR above 8.56 %, the project with red line is selected. Part E (MIRR) Discount rate Year MIRR 12% Expected Cash Flow Project A Project B 0 -375 1 -300 2 -200 3 -100 4 600 5 600 6 925 7 -200 15% 18% -575 190 190 190 190 190 190 0 20% MIRR is calculated by inserting = sign and then MIRR and follow the process. Click on the calculated value and you will see the process. Part F (Pay Back Period). Expected cash flows Year Project A Balance 0 -375 1 -300 2 -200 3 -100 4 600 5 600 6 925 7 -200 Project B -375 -675 -875 -975 -375 225 1150 950 Balance -575 190 190 190 190 190 190 0 -575 -385 -195 -5 185 375 565 565 Pay Back period Project A Project B 4.625 Years. 3.026 Years. To find the pay back period, click on the answer to see the formula. Part G (Discounted Pay Back Period) Expected cash flows Year Project A Discounting factor (12%) Pv of cash flows 0 -375 1.0000 1 -300 0.8929 2 -200 0.7972 3 -100 0.7118 4 600 0.6355 5 600 0.5674 6 925 0.5066 7 -200 0.4523 Total PV of Cash Flows Discounted Pay Back Period Project A Project B 5.3 Years 4.0 Years Part H (Profitability Index) Profitability index = Present Value of Future Cash Flows/Initial Investment Required Project A = 601.395/375 = 1.60372 Project B = 781.166/575 = 1.35855 Cummulative PV Project B -375 -375 -267.87 -642.87 -159.44 -802.31 -71.18 -873.49 381.3 -492.19 340.44 -151.75 468.605 316.855 -90.46 226.395 601.395 PV of cash flows Cummulative PV -575 -575 -575 190 169.651 -405.349 190 151.468 -253.881 190 135.242 -118.639 190 120.745 2.106 190 107.806 109.912 190 96.254 206.166 0 0 206.166 781.166 h flows Part A Capital Budgeting refers to the evaluation and selection of long-term investements that are in consistent with the frim's goal of owners wealth maximization. Simply put,it refers to the selction of long-term projects that provide the best finacial benefits to the firm in any given situation. Part B Independent projects are projects whereby selection of one project does not affect selction of the others. Conversely, in mutually exclusive projects, selection of one project means the other project (s) has to be rejected. Part C Part C one Net Present Value, popularly known as NPV, is a type of calculation where initial ivestment amount is compared with the present value of future cash flows expected from the investment. To arrive at NPV figure, intial investment amount is subtracted from the total present value of cash flows. Expected Net Cash Flow Discounting Rate (Required rate of return) 10% Franchise L Franchise S 0 -100 -100 1 10 70 2 60 50 3 80 20 NPV $17.08 $18.17 Explanation on how to calculate NPV is done in the previous sheet. Part C two The rationale for NPV is that accept the project if NPV is greater than zeron in case of independent projects. In case of mutually exclsuive projects, the project with the highes NPV is selcted. If the two fanchises are indepndent, both of them will be selcted. If they are mutually exclusive, Franchise S will be selected as it has the higher NPV. Part C three NPV will absolotely change when cost of capital (the required rate of return is changed) since the Present Value of cashflows will also change. Part D Part D one Internal rate refetrs to theinterst rate at which NPV of a project is zero. Expcted Net Cash Flows Year Franchise L Franchise S 0 -100 -100 1 10 70 2 60 50 3 80 20 IRR 18.125779831658% 23.5640647468174% Year Expalanation on how to calculate IRR in excel in the previous sheet Part D two Bond yield to maturity equates the nominal value of the bond to the exected cupon payments to be received from the bond. Cupon paymanets are a form of cash flows. An investor cannot accept to invest in bond if its yield to maturity is less than the required rate of return. IRR on its part refers to the rate at which cash flows from a project is equal to zero, and a investor cannot invest in a project whose IRR is less than the investor's required rate of return. Both IRR and Yiled to Maturity are the same only that YTM is applied in bonds and IRR in the evaluation of projects, since both of them equates expected future cash flows to the initial investment cash outlay and decision criteria is the same. Part D three Logic behind IRR lies in the fact it indicates the profitability of a project in the arte of return sense. For example, if the rate of return is equal to the required rate of return of the investtor, the investor is just earning enough to recover the initial investment made and if IRR is greater than required rate of return, the investor earns a profit equivqlent to the duifference between the two projects. In making decision with IRR, in the case of independent projects, projects with higher IRR than the cost of capital are acceptd. In thecase of mutually exclusive projects, the project with the higher IRR is accepeted. Part D Four IRR is idependent of cost of capital as it relies on cash flows of the projects in their undiscounted form. Chage of cost of capital can only affect the acceptability of the project. Part E Part E One To come up with a table that can assit in plotiing the graph, use different disounting rates to find NPV Expected Cash Flows Yaer Franchise L Franchise L Cash Flows Difference Table of NPV Disount rates 0 -100 -100 0 Discount rate Franchise L Franchsie L 1 10 70 -60 0% $50 $40 2 60 50 10 5% $31.48 $27.90 3 80 20 60 10% $17.08 $18.17 NPV (0%) $50.00 $40.00 15% $5.08 $10.28 NPV (5%) $31.48 $27.90 20% ($3.09) $3.86 NPV (10%) $17.08 $18.17 25% ($10.11) ($1.41) NPV (15%) $5.80 $10.28 NPV (20%) ($3.09) $3.86 NPV (25%) ($10.11) ($1.41) IRR 8.68% NPV profile Plot $60 $50 $40 $30 NPV The two projects seem to intersect at a discount rate of between 8% and less than 10%. The rate at which they intersect is 8.68%, the crossover rate, that has been calculated in the previous sheet and shown above. Part E two The NPV profiles show that the IRR and NPV criteria lead to the sme accept/reject decision for any independent project. Looking at the two graphs, Franchise L Franchsie L $20 $10 $0 0% 5% 10% 15% ($10) ($20) Discount Rate 20% 25%