!!!PLEASE POST FORMULA VIEW ALONG WITH ANSWER!!! Cubbies Corporation is a relatively young company that has enjoyed grea
Posted: Fri Jul 01, 2022 7:42 am
!!!PLEASE POST FORMULA VIEW ALONG WITHANSWER!!!
Cubbies Corporation is a relatively young company that hasenjoyed great financial success and a verystrong growth pattern. However, Cubbies's management realizes thatthe company has outgrownits "seat of the pants" management style, and must start to developmore sophisticated means ofanalyzing financial decisions. For example, the company iscurrently considering two projects, both of whichhave the same initial investment and, over a 6-year life, willreturn approximately the same amount of income to thecompany. To aid in choosing between these projects, the CFO hasasked for an Excel model that candetermine each project's net present value, profitability index,payback period, and internal rateof 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 tohandle other projects with a variety of lives, cash flow patterns,and hurdle rates.Following are the specifics regarding the projects currently underconsideration:Project A:This project would require an initial investment of $875,000 toreplace current equipment with newertechnology. The replaced equipment could be sold immediately for$30,000. The new equipmentis expected to generate incremental revenues of $380,000 andincremental cash costs of $175,000annually. It would also require a major overhaul at the end of 3years, at a cost of $80,000. Theequipment is expected to last for 6 years, and to have no salvagevalue 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, andthe project is expected to last 6 years.This would be a new venture for Cubbies, so no existing equipmentwould be sold. Because it isa new business, revenues are expected to grow from $150,000 in year1, to $250,000 in year 2, to$450,000 annually in years 3 through 6. Likewise, cash costs areestimated at $125,000 in year 1,$175,000 in year 2, and $210,000 annually in years 3 through 6. Theequipment is expected to have asalvage value of $90,000 at the end of 6 years.Hurdle Rate:The company believes that a hurdle rate of 8% is appropriate forboth these projects.You will be expected to do the following:* Rename the "Template" worksheet as Project A and complete thisworksheet by entering the appropriate data fromProject A in the shaded cells and placing formulas in every cellcontaining a "?". The cells containing a "?" cannotcontain any hard-coded numbers! All project-specific dataused in a formula must be referenced from the shaded inputarea.* Create a copy of your Project A worksheet in your Excel workbookfile. 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 fromthe shaded input area.* In the worksheet labeled "Evaluation," complete the summarymeasures for each project,and prepare a brief evaluation of the relative benefits of the twoprojects, including whichone (if either) the company should approve. Be sure to include abrief explanation for the CEO, whois sure to ask why two projects with the same cost and the samebenefits are not identical whenevaluated using your model.* Deliver the project outputs with an Excel File via Canvas to yourprofessor, 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 flexibletool that is able to handle awide variety of projects:* When moving from one project to another, you will NOT need toredo any of the formulas;only the inputs in the shaded area will change.* Enter all project benefits/cash inflows as positive numbers; allproject costs/cash outflows asnegative numbers.* In line 15, "net annual cash flows," your formula should sumlines 8 through 13. Even thoughnot every cell in that range will have inputs for every project,you will be sure to pick up datafor 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 allinputs from the shaded areas except the PV factors.* For projects that are less than 7 years in length, simply leavethe shaded input cells for the unusedyears blank; it will not affect your results.
!!!PLEASE POST FORMULA VIEW ALONG WITHANSWER!!!
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
Cubbies Corporation is a relatively young company that hasenjoyed great financial success and a verystrong growth pattern. However, Cubbies's management realizes thatthe company has outgrownits "seat of the pants" management style, and must start to developmore sophisticated means ofanalyzing financial decisions. For example, the company iscurrently considering two projects, both of whichhave the same initial investment and, over a 6-year life, willreturn approximately the same amount of income to thecompany. To aid in choosing between these projects, the CFO hasasked for an Excel model that candetermine each project's net present value, profitability index,payback period, and internal rateof 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 tohandle other projects with a variety of lives, cash flow patterns,and hurdle rates.Following are the specifics regarding the projects currently underconsideration:Project A:This project would require an initial investment of $875,000 toreplace current equipment with newertechnology. The replaced equipment could be sold immediately for$30,000. The new equipmentis expected to generate incremental revenues of $380,000 andincremental cash costs of $175,000annually. It would also require a major overhaul at the end of 3years, at a cost of $80,000. Theequipment is expected to last for 6 years, and to have no salvagevalue 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, andthe project is expected to last 6 years.This would be a new venture for Cubbies, so no existing equipmentwould be sold. Because it isa new business, revenues are expected to grow from $150,000 in year1, to $250,000 in year 2, to$450,000 annually in years 3 through 6. Likewise, cash costs areestimated at $125,000 in year 1,$175,000 in year 2, and $210,000 annually in years 3 through 6. Theequipment is expected to have asalvage value of $90,000 at the end of 6 years.Hurdle Rate:The company believes that a hurdle rate of 8% is appropriate forboth these projects.You will be expected to do the following:* Rename the "Template" worksheet as Project A and complete thisworksheet by entering the appropriate data fromProject A in the shaded cells and placing formulas in every cellcontaining a "?". The cells containing a "?" cannotcontain any hard-coded numbers! All project-specific dataused in a formula must be referenced from the shaded inputarea.* Create a copy of your Project A worksheet in your Excel workbookfile. 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 fromthe shaded input area.* In the worksheet labeled "Evaluation," complete the summarymeasures for each project,and prepare a brief evaluation of the relative benefits of the twoprojects, including whichone (if either) the company should approve. Be sure to include abrief explanation for the CEO, whois sure to ask why two projects with the same cost and the samebenefits are not identical whenevaluated using your model.* Deliver the project outputs with an Excel File via Canvas to yourprofessor, 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 flexibletool that is able to handle awide variety of projects:* When moving from one project to another, you will NOT need toredo any of the formulas;only the inputs in the shaded area will change.* Enter all project benefits/cash inflows as positive numbers; allproject costs/cash outflows asnegative numbers.* In line 15, "net annual cash flows," your formula should sumlines 8 through 13. Even thoughnot every cell in that range will have inputs for every project,you will be sure to pick up datafor 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 allinputs from the shaded areas except the PV factors.* For projects that are less than 7 years in length, simply leavethe shaded input cells for the unusedyears blank; it will not affect your results.
!!!PLEASE POST FORMULA VIEW ALONG WITHANSWER!!!
- Please Post Formula View Along With Answer Cubbies Corporation Is A Relatively Young Company That Has Enjoyed Grea 1 (40.84 KiB) Viewed 36 times
- Please Post Formula View Along With Answer Cubbies Corporation Is A Relatively Young Company That Has Enjoyed Grea 2 (27.15 KiB) Viewed 36 times
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