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 $5,600 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 $215,000. A commercial realtor believes that the price is likely to remain unchanged in the near future. The building originally cost $63,000 and is being depreciated at $1,800 annually. Its current net book value (NBV) is $7,800. c. Lewisville Company is seriously considering converting the warehouse into a factory outlet for furniture. The remodeling will cost $130,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 $645,000. This total is fully recoverable whenever operations terminate. e. Lou is fairly certain that the warehouse will be condemned in 10 years to make room for a new highway. The firm most likely would receive $230,000 from the condemnation. f. Estimated annual operating data, exclusive of depreciation, are as follows: Sales (cash) Operating expenses $930,000 $530,000 g. Nonrecurring sales promotion costs at the beginning of year 1 (i.e., time 0) are expected to be $109,000. (These costs are fully deductible for tax purposes.) h. Nonrecurring termination costs at the end of year 5 are $56,000. (These costs are fully deductible for tax purposes.) i. The after-tax discount rate for capital budgeting purposes is 10%. (To calculate the present value factor for each year, i, i = 1, 5, use the following formula: PV factor; = (1 = 1.10?). The company is in the 37% 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.

Answer 1

Before start answering the above question, let me clarify or explain certain factor in relates to NPV ( Net present value ) . In basic terms , NPV mean difference between Present value of cash inflow and cash outflow . This is purely part of capital budgeting . Base formula for NPV would be = x1/(1+r) + x2 ( 1+r)2( Square )-X0

x1 - mean cash flow in Year 1, x2 - mean cash flow in Year 2 and X0 - mean Initial cash Investment. r - discount factor

In this problem r- discount factor is 10% ( as mentioned in the question) , We need to derived r value Year wise : see below - Year 0= 1/(1.1), Year 1= Yr0/( 1.1) , Year 2= Yr1/( 1.1) so on ... to derived below number . Cumulative sum number ( Yr 0-Yr 5)= 3.791 .

Year
0	Discount Factor@10%)
1	0.909
2	0.826
3	0.751
4	0.683
5	0.621
Sum	3.791

Step 1- Leasing system , rent foregone .

Basis - Monthly rent foregone -$ 5600 = yearly rent = $5600*12 . After tax - $ 5600*12* ( 1-37%)

Tax rate mentioned in the Question

Year wise Rent foregone ( after tax) * Discount factor as mentioned above .

Like Year 1= $5600*12*(1-37%)*Yr 1 ( discount factor=0.909) =$38487

Tax Bracket	37%
PV factor to use in this question
Rent amount- Monthly( $)	5,600
After tax Rent amount would be ($)	5600(1-37%)	3,528	( derived Value )
We are going to use PV to factor rent	Year 0	Year 1	Year 2	Year 3	Year 4	Year 5
Rent ( cash inflow)- Foregone		42,336	42,336	42,336	42,336	42,336
Discount Factor @10%	-1,60,487	38,487	34,988	31,808	28,916	26,287

So on the above basis, we derived total rent foregone amount .

Derived NPV value : Rent forgone amount as explained above. Remodelling cost - cash outflow at year 1 ( No discount factor ) ,Working capital Outflow ( year 1 - no discount factor ) . Remember , Working capital will be fully recovered at Year 5 so Cash Inflow with 5th year discount factor -$645000*0.621 = $400545

Sales - Cash inflow ( after tax) * sum of discount factor ( 3.791- as derived above ) , Operating Expenses - Cash outflow * Sum of Discount factor ( 3.7919 as derived above )

Sales promotion - Year 1 No discount factor - Just cash outgo

Terminal Value - at the end of 5th year - use 5th year discount factor 0.621 ( ( $56000*0.621)

Rent ( cash inflow)- Foregone
Discount Factor @10%	-1,60,487
( Present Value formula used here )
Cash outflow :
Remodelling cost ($)	-1,30,000
Working capital ( $)	-6,45,000
( Inventory + Receivable )
Depreciation ( after tax ) PV	39,009
( please see below workings )
Recovery of Working capital	4,00,545
at end of 5th Year ( PV factor '0.621)
Sales ( After Tax)-$930,000(1-37%)*PV factor ( as calculated below -3.791	22,21,147
Operating Expenses( after Tax)	-12,65,815
$530,000(1-37%)*PV factor ( as calculated below -3.791
sales Promotion ( start of the year)($)	-1,09,000
Terminal Value ($)
Value -$ 56000*PV factor at 05th Year	-34,776
( $56000*0.621)
NPV ($)	3,15,623

Decision - I think after considering all above factor , we derived +NPV , which is really good for the company , Company can opt for Convert to factory outlet instead of Leasing . After consider all cash inflow and outflow , finally company can able to reach at good amount of +ve NPV . This showing perfect indicator to take conversion decision .

Depreciation Tax saving calculate based on VDB method . VDB formula also updated in below workings

Formula - VDB( Cost of the asset, salvage value, Depreciation period, Year start , Year end )

Year 0 - No depreciation . Year 1 = VDB( Original cost($1,30,000)*Salvage value(0),Depreciation period (5),Year start (0), Year end (1) = derived number $ 52000. Same formula use for others ( Year 2-5)

Tax advantage on Depreciated Value * Year wise Discount factor = PV of Tax saving in depreciation

Depreciation calculation based on VDB function
Cost of the Asset ( $)	1,30,000
Salvage Value ( $)	-
Depreciation period ( Yr)	5
Income Tax rate	37%
Depreciation calculation based on VDB function	DDB	VDB Formual
Year	Depreciation		Tax base Advantage
0		VDB( Cost of the asset, salvage value, Depreciation period, Year start , Year end )		Discount Factor@10%)	Depreciation PV effect
1	52,000		19,240	0.909	17,491
2	31,200		11,544	0.826	9,540
3	18,720		6,926	0.751	5,204
4	14,040		5,195	0.683	3,548
5	14,040		5,195	0.621	3,226
Sum	1,30,000		48,100	3.791	39,009