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.

Project Summaries:
	Project 1	Project 2
Net (undiscounted) cash flows
Net present value
Payback period (in years)
Profitability index:
Internal rate of return:
Recommendation: