Question

Use the computerized model in File C10 to work this problem.

Golden State Bakers, Inc. (GSB) has an opportunity to invest in a new bread-making machine. GSB needs more productive capacity, so the new machine will not replace an existing machine. The new machine is priced at $260,000 and will require modifications costing $15,000. It has an expected useful life of 10 years, will be depreciated using the MACRS method over its 5-year class life, and has an expected salvage value of $12,500 at the end of Year 10. (See Table 10A.2 for MACRS recovery allowance percentages.) The machine will require a $22,500 investment in net working capital. It is expected to generate additional sales revenues of $125,000 per year, but its use also will increase annual cash operating expenses by $55,000. GSB’s required rate of return is 10 percent, and its marginal tax rate is 40 percent. The machine’s book value at the end of Year 10 will be $0, so GSB will have to pay taxes on the $12,500 salvage value.

a. What is the NPV of this expansion project? Should GSB purchase the new machine?

b. Suppose GSB’s required rate of return is 12 percent rather than 10 percent. Should the new machine be purchased in this case?

c. Should GSB purchase the new machine if it is expected to be used for only five years and then sold for $31,250? (Note that the model is set up to handle a five-year life; you need enter only the new life and salvage value.)

d. Would the machine be profitable if revenues increased by only $105,000 per year? Assume everything else is as originally presented and evaluated in part a.

e. Suppose that revenues rose by $125,000 but expenses rose by $65,000. Would the machine be acceptable under these conditions? Assume a 10-year project life and a salvage value of $12,500.

Chapter 10 Spreadsheet Problem Solutions (C10)Expansion Project1. There are a number of instructions with which you should be familiarto use these computerized models. These instructions appear in aseparate worksheet labeled INSTRUCTIONS. If you have not alreadydone so, you should read these instructions now. To read theseinstructions, click on theworksheet labeled INSTRUCTIONS.2. The model is set up to deal with a situation where the entireinvestment outlay occurs at t=0 and the inflows occur over thesubsequent five to 10 years. Modification of the model would berequired to deal with a shorter or longer time frame.INPUT DATA:KEY OUTPUT:Base price($260,000)NPVModifications($15,000)-6,216Increase in NWC($22,500)Increase in sales revenue220,000Operating costs150,000Salvage value8,500Required rate of return13%Tax rate40%MACRS class life (years)5Useful life (years)8MODEL-GENERATED DATA:Initial investment at t=0:Base price($260,000)Modification($15,000)Increase in NWC($22,500)Initial investment outlay($297,500)Depreciation schedule:Terminal cash flow:Depr. basis =$275,000Salvage value8,500EndingTax on sale of asset(3,400)YearMACRSDepreciationBookReverse of NWC22,500RateAllowanceValueTerminal CF27,60010.2055,000220,00020.3288,000132,00030.1952,25079,75040.1233,00046,75050.1130,25016,50060.0616,5000Annual cash flows:012345678910Initial invest.(297,500)Sales increase220,000220,000220,000220,000220,000220,000220,000220,00000Operating costs(150,000)(150,000)(150,000)(150,000)(150,000)(150,000)(150,000)(150,000)00Depreciation(55,000)(88,000)(52,250)(33,000)(30,250)(16,500)0000Earn. b/f taxes15,000(18,000)17,75037,00039,75053,50070,00070,00000Taxes(6,000)7,200(7,100)(14,800)(15,900)(21,400)(28,000)(28,000)00Net income9,000(10,800)10,65022,20023,85032,10042,00042,00000Add back deprec.55,00088,00052,25033,00030,25016,5000000Supplemental oper. CF64,00077,20062,90055,20054,10048,60042,00042,00000Salvage AT000000027,60000Net cash flow(297,500)64,00077,20062,90055,20054,10048,60042,00069,60000NPV(6,216)

Answer 1

1	B	C	D	E	F	G	H	I	J
2		Cost of Machine =		260000		Depreciation Rates =
3		Modification cost =		15000		1	0.2
4		Useful life =		10		2	0.32
5		Salvage value =		12500		3	0.192
6		Increase in NWC =		22500		4	0.1152
7		Increase in sales =		125000		5	0.1152
8		Increase in cost =		55000		6	0.06
9		Required Return =		10%						Formulas used -
10		Tax Rate =		40%
11
12	A	Depreciation tax savings -								Depreciation tax savings -
13		Year	Depreciation rate	Depreciation	Depreciation Tax savings	PV factor@Required return	PV of Tax savings			Year	Depreciation rate	Depreciation	Depreciation Tax savings	PV factor@Required return	PV of Tax savings
14		1	20.00%	55000	22000	0.909091	20000			1	=h3	=D14*(E2+E3)	=E14*(E10)	=1/(1+E9)^C14	=G14*F14
15		2	32.00%	88000	35200	0.826446	29090.91			2	=h4	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Drag the formula down
16		3	19.20%	52800	21120	0.751315	15867.77			3	=h5	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Drag the formula down
17		4	11.52%	31680	12672	0.683013	8655.147			4	=h6	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Drag the formula down
18		5	11.52%	31680	12672	0.620921	7868.315			5	=h7	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Drag the formula down
19		6	5.76%	15840	6336	0.564474	3576.507			=IF(E4>5,6,IF(E4<=5,0,0))	=IF(E4>5,H8,IF(E4<=5,0,0))	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Repeat and change rate cells respectively	Drag the formula down
20				275000			85058.65					sum			sum
21
22	B	Pv of cash flows other then Depreciation tax savings =								Pv of cash flows other then Depreciation tax savings =
23
24		Time		0	1 to useful life	Last year				Time		0	1 to useful life	Last year
25		Cost of machine =		-260000						Cost of machine =		-e2
26		Modification cost =		-15000						0		-e3
27		Increase in NWC =		-22500						0		-e6
28
29		Increase in sales =			125000					0			=e7
30		Increase in cost =			-55000					0			-e8
31		EBIT =			70000					EBIT =			=F29+F30
32		Tax			28000					Tax			=F31*E10
33		NOPAT =			42000					NOPAT =			=F31-F32
34
35		Post tax Salvage value				7500				Post tax Salvage value				g49
36		Recovery of NWC				22500				Recovery of NWC				=-E27
37		Net cashflows =		-297500	42000	30000				Net cashflows =		=SUM(E25:E27)	=f33
38		PV Factors @ Required return =		1	6.1446	0.385543				PV Factors @ Required return =		1	-PV(E9,E4,1)	1/(1+E9)^E4
39		PV of NCF		-297500	258071.8	11566.3				PV of NCF		=E38*E37	=f38*f37	g38*g37
40		Total PV of NCF =		-27861.9						Total PV of NCF =		=SUM(E39:G39)
41
42	C	Post tax salvage value =								Post tax salvage value =
43		Salvage value =				12500				Salvage value =				=E5
44		Less: Carrying value =								Less: Carrying value =
45			Cost	275000							Cost	0
46			Depreciation till now	-275000		0					Depreciation till now	=-E20		=E46+E45
47		Gain / Loss on sale				12500				Gain / Loss on sale				=G43-G46
48		Tax on Gain/ tax saving on loss				5000				Tax on Gain/ tax saving on loss				=G47*E10
49		Post tax salvage value =				7500				Post tax salvage value =				=G43-G48
50
51
52	(iv)	NPV =				57196.76				NPV =				=E40+H20
53
54		Now after you are done with this just change the required figures and you will find the answers.
55		If you want me to send the final answers for each part write in comment.
56
57