Question

Cirrus Cloud, Inc., (CCI) host cloud storage on its server farms. It has recently been losing ground to competitors, and is evaluating whether to invest in an upgraded server farm. You have researched the latest technology along with your own IT people, and have come up with a proposal. The expansion will be phased in over a three year investment period. Assume all cash flows occur at the end of the year. The initial investment begins in 2018 (year 0) with the purchase of land for $5 million and a building to house the new servers for $95 million. In 2019, the initial set of servers will be purchased for $150 million. Additional installation costs of $25 million will also be incurred. Preparation for operations will include an investment of $30 million worth of accounts receivable.

In 2020, CCI will be able to begin operations with the new servers, though it will continue to add capacity during the year as well. Revenues for the year will be $50 million. Fixed operating costs of $7 million will be incurred, and the variable operating costs are estimated to be 10% of revenues. New servers will be added during the year costing $15 million, with $3 million in installation costs. In anticipation of increased sales, additional investment in accounts receivable will be needed in the amount of $2 million.

In 2021, an additional $15 million in servers, with $3 million in installation costs will be added. Accounts receivable investment of $2 million will be made. Revenues will be $60 million, with fixed costs of $9 million and variable costs of 10%. At this point the investment in servers is complete.

Revenues for 2022 will be $80 million, with fixed operating costs of $10 million and variable costs of 10%. Revenues in 2023 will be $125 million, with fixed costs of $12 million and variable costs of 10%. In 2024, revenues will be $135 million with fixed costs of $15 million and a variable cost ratio of 10%.

Beginning in 2025, and ending in 2030, Revenues will increase by $5 million per year. Fixed operating costs will increase by 5% each of these years, and variable costs will remain 10% of sales. The project is scheduled to end in year 2030. At that time, all of the investment in accounts receivable will cash out, and the servers will be sold for $25 million. The land will be sold for $10 million, and the building will be sold for $75 million.

Depreciation of the building will be MACRS 39 year, which is straight line. Ignore the half year convention for the building. The equipment qualifies as a 7 year asset under MACRS. Depreciation will begin in the year the equipment is put into operations. For example, the servers purchased in 2019 will begin depreciation in year 2020. The tax rate for ordinary income and capital gains is 40%. Assume CCI’s cost of capital is 14%. Assume that CCI analyzes its projects using Payback, NPV, IRR and the Profitability Index. For a best case scenario, assume that variable costs are only 7% throughout the project’s life. For worst case, assume that the variable cost ratio is 25%. How sensitive do you think the NPV is to the variable cost ratio?

