Because East Coast Yachts is producing at full capacity, Larissa has decided to have Dan examine the feasibility of a new manufacturing plant. This expansion would represent a major capital outlay for the company. A preliminary analysis of the project has been conducted at a cost of $1.2 million. This analysis determined that the new plant will require an immediate outlay of $55 million and an additional outlay of $30 million in one year. The company has received a special tax dispensation that will allow the building and equipment to be depreciated on a 20-year MACRS schedule. Because of the time necessary to build the new plant, no sales will be possible for the next year. Two years from now, the company will have partial-year sales of $18 million. Sales in the following four years will be $27 million, $35 million, $39 million, and $43 million. Because the new plant will be more efficient than East Coast Yachtss current manufacturing facilities, variable costs are expected to be 60 percent of sales, and fixed costs will be $3.5 million per year. The new plant will also require net working capital amounting to 8 percent of sales for the next year. Dan realizes that sales from the new plant will continue into the indefinite future. Because of this, he believes the cash flows after Year 5 will continue to grow at 3 percent indefinitely. The companys tax rate is 21 percent and the required return is 11 percent. Larissa would like Dan to analyze the financial viability of the new plant and calculate the preceding blue cells in order to determine the profitability index, NPV, and IRR. Also, Larissa has instructed Dan to disregard the value of the land that the new plant will require. East Coast Yachts already owns it, and, as a practical matter, it will go unused indefinitely. She has asked Dan to discuss this issue in his report.

Please Attach Excel File with Formulas

Excel File Edit View Insert Format Tools Data Window Help Q 8 Mon Oct 25 11:57 AM AutoSave OFF Chapter 8 Case MBA 661 AA 20. Page Layout Formulas Home Insert Draw 4 Share D Comments Conditional Formatting Arial 2. Insert Data Review View Tell me = Custom ===y $ % ) X [C ' O Paste FO 10 ~ A OA A O V Y DX Delete v B I U Uv 588 e v Format as Table Cell Styles Sensitivity Format v Sort & Filter Find & Select Analyze Data G3 fx 7 A B F G G H JK L M N 0 o P P R 1 4 Chapter 8 Expansion at East Coast Yachts 2 3 4 Inpur area: 5 $ $ 7 8 9 10 11 12 13 14 15 16 17 18 19 Initial equipment cost Equipment in 1 year Year 1 depreciation Year 2 depreciation Year 3 depreciation Year 4 depreciation Year 5 depreciation 55,000,0DD 30,000,000 3.750% 7.219% 6.677% 6.177% 5.713% Year 3 27,000,000 $ Year 4 35,000,000 S $ Year 5 39,000,000 $ Year 6 43,000,000 $ Sales Variable cost Fixed cost NWC percentage of sales Terminal growth rate Tax rate Required return Year 2 18,000,000 $ 60% 3,500,000 8% 3% 2196 11.000% 21 22 23 24 25 Output area: Provide answers in blue cells 27 Year Year 1 Yeni 2 Year 3 YA 4 Year 5 Depreciation Year O investment Year 1 investment Total depreciation This ruw reflects cepreciation over the five years on the 355M ouilsy at time This row reflects deprecat on over the five years on the $30Moulay at time- 31 32 33 - -- W--- w.. ---- Cases Chapter 8 Case #1 + Ready E + 90% CCT 25 W tv A P 1 0 TEL Q 8 Mon Oct 25 11:58 AM Excel File Edit View Insert Format Tools Data Window Help AutoSaveFF A A O ... PO Chapter 8 Case MBA 661 Home Insert Draw Page Layout Formulas Data Review View Tell me X 2 ~ A Arial 10 Conditional Formatting Custom O LE ===y Format as Table Paste B I Uv OA, $ $ % 68 - 1 Cell Styles 4 Share D Comments Insert 7 O FO V Y X Dalate v v Sensitivity Format v Sort & Filter Find & Select Analyze Data G3 X fx A B C D E F G H L M 0 F R S U 24 25 Output area: Provide answers in blue cells Year Year 1 Year 2 Year 3 Year 4 Years 27 28 29 20 31 Depreciation Year investment Year 1 investment Total depreciation This row relacion certa ters on the $15 tatt mer Thiarow ridicarea on the 901 ayatime-1. 33 11 95 Year Year 1 Year 2 Year 3 Yeard Years Takanan 16 0 sale 32 Taken from 31 rom 19 : 40 41 42 Revenue Variable costs Fixed costs Depreciation Tax Net income OCF 43 41 45 New working capital Beginning Ending NWC cash flow Nobelow F46 ia ceained and proved to the right 145 Nole how E46 is dones and proceed to the right to 145 Nobelow E47 is cobard and light a HS 42 43 49 S Year 5 cash flow Value at end of year 5 of perpetual CF 61 Year Year 1 Year 2 Year? Yeard Year Taken from line 42 S (55,000,000) 130.000.000) Operating cash flow Capital spending Networking capital Terminal value Total cash flows Taban from line 47 Taken from DOO G3 51 55 08 57 GB 50 ee 61 62 NPV Profitability index IRR Cases Chapter 8 Case #1 + Ready E + 80% CCT 25 W itv 4 0 TEL