Question

Calculations must be done in Excel - Using the capital budgeting method of; Tax Effects, then Cash flows, then NPV.

As the financial advisor to All Star Manufacturing you are evaluating the following new investment in a manufacturing project: -

The project has a useful life of 12 years.

Land costs $6m and is estimated to have a resale value of $10m at the completion of the project.

Buildings cost $5m, with allowable depreciation of 10% pa reducing balance and a salvage value of $0.9m.

Equipment costs $4m, with allowable depreciation of 20% pa reducing balance and a salvage value of $0.5m. An investment allowance of 20% of the equipment cost is available.

Revenues are expected to be $7m in year one and rise at 5% pa.

Cash expenses are estimated at $3m in year one and rise at 3% pa.

The new product will be charged $400,000 of allocated head office administration costs each year even though head office will not actually incur any extra costs to manage the project.

An amount of $100,000 has been spent on a feasibility study for the new project.

The project is to be partially financed with a loan of $7.5m to be repaid annually with equal instalments at a rate of 4% pa over 12 years.

Except for initial outlays, assume cash flows occur at the end of each year.

The tax rate is 30% and is payable in the year in which profit is earned.

The after-tax required return for the project is 8% pa.

Required

1. Calculate the NPV. Is the project acceptable? Why or why not?
2. Conduct a sensitivity analysis showing how sensitive the project is to revenues, the resale value of the land and to the required rate of return. Explain your results.

Please provide an Excel calculation answer + An explanation of how the answers were found.

Answer 1

The annual payment on loan will be

7.5=Pmt.*(1-1.04^-12)/0.04

Pmt.=7.5/((1-1.04^-12)/0.04)=

0.799141

Now, constructing the Loan amortisation table, to know the annual Interest Tax shields

Year	Annual Pmt.	Tow. Int.	Tow. Loan	Loan bal.	ITS=Int.*30%
0				7.5
1	0.799141	0.3	0.499141	7.000859	0.09
2	0.799141	0.280034	0.519107	6.481752	0.08401
3	0.799141	0.25927	0.539871	5.941881	0.077781
4	0.799141	0.237675	0.561466	5.380414	0.071303
5	0.799141	0.215217	0.583925	4.79649	0.064565
6	0.799141	0.19186	0.607282	4.189208	0.057558
7	0.799141	0.167568	0.631573	3.557635	0.05027
8	0.799141	0.142305	0.656836	2.900799	0.042692
9	0.799141	0.116032	0.683109	2.21769	0.03481
10	0.799141	0.088708	0.710434	1.507256	0.026612
11	0.799141	0.06029	0.738851	0.768405	0.018087
12	0.799141	0.030736	0.768405	0.00	0.009221
	9.589696	2.089696	7.5		0.626909

Workings:
Building depreciation
Book/carrying Value	1.412148	5*(1-10%)^12
Salvage	0.9
Loss on salvage	0.512148
Tax CF saved on loss	0.153644
ATCF on salvage(Salvag+Cash saved)	1.053644
Equipment depreciation
Book/carrying Value	0.219902	(4-0.8)*(1-20%)^12
Salvage	0.5
Gain on salvage	0.280098
Tax CF on gain	0.084029
ATCF on salvage(Salvage-Tax CF on gain)	0.415971

Fig.in mlns.
Year	0	1	2	3	4	5	6	7	8	9	10	11	12
1.Land	-6
2.Buildings	-5
3.Equipment	-4
4.Investment allowance(4*20%)	0.8
5.ATCF on sale of land(10*(1-30%))													7
6.ATCF on salvage of Building													1.05364
7.ATCF on salvage of Equipment													0.41597
Operating cash flows:
Revenues		7	7.35	7.7175	8.10338	8.50854	8.93397	9.38067	9.8497	10.3422	10.8593	11.4023	11.9724
Cash expenses		-3	-3.09	-3.1827	-3.27818	-3.3765	-3.4778	-3.5822	-3.6896	-3.8003	-3.9143	-4.0317	-4.1527
Depn.-Bldgs.		-0.5	-0.45	-0.405	-0.3645	-0.3281	-0.2952	-0.2657	-0.2391	-0.2152	-0.1937	-0.1743	-0.1569
Depn. Eqpt.		-0.64	-0.512	-0.4096	-0.32768	-0.2621	-0.2097	-0.1678	-0.1342	-0.1074	-0.0859	-0.0687	-0.055
EBIT		2.86	3.298	3.7202	4.13301	4.54182	4.95119	5.36502	5.78672	6.21927	6.66537	7.12745	7.60779
Tax at 30%		-0.858	-0.9894	-1.1161	-1.2399	-1.3625	-1.4854	-1.6095	-1.736	-1.8658	-1.9996	-2.1382	-2.2823
EAT		2.002	2.3086	2.60414	2.89311	3.17928	3.46583	3.75551	4.0507	4.35349	4.66576	4.98922	5.32546
Add back: depn.(Bldgs.+Eq.)		1.14	0.962	0.8146	0.69218	0.59019	0.50496	0.43349	0.37337	0.32261	0.27961	0.24306	0.21188
Add:Interest tax shields		0.09	0.08401	0.07778	0.0713	0.06456	0.05756	0.05027	0.04269	0.03481	0.02661	0.01809	0.00922
8.Operating Cash flows		3.232	3.35461	3.49652	3.65659	3.83404	4.02835	4.23928	4.46676	4.71091	4.97198	5.25036	5.54656
9.NET annual FCFs(1 to 7)+8	-14.2	3.232	3.35461	3.49652	3.65659	3.83404	4.02835	4.23928	4.46676	4.71091	4.97198	5.25036	14.0162
PV F at 8%(1/1.08^ yr.n)	1	0.92593	0.85734	0.79383	0.73503	0.68058	0.63017	0.58349	0.54027	0.50025	0.46319	0.42888	0.39711
PV at 8%(FCF*PV F)	-14.2	2.99259	2.87604	2.77565	2.6877	2.60938	2.53854	2.47358	2.41325	2.35663	2.30299	2.25179	5.56601
NPV at 8%(Sum PVs)	19.6442		YES. The project is acceptable as the NPV of its cashflows is POSITIVE.

b..Sensitivity of NPV
Revenues	NPV	Change from Base case NPV	% change
Base case	19.6442
10% inc.	24.3292	4.6850	23.85%
-10%	14.9591	-4.6851	-23.85%
Resale value of Land
10% inc.
Base case	19.6442
10% inc.	19.9221	0.2779	1.41%
-10%	19.3662	-0.278	-1.42%
Reqd. Return
Base case	19.6442
10% inc.	18.0111	-1.6331	-8.31%
-10%	21.4002	1.756	8.94%

From the above sensitivity table, we can see that the project's NPV is

Maximum sensitive to revenues   &

Minimum sensitive to resale value of land