Question

For your analysis assume that the ASC uses a 7% discount rate for projects of this risk level, and that they will initially use a five-year time horizon. This is a tax-exempt not-for-profit organization so there will not be any income tax effects to consider in the calculations.

The business after buying the equipment is expected to generate gross revenues of $100,000 each year in the first two years and is expected to be $120,000 each year in the next three years. The services will be paid for by third parties and there is a demand for this new service. Deductions from revenue are expected to average 25% of gross revenues in each of the five years. The equipment cost is $210,000 and will cost $14,988 to install. After five years the equipment will be retired and it is expected that it could be sold for $25,000.     

The costs for the service include part-time staffing costs of $12,000 and supply costs of $10,000 in each of the first two years. For the last three years, salaries are expected to be $14,000 and supplies are estimated to be $12,000 in each of those last three years.   The equipment is under warranty in the first year so there is no extra fee paid. A maintenance contract costing $6,000 per year will be paid in years 2 through 5.

Required:

1. Set up the spreadsheet by inputting the above assumptions in the appropriate cells.

2. Compute the Net Present Value of Future Cash Flows, and the Internal Rate of Return.   Highlight in yellow those two answers on your spreadsheet.   Note those answers in the table below so that they are in both places.

3. Note at the bottom of the schedule whether this is an attractive project from a purely financial point of view based upon the numbers that you calculated on the spreadsheet. Why did you make that decision? Note your answers in the table below so that they are in both places.

Answer 1

As per the data given, the NPV is calculated as per the below table:

Years	0	1	2	3	4	5	Total
Equipment Cost	-$210,000						-$210,000
Equipment Installation	-$14,988						-$14,988
Gross Revenues		$100,000	$100,000	$120,000	$120,000	$120,000	$560,000
Deduction from revenue		-$25,000	-$25,000	-$30,000	-$30,000	-$30,000	-$140,000
Sale of Equipment						$25,000	$25,000
Part-time staffing costs		-$12,000	-$12,000	-$14,000	-$14,000	-$14,000	-$66,000
Supply Costs		-$10,000	-$10,000	-$12,000	-$12,000	-$12,000	-$56,000
Machine Maintenance Contract			-$6,000	-$6,000	-$6,000	-$6,000	-$24,000
Net Cash flow	-$224,988	$53,000	$47,000	$58,000	$58,000	$83,000	$74,012
Present value factor @ 7.00%	1.0000	0.9346	0.8734	0.8163	0.7629	0.7130
PV of Cash flows	-$224,988	$49,533	$41,052	$47,345	$44,248	$59,178	$16,367

As calculated, the NPV of future cash flows is $16,367.

IRR is the rate at which the NPV of future cash flows is zero.

By trial and error method, the IRR comes to 9.48%.

Evaluation of the project as per NPV:

As the NPV of future cash flows is positive, the project looks attractive from the financial point of view.

Evaluation of the project as per IRR:

If the required rate of return from the company is less than the IRR i.e. 9.48%, the company should go ahead with the project.

However, if the required rate of return is more than the IRR, the company should not go ahead with the project.