Question

Calculations of the NPV, IRR and the payback for the project and an analysis of the results. I need the excel formula calculation and analysis of the questions

You have identified a potential opportunity for WBC, which involves undertaking a project that will have a ten-year life. The project requires an initial purchase of equipment and furniture totalling $4,500,000, plus ancillary programming capability and machinery costing $1,500,000. The equipment and furniture will depreciate and have a salvage value of $500,000 at the end of the project’s life, and the programing machinery will have nil salvage value at the end of the project’s life. Depreciation is calculated on a straight-line basis over five years. Information related to the project is as follows: • Sales will be $3,050,000, $4,000,000 and $5,000,000 respectively in each of the first three years of operation, expected to grow at 10 per cent per annum for a further four years thereafter, and then settle to a growth of 5 per cent per annum indefinitely thereafter. In the event of not undertaking this project, all of this income would be lost. • Variable costs associated with the project will be 65 per cent of sales. • Fixed costs associated with the project will be $400,000 in the first year and expected to grow at 5 per cent per annum thereafter. • Even though this project will not add additional expenses to head office, WBC has a policy of allocating a ‘head office’ charge of $200,000 a year to each major project. • Research for this project and its capability was conducted during the previous year at a cost of $300,000. It yielded valuable information. • The corporate tax rate is 30 per cent.
GSB003 Managing Financial Resources: Course Outline
3
• Financiers of this type and risk in this industry are presently requiring a rate of 12 per cent after corporate tax.

In order to undertake this project, WBC is considering various financing options. One option is to borrowing $5,000,000 at 7 per cent per annum. This loan will be paid off in 10 equal annual instalments.

Required Evaluate this project, and provide a report to WBC management discussing whether or not you recommend it should undertake the project, providing a full explanation of your recommendation. As support for your recommendation ensure your answer includes the following: • Calculations of the NPV, IRR and the payback for the project and an analysis of the results. • Justification for the correct discount rate to be used in evaluating the project. • Your assessment of the advantages and disadvantages of each methodology (NPV, IRR and payback), and which you therefore recommend is applied to evaluate this project. • Details of any other (financial and non-financial) matters you would consider before making a recommendation in respect of this project.

Answer 1

The required rate of return 12% is the discount rate to discount the cash flows.

We need to find the annual interest payments, so as to get the annual interest tax shields(ITS)

so,using the PV(OA) formula,

5000000=Pmt.*(1-1.07^-10)/0.07

The annual Pmt.= 711888

Now, drawing up the loan amortisation schedule,

Year	Annuity	Tow. Int.	Tow. Loan	Loan Bal.	ITS
1	2	3=Prev.5*7%	4=2-3	5=Prev.5-4	6=3*30%
0				5000000
1	711888	350000	361888	4638112	105000
2	711888	324668	387220	4250893	97400
3	711888	297562	414325	3836568	89269
4	711888	268560	443328	3393240	80568
5	711888	237527	474361	2918879	71258
6	711888	204322	507566	2411313	61296
7	711888	168792	543096	1868218	50638
8	711888	130775	581112	1287106	39233
9	711888	90097	621790	665315	27029
10	711888	46572	665315	0	13972
	7118875	2118875	5000000		635663

Year	0	1	2	3	4	5	6	7	8	9	10
1.Cost of Equip.& Furn.(4500000)	-4500000
2.Cost of anc.pgmg.cap.& m/c	-1500000
3. After-tax salvage(500000*(1-30%))											350000
4.Sales(given )		3050000	4000000	5000000	5500000	6050000	6655000	7320500	7686525	8070851	8474394
5.Var. cost(Sales*65%)		-1982500	-2600000	-3250000	-3575000	-3932500	-4325750	-4758325	-4996241	-5246053	-5508356
6. Fixed cost(Given)		-400000	-420000	-441000	-463050	-486203	-510513	-536038	-562840	-590982	-620531
7. Depn.(E&F)(4500000-500000)/5		-800000	-800000	-800000	-800000	-800000
8.Depn.(M/c(1500000/5)		-300000	-300000	-300000	-300000	-300000
9.EBT(4-5-6-7-8)		-432500	-120000	209000	361950	531298	1818737	2026137	2127444	2233816	2345507
10.Tax at 30%(9*30%)		129750	36000	-62700	-108585	-159389	-545621	-607841	-638233	-670145	-703652
11.EAT(9-10)		-302750	-84000	146300	253365	371908	1273116	1418296	1489211	1563671	1641855
12.Add back: Depn.(7+8)		1100000	1100000	1100000	1100000	1100000	0	0	0	0	0
13.Annual Opg. Cash flow(11+12)		797250	1016000	1246300	1353365	1471908	1273116	1418296	1489211	1563671	1641855
14.Add: ITS (as per Table)		105000	97400	89269	80568	71258	61296	50638	39233	27029	13972
15.Total annual cashflows(1+2+3+13+14)	-6000000	902250	1113400	1335569	1433933	1543166	1334413	1468933	1528443	1590700	2005826
16. PV F at 12%(1/1.12^Yr.n)	1	0.89286	0.79719	0.71178	0.63552	0.56743	0.50663	0.45235	0.40388	0.36061	0.32197
17.PV at 12%(15*16)	-6000000	805580	887596	950631	911290	875634	676055	664471	617313	573622	645822
18. NPV(Sum of Row.17)	1608015
19. IRR(of Row 15)	18%
20. Payback period
15.Total annual cashflows(1+2+3+13+14)	-6000000	902250	1113400	1335569	1433933	1543166	1334413	1468933	1528443	1590700	2005826
Cumulative c/f	-6000000	-5097750	-3984350	-2648781	-1214848	328318	1662731	3131664	4660107	6250808	8256634
P/B=
4+(1214848/1543166)=
4.79
Years.

Based on the above workings:

The project is recommended for the following reasons:

NPV ,ie. Net present values of the cash flows is POSITIVE

IRR 18% > Reqd. return 12%

& the project pays back in < 5 yrs. --almost < 50% of the project's life.

NPV is the single most reliable measure as it takes into a/c the entire cash flows of a project.

the only limitation being , its results are as reliable as the inputs used, ie. The forecasted cash flows, discount rate to discount the cash flows used, etc.

IRR is a bench mark rate that can be compared with the actual results & very easy to eliminate the projects , at the outset itself.

But it does not consider the cash flows after the IRR & sometimes yields multiple IRRs , when the cash flows change signs(+ve or -ve) more than once.

Pay back helps us to know immediately, when the initial invetsment will be recovered/recouped .

But it does attach time value to the cash flows & hence may be grossly misleading.

Any other (financial and non-financial) matters to would consider before making a recommendation in respect of this project

Given that,

In the event of not undertaking this project, all of this income would be lost

The management should discuss the possibilities which may result in them , not undertaking this project--which happens to look like adding to shareholders'networth --with its POSITIVE NPV.

"DO NOTHING" possibility exists for all projects under the sun.

In the event of not undertaking this project, all of this income would be lost

Not only the incomes, but also all costs like initial invetsment & its depreciation effects , variable & fixed costs, loan taken & its interest tax shields-- nothing will be there.