i will rate

5 6 Cubbies Corporation is a relatively young company that has enjoyed great financial success and a very 7 strong growth pattem. However, Cubbies's management realizes that the company has outgrown 8 its "seat of the pants" management style, and must start to develop more sophisticated means of 9 analyzing financial decisions. For example, the company is currently considering two projects, both of which 10 have the same initial investment and over a 6-year life, will retum approximately the same amount of income to the 11 company. To aid in choosing between these projects, the CFO has asked for an Excel model that can 12 determine each project's net present value, profitability index, payback period, and internal rate 13 of retum, based on the project's cash flow projections. 14 15 Your job is to develop a program that can analyze these projects, but that is also flexible enough to 16 handle other projects with a variety of lives, cash flow pattems, and hurdle rates. 17 18 Following are the specifics regarding the projects currently under consideration: 19 20 Project A: 21 This project would require an initial investment of $875,000 to replace current equipment with newer 22 technology. The replaced equipment could be sold immediately for $30,000. The new equipment 23 is expected to generate incremental revenues of $380,000 and incremental cash costs of $175,000 24 annually. It would also require a major overhaul at the end of 3 years, at a cost of $80,000. The 25 equipment is expected to last 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 Project 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 40 JIE 45 46 47 49 50 53 41 You will be expected to do the following: 42 43 Rename the "Template worksheet as Project A and complete this worksheet by entering the appropriate data from 44 Project A in the shaded cells and placing formulas in every cell containing a "?". The cells containing a "?" cannot contain any hard-coded numbers! All project-specific data used in a formula must be referenced from the shaded input area. 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. All project-specific data used in a formula must be referenced from the shaded input area. 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 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, 58 by the due date indicated by your instructor. 59 60 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: 63 When moving from one project to another, you will NOT need to redo any of the formulas; 65 only the inputs in the shaded area will change. 66 67 Enter all project benefits/cash inflows as positive numbers; all project costs/cash outflows as 68 negative numbers 69 70 In line 15, "net annual cash flows," your formula should sum lines 8 through 13. Even though 71 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 73 74 The inputs from the PV table in line 16, "present value factor" should refer to the hurdle rate in cell B5, 75 76 When moving from one project to the next, be sure to delete all inputs from the shaded areas except the PV factors. 77 78 For projects that are less than 7 years in length, simply leave the shaded input cells for the unused 79 years blank; it will not affect your results. Project: # Hurdle Rate: Time 0 Year 1 Year 2 Year 3 Year 4 Year 4 Year 5 Year 6 Year 7 Project cash flows: Initial investment Salvage value of new or replaced asset Working Capital Annual savings/revenues Annual costs/cash outflows Overhaulrefurbishment cost 7 ? ? ? ? 2 7 Net annual cash flows Present value factor Present value of annual cash flows ? 7 2 7 ? 2 2 Net Undiscounted Cash Flows: ? Net Present Value of project: ? Payback Period: Cumulative Undiscounted Cash Flows Payback Period (Years) 7 ? ? ? ? ? ? ? Profitability Index: ? ? Internal Rate of Return (IRR): 1 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 28 29 30 31 32 33 34