Problem 8-10 Complete the steps below using cell references to give data or previous calculations. In some cases, a simple cell reference is all you need. To copy paste a formulacrou a row or down a colume, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. De not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section You are a manager at Percolated Fiber, which is considering expanding its operations in synthetic fiber manufacturing. Your boss comes into your office, drops a consultant's report on your desk, and complains, "We owe these consultants million for this report, and I am not sure their analysis makes sense. Before we spend the 525 million on new equipment needed for this project, look it over and give me your opinion." You open the report and find the following estimates (in thousands of 10 12 33 Project Year 1 2 9 10 Sales res 50.000 530,000 50.000 Cost of good sold S18.000 S18.000 SIN,000 S18.000 - Cross profit S12.000 $12,000 $12.000 $12,000 General, sales, and administrative expenses 52.000 $2.000 $2,000 - Depreciation $2.500 $2.500 -Net operating income 57.000 57.500 $7.500 57.00 -Income tax 52.624 52.625 Nel come SUS SUS SUNT 54,875 All of the estimates in the report sem correct. You note that the consultants ved straightline depreciation for the new equipment that will be purchased today yar ) which is what the accounting department recommended. The report concludes that become the project will increase earnings by 51.875 million per year for 10 years, the pot is worth $48.75 million. You think back to your halcyon days in finance can and realise there is more work to be done! First, you note that the consultants have not actored in that the project will require 510 million in working capitalupfront year, which will be fully recovered in your 10. Next, you see they have attributed 52 million of administrative expenses to the project, but you know that 1 million of this amount is overhead that will be incurred even if the project is not spod hally, you know that accounting camnings are not the right thing to focus on! information, what are the free cash flows in years through 10 that should be wed to evaluate the proposed project 15 a. Given the available information, what are the free cash flows in years through 10 that should be used to evaluate the proposed project! b. If the cost of capital for this project is 14%, what is your estimate of the value of the new project? 35% Tax rate a. Given the available information, what are the free cash flows in years through 10 that should be used to evaluate the proposed project Year 6 Year 5 Year Year 4 Year Year 1 Year 2 Year 0 525.000 S1000 530,000 SIN 000 530.000 SIX 000 Cost of machine Change in net working capital Sales revenue Minas con of goods sold Tagual gross profit Minus General, sales and administrative $30,000 $18,000 $30,000 SI8,000 530,000 SIN.000 530,000 SIN.000 530,000 $18,000 S2,000 $2,000 $2,000 S2,000 $2.000 52.000 52.000 2 $1.000 S1,000 $2,500 $1,000 $2.500 $1.000 $2.500 $1,000 $2.500 S1.000 $2.500 $1.000 $2,500 $2.500 35 Plus overhead that would have occurred anyway Mis depreciation Equals et operating income Minus income tax Equals Netcome Plus depreciation Cost of machine plus change in not working 31 S. 53.500 $2.500 SO $2.500 $2.500 52.00 so V so SO SO SO 50 11 Equal cash flow h. If the cost of capital for this project is 14%, what is your estimate of the vahe of the new project? Cast of capital 1496 NPV 06 4 wwwwwwww late the root for varl () 1 -3 55 56 57 Requirements 1. Start Excel - completed 2. In cell E29, by using cell references, calculate the gross profit for year 1 (1 pr.). 2 3. To calculate the gross profit for years 2 through 10, copy cell E29 and paste it onto cells F29:N29 (1 pt.). 4. In cell E35, by using cell references, calculate the net operating income for year 1 (1 pt.). 5. To calculate the net operating income for years 2 through 10, copy cell E35 and paste it onto cells F35:N35 (1 pt.). 6. In cell E36, by using cell references, calculate the income tax for year 1 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. 7. To calculate the income tax for years 2 through 10, copy cell E36 and paste it onto cells F36:N36 (1 pt.). 8. In cell E37, by using cell references, calculate the net income for year 1 (1 pt.). 9. To calculate the net income for years 2 through 10, copy cell E37 and paste it onto cells F37:N37(pt.). 10. In cell D40, by using cell references, calculate the sum of the cost of the machine and the change in the net working capital (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a negative number. 11. In cell D41, by using cell references, calculate the cash flow in year 0 (1 pt.). 12. To calculate the cash flows for years through 10, copy cell 041 and paste it onto cells E41:N41 (1 pr.). 13. In cell 046, by using cell references and the function NPV, calculate the net present value of this project (1 pt.). 65 14. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. 58 59 60 61 62 63 64 66 67 68 70