please help, i need to have cell references

hope this is better!

2 Wrap Text General A A Times New Romar 14 LO I Copy Paste Conditional Format as Cell Insert Delete Forma E E Merge & Center Table Styles A- BIU Formatting- 3Format Painter Cells Number Styles Alignment Font Cipboard Update now (i) UPDATES AVAILABLE Updates for Office are ready to be installed, but first we need to close some apps M20 G A The Shome Corporation is considering a new project that involves the introduction of a new product. The fim in in the 34 percent marginal tax bracket and has a 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, profitability index, and internal rate of retum. Apply the appropriate decision criteria. Cost of new plant and equipment: $6,900,000 Shipping and installation costs Sales price per unit: $250/umit in Years 1-4, $200/unit in Year 5 Variable cost per unit: $130/unit Anmual fixed costs: $300,000 Working capital requirements: There will be an mitial workitg capital requirement of $100,000 jast to get production started. For each year, the total investment in net working capital will be equal to 10 percent of the dollar valae of sales for that year. Thas, the investment in working capital will increase during Years I through 3, then decrease in Year 4. Finally, all working capital is liquidated at the tensination of the project at the end of Year 5 The depreciation method: Use the simplified straight-line method over flve years Ir is assumed that the plant and equipment will have no salvage value after five years S 100,000 Complete the steps below esing cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formala across a row or down a colansn, au absolste cell reference or a aised cell reference may he preferred. If a specific Excel fanction is to be used, the directions will specify the use of that function. De not type is aumerical data into a cell or fenction. Iastead, make a reference te the cell in which the data is found. Make your computations enly in the green cells highlighted below, Io all cases, unless otherwise directed, use the eartiest appearance of the data in your fermalas, usually the Given Data section Gives data: tnitial capital expendinue Shipping and installation costs Life of the initial expendinure Marginal tax rate Discount rate Net working cspital ( of Salesk Net working capital investent (0) Fixed costs per year Sales price (1-) Sales price (5) Variable cost of peodsct ($6,900,000.00) (S100,000.00) 34.00% 15.00% 10 10.00%% (S100,000 00) (5300,000.00) 5250.00 5200.00 (S13000) 12-24 Fa Insert Delete Format Conditional Format as Cell Paste Merge & Center $% IU Clear w Formatting Table Styles- Foma Cigboard Alignment Number Font Styles Cells UPDATES AVAILABLE vodates for Office are ready to be installed, but first we need to close some apps Update now fi M20 G Variable cost of product ($130.00) 16 17 Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 Units sold 19 80,000 100,000 120,000 70,000 $17.500,000,00 70,000 $14,000,000.00 (59,100,000.00) (S300,000.00) $4,600,000.00 Revenses $20,000,000.00 $25,000,000.00 S30,000,000.00 Variable costs 21 (s10,400,000,00) (S13,000,000.00) ($9,100,000.00) (S15,600,000.00) Fixed costs 22 ($300,000,00 (S300,000.00) $11,700,000.00 ($1,400,000,00) S10,300,000,00 (S3,502,000.00) $6,798,000.00 S8.198.000.000 (S300,000.00) $14,100,000.00 (S300,000,00) Gross profit Depeeciation Net opearting income Income taxes. 23 S9,300,000,00 S8,100,000,00 ($1,400,000,00) (S1,400,000.00) $12.700,000.00 ($4,318,000.00) (S1,400,000.00) (S1,400,000.00) 25 $7,900,000,00 $6,700,000.00 S3,200,000,00 ($1,088,000.00) $2,112,000.00 ($2.686,000,.00) ($2,278,000.00) $4,422,000,00 Net income Cash flow $5.214.000.00 S8,382,000.00 S9,782,000.00e 56,614,000,00 55.822.000.00 $3.512.000.00 29 Additional net working capital Capital expendinure 31 32 Free cash flow 11 NPV Profitabiliny lndex 11 IRR Requirements 1 Start Excel In cells D30 130 do the following s cell D30, by sing a cell reference, detemine the sew project's addirional set woeking capital for Year 0 ln cell E30, by using cell references, calculate the sew project's aditional set working captal for Year 1 2 tn cell 12-24 ieve andahete.cell.ef es.calulate.the ne neoiects additional.net.working.comnital.foe Vear Coms sla fiom.cell Eto d Times New Romar A A Wrap Text General 14 Conditional Format as Cell Formatting Table Styles- Paste A EMerge & Center $ BIU Format Painter Clipboard Alignment Font Number Styles UPDATES AVAILABLE Updates for Office are ready to be installed, but first we need to dose some apps. Update now M20 A B C In cells D30-130 do the following: In cell D30, by using a cell reference, determine the new project's additional net working capital for Year 0. In cell E30, by using cell references, calculate the new project's additional I net working capital for Year 1. 2 In cell F30, by using relative and absolute cell references, calculate the new project's additional working capital for Year 2. Copy the formula from c net across columns G- H. In cell 130, by using cell references, calculate the new projects additional net working capital for Year 5. Note: The outputs of the expression or function you typed in cells D30 G30 are expected as negative numbers. (4 pt.) In cell D31, by using cell references, calculate the capital expenditure associated with the new project for Year 0. Note: The output of the expression or function you typed in this cell is expected as a negative number. (1 pt.) 43 In cells D33-133 do the following In cell D33, by using cell references, calculate the free cash flow associated with the new project for Year 0 4 In cell E33, by using cell references, calculate the free cash flow associated with the new project for Year 1. Copy the formula from cell E33 across columa L (1 pt.) In cell D35, by using cell references and the Excel NPV function, calculate the project's net present valae. Nete: Enter the appropriate aray into the Valuel argument of the E In cell D36, by using cell references and the Excel NPV fanction, calculate the project's profitability index NPV fanction. (1 pt.) Note: Enter the appropriate aray into the Valuel argument of the Excel NPV fanction. (1 pt.) In cell D37, by using cell references and the Excel IRR fnction, calculate the project's internal rate of return Note: Do not enter any valae for the Guess agument of the Excel IRR fnction (1 pt.) 12-24 Ili A A 1Wrap Text Times New Romar 14 General Insert Delete Format Conditional Format as Cell # Merge &Center Paste fom A. $% U Formatting Table Styles Olighow Nignment Number Styles Cells Fort UPDATES AVAILABLE Updates for Office are ready to be installed, but first we need to dose some apps Update now fr M20 X C D E G In cells D33-133 do the following In cell D33, by using cell references, cakculate the free cash flow associated with the new project for Year 0 In cell E33, by using cell references, calculate the free cash flow associated with the new project for Year 1. Copy the formula from cell E33 across columas F I (1 pt.) In cell D35, by using cell references and the Excel NPV faction, calculate the project's net present valuc Note: Enter the appropriate aray into the Valael argument of the Excel NPV fnction (1 pt.) In cell D36, by sing cell references and the Excel NPV nrion calculate the peoject's profitability indes Note: Enter the appropriate amay into the Valuel arument of the Excel NPV fnction (1 pt) In cell D37, by using cell references and the FExcel IRR fanction calculane the projecfs intersal rate of returs Note: Do not enter any value for the Guess aument of the Ecel IRR fcticn (1 pt.) 8 Save the workbook Close the workbook and them exit Escel Sabmit the urkbook as directed 54 54 61 12-24 e AY C Problem 12.24 The Shome Corporation is considering a new project that involves the introduction of a new product. The firm in in the 34 percent marginal tax bracket and has a 15 percent required rate of returm 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, profitability index, and intenal rate of rehrm. Apply the appropriate decision criteria Cost of new plant and equipment: $6.900,000 Shipping and installation costs Sales price per unit: $250umit in Years 1-4, S200unit in Year 5 Variable cost per unit: $130mit Amual foed costs $300 000 Working capital requirements There will be an initial workint capital requirement of S100,000 just to get production started. For cach year, the total investment in net working capital will be equal to 10 percent of the dollar valae of sales for that vear Thas, the investment in working capital will increase during Yecars 1 through 3, then decrcase in Year 4. Finally, all working capital is liqidated at the teicion of the peoject at the end of Year 5 The depreciation metbod: Use the simplified straight-line tethod ever Sve yor t is asmed that the plt and equipment will have no salvage value after five years S 100,000 Complete the steps belaw asing cell references to given data er peevinas calcalatiens. Is some cases, a simple cell reference is all you need. To copy paste a fermala across a row or dewa a colums, an absalste cela rederence or a mised cell refereace may be preferred. If a specific Excel function is to be ased, the directieas will sperity the ase of that fuactinn. Do aot type in aamerical data late a cell or fanction. Instrad, make a reference te the cell in which the data is fwead. Make your compatations ealy in the green cels highlighted below. Is all cases, anless otherwise directed, use the earliest appearance of the data in your formalas, asaally the Gives Data srctios Gives data: Initial capital expendihure Shipping and installation costs. Lide of the isitial espenir Marinal tas este Discmt rate Net working capital ( of Sales) Net woking capital ivesmnt ( Faod cos pe you Sales price (1-4) Sales Vaidle cost of yoodact (56.900000.00 (S100.000.00 34.00% 15.00% 10.00s% (S100,0.00 (5300,p0 S1300 12 24 Esc Given da Initial capi ure Shipping and installation costs Life of the initial expenditure Marginal tax rate ($6,900,000.00) ($100,000.00) 5 34.00% 15.00% Discount rate Net working capital (% of Sales) Net working capital investment (0) Fixed costs per year Sales price (1-4) Sales price (5) Variable cost of product 10.00% ($100,000,00) 12 ($300,000,00) 13 $250.00 14 $200,00 15 ($130.00) 16 17 Year 0 Year 5 Year 1 Year 2 Year 3 Year 4 18 19 Units sold Revemies Variable costs Fixed costs Gross profit Depreciation Net opearting income 80,000 $20,000,000,00 ($10,400,000.00) (S300,000.00) $9300,000.00 ($1,400,000.00) S7.900.000.00 100,000 $25,000,000.00 (S13,000,000.00) (S300,000.00) $11,700,000.00 (S1,400,000.00) $10 300,000.00 (S3.502.000.00y $6,798,000.00 58,198,000.00 70,000 $17,500,000.00 (S9,100,000.00) (S300,000.00) $8,100,000.00 (S1,400,000.00) 70,000 $14,000,000.00 (S9,100,000.00) (S300,000.00) $4,600,000.00 (S1,400,000.00) $3,200,000.00 (S1,088,000.00) $2,112,000,00 S3,512,000,00 120,000 20 $30,000,000.00 (S15,600,000.00) ($300,000.00) $14,100,000.00 (S1,400,000.00) $12.700,000.00 21 22 23 24 25 $6,700,000.00 ($2,278,000.00) $4.422,000.00 $5,822.000.00 ecome tases Net income 26 ($2,686,000.00) $5,214.000.00 56.614.000.00 (S4.318,000.00) S8.382.000.00 $9,782,000.00 27 20 Cash flow Additional set working capital. Capital expendithare 31 Free cash flow NPV Profitability todex RR Requirements 12-24 e G D C 1 Start Excel. In cells D30-130 do the following: 41 working capital for Year 0. In cell D30, by using a cell reference, determine the new project's additional net In cell E30, by using cell references, calculate the new project's additional net working capital for Year 1 2 In cell F30, by using relative and absolute cell references, calculate the new project's additional net working capital for Year 2. Copy the formula from cell across columns G- H project's additional net working capital for Year 5 In cell 130, by using cell references, calculate the new Note: The outputs of the expression or function you typed in cells D30 G30 are expected as negative numbers. (4 pt.) 42 In cell D31, by using cell references, calculate the capital expenditure associated with the new project for Year 0. 3 Note: The output of the expression or function you typed in this cell is expected as a negative mumber. 4 - (1 pt.) In cells D33-133 do the following In cell D33, by using cell references, calculate the free cash flow associated with the new project for Year 0. In cell E33, by using cell references, calculate the free cash flow associated with the new project for Year 1, Copy the formula from cell E33 across columas F 1 (1 pt.) n cell D35, by using cell references and the Excel NPV function, calculate the project's net present value. Nate: Enter the appropriate array into the Valucl argument of the Excel NPV function. (1 pt.) 45 In cell D36, by using cell references and the Excel NPV function, calculate the project's profitability index. Note: Enter the appropriate array into the Valucl argament of the Excel NPV fianction (1 pt.) n cell D37, by using cell references and the Excel IRR fianction, calculate the project's internal rate of retur Nate: Do tot ester ay valoe for the Guess argument of the Excel IRR function (t pt.) s Save the workbook Close the workbook and then exit Excel. Subenit the workbook as directed 12-24