Question

Sugar Land Company is considering adding a new line to its product mix, and the capital budgeting analysis is being conducted by a MBA student. The production line would be set up in unused space (Market Value Zero) in Sugar Land’ main plant. Total cost of the machine is $240,000. The machinery has an economic life of 4 years, and MACRS will be used for depreciation. The machine will have a salvage value of $25,000 after 4 years. MACRS calculated by 5 year period

The new line will generate Sales of 1,250 units per year for 4 years and the variable cost per unit is $100 in the first year. Each unit can be sold for $200 in the first year. The sales price and variable cost are expected to increase by 3% per year due to inflation. Further, to handle the new line, the firm’s net working capital would have to increase by $30,000 at time zero (No additional NWC is needed in years 2, 3 and the NWC will be recouped at the end of year 4). The firm’s tax rate is 40% and its weighted average cost of capital is 10%.

The text below can be copy/pasted into the submission box below. Please save your work often as you type your answers.

What are the annual depreciation expenses for years 1 through 4? (10 P0ints)

	Year 1	Year 2	Year 3	Year4
Depreciation

Calculate the annual sales revenues and variable costs (Don’t include depreciation in your cost estimation), for years 1 through 4. (15 points)

	Year 1	Year 2	Year 3	Year4
$ Sales
$ Variable costs

Estimate annual (Year 1 through 4) operating cash flows (25 Points)

	Year 1	Year 2	Year 3	Year4
Sales
OCF

Estimate the after tax salvage cash flow (5 points)

Estimate the net cash flow of this project (25 points)

	Year zero	Year 1	Year 2	Year 3	Year4
CF of the project

Estimate the NPV, IRR, MIRR, and profitability Index of the project. (20 points)

NPV =
IRR =
MIRR =
PI

Answer 1

Formula sheet

