Question

MGMT-6005 Group Assignment # 4 (Due by March 31*') Refer Chapter 12 - Integrated Case: Allied Food Products (pp. 444 – 447) Topic: Cash Flow Estimation Assignment Objective: Project Cash Flow Projections and Investment Analysis Project ECF = (EBIT(1-t) +Dep. - Capex - Chng. NOWC) Examine Capital investment details for the expansion project. a) Incorporate Table IC 12.1 (page 444) in an Excel worksheet and fill in the missing information. Determine NPV (@ WACC discount rate of 10%) and IRR

Answer 1

Please see the table below. Please be guided by the second column titled “Linkage” to understand the mathematics. The last row highlighted in yellow is your answer. Figures in parenthesis, if any, mean negative values. All financials are in $. Adjacent cells in blue contain the formula in excel I have used to get the final output. I have also produced the output in a tabular form towards the end so that you can simply copy and paste them in excel.

В C D E F G 140 (All financials are in $ '000) Linkage 141 Year 0 2 142 Investment Outlay 143 Equipment Cost A 200.0 144 Shipping and installation B 40.0 145 CAPEX C A+B 240,0 146 Increase in inventory D 25.0 147 Increase in accounts payable 5.0 E 148 ΔΝOWC F D- E 20.0 149 150 Depreciation rate d 33% 45% 15% 7% 151 152 Operating cash flows 153 Unit sales (thousands) 100 100 100 G 100 154 Price per unit н 2.00 2.00 2.00 2.00 155 Total revenues | G x H 200.0 200.0 200.0 200.0 156 Operating costs (excluding depreciation) 120.0 120.0 120.0 120.0 K dx C 157 Depreciation 158 Total costs 79.2 108.0 36.0 16.8 L JK 199.2 228.0 156.0 136.8 M 1-L 159 Operating income (28.0) 0.8 44.0 63.2 N 160 Taxes on income (11.2) 40% x M 0.3 17.6 25.3 161 After tax operating income O M-N (16.8) 0.5 26.4 37.9 162 Add back depreciation P 16,8 79.2 108.0 36.0 163 EBIT(1- T) + Dep Q O+P 79.7 91.2 62.4 54.7 164 165 Terminal year cash flows 166 Salvage value 167 Tax on salvage value 25.0 S = 40% x R 10.0 168 After tax salvage value T R- S 15.0 169 Recovery of NOWC U F 20.0 V = Q +T+ U -c-F (260.0) 170 Project Free Cash flows 79.7 91.2 62.4 89.7 171 (4.03) NPV(10%,D170:G170)+C170 9.28%=IRR(C170:G170) 172 NPV 173 IRR 9.57%'MIRR(C170:G170,10%,10%) 174 MIRR

The working in table:

(All financials are in $ '000)
Year	Linkage	0	1	2	3	4
Investment Outlay
Equipment Cost	A	200.0
Shipping and installation	B	40.0
CAPEX	C = A + B	240.0
Increase in inventory	D	25.0
Increase in accounts payable	E	5.0
ΔNOWC	F = D - E	20.0
Depreciation rate	d		33%	45%	15%	7%
Operating cash flows
Unit sales (thousands)	G		100	100	100	100
Price per unit	H		2.00	2.00	2.00	2.00
Total revenues	I = G x H		200.0	200.0	200.0	200.0
Operating costs (excluding depreciation)	J		120.0	120.0	120.0	120.0
Depreciation	K = d x C		79.2	108.0	36.0	16.8
Total costs	L = J + K		199.2	228.0	156.0	136.8
Operating income	M = I - L		0.8	(28.0)	44.0	63.2
Taxes on income	N = 40% x M		0.3	(11.2)	17.6	25.3
After tax operating income	O = M - N		0.5	(16.8)	26.4	37.9
Add back depreciation	P		79.2	108.0	36.0	16.8
EBIT(1 - T) + Dep	Q = O + P	-	79.7	91.2	62.4	54.7
Terminal year cash flows
Salvage value	R					25.0
Tax on salvage value	S = 40% x R					10.0
After tax salvage value	T = R - S					15.0
Recovery of NOWC	U = F					20.0
Project Free Cash flows	V = Q + T + U - C - F	(260.0)	79.7	91.2	62.4	89.7
NPV		(4.03)	'=NPV(10%,D170:G170)+C170
IRR		9.28%	'=IRR(C170:G170)
MIRR		9.57%	'=MIRR(C170:G170,10%,10%)