i will rate!

Cubbies Corporation is a relatively young company that has enjoyed great financial success and a very strong growth pattem. However, Cubbies's management realizes that the company has outgrown Its "seat of the pants" management style, and must start to develop more sophisticated means of analyzing financial decisions. For example, the company is currently considering two projects, both of which have the same initial investment and over a 6-year life, will retum approximately the same amount of income to the company. To aid in choosing between these projects, the CFO has asked for an Excel model that can determine cach project's net prenent value, profitability index, payback period, and intemal rato of retum, based on the project's cash flow projections Your job is to develop a program that can analyze these projects, but that is also flexible enough to handle other projects with a variety of lives, cash flow pattems, and hurdle ratos Following are the specifics regarding the projects currently under consideration; Project A: This project would require an initial investment of $875,000 to replace current equipment with newer 2 technology. The replaced equipment could be sold immediately for $30,000. The new equipment 3 is expected to generate incremental revenues of $380,000 and incremental cash costs of $175,000 annually. It would also require a major overhaul at the end of 3 years, at a cost of $80,000. The 5 equipment is expected to tast for 6 years, and to have no salvage value at that time 26 This project would require initial working capital of $60,000 27 28 Prolect B: 29 The Initial investment for Project B would also be $875,000, and the project is expected to last 6 years 30 This would be a new venture for Cubbies, so no existing equipment would be sold. Because it is 31 a new business, revenues are expected to grow from $150,000 in year 1 to $250,000 in year 2. to 32 $450,000 annually in years 3 through 6. Likewise, cash costs are estimated at $125,000 in year 1, 33 $175,000 in year 2, and $210,000 annually in years 3 through 6. The equipment is expected to have a 34 salvage value of $90,000 at the end of 6 years. 35 36 37 Hurdle Rate: 38 The company believes that a hurdle rate of 8% is appropriate for both these projects 39 An 7 Hurdle Rate: 3 The company believes that a hurdle rate of 8% is appropriate for both these projects. 19 0 -1 You will be expected to do the following: 12 13 Rename the "Template worksheet as Project A and complete this worksheet by entering the appropriate data from 14 Project A in the shaded cells and placing fomulas in every cell containing a "?". The cells containing a "7* cannot 45 contain any hard-coded numbersAll project-specific data used in a formula must be referenced from the shaded input area. 46 47 Create a copy of your Project A worksheet in your Excel workbook file. Rename this new sheet Project B. 48 Then use this project B worksheet to analyze Project B. 49 All project-specific data used in a fomula must be referenced from the shaded input area. 50 51 In the worksheet labeled "Evaluation," complete the summary measures for each project, 52 and prepare a brief evaluation of the relative benefits of the two projects, including which 53 one (if either the company should approve. Be sure to include a brief explanation for the CEO, who 54 is sure to ask why two projects with the same cost and the same benefits are not identical when 55 evaluated using your model. 56 57 Deliver the project outputs with an Excel File via Canvas to your professor, with YOUR NAME in the name of the file, by the due date indicated by your instructor. 59 58 60 63 64. 61 Following are hints that will help to make your program a flexible tool that is able to handle a 62 wide variety of projects: When moving from one project to another, you will NOT need to redo any of the formulas; only the inputs in the shaded area will change. Enter all project benefits/cash inflows as positive numbers: all project costs/cash outflows as negative numbers In line 15, 'net annual cash flows," your formula should sum lines 8 through 13. Even though not every cell in that range will have inputs for every project, you will be sure to pick up data 72 for projects that do The inputs from the PV table in line 16, "prosent value factor" should refer to the hurdle rate in coll B5. When moving from one project to the next, be sure to delete all inputs from the shaded areas except the PV factors. 65 66 67 68 69 70 71 73 74 - 75 76 77 78 79 80 For projects that are less than 7 years in length, simply leave the shaded input cells for the unused years blank; it will not affect your results 81 82 83 84 Time 0 Year 1 Year 2 Yoar 3 Year 4 Year 5 Year 6 Year 7 Hurdle Rate 3 7 Proiect cash flows: 8 Initial investment Salvage value of new or replaced asset 10 Working Capital 11 Annual savings revenues 12 Annual costs cash outflows 13 Overhaulrefurbishment cos! ? 7 7 2 2 ? ? 2 ? ? 7 15 Net annual cash flows 16 Present value factor 17 Present value of annual cash flows 18 19 20 Net Undiscounted Cash Flows: 21 22 Net Present Value of project: 23 24 Payback Period: 25 Cumulative Undiscounted Cash Flows 20 Paytrook Perod (years) 27 28 Profitability Index 20 30 Internal Rate of Return (IRR) 7 7 7 7 7 7 7 7 32 33 Project A Project B 2 3 Project Summaries: 4 5 6 Net (undiscounted) cash flows 7 Net present value 8 Payback period (in years) 9 Profitability index: 10 Intemal rate of retum: 11 12 13 Recommendation: 14 15 16 17 18 19 20 21 22 23 24 25 26 27