A1	B	C	D	E	F	G	H	I	J	K
2
3		To calculate NPV of the project, free cash flow needs to be calculated as follows:
4		Free Cash Flow = Operating Cash Flow - Capital Expenditures - Change in working capital
5		Operating Cash Flow = EBIT*(1-Tax Rate)+Depreciation
6
7		Using the following data:
8		Tax Rate	0.4
9		Discount Rate	0.1
10		Investment in new Machine	240000
11		Sales	1250
12		Variable cost per unit	100
13		Price per unit	200
14		Project Life	4	Years
15		Salvage value at the end of 4 years	25000
16		Inflation	0.03
17		Net working capital required at time 0	30000
18		Depreciation each year can be calculated as follows:
19		Investment in system (B)	=D10
20		Depreciation follows MACRS 5 year
21
22		Hence depreciation each year can be calculated as follows:
23				Year 1	Year 2	Year 3	Year 4	Year 5	Year 6
24		MACRS 5 Year depreciation rate (rt)		0.2	0.32	0.192	0.1152	0.1152	0.0576
25		Depreciation (B*rt)		=$D$26*E24	=$D$26*F24	=$D$26*G24	=$D$26*H24	=$D$26*I24	=$D$26*J24
26		Book Value	=D19	=D26-E25	=E26-F25	=F26-G25	=G26-H25	=H26-I25	=I26-J25
27
28
29		Net Proceed from sale calculation:
30
31		Market Value at the end of 4th year	=D15
32		Book Value of Machines at the end of 4th year	=H26
33		Gain or Loss on sale of Machine	=Proceed From Sale - Book value at the end of sale
34			=D31-D32
35
36		Gain or Loss on sale of Machine	=D34
37		Tax on Gain & Loss	=D36*D8
38		Net Proceed from Sale of Machine	=Proceed from Sale - Tax Expense on gain or loss
39			=D31-D37
40
41		Free cash flow can be calculated as followed:
42		Year	0	1	2	3	4
43		Sales (units)		1250	1250	1250	1250
44		Price per unit		200	=E44*(1+$D$16)	=F44*(1+$D$16)	=G44*(1+$D$16)
45		$ Sales		=E43*E44	=F43*F44	=G43*G44	=H43*H44
46		$ Variable cost		=-E43*100	=-F43*100	=-G43*100	=-H43*100
47		Depreciation		=-E25	=-F25	=-G25	=-H25
48		Operating Income Before Tax (EBIT)		=SUM(E45:E47)	=SUM(F45:F47)	=SUM(G45:G47)	=SUM(H45:H47)
49		Tax (@40%)		=-E48*$D$8	=-F48*$D$8	=-G48*$D$8	=-H48*$D$8
50		After Tax operating income (EBIT*(1-T))		=E48+E49	=F48+F49	=G48+G49	=H48+H49
51		Add Depreciation		=-E47	=-F47	=-G47	=-H47
52		Operating Cash Flow		=E50+E51	=F50+F51	=G50+G51	=H50+H51
53		Initial investment in Machine	=-D10
54		Increase in Net Working Capital	=-D17				=-D54
55		Net Proceed from Sale of Machine					=D39
56		Free Cash Flow	=D53+D54	=E52+E53-E54+E55	=F52+F53-F54+F55	=G52+G53-G54+G55	=H52+H53-H54+H55
57
58		NPV calculation:
59		NPV of the project is present value of future cash flows discounted at required rate of return less the initial investment.
60		Given the following cash flow and WACC, NPV for the project can be calculated as follows:
61		Year	0	1	2	3	4
62		Free Cash Flow (FCF)	=D56	=E56	=F56	=G56	=H56
63		MARR (i)	=D9
64		(P/F,i,n) for each year		=1/((1+$D$63)^E61)	=1/((1+$D$63)^F61)	=1/((1+$D$63)^G61)	=1/((1+$D$63)^H61)
65		Present Value of cash flows = FCF*(P/F,i,n)		=E62*E64	=F62*F64	=G62*G64	=H62*H64
66		Present value if future cash flows	=SUM(E65:H65)
67
68		NPV for Project	=Present value fo future cash flows - Initial investment
69			=D66+D62
70
71		Hence NPV of the project is	=D69
72
73		Calculation of IRR:
74		IRR is the rate at which NPV of the project will be zero.
75		Given the following cash flow IRR can be calculated as below:
76
77		Year	0	=D77+1	=E77+1	=F77+1	=G77+1
78		Net Cash Flow	=D62	=E62	=F62	=G62	=H62
79
80		NPV=-27000+94200/(1+IRR)^1 +110220/(1+IRR)^2+102567/(1+IRR)^3+101,557/(1+IRR)^4
81		0=-27000+94200/(1+IRR)^1 +110220/(1+IRR)^2+102567/(1+IRR)^3+101,557/(1+IRR)^4
82		IRR can be found using hit and trial method for above equation.
83
84		IRR can also be found using IRR function in excel as follows:
85		Year	0	=D85+1	=E85+1	=F85+1	=G85+1
86		Cash Flow	=D78	=E78	=F78	=G78	=H78
87		IRR	=IRR(D86:H86)	=IRR(D86:H86)
88
89		Hence IRR of the project is	=D87
90
91		Calculation of MIRR:
92		MIRR is the rate at which PV of cash outflows is equal to the PV of FV of cash inflows.
93		Project term	4	years
94		Incremental cash flows:
95		Year	0	=D95+1	=E95+1	=F95+1	=G95+1
96		Incremental Cash Flow	=D56	=E56	=F56	=G56	=H56
97
98		PV of cash outflow	=-D96
99
100		Calculation of Future Value of cash inflows
101		Year	0	=D101+1	=E101+1	=F101+1	=G101+1
102		Incremental Cash Flow		=E96	=F96	=G96	=H96
103		WACC	=D9
104		Future Value of cash inflows		=E102*((1+$D103)^($H$101-E101))	=F102*((1+$D103)^($H$101-F101))	=G102*((1+$D103)^($H$101-G101))	=H102*((1+$D103)^($H$101-H101))
105		Total FV of cash inflows	=SUM(E104:H104)
106
107		Let r be the MIRR then,
108		PV of cash outflow*(1+r)4=FV of cash inflow
109		270000*(1+r)4=473127
110
111		Sovling the above equation:
112		r =	=((D105/D98)^(1/D93))-1
113
114		Hence MIRR is	=D112
115
116		Calculation of Profitability Index
117
118		Profitability index is given by following formula:
119		Profitability index	= Present value of future cash flows / Initial Investment
120			=D66/D10	=D66/D10
121
122		Hence Profitability index	=D120
123
124		Hence,
125		NPV	=D71
126		IRR	=D89
127		MIRR	=D114
128		PI	=D122
129

