Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Accounting 2020 - Introduction to Managerial Accounting Excel Project - Capital Budget Analysis Cleveland Corporation Cleveland Corporation is a relatively young company that has enjoyed
Accounting 2020 - Introduction to Managerial Accounting | |||||||
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 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 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 6 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 6 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 6. Likewise, costs are estimated at $100,000 in year 1, | |||||||
$150,000 in year 2, and $250,000 annually in years 3 through 6. The equipment is expected to have a | |||||||
salvage value of $50,000 at the end of 6 years. | |||||||
Hurdle Rate: | |||||||
The company believes that a hurdle rate of 4% is appropriate for both these projects. | |||||||
You will be expected to do the following: | |||||||
* | Refer to the "Template" worksheet and complete by entering the appropriate data from Project 1 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. | |||||||
* | Use your program to analyze Project 1 first and complete the "Evaluation" worksheet, then clear out that data | ||||||
and use the same worksheet to analyze Project 2. | |||||||
* | 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 Blackboard 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 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. |
Cleveland Corporation | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Capital Budget Projections | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Project: # | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Hurdle Rate: | 4% | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Project cash flows: | Time 0 | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Initial investment | Shaded 0 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Salvage value of replaced asset | Shaded 0-7 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Working Capital | Shaded 0-7 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Annual savings/revenues | Shaded 1-7 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Annual costs/cash outflows | Shaded 1-7 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Overhaul/refurbishment cost | Shaded 1-7 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Net annual cash flows | ? | ? | ? | ? | ? | ? | ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Present value factor | 1.000 | 0.962 | 0.925 | 0.889 | 0.855 | 0.822 | 0.790 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Present value of annual cash flows | ? | ? | ? | ? | ? | ? | ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Net Undiscounted Cash Flows: | ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Net Present Value of project: | ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Payback Period: | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Cumulative Undiscounted Cash Flows | ? | ? | ? | ? | ? | ? | ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Payback Period (Years)* | * | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Profitability Index: | ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
Internal Rate of Return (IRR): | ? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
*Use the data from your "Cumulative Undiscounted Cash Flows" to manually calculate the payback period.
|
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started