Question

Please show answers using Excel formulas.

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) Set up a spreadsheet showing input and output sections next to each other to facilitate ease of sensitivity analysis. 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,462	$137,606
	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)				27.75%
	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