Hello, I am having some trouble with finalizing my project spread sheet. Could you help me, please?

Here is the Project outline:

Hampton Company: The production department has been investigating possible ways to trim total production

costs. One possibility currently being examined is to make the cans instead of purchasing them. The equipment

needed would cost $1,000,000, with a disposal value of $200,000, and would be able to produce 27,500,000

cans over the life of the machinery. The production department estimates that approximately 5,500,000 cans

would be needed for each of the next 5 years.

The company would hire six new employees. These six individuals would be full-time employees working 2,000

hours per year and earning $15.00 per hour. They would also receive the same benefits as other production

employees, 15% of wages in addition to $2,000 of health benefits.

It is estimated that the raw materials will cost 30 per can and that other variable costs would be 10 per can.

Because there is currently unused space in the factory, no additional fixed costs would be incurred if this

proposal is accepted.

It is expected that cans would cost 50 each if purchased from the current supplier. The company's minimum

rate of return (hurdle rate) has been determined to be 11% for all new projects, and the current tax rate of 35%

is anticipated to remain unchanged. The pricing for the companys products as well as number of units sold will

not be affected by this decision. The unit-of-production depreciation method would be used if the new

equipment is purchased.

Required:

1. Based on the above information and using Excel, calculate the following items for this proposed equipment

purchase.

o Annual cash flows over the expected life of the equipment

o Payback period

o Simple rate of return

o Net present value

o Internal rate of return

The check figure for the total annual after-tax cash flows is $271,150.

2. Would you recommend the acceptance of this proposal? Why or why not? Prepare a short, double-spaced

paper in MS Word elaborating on and supporting your answer.

Here is my spreadsheet so far:

		ACCT505
		Project 2
		Sample Capital Budgeting Decision
Data:
		Cost of new equipment	$1,000,000
		Expected life of equipment in years	5
		Disposal value in 5 years	$200,000.00
		Life productionnumber of cans	27,500,000
		Annual production or purchase needs	5,500,000
		Initial training costs	0
		Number of workers needed	6
		Annual hours to be worked per employee	2,000
		Earnings per hour for employees	$15.00
		Annual health benefits per employee	$2,000
		Other annual benefits per employee% of wages	15%
		Cost of raw materials per can	$0.30
		Other variable production costs per can	$0.10
		Costs to purchase cansper can	$0.50
		Required rate of return	11%
		Tax rate	35%
			Make	Purchase
Cost to Produce
	Annual cost of direct material:
		Need of 5.5 million cans per year	$1,650,000
	Annual cost of direct labor for new employees:
		Wages	180,000
		Health benefits	12,000
		Other benefits	27,000
		Total wages and benefits	219000
	Other variable production costs		550,000
	Total annual production costs		$2,419,000
	Annual cost to purchase cans			$2,750,000
Part 1 Cash Flows Over the Life of the Project
			Before Tax	Tax	After Tax
		Item	Amount	Effect	Amount
		Annual cash savings	$331,000	0.65	$215,150
		Tax savings due to depreciation	160,000	0.35	56,000
		Total after-tax annual cash flow			$271,150
Part 2 Payback Period
		1,000,000/271,150 =	3.69	years
Part 3 Simple Rate of Return
	Accounting income as result of decreased costs
		Annual cash savings	$331,000
		Less depreciation	160,000
		Before tax income	171,000
		Tax at 35% rate	59,850
		After tax income	$111,150
		$111,150 / $1,000,000 =	11.12%
Part 4 Net Present Value
				Before Tax		After Tax	11% PV	Present
		Item	Year	Amount	Tax %	Amount	Factor	Value
		Cost of machine	0	$1,000,000		$1,000,000	1.000	$1,000,000
		Cost of training	0	0		0	1.000	0
		Annual cash savings	1-5	331,000	0.65	215,150		215,150
		Tax savings due to depreciation	1-5	56,000	0.35	19,600		19,600
		Disposal value	5	200,000		200,000	0.59345	118,690
		Net Present Value						$1,353,440
Part 5 Internal Rate of Return
		Excel function method to calculate IRR
			This function requires that you have only one cash flow per period (Period 0 through Period 5, for our example).
			This means that no annuity figures can be used. The chart for our example can be revised as follows.
					After Tax
			Item	Year	Amount
			Cost of machine and training	0	$(1,000,000)
			Year 1 inflow	1
			Year 2 inflow	2
			Year 3 inflow	3
			Year 4 inflow	4
			Year 5 inflow	5
			The IRR function will require the range of cash flows, beginning with the initial cash outflow for the investment
			and progressing through each year of the project. You also have to include an initial guess for the
			possible IRR. The formula is: =IRR(values,guess)
			IRR Function	IRR(f84..f89,.30)	#NUM!