Please show formulas on how to get answers! I appreciate it

Problem 9-31 Complete the steps below using cell references to givez dala or previous calculations. In some cases, a simple cell reference is all you need. To copypaste a formale across a row ar down a com, an absolute cell reference ar a mixed cell reference may be preferred. If a specific Excel fwiction is to be used the directions will specify the use of that function. Do 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 formules, usually ike Given Dala section. You are a manager at Percolated Fiber, which is considering expanding is operations in synthetic liber manufacturing. Your boss comes into your office, drups a consultani's report on your desk, and complains, "We owe these consultants S1 million for this report, and I am not sure their analysis makes sense. Before we spend the $25 million on new equipmical aceded for this projet, look at over and give me your opinion." You opca the report and lind the following climals (in thousands of dollars) O 9 10 $2.000 Project Year 1 2 Sales Revenue $30.000 8.30,000 $30.000 8.30,000 - Cost of Goods Sold 518,000 S18.000 518,000 S18.000 - Gross Profit $12.000 $12,000 $12,000 $12,000 - Selling, General, and Administrative Expenses $2.000 $ $2,000 $2.000 $ - Depreciation 52,500 52,500 $2,500 52.500 = ERIT $7,500 $7,500 $7,500 $7,500 - Income Tax $1.575 $1,575 $1.575 $1,575 Net income $5.925 $5,925 $5,925 $5.925 All of the estimates in the report sem correct. You note that the consultants used straight-line depreciation for the new equipment that will be purchased today (ycar 0), which is what the counting department recommended. They also calculated the depreciation assuming no salvage value for the cquipmcat, which is the company's 8:35umption in this CASC. The report concludes that because the project will increase earnings by $5.925 million per year for 10 years, the project is worth $59.25 million. You think back to your glory days in finance class and realize there is more work to be done! First, you note that the consultants have not included the fact thint the project will require $10 million in working capital op front (year (), which will be fully recovered in year 10. Next, you see they have attributed $2 million of selling general, and administrative expenses to the project, hat you know that I million of this amount is overhead that will he incurred even if the project is not accepted. Finally, you know that accounting earnings are not the right thing to focus on! 1. Given the available information, what are the free cash flows in years through 10 that should he 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? Tax Rate 21% u. Given the available information, what are the free cash flows in years 0 through 10 that should be used to evaluate the proposed project? Year 1 Year 2 Year 3 Year 4 Yeurs Year 6 Year 7 Year 8 Year 9 Year 10 Year 0 -525.000 -510.000 Cost of Machine Change in Net Working Capital Sales Revenue Minus Cost of Goods Sold Equals Gross Prolit S30.000 $18.000 530,000 $18,000 530.000 $18.000 S30,000 S18.000 530,000 $18,000 530.000 518,000 S30,000 $18,000 $ $30,000 $18,000 530,000 $18.000 S10.000 $30.000 $18.000 Tax Rate 21% 1. Given the available information, what are the free cash flows in years 0 through 10 that should be used to evaluate the proposed project? Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year? Year Year 9 Year 10 Year 0 -$25.000 -810,000 Cost of Machine Change in Net Working Capital Sales Revenge Minus Cost of Goods Sold quals Gross Profit Minus Selling, General, and Administrative Expenses $.30,000 SI5.000 $30,000 518,000 8.10,000 $18,000 $30,000 $18.000 $30,000 518,000 8.30.000 $18,000 8.30.000 $18.000 $ $30,000 510.000 S50,000 S18,000 $10,000 8.30.000 $18.000 S2,000 $2,000 52,000 S2,000 $2,000 52,000 $2,000 S2,UOU 52,000 52,000 $1,000 S2,500 $1,000 $2,500 $1,000 $2,500 $1.000 $2,500 S1,000 $2,500 $1,000 $2.500 $1.000 $2,500 $1,000 $2,500 $1,000 52,500 $1.000 $2,500 Plus Overhead that would have occurred anyway Minus Depreciation Equals Net Operating Income Minus Income Tax Equals Net Income Plus Depreciation Cost of Mechinc plus Change in Ne Working Capital Equals Cash Flow $2.500 $2,500 $2.500 $2,500 $2.500 $2.500 $2.500 $2.500 $2.500 $2.500 SO SO $0 $ SO SO $ $0 SO SO $0 $10,000 b. If the cost of capital for this project is 14%, what is your estimate of the value of the new project? 14% Cost of Capital NPV 6 Requirements 1 In all E29, by using all references, calculate the gross prolil for yea 1 (1 pl.). 2 To calculate the gross profit for years 2 through 10, copy cell E29 and paste it onto cells F29:N29 (1 pr.). 3 In coll 35, by using coll references, calculate the net operating income for year 1 (1 pt.). 4 To calculate the net operating income for you through 10, copy cell E35 and paste it onto cells F35:N35 (1 pr.) $ In cell 36, by using coll references, calculate the income tax for year 1 (I pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number To calculate the income tax for year: 2 through copy cell 1:36 and paste it onto cells F36:N36 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. In cell F37, by using cell references, calculate the net income for year 1 (1 pt.). 8 To calculate the nel income for years 2 through 10. copy od E37 and paste il onlu olls F37:N37 (I pl.). In cell 1940, by using cell references, calculate the sum of the cost of the machine and the change in the networking capital (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a negative number. 10 In cell 041, by using cell references, calculate the cash flow in year (1 pt.) 11 To calculate the cash flows for years through 10. copy cell D41 and paste it onto cells E41:N41 (1 p.) 12 In all D46, by using cl rclerces and the function NPV, calculate the nel present value of this projat (1 pl.). 9 Problem 9-31 Complete the steps below using cell references to givez dala or previous calculations. In some cases, a simple cell reference is all you need. To copypaste a formale across a row ar down a com, an absolute cell reference ar a mixed cell reference may be preferred. If a specific Excel fwiction is to be used the directions will specify the use of that function. Do 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 formules, usually ike Given Dala section. You are a manager at Percolated Fiber, which is considering expanding is operations in synthetic liber manufacturing. Your boss comes into your office, drups a consultani's report on your desk, and complains, "We owe these consultants S1 million for this report, and I am not sure their analysis makes sense. Before we spend the $25 million on new equipmical aceded for this projet, look at over and give me your opinion." You opca the report and lind the following climals (in thousands of dollars) O 9 10 $2.000 Project Year 1 2 Sales Revenue $30.000 8.30,000 $30.000 8.30,000 - Cost of Goods Sold 518,000 S18.000 518,000 S18.000 - Gross Profit $12.000 $12,000 $12,000 $12,000 - Selling, General, and Administrative Expenses $2.000 $ $2,000 $2.000 $ - Depreciation 52,500 52,500 $2,500 52.500 = ERIT $7,500 $7,500 $7,500 $7,500 - Income Tax $1.575 $1,575 $1.575 $1,575 Net income $5.925 $5,925 $5,925 $5.925 All of the estimates in the report sem correct. You note that the consultants used straight-line depreciation for the new equipment that will be purchased today (ycar 0), which is what the counting department recommended. They also calculated the depreciation assuming no salvage value for the cquipmcat, which is the company's 8:35umption in this CASC. The report concludes that because the project will increase earnings by $5.925 million per year for 10 years, the project is worth $59.25 million. You think back to your glory days in finance class and realize there is more work to be done! First, you note that the consultants have not included the fact thint the project will require $10 million in working capital op front (year (), which will be fully recovered in year 10. Next, you see they have attributed $2 million of selling general, and administrative expenses to the project, hat you know that I million of this amount is overhead that will he incurred even if the project is not accepted. Finally, you know that accounting earnings are not the right thing to focus on! 1. Given the available information, what are the free cash flows in years through 10 that should he 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? Tax Rate 21% u. Given the available information, what are the free cash flows in years 0 through 10 that should be used to evaluate the proposed project? Year 1 Year 2 Year 3 Year 4 Yeurs Year 6 Year 7 Year 8 Year 9 Year 10 Year 0 -525.000 -510.000 Cost of Machine Change in Net Working Capital Sales Revenue Minus Cost of Goods Sold Equals Gross Prolit S30.000 $18.000 530,000 $18,000 530.000 $18.000 S30,000 S18.000 530,000 $18,000 530.000 518,000 S30,000 $18,000 $ $30,000 $18,000 530,000 $18.000 S10.000 $30.000 $18.000 Tax Rate 21% 1. Given the available information, what are the free cash flows in years 0 through 10 that should be used to evaluate the proposed project? Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 Year? Year Year 9 Year 10 Year 0 -$25.000 -810,000 Cost of Machine Change in Net Working Capital Sales Revenge Minus Cost of Goods Sold quals Gross Profit Minus Selling, General, and Administrative Expenses $.30,000 SI5.000 $30,000 518,000 8.10,000 $18,000 $30,000 $18.000 $30,000 518,000 8.30.000 $18,000 8.30.000 $18.000 $ $30,000 510.000 S50,000 S18,000 $10,000 8.30.000 $18.000 S2,000 $2,000 52,000 S2,000 $2,000 52,000 $2,000 S2,UOU 52,000 52,000 $1,000 S2,500 $1,000 $2,500 $1,000 $2,500 $1.000 $2,500 S1,000 $2,500 $1,000 $2.500 $1.000 $2,500 $1,000 $2,500 $1,000 52,500 $1.000 $2,500 Plus Overhead that would have occurred anyway Minus Depreciation Equals Net Operating Income Minus Income Tax Equals Net Income Plus Depreciation Cost of Mechinc plus Change in Ne Working Capital Equals Cash Flow $2.500 $2,500 $2.500 $2,500 $2.500 $2.500 $2.500 $2.500 $2.500 $2.500 SO SO $0 $ SO SO $ $0 SO SO $0 $10,000 b. If the cost of capital for this project is 14%, what is your estimate of the value of the new project? 14% Cost of Capital NPV 6 Requirements 1 In all E29, by using all references, calculate the gross prolil for yea 1 (1 pl.). 2 To calculate the gross profit for years 2 through 10, copy cell E29 and paste it onto cells F29:N29 (1 pr.). 3 In coll 35, by using coll references, calculate the net operating income for year 1 (1 pt.). 4 To calculate the net operating income for you through 10, copy cell E35 and paste it onto cells F35:N35 (1 pr.) $ In cell 36, by using coll references, calculate the income tax for year 1 (I pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number To calculate the income tax for year: 2 through copy cell 1:36 and paste it onto cells F36:N36 (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a positive number. In cell F37, by using cell references, calculate the net income for year 1 (1 pt.). 8 To calculate the nel income for years 2 through 10. copy od E37 and paste il onlu olls F37:N37 (I pl.). In cell 1940, by using cell references, calculate the sum of the cost of the machine and the change in the networking capital (1 pt.). Note: The output of the expression or function you typed in this cell is expected as a negative number. 10 In cell 041, by using cell references, calculate the cash flow in year (1 pt.) 11 To calculate the cash flows for years through 10. copy cell D41 and paste it onto cells E41:N41 (1 p.) 12 In all D46, by using cl rclerces and the function NPV, calculate the nel present value of this projat (1 pl.). 9