Question

Problem 1

(85% of grade)

Summer Tyme, Inc. has cash available and is considering a new three-year expansion project that requires an initial fixed asset investment of $195.5 million. The fixed assets will be depreciated straight-line to zero over its three-year tax life. The fixed assets will have a market value of $95,871,755 at the end of the project. The project is estimated to generate following revenues during those three years: $84,563,662 for year one, $66,352,100 for year two, and $58,784,123 for year three. Costs are equal to 55.75% of the same year sales. The project net working capital is equal to 14.5% of the next year's revenue. The tax-rate is 21%. What are the project’s net cash flows for years 0-3? What is the IRR on this project?

Use available Excel template and complete using "best practices" (use formulas - no hardcoding in model).

Revenue t=1

$      84,563,662

Revenue t=2

$      66,352,100

Revenue t=3

$      58,784,123

Investment

$    195,500,000

Depr. years

3

Final book value

$                     -  

FA Sale value

$      95,871,755

NWC req't

14.50%

Costs

55.75%

Tax rate

21.00%

You may use positive or negative numbers in this section below in any consistent manner. Please make sure your Excel formulas are consistent and that your cash flow numbers are correct.

		Year 0	Year 1	Year 2	Year 3
Revenue		$                     -
Expenses		$                     -
Depreciation		$                     -
EBIT		$                     -
Taxes		$                     -
Net Income (NI)		$                     -
OCF		$                     -
NWC total
Change in NWC
Net Capital Spending
CFFA
Project IRR

Problem 2		(15% of grade)	Use Excel formulas for a) and c) below
		Year 0	Year 1	Year 2	Year 3
Total Cash Flow		$         (295,988)	$           174,126	$             128,637	$           115,982
Discount rate		21.00%
a)   NPV
b) Accept/Reject
c)    IRR

Answer 1

1)

	Year 0	Year 1	Year 2	Year 3
Revenue	-	84,563,662.00	66,352,100.00	58,784,123.00
Expenses	-	47,144,241.57	36,991,295.75	32,772,148.57
Depreciation	-	65,166,666.67	65,166,666.67	65,166,666.67
EBIT	-	-      27,747,246.23	-    35,805,862.42	-       39,154,692.24
Taxes	-	5,826,921.71	7,519,231.11	8,222,485.37
Net Income (NI)	-	-      33,574,167.94	-    43,325,093.52	-       47,377,177.61
OCF	-	31,592,498.73	21,841,573.14	17,789,489.06
NWC total	12,261,730.99	9,621,054.50	8,523,697.84	-
Change in NWC	12,261,730.99	-         2,640,676.49	-       1,097,356.67	-         8,523,697.84
Net Capital Spending	195,500,000.00	-	-	-       75,738,686.45
CFFA	-    207,761,730.99	2,640,676.49	1,097,356.67	84,262,384.29
Net Cash Flow	-    207,761,730.99	34,233,175.22	22,938,929.81	102,051,873.34
IRR	-10.25%

Sale of FA, net of tax:
Book Value	-
Sale price	95,871,755.00
Profit	95,871,755.00
Tax on profit	20,133,068.55
Net sale proceeds	75,738,686.45

2)

	Year 0	Year 1	Year 2	Year 3
Total Cash Flow	-295,988	174,126	128,637	115,982
Discount rate	21.00%
a)   NPV	1,030.91
b) Accept/Reject	Accept
c)    IRR	21.29%