Spring 2021 Excel Project - Capital Budget Analysis Cleveland Corporation Cleveland Corporation is a relatively young company that has enjoyed great financial success and a very strong growth pattern. However, Cleveland'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 cost the same and, over a 5-year life, will return 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 each project's net present value, profitability index, payback period, and internal rate of return, 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 patterns, and hurdle rates. Following are the specifics regarding the projects currently under consideration: Project 1: This project would require an initial investment of $800,000 to replace current equipment with newer technology. The replaced equipment could be sold immediately for $100,000. The new equipment is expected to generate incremental revenues of $400,000 and incremental costs of $200,000 annually. It would also require a major overhaul at the end of 3 years, at a cost of $60,000. The equipment is expected to last for 5 years, and to have no salvage value at that time. This project would require working capital of $50,000 initially. Project 2: The Initial investment for Project 2 would also be $800,000, and the project is expected to last 5 years. This would be a new venture for Cleveland, so no existing equipment would be sold. Because it is a new business, revenues are expected to grow from $200,000 in year 1, to $300,000 in year 2, to $500,000 annually in years 3 through 5. Likewise, costs are estimated at $100,000 in year 1, $150,000 in year 2, and $250,000 annually in years 3 through 5. The equipment is expected to have a salvage value of $50,000 at the end of 5 years. Hurdle Rate: The company believes that a hurdle rate of 5% appropriate for both these projects. You will be expected to do the following: Refer to the formula in the "Template" worksheet and Complete by placing formulas in every cell in "Sheet 2" containing a "?". These cells cannot contain any hard-coded numbers! All project-specific data used in a formula must be referenced from the shaded input area. Use your program to analyze only Project 1. * In the worksheet labeled "Evaluation," complete the summary measures for each project, 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 is sure to ask why two projects with the same cost and the same benefits are not identical when evaluated using your model. Following are hints that will help to make your program a flexible tool that is able to handle a 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 for projects that do * The formulas in line 16, "present value factor" should refer to the hurdle rate in cell B5. * Before moving from one project to the next, be sure to delete all inputs from the shaded area. 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. Project Summaries: Project 1 Project 2 Net (undiscounted) cash flows Net present value Payback period Profitability index: Internal rate of return; Recommendation: The net present value and IRR of project 2 is higher than project 1. Therefore, project 2 can be selected. Project: 1 Hurdle Rate: 5% Time 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Project cash flows: Initial investment Salvage value of replaced asset Working Capital Annual savings/revenues Annual costs/cash outflows Overhaul/refurbishment cost $ $ $ $ $ $ $ Net annual cash flows Present value factor Present value of annual cash flows 1.000 0.952 0.907 0.864 0.823 0.784 0.746 $ $ $ $ Net Undiscounted Cash Flows: $ Net Present Value of project: $ Payback Period: $ $ $ $ $ $ $ Profitability Index: #DIV/0! Internal Rate of Return (IRR): #NUM! Cleveland Corporation Capital Budget Projections Project: 2 Hurdle Rate: 5% Time 0 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year 7 Project cash flows: Initial investment Salvage value of replaced asset Working Capital Annual savings/revenues Annual costs/cash outflows Overhaul/refurbishment cost $ $ $ $ $ $ Net annual cash flows $ Present value factor Present value of annual cash flows $ 1.000 0.952 0.907 0.864 0.823 0.784 $ $ $ $ $ $ Net Undiscounted Cash Flows: $ Net Present Value of project: $ Payback Period: $ $ $ $ $ $ $ Profitability Index: #DIV/0! Internal Rate of Return (IRR): #NUM