Answer 2

1. Annual depreciation calculation

PARTICULARS	YEAR 1	YEAR 2	YEAR 3	YEAR 4
MACRS	33.33%	44.45%	14.81%	7.41%
DEPRECIATION $	79992	106680	35544	17784

2. Annual Sales and Variable Costs

PARTICULARS		YEAR 1	YEAR 2	YEAR 3	YEAR 4
$ Sales		250000.00	257500.00	265225.00	273181.75
$ Variable Costs		125000.00	128750.00	132612.50	136590.88

3. Annual Operating Cash Flows

	PARTICULARS	YEAR 1	YEAR 2	YEAR 3	YEAR 4
	Sales	250000.00	257500.00	265225.00	273181.75
Less:	Variable Costs	125000.00	128750.00	132612.50	136590.88
Less:	Depreciation	79992.00	106680.00	35544.00	17784.00
	Profit before tax	45008.00	22070.00	97068.50	118806.88
Less:	tax@40%	18003.20	8828.00	38827.40	47522.75
	Net Income	27004.80	13242.00	58241.10	71284.13
Add:	Depreciation	79992.00	106680.00	35544.00	17784.00
	OCF	106996.80	119922.00	93785.10	89068.13

4. After Tax salvage value= $25000*(1-0.4) = $15000

5. Net Cash flow of the project year wise

	OCF	-	106996.80	119922.00	93785.10	89068.13
Less:	Investment	240000	0	0	0	0
Add:	Addition to NWC	30000	0	0	0	30000
Add:	After tax salvage value	-	0	0	0	15000
	CF	-270000	106996.80	119922.00	93785.10	134068.13

6. Calculation Of NPV

CF	-270000	106996.80	119922.00	93785.10	134068.13
PVF@10%	1	0.9090909	0.826446	0.751315	0.68301346
PV	-270000	97269.818	99109.09	70462.13	91570.3333

NPV = 97269.818+99109.09+70462.13+91570.3333-270000 = +$ 88411.38

Now IRR by interpolation Technique:

NPV @ 25%:

PARTICULARS	YEAR 0	YEAR 1	YEAR 2	YEAR 3	YEAR 4
CF	-270000	106996.80	119922.00	93785.10	134068.13
PVF@25%		0.8	0.64	0.512	0.4096
PV	-270000	85597.44	76750.08	48017.97	54914.304

NPV= 85597.44+76750.08+48017.97+54914.304-270000= -$ 4720.2

Now NPV @23%:

PARTICULARS	YEAR 0	YEAR 1	YEAR 2	YEAR 3	YEAR 4
CF	-270000	106996.80	119922.00	93785.10	134068.13
PVF@23%	1	0.8130081	0.660982	0.537384	0.43689749
	-270000	86989.268	79266.31	50398.6	58574.0279

NPV = 86989.268+79266.31+50398.6+58574.0279-270000 = +$5228.21

IRR= Lower rate + NPV(L)/NPV(L)-NPV(H) * )Higher rate- Lower rate)

IRR= 23% + 5228.31/5228.31-(-4720.2) * (25-23) = 24.05%

Profitability Index: Present Value of Cash Inflows / Cash Outlay =

97629.818+99109.09+70462.13+91570.33 / 270000 =358411.4/270000 = 1.32745