AutoSave om Excel L e Search 2- Lopez, Teman Problem 12-34. Start sert Page Layout Formulas Data Review View Help HAA = post BIU A - C Cut Copy - Conditional Formatas The Shome Corporation is considerint a new project that evolves the introduction of sew product. The firm in the 34 percen t ax bracket and has 15 percent required rate of return or discount rate for new investments. This project is expected to last five years, and then because this is somewhat of a fad project, it will be terminated. Given the following information determine the net cash flows associated with the project and the project's NPV, prob index and internal rate of return. Apply the appropriate decision criteria Cost of new plant and equipment 16.900.000 Shipping and installation costs $100000 Sales price per unit $250 unit in Years 1 $200 unit in Years Vale cost per unit: Suomi Annual fixed costs: $300.000 Working capital requirements. There will be awal working capital regement of $100.000 store production started. For each year, the total investi networking capital will be equal to 10 percent of the dollar value of sales for that year. Thus, the investment in working capital will increased Years through then decrease in Year 4 Finally all working capital is liquidated at the termination of the project at the end of Year 5. The depreciation method Use the simplified t he line method over five years. It is assumed that the plant and equipment we have ne salvare vale after five Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formulacross a row or down a columnas absolute cel reference or a mixed cell reference may be preferred a specific Excel tronction is to be used the directions will specify the use of that function. Do not type umerical data inte a celor function. Instead, make reference to the cell in which the data is found. Make your computatis only is the green cells highlighted below. Ta all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, ly the Give Data section Trial capital expenditure Shipping and installation costs Life of the initial expenditure Marginal tax rate (S100.000,00 $100,000.00 Networking capital of Sales) Networking capital wovestment (0) Faced costs per year Sales price (1-6 Sales price (5) Variable cost of product Year o Year 2 100,000 0 Years 120,000 $30,000,000.00 320,000,000, 00 70.000 $17.500,000.00 $14,000,000.00 Lopez Terman Problem 13-34. Start - Excel Data Review View Help Insert Page Layout Formulas Soutely23. BUA Fixed costs per year Sales price (1-4) Salesice (5) Vale cost of product (5300,000.00) $250.00 5300.00 (513000 Year o Year 2 Us Reves Vacats Fard costs Gross profit Depreciation Netopearing became Net income Cash flow Year! Year Year 4 Years 80.000 100.000 120.000 70,000 70,000 520000.000,00 $25.000.000.00 $30,000,000.00 $17.500,000.00 $14,000,000.00 (510.400.000,00) 513000.000,00) 515.000.000.0) (59.100.000,00) (9.100.000,00) (5300,000.00) (5300,000.00) (5.500.000,00) (5.300.000,00) 5.300.000,00) 59,800,000.00 $11.700,000.00 $14,100,000.00 $8,100,000.00 $4,600,000.00 ($1.400.000,00) ($1.400.000,00) ($1.400,000.00) (51.400,000.00) (1,400,000.00) $7.900,000.00 $10,300,000.00 $12.700.000,00 6,700.000,00 1.200,000.00 53.636.000.0) (0 SO.000.0) (543.000,00 (52 278.000,00) 51 .000.00) S5214.000 $6,795,000.00 1,000.00 $4,422,000.00 $2,112,000.00 S6,614,000.00 $8,198,000.00 $9,782,000.00 55,822.0 S10DLOON Cal expedie Free cash flow espected NOV IRR Requirements sces 30-30 do before In cell 30, by w cell reference determine the new project's additional networking capital for Year Inc. 30, by cercet a t ca for Year In cel F30, by using relative and about cell references, calculate the new project's d FM across columns GH eals working capital for Your 2 copy the form from cell 1224 L e Search 82 - Lopez, Teman_Problem, 12-24_Start Excel Home Insert Page Layout Formulas Data Review View Help 6 - AA 2 Copy - A- 3 Format Painter BIU-18- Merge Center shquze7y2 fil dig S 9 Conditional Format Requirements 1 Start Excel In ces D30130 do the fobowing A negative In cell D30, by using a cell reference, determine the new project values t working capital for Year , expected In cell E30, by using cell references, calculate the new projects additional networking capital for Year 1. In cel F30, by using relative and absolute cell references, calculate the new project's additional networking capital for Year 2. Copy the form F30 Cross Columns GH from cell In cell 130, by using cell references, calcolate the new project's additional networking capital for Years Note: The outputs of the expression or function you typed in cells 30-GO are expected as negative numbers (4 p.) In cell 31, by using cell references, caldate the capital expenditure associated with the new project for Year Note: The output of the expression or function you typed in this cell is expected as a nettive amber (1 p.) In cells 35-133 do the following In cell 33, by using cel references, calculate the free cash flow associated with the new project for Year 0. from cell 33 across In cel 33 by using cell references, calculate the free cash flow associated with the new project for Year 1. Copy the form columes F-(pl.) In cel by using cell references and the Excel NPV function calculate the projects we present value Note: Ester the appropriate array into the Value wament of the Excel NPV function (pt.) In cel 36, by using cell references and the Excel NPV function calculate the project's profitabindex Note: Ester the appropriate way into the Valow me of the Excel NPV fiction (1 pr.) la cel 37 by interferences and the Excel IRR fiction calculate the project's mal rate of return Note: Do not enter any value for the Guess argument of the Excel IRR function (pl.) 8 Save the workbook Close the workbook and then exit Excel Sabthe workbook as directed