!!!PLEASE POST FORMULA VIEW ALONG WITH ANSWER!!!

Cubbies Corporation is a relatively young company that has enjoyed great financial success and a very strong growth pattern. 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 return 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 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 A: This project would require an initial investment of $875,000 to replace current equipment with newer technology. The replaced equipment could be sold immediately for $30,000. The new equipment 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 equipment is expected to last for 6 years, and to have no salvage value at that time. This project would require initial working capital of $60,000. Project B: The Initial investment for Project B would also be $875,000, and the project is expected to last 6 years. This would be a new venture for Cubbies, so no existing equipment would be sold. Because it is a new business, revenues are expected to grow from $150,000 in year 1, to $250,000 in year 2, to $450,000 annually in years 3 through 6. Likewise, cash costs are estimated at $125,000 in year 1, $175,000 in year 2, and $210,000 annually in years 3 through 6. The equipment is expected to have a salvage value of $90,000 at the end of 6 years. Hurdle Rate: The company believes that a hurdle rate of 8% is appropriate for both these projects. You will be expected to do the following: * Rename the "Template" worksheet as Project A and complete this worksheet by entering the appropriate data from 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. 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. * 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. * 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. 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 inputs from the PV table in line 16, "present value factor" should refer to the hurdle rate in cell B5. * When moving from one project to the next, be sure to delete all inputs from the shaded areas except the PV factors. * 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.

!!!PLEASE POST FORMULA VIEW ALONG WITH ANSWER!!!

1 2 3 4 5 Hurdle Rate: 6 7 8 Initial investment 9 Salvage value of new or replaced asset 10 Working Capital 11 Annual savings/revenues 12 Annual costs/cash outflows 13 Overhaul/refurbishment cost A 14 15 Net annual cash flows 16 Present value factor 17 Present value of annual cash flows 18 19 20 Net Undiscounted Cash Flows: Project cash flows: 21 22 Net Present Value of project: 23 24 Payback Period: 25 Cumulative Undiscounted Cash Flows 26 Payback Period (Years) 27 28 Profitability Index: 31 32 29 30 Internal Rate of Return (IRR): 33 34 35 36 37 B Time 0 ? ? ? ? ? ? ? ? C D Cubbies Corporation Capital Budget Projections Project: # Laura Ilcisin - Seat #1 Cleveland Corporation Year 1 ? ? ? Year 2 ? ? ? E Year 3 ? ? ? F Year 4 ? ? ? G Year 5 ? ? ? H Year 6 ? ? ? Year 7 Project Summaries: Net (undiscounted) cash flows Net present value Payback period (in years) Profitability index: Internal rate of return: Recommendation: Laura Ilcisin - Seat #1 Cleveland Corporation Project A Project B 1 2 3 4 5 Hurdle Rate: 6 7 8 Initial investment 9 Salvage value of new or replaced asset 10 Working Capital 11 Annual savings/revenues 12 Annual costs/cash outflows 13 Overhaul/refurbishment cost A 14 15 Net annual cash flows 16 Present value factor 17 Present value of annual cash flows 18 19 20 Net Undiscounted Cash Flows: Project cash flows: 21 22 Net Present Value of project: 23 24 Payback Period: 25 Cumulative Undiscounted Cash Flows 26 Payback Period (Years) 27 28 Profitability Index: 31 32 29 30 Internal Rate of Return (IRR): 33 34 35 36 37 B Time 0 ? ? ? ? ? ? ? ? C D Cubbies Corporation Capital Budget Projections Project: # Laura Ilcisin - Seat #1 Cleveland Corporation Year 1 ? ? ? Year 2 ? ? ? E Year 3 ? ? ? F Year 4 ? ? ? G Year 5 ? ? ? H Year 6 ? ? ? Year 7 Project Summaries: Net (undiscounted) cash flows Net present value Payback period (in years) Profitability index: Internal rate of return: Recommendation: Laura Ilcisin - Seat #1 Cleveland Corporation Project A Project B