Question

 

Superficial Casting Company is considering adding a new line to its product mix. The production line would be set up in unused space in Superficial’s main plant based in Hollywood, California. The machinery’s invoice price is $208,000, another $12,000 in shipping charges is required, and installation costs would amount to $27,000. The machinery has an economic life of 4 years, and Superficial management has obtained a special tax ruling that places the equipment in MACRS 3-year class. The machinery is expected to have a salvage value of $28,000 after 4 years of use. The new line would generate incremental sales of 1,325 units per year for all 4 years at an incremental cost of $112 per unit in the first year, excluding depreciation. Costs (excluding depreciation) are expected to increase by 2% per year due to inflation after the first year. Each unit can be sold for $219 in the first year. The sales price is expected to rise by 3.75% in each of the next two years, and then to fall by 8% in the fourth year, due to fast diminishing demand. To handle the new line, the company will require net operating working capital at 11.5% of the sales anticipated at the end of the next period. In other words, the company will need net operating working capital at time 0 of 11.5% of the sales expected at the end of year 1, etc. Net operating working capital is expected to be fully recoverable at the end of year 4, the last year of the product’s life cycle. The firm’s marginal tax rate is 38.2%, and its cost of capital is 10.75% per year.

(1) c Input should include all parameters discussed in the text of the problem. Output should include: NPV, IRR, MIRR, PI, Payback Years and a Decision Box (Invest or Don’t Invest). If you want extra credit for including the Discounted Years to Payback, you should include that in your output section, as well.

(2) Show a calculation area separate from the Input/Output area where the parameters used in the calculations are LINKED to the input section, where necessary, and the output in the calculation area is LINKED to the output section. Your logic should be easy to follow. Ideally, MACRS factors, annual depreciation and book value should be shown together on three separate lines. Net Working Capital Requirements should be shown on a line with Changes in Net Working Capital Requirements shown on a line below the Requirements line.

(3) Derive a one way DATA TABLE for one of the variables in the input section versus the NPV of the output section. Graph this table and label the graph following the label standards discussed in class.

(4) Derive a two way DATA TABLE using any two input variables that you like against NPV. Color code the NPV values less than 25% of your best estimate of the NPV. This table should be properly labeled, as well.

(5) All dollar results should be shown in the nearest dollar. All percent values should be shown as xx.xx%. And all years to payback or index values should be shown to two decimal places. Do not round your results until the end of the calculations.

(6) Derive a formula for the Discounted Years to Payback for this case and include the result in the Output Section.

(7) Use a vertical or horizontal table lookup for revenue. Generate several different revenue tables, using different inflation rates. When creating your graph, do it by showing NPV or IRR vs the different table options.

Answer 1

		Initial Investment:
		Machinery invoice price	$208,000
		Shipping charges	$12,000
		Installation cost	$27,000
	X	Total initial investment	$247,000
		Tax Rate	0.382
		Cost of Capital	0.1075
		Depreciation Tax shield:
		Sales in incremental units	1325
		Incremental cost per unit in year 1	$112
		Incremental cost per unitin year 2	$114.24	(112*1.02)
		Incremental cost per unit in year 3	$116.52	(112*(1.02^2)
		Incremental cost per unit in year 4	$118.86	(112*(1.02^3)
		Sales price per unit in year1	$219
		Sales price per unit in year2	$227.21	(219*1.0375)
		Sales price per unit in year3	$235.73	(219*(1.0375^2)
		Sales price per unit in year4	$216.87	235.73*(1-0.08)
		Present Value (PV) of Cash Flow:
		(Cash Flow)/((1+i)^N)
		i=Discount Rate=Cost of Capital=10.75%=	0.1075
		N=Year of Cash Flow
	N	Year	0	1	2	3	4
	A	MACRS Depreciation rate		33.33%	44.45%	14.81%	7.41%
	B=A*247000	Depreciation amount		$          82,325	$         109,792	$      36,581	$       18,303
	C=B*0.382	Tax flow due to Depreciation Tax shield:		$          31,448	$            41,940	$      13,974	$          6,992
		Cash flow due to Salvage:
	D	Before tax salvage value					$28,000
	E=D*(1-0.382)	After tax salvage value					$       17,304
		Cash Flow from Sales:
	F	IncrementalUnit Sales		1325	1325	1325	1325
	G	Sales Price		$219	$227.21	$235.73	$216.87
	H=F*G	Incremental Sales Revenue		$290,175	$301,057	$312,346	$287,358
	I	Unit Cost		$112	$114.24	$116.52	$118.86
	J=F*I	Incremental Costs		$148,400	$151,368	$154,395	$157,483
	K=H-J	Cash flow from Sales(Before Tax)		$141,775	$149,689	$157,951	$129,875
	L=K*(1-0.382)	After tax cash flow from Sales		$          87,617	$            92,508	$      97,614	$       80,263
	M	Working Capital need	$33,370.13	$34,621.50	$35,919.81	$33,046.23
	O	Cash flow due to working capital need	($33,370)	($1,251)	($1,298)	$2,874	$33,046
	N	Year	0	1	2	3	4
	P=-X+C+E+L+O	Total Cash flow	($280,370)	$        117,814	$         133,150	$    114,461	$     137,605
		Cumulative cash flow	($280,370)	$     (162,556)	$         (29,407)	$      85,054	$     222,659	SUM
	Q=P/(1.1075^N)	Present Value (PV)of Cash Flow	$       (280,370)	$        106,378	$         108,556	$      84,261	$       91,466	$ 110,290
	FV=P*(1.075^(4-N))	Future value (FV )of positive cash flows		$        146,360	$         153,871	$    123,046	$     137,605	$ 560,881
	NPV	Net Present Value=Sum of PV of cash flows	$          110,290
	IRR	Internal Rate of Return	27.75%	(Using IRR function of excel on the cash flow)
		Sum of FV of positive cash flows	$          560,881
	MIRR	((560881/280370)^(1/4))-1	0.189281727
	MIRR	Modified Rate of Return	18.93%
	PI	Profitability Index=(NPV+280370)/280370	1.39
	Pay Back	Period when Cumulative cash flow=Zero	2.26	Years	(2+(29407/114461)
		DECISION :
		PROJECT IS ACCEPTED
		Since , NPV POSITIVE
		IRR, MIRR greater than Cost of capital
		PI greater than 1