Question

Lou Lewis, the president of Lewisville Company, has asked you to give him an analysis of the best use of a warehouse the company owns a. Lewisville Company is currently leasing the warehouse to another company for $6,100 per month on a year-to- year basis. (Hint Use the PV function in Excel to calculate, on an after-tax basis, the PV of this stream of monthly rental receipts.) b. The warehouse's estimated sales value is $227,000. A commercial realtor believes that the price is likely to remain unchanged in the near future. The building originally cost $65,500 and is being depreciated at $2,050 annually. Its current net book value (NBV) is $8,050 C. Lewisville Company is seriously considering converting the warehouse into a factory outlet for furniture. The remodeling will cost $155,000 and will be modest because the major attraction will be rock-bottom prices. The remodeling cost will be depreciated over the next 5 years using the double-declining-balance method. (Note: Use the VDB function in Excel to calculate depreciation charges. The advantage of using the VDB, rather than the DDB, function is that there is a (default) option in the former that provides an automatic switch to the straight-line method when it is advantageous to do so.) d. The inventory and receivables (net of current liabilities) needed to open and sustain the factory outlet would be e. Lou is fairly certain that the warehouse will be condemned in 10 years to make room for a new highway. The f. Estimated annual operating data, exclusive of depreciation, are as follows: $685,000. This total is fully recoverable whenever operations terminate firm most likely would receive $255,000 from the condemnation Sales (cash) Operating expenses $955,000 $555,000 g. Nonrecurring sales promotion costs at the beginning of year 1 (i.e., time O) are expected to be $117,000. (These costs are fully deductible for tax purposes.) h. Nonrecurring termination costs at the end of year 5 are $61,000. (These costs are fully deductible for tax i. The after-tax discount rate for capital budgeting purposes is 12%. (To calculate the present value factor for each year, , i= 1, 5, use the following formula: PV factor!-(1 ÷ 1.121). The company is in the 35% tax bracket (federal and state combined). Required: 1. Show how you would handle the individual items in determining whether the company should continue to lease the space or convert it to a factory outlet. Use PV function in Excel, VDB function in Excel to calculate annual depreciation charges. Use NPV function to calculate depreciation tax savings. 2. Indicate which course of action, based only on these data, should be taken Req 1 Req 2 Show how you would handle the individual items in determining whether the company should continue to lease the space function in Excel to calculate annual depreciation charges. Use NPV function to calculate depreciation tax savings. (Negati answers to the nearest whole dollar.) Cash Flows in Yea Item Description PV 2 a. After-tax monthly rent foregone All are irrelevant c. Remodeling cost (capitalized) Depreciation tax savings (DDB Investment in net working capital Recovery After-tax cash sales After-tax cash operating expenses After-tax sales promotion cost, year 0 After-tax termination cost, year 5 method. @35%) of net working capital 9 Req 1 Req 2

Answer 1

1.

Item	Description	PV Factor	PV	0	1	2	3	4	5
a.	After tax monthly Rent Foregone ($6,100*12*.65)	3.605	(171,525.90)		(47,580)	(47,580)	(47,580)	(47,580)	(47,580)
b.	All are irrelevant
c.	Remodelling Cost		($155,000)	($155,000)
	Depreciation	0.893 0.797 0.712 0.636 0.567	19378.10 10376.94 5562.14 3,726.32 3,322.05		$21,700	$13,020	$7,812	$5859	$5859
d.	Investment in net working capital		($685,000)	($685,000)
e	Recovery of net working capital	0.567	388,395			7048.37
f.	After Tax Cash Sales ($955,000*.65)	3.605	2,237,803.75	620,750	620,750	620,750	620,750	620,750	620,750
	After Tax Operating expenses($555,000*.65)	3.605	(1,300,503.75)	360,750	360,750	360,750	360,750	360,750	360,750
g.	After Tax Sales promotion(117,000*.65)		(76,050)	(76,050)
h.	After Tax Termination Cost	0.567	(22,481.55)						(39,650)
	NPV		258,003.10

2. The positive net present value $258,003.10 suggest that, comparing to the leasing alternative it is financially advantageous to comparing the facility into a factory outlet. The net present value from converting into the factory outlet is also better than the alternative of selling the warehouse for $255,000.

Expanation -

Calculation depreciation

End of Year	BV	Depreciation	Tax Shield
0	$155,000	0	0
1	$93,000	$62,000*.35	$21,700
2	$55,800	$37,200*.35	$13,020
3	$33,480	$22,320*.35	$7,812
4	$16,740	$16740*.35	$5,859
5	0	$16,740*.35	$5,859