If possible, complete in excel with formulas.

J7 XV fx AB D F 0 7 Cleveland Corporation is a relatively young company that has enjoyed great financial success and a very 8 strong growth pattem. However, Cleveland's management realizes that the company has outgrown 9 Its "seat of the pants management style, and must start to develop more sophisticated means of 10 analyzing financial decisions. For example, the company is currently considering two projects, 11 both of which cost the same and, over a 5-year life, will return the same amount of income to the 12 company. To aid in choosing between these projects, the CFO has asked for an Excel model that can 13 determine each project's not present value, profitability index, payback period, and intemal rate 14 of retum, based on the project's cash flow projections. 15 16 Your job is to develop a program that can analyze these projects, but that is also flexible enough to 17 handle other projects with a variety of lives, cash flow pattems, and hurdle rates 18 19 Following are the specifics regarding the projects currently under consideration 20 21 Project 1: 22 This project would require an initial investment of $800,000 to replace current equipment with newer 23 technology. The replaced equipment could be sold immediately for $100,000. The new equipment 24 is expected to generate incremental revenues of $400,000 and incremental costs of $200,000 25 annually. It would also require a major overhaul at the end of 3 years, at a cost of $60,000. The 26 equipment is expected to last for 5 years, and to have no salvage value at that time. 27 This project would require working capital of $50,000 initially 28 Proiect 2: 29 The Initial investment for Project 2 would also be $800,000, and the project is expected to last 5 years. 30 This would be a new venture for Cleveland, so no existing equipment would be sold. Because it is 31 a new business, revenues are expected to grow from $200,000 in year 1, to $300,000 in year 2, to 32 $500,000 annually in years 3 through 5. Likewise, costs are estimated at $100,000 in year 1, 33 $150,000 in year 2, and $250,000 annually in years 3 through 5. The equipment is expected to have a 34 salvage value of $50,000 at the end of 5 years. 35 36 37 Hurdle Rate: 38 The company believes that a hurdle rate of 5% is appropriate for both these projects. 39 40 41 You will be expected to do the following: 42 43 Refer to the formula in the "Template worksheet and Complete by placing fomulas in every colin "Sheet 2" 44 containing a "?". These cells cannot contain any hard-coded numbers! All project-specific data 45 used in a fomula must be referenced from the shaded input area, 46 47 - Use your program to analyze only Project 1. 49 - In the worksheet labeled "Evaluation, complete the summary measures for each project, 50 and prepare a brief evaluation of the relative benefits of the two projects, including which 61 one (if either the company should approve. Be sure to include a brief explanation for the CEO, who 52 is sure to ask why two projects with the same cost and the same benefits are not identical when 53 evaluated using your model 54 65 56 Deliver the project via e-mail at wkwak@unomaha.edu, with YOUR NAMES as the name of the file Instructions Evaluation Project 1 Project 2 Template + Ready Hurdle Rate: The company believes that a hurdle rate of 5% is appropriate for both these projects m 1 You will be expected to do the following: 2 + 3 - Refer to the formula in the "Template worksheet and Complete by placing formulas in every cel in "Sheet 2" 4 containing a "?". These cells cannot contain any hard-coded numbers! Al project-specific data 5 used in a formula must be referenced from the shaded input area. 6 -7. Use your program to analyze only Project 1. 19 In the worksheet labeled "Evaluation, complete the summary measures for each project, 50 and prepare a brief evaluation of the relative benefits of the two projects, including which 51 one (if either the company should approve. Be sure to include a brief explanation for the CEO, who 52 is sure to ask why two projects with the same cost and the same benefits are not identical when 53 evaluated using your model 64 55 56 Deliver the project via e-mail at wkwak@unomaha.edu, with YOUR NAMES as the name of tho file, 57 by 11:00 pm. on August 9. 5B 59 60 Followng are hints that will help to make your program a flexible tool that is able to handle a 61 wide variety of projects 02 63 When moving from one project to another, you will NOT need to redo any of the formulas; 64 only the inputs in the shaded area will change. 65 66 Enter all project benefits/cash inflows as positive numbers; al project costs/cash outflows as 67 negative numbers 68 69 In line 15. "net annual cash flows, your formula should sum lines 8 through 13. Even though 70 not every call in that range will have inputs for every project, you will be sure to pick up data 71 for projects that do 72 73. The formulas in line 16, "present value factor should refer to the hurdle rate in cell B5. 74 75 Before moving from one project to the next, be sure to delete all inputs from the shaded area 76 77 For projects that are less than 7 years in length, simply leave the shaded Input cells for the unused 78 years blank; it will not affect your results 79 80 81 82 83 84 05 86 Instructions Evaluation Project 1 Project 2 Template + Ready IT F H . K Project 1 Project 2 0 1 2 3 Project Summaries: 4 5 6 Net (undiscounted) cash flows 7 Net present value 8 Payback period 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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 Instructions Evaluation Project 1 Project 2 Template + Ready 3 Year 4 Year 5 Year 6 Year 7 $ $ $ $ 0.823 0784 0.746 $ $ $ $ 4 5 Hurdle Rate: 5% 6 7 Project cash flows Time 0 Year 1 Year 2 Year 3 8 Initial investment 9 Salvage value of replaced asset 10 Working Capital 11 Annual savings/revenues 12 Annual costs/cash outflows 13 Overhaulrefurbishment cost 14 15 Net annual cash flows $ 16 Present value factor 1.000 0.952 0907 0.864 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 20 Profitability Index: NOIVIO 27 20 Intemal Rule of Retum (IRR): ENUMI 20 30 31 32 33 34 35 36 37 38 30 40 Instructions Evaluation Project 1 Project 2 Template > + Ready H 1 2 Cleveland Corporation Capital Budget Projections Project: 2 3 5% Time 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 $ $ $ $ $ $ $ 1.000 0.952 0.907 0.823 0.784 0.864 . $ $ $ $ $ $ - $ 4 5 Hurdle Rate: 6 7 Project cash flows: 8 Initial investment 9 Salvage value of replaced asset 10 Working Capital 11 Annual savings/revenues 12 Annual costs/cash outflows 13 Overhaul refurbishment cost 14 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 26 Profitability Index: 27 28 Internal Rate of Return (IRR): 29 30 31 32 33 $ $ $ $ $ - $ $ $ $ #DIV/0! WNUMI 34 35 36 37 38 39 40 Instructions Evaluation Project 1 Project 2 Template + Ready 1 2 Cleveland Corporation Capital Budget Projections Project: 2 3 5% Time 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 4 5 Hurdle Rate: 6 7 Project cash fows: 8 Initial investment 9 Salvage value of replaced asset 10 Working Capital 11 Annual savings revenues 12 Annual costs/cash outflows 13 Overhaurefurbishment cost 14 15 Net annual cash flows 16 Present value factor 17 Present value of annual cash flows 18 19 20 Net Undiscounted Cash Flows: $ $ $ $ $ $ $ 1.000 0.952 0.907 0.864 0.823 0.784 0.746 $ $ $ $ $ $ $ $ $ $ $ $ . $ - $ . $ $ 21 22 Net Present Value of project: 23 24 Payback Period: 25 26 Profitability Index: 27 28 Internal Rate of Return (IRR): 20 30 31 32 33 34 35 30 37 30 39 40 Instructions Evaluation Project 1 Project 2 Template + Ready