Excel 5.19-6 Due Apr 24 - Rusincovitch

INSTRUCTIONS

1. This assignment is worth 10 points. 2. It is due 11:59pm, Apr 24, Thursday. You can have a 1-day grace period with an upfront loss of 2 points. Submission later than 11:59 pm, Apr 25 will not be accepted. 3. Use Excel functions, formulas and cell reference to f ill in the colored cells to complete the work. 4. After completing the work, work, upload the Excel Excel file to Blackboard. Blackboard.

The world’s largest digital library

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.





A

B

C

D

E

F

G

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

Chapter: Problem:

19 6

As part of its overall overall plant modernization modernization and cost reduction reduction program, program, Western Western Fabrics' Fabrics' management management h automated autom ated weavi weaving ng loom. In the capita capitall budgeting budgeting analys analysis is of this equip equipment, ment, the the IRR of the the project project w project's required return of 12%.

The loom has an an invoice invoice price of $250,000 $250,000,, including including delivery delivery and installation installation charges. The funds need the bank through a 4-year amortized amortized loan at a 10% interest interest rate, with payments to be made at the end o the loom is purchased, the manufacturer manufacturer will contract to maintain and service it for a fee of $20,000 $20,000 pe year. The loom falls in the MACRS 5-year class, and Western's marginal federal-plus-state federal- plus-state tax rate is 4

Gardial Automation Inc., maker maker of the loom, has offered to lease the loom to Westen for $70,000 upon plus 4 additional annual lease payments payments of $70,000 to be made at the ends of Years 1 through 4. (Note payments in total.) The lease agreement includes maintenance and servicing. servicing. Actually, the loom has at which time its expected salvage value is zero; however, after 4 years, its market value is expected to $42, $4 2,50 500. 0. Tan Tanner ner-Wo -Woods ods pla plans ns to buil build d and and enti entire rely ly new pla plant nt in 4 ye year ars, s, so it has no inte intere rest st in eith either er l loom for more than that period. a. Should the loom be leased or purchased? purchased? First, we want to lay out all of the input data in the problem. INPUT DATA Invoice Price Length of loan Loan Interest rate Maintenance fee Tax Rate Lease fee Equipment expected life Expected salvage value Market value after 4 years Book value after 4 years

$250,000 4 10% $20,000 40% $70,000 8 $0 $42,500 $42,500

First, we can determine the annual loan payment payment that must be made on the new equipment. We will do function wizard for PMT. Annual loan payment = Year

$78,868 1

2

3

4





A B C D E F G see that that the decis decision ion being being made is whether whether to purcha purchase se the equipment equipment at at a net cost cost of $250 $250,, 53 Now, we see $78,868) or lease lease the equipment and make make annual payments of $70,000. $70,000. To make this decision, decision, we mu 54 $78,868) 55 flows. 56





A 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97

B

C

D

E

F

G

Before proceeding with our NPV analysis we must determine the schedule of depreciation charges for MACRS 5-year Depreciation Schedule Year 1 2 Depr. Rate 20.00% 32.00% Depr. Exp. $50,000 $80,000

3 19.20% $48,000

4 11.52% $28,800

5 11.52% $28,800

6 5.76% $14,400

We can can now construct construct our table of incremental cash flows from these these two two alternatives. alternatives. Remember, th in this scenario is the after tax cost of borrowing, borrowin g, or: 10%*(1-40%) = 6%. 0.06 NPV LEASE ANALYSIS OF INCREMENTAL CASH FLOWS Year =

0

1

2

3

Cost of ownership

Purchase cost Loan proceeds After-tax interest payment Principal payment Maintenance cost Tax savings from maintenance cost Tax savings from depreciation Salvage value Net cash flow from ownership PV cost of ownership

($250,000) $250,000 ($15,000) ($53,868) ($20,000) $8,000 $20,000

($11,768) ($59,254) ($20,000) $8,000 $32,000

($8,213) ($65,180) ($20,000) $8,000 $19,200

$0 ($185,323.87)

($60,868)

($51,022)

($66,193)

($70,000) $28,000 ($42,000) ($187,534.44)

($70,000) $28,000 ($42,000)

($70,000) $28,000 ($42,000)

($70,000) $28,000 ($42,000)

Cost of lea leasing sing

Lease payment Tax savings from lease payment Net cash flow from leasing PV cost of leasing Cost Comp Comp arison

PV ownership cost @ 6% PV of leasing @ 6% Net Advantage to Leasing

($185,324) ($187,534) ($2,211)

What is your suggestion, owning or leasing? Owning since NAL is negative





A

B

C

D

E

F

G

98 b. The salv salvage age val value ue is is clear clearly ly the most most unce uncertai rtain n cash cash flow flow in the analy analysis. sis. As Assume sume that the appropr approprii 99 discount rate is 15 percent. What would woul d be the effect of a salvage value risk adjustmen adjustmentt on the decisio 100 101 salvage value. Our new array of cash flows 102 All cash flows would remain unchanged except that of the salvage 103 following: 104 105 Standard discount rate 10% 0.06 106 Salvage value rate 15% 0.09 Operating c 107 108 Year = 0 1 2 3 4 109 Net cash flow $0 ($60,868) ($51,022) ($66,193) ($76,480) 110 PV of net cash flows $0 ($57,422) ($45,410) ($55,577) ($60,579) 111 112 NPV of ownership ($188,880) 113 is o n 114 New C o s t C o m p a r is 115 PV ownership cost @ 6% ($188,880) 116 PV of leasing @ 6% ($187,534) 117 Net Advantage to Leasing $1,345 118 119 Now what do you say? 120 Buying since NAL is positive 121 122 that the after-tax after-tax cost of debt should be used to discount all anticipated anticipated cash cash flows, at 123 c. Assuming that 124 firm be indifferent to either leasing or buying? 125 the Goal Goal Seek Seek function function to determ determine ine the the lease lease payment payment that that make makes s the Net Adva Advantage ntage to Lea 126 Hint: Use the 127 128 Crossover = $70,502 Used what if analysis - set C117, to value 0, by changing 129 130 131 132 133 134 135





H

I

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47

as decided to install install a new s found to be 20% versus the

ed could be borrowed from each year. year. In the event event that year paid at the end of each %.

elivery and installatio installation n (at t=0) that there are 5 lease n expected life of eight years, equal its book value of asing or owning the proposed

so using the







H I 53 00 (with annual payments of 54 t analyze the incremental cash 55 56





H

I

57 his new equipment. 58 59 60 61 62 63 64 65 t the appropriate discount rate 66 67 68 69 70 4 71 72 73 74 ($4,301.87) 75 ($71,698) 76 ($20,000) $8,000 77 78 $11,520 $42,500 79 80 ($33,980) 81 82 83 84 ($70,000) $28,000 85 86 ($42,000) 87 88 89 90 91 92





H

I

98 ate salvage value pre-tax 99 n? 100 101 102 ould resemble the 103 104 105 106 107 Salvage value cash 108 4 109 $42,500 110 $30,108 111 112 113 114 115 116 117 118 119 120 121 122 123 hat lease payment would the 124 125 126 ing zero. 127 128 36 129 130 131 132 133





loan amount term in mos interest rate payment

100,000 180 7.0%

#######

Excel 5.19-6 Due Apr 24 - Rusincovitch

Recommend Documents