Answer 1

		A	Annual Depreciation of the building:	$        2,435,800	(2.564% of$95 million)
		B=A*0.4	Annual Depreciation tax shield for building	$            974,320
			Accumulated de[preciation at the end of 2030	$      29,229,600	(2435800*12)
			Book Value of Building at end 2030	$      65,770,400	(95000000-29229600)
			Year	1	2	3	4	5	6	7	8
			MACRS 7 year recovery for equipment	14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46%
				2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
			Year	0	1	2	3	4	5	6	7	8	9	10	11	12
		A	Initial investment in equipment in 2019(150+25)million		$175,000,000
		B	MACRS Recovery			14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46%
		C=B*175 Million	Depreciation amount			$        25,007,500	$        42,857,500	$        30,607,500	$        21,857,500	$        15,627,500	$        15,610,000	$        15,627,500	$        7,805,000
		D=C*0.4	Annual Depreciation tax shield			$        10,003,000	$        17,143,000	$        12,243,000	$          8,743,000	$          6,251,000	$          6,244,000	$          6,251,000	$        3,122,000
		E	Initial investment in equipment in 2020(15+3)million			$18,000,000
		F	MACRS Recovery				14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46%
		G=F*18 million	Depreciation amount				$          2,572,200	$          4,408,200	$          3,148,200	$          2,248,200	$          1,607,400	$          1,605,600	$        1,607,400	$            802,800
		H=G*0.4	Annual Depreciation tax shield				$          1,028,880	$          1,763,280	$          1,259,280	$              899,280	$              642,960	$              642,240	$            642,960	$            321,120
		I	Initial investment in equipment in 2021(15+3)million				$18,000,000
		J	MACRS Recovery					14.29%	24.49%	17.49%	12.49%	8.93%	8.92%	8.93%	4.46%
		K=J*18 million	Depreciation amount					$          2,572,200	$          4,408,200	$          3,148,200	$          2,248,200	$          1,607,400	$        1,605,600	$        1,607,400	$        802,800
		L=K*0.4	Annual Depreciation tax shield					$          1,028,880	$          1,763,280	$          1,259,280	$              899,280	$              642,960	$            642,240	$            642,960	$        321,120
		M=974320+D+H+L	Total Depreciation Tax Shield		$          974,320	$        10,977,320	$        19,146,200	$        16,009,480	$        12,739,880	$          9,383,880	$          8,760,560	$          8,510,520	$        5,381,520	$        1,938,400	$    1,295,440
			Terminal Cash Flow:
		N	Release of accounts receivable	$34,000,000	(30+2+2)Million
			Before tax Salvage value of building	$75,000,000
			Book Value of building	$      65,770,400
			Gain on sale of building	$        9,229,600
			Tax on gain	$        3,691,840	(9229600*0.4)
		P	After tax Salvage value of building	$71,308,160	(75000000-3691840)
			Before tax sales value of land	$10,000,000
			Capital gain	$5,000,000	(10-5) million
			Capital gain tax	$2,000,000	(0.4*5000000)
		Q	After tax sales value of land	$8,000,000	(10-2 )million
			Before tax sales value of equipment	$25,000,000
		R=(1-0.4)*25 million	After tax Sales value of Equipment	$15,000,000
		T=N+P+Q+R	Total Terminal cash Flow	$128,308,160
				2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030
		N	YEAR	0	1	2	3	4	5	6	7	8	9	10	11	12
		A	Initial Cash Flow	($100,000,000)	($205,000,000)	($20,000,000)	($20,000,000)
		B	Revenue			$50,000,000	$60,000,000	$80,000,000	$125,000,000	$135,000,000	$140,000,000	$145,000,000	$150,000,000	$155,000,000	$160,000,000	$165,000,000
		C	Fixed Operating Cost			($7,000,000)	($9,000,000)	($10,000,000)	($12,000,000)	($15,000,000)	($15,750,000)	($16,537,500)	($17,364,375)	($18,232,594)	($19,144,223)	($20,101,435)
		D	Variable Operating cost			($5,000,000)	($6,000,000)	($8,000,000)	($12,500,000)	($13,500,000)	($14,000,000)	($14,500,000)	($15,000,000)	($15,500,000)	($16,000,000)	($16,500,000)
		E=B+C+D	Operating cash flow(Before Tax)			$38,000,000	$45,000,000	$62,000,000	$100,500,000	$106,500,000	$110,250,000	$113,962,500	$117,635,625	$121,267,406	$124,855,777	$128,398,565
		F=E*(1-0.4)	After Tax Operating cash Flow			$22,800,000	$27,000,000	$37,200,000	$60,300,000	$63,900,000	$66,150,000	$68,377,500	$70,581,375	$72,760,444	$74,913,466	$77,039,139
		G	Total Depreciation tax shield		$          974,320	$        10,977,320	$        19,146,200	$        16,009,480	$        12,739,880	$          9,383,880	$          8,760,560	$          8,510,520	$        5,381,520	$        1,938,400	$    1,295,440
		H	Total Terminal Cash Flow													$128,308,160
		I=A+F+G+H	Net After Tax Cash Flow	($100,000,000)	($204,025,680)	$13,777,320	$26,146,200	$53,209,480	$73,039,880	$73,283,880	$74,910,560	$76,888,020	$75,962,895	$74,698,844	$76,208,906	$205,347,299
		J	Cumilative after tax cash flow	($100,000,000)	($304,025,680)	($290,248,360)	($264,102,160)	($210,892,680)	($137,852,800)	($64,568,920)	$10,341,640	$87,229,660	$163,192,555	$237,891,399	$314,100,305	$519,447,604	SUM
		K=I/(1.14^N)	Present value of Cash Flow at 14% discount rate	-100000000	-178969894.7	10601200.37	17647940.26	31504283.68	37934624.92	33387149.89	29937055.63	26953791.62	23359193.57	20149550.68	18032351.41	42621701.12	13158948.4
			Pay Back Period=period at cumulative cash flow=0
			Payback Period=6+(64568920/74910560)	6.9	YEARS
			Net Present value=Sum of Present Values of cash flows
			NPV=Net Present value=	$      13,158,948
			IRR (Using excel IRR function over cash flow)	14.79%
			Profitability Index	1.047169779	(NPV+PV of initial Cash flow)/PV of Initial cash flow=(13158948+100000000+178969894/7)/(100000000+178969894.7)