Please show formula values..

Garca and Martinez manufacture widgets and currently have $16 million in taxable income. The company is considering an expansion, and they've asked you to evaluate the project. The expansion requires the firm to produce 70,000 widgets a year for 6 years, and the company estimates they can sell them for $30 per widget. Garca and Martinez estimate they will need an additional $5,000,000 worth of machinery. The machinery costs $150,000 a year to operate and maintain. The machinery's depreciable life is 7-years, and the company expects to salvage the machinery for $160,000 at the end of year 6. If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the project's life. Similarly, the company will immediately add $50,000 to their cash balance and maintain that higher cash balance over the project's life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 14%. Calculate the project's NPV by linking to the information/variable values in Column K. 5. Evaluate the project. a. Calculate the cash flow from assets in Row 13. (2 points) b. Using relative and/or absolute cell references, use the PV function to calculate the present value of CFFA for each year in Row 15. If done correctly, I will be able to copy cell B15 and paste it into cells C15:H15 and those cells will display the correct present value. (3 points) c. Use the NPV function in D18 to calculate the net present value of the project. If done correctly, it will give the same value as summing cells B15 to H15. (3 points) d. Use the IRR function in C18 to calculate the internal rate of return on the project. (3 points) e. In E19, use an IF statement to return the text "Accept" or "Reject" depending on the NPV calculation. (2 points) | C | 5 | 6 | E Year 0 year1 Year 1 Year 2 year 2 year3 Year 3 Year 4 Year 5 Year 6 Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCF Change NWC Net Capital Spending CFFA Information/Variables $25,000,000.00 Company's Other Taxable income $30.00 Price per Unit 7 Years Depreciable Life (machinery) 70,000.00 Units Sold $500,000.00 Inventory 14.00% Cost of Capital $6.00 Marginal Cost (per unit) $5,000,000.00 Machinery $150,000.00 Machinery maintenance costs $50,000.00 Increase in Cash Balance 6 Years Project Life (years) $160,000.00 Salvage Value 35.00% Marginal Tax Rate PV (CFFA) IRR NPV Decision Units Sold 50000 55000 60000 65000 70000 75000 80000 85000 90000 Taxable Income $ 50,000 50,000.00 $ 75,000 75,000.00 $ 100,000 100,000.00 $ 335,000 335,000.00 $ 10,000,000 10,000,000.00 $ 15,000,000 15,000,000.00 $ 18,333,333 18,333,333.00 Above $18,333,333 Tax Rate 15% 25% 34% 39% 34% 35% 38% 35% Note: Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the VLOOKUP/HLOOKUP function. In addition, personal taxes are still based on similar tables, as are state corporate taxes. Garca and Martinez manufacture widgets and currently have $16 million in taxable income. The company is considering an expansion, and they've asked you to evaluate the project. The expansion requires the firm to produce 70,000 widgets a year for 6 years, and the company estimates they can sell them for $30 per widget. Garca and Martinez estimate they will need an additional $5,000,000 worth of machinery. The machinery costs $150,000 a year to operate and maintain. The machinery's depreciable life is 7-years, and the company expects to salvage the machinery for $160,000 at the end of year 6. If the project is accepted, the company will immediately increase inventory by $500,000 and maintain the new inventory level over the project's life. Similarly, the company will immediately add $50,000 to their cash balance and maintain that higher cash balance over the project's life. The investments in cash and inventory will be recovered when the project is completed. The marginal cost of producing a widget is $6.00 and the cost of capital is 14%. Calculate the project's NPV by linking to the information/variable values in Column K. 5. Evaluate the project. a. Calculate the cash flow from assets in Row 13. (2 points) b. Using relative and/or absolute cell references, use the PV function to calculate the present value of CFFA for each year in Row 15. If done correctly, I will be able to copy cell B15 and paste it into cells C15:H15 and those cells will display the correct present value. (3 points) c. Use the NPV function in D18 to calculate the net present value of the project. If done correctly, it will give the same value as summing cells B15 to H15. (3 points) d. Use the IRR function in C18 to calculate the internal rate of return on the project. (3 points) e. In E19, use an IF statement to return the text "Accept" or "Reject" depending on the NPV calculation. (2 points) | C | 5 | 6 | E Year 0 year1 Year 1 Year 2 year 2 year3 Year 3 Year 4 Year 5 Year 6 Revenues Variable Costs Fixed Costs/Expenses EBITDA Depreciation EBIT Taxes OCF Change NWC Net Capital Spending CFFA Information/Variables $25,000,000.00 Company's Other Taxable income $30.00 Price per Unit 7 Years Depreciable Life (machinery) 70,000.00 Units Sold $500,000.00 Inventory 14.00% Cost of Capital $6.00 Marginal Cost (per unit) $5,000,000.00 Machinery $150,000.00 Machinery maintenance costs $50,000.00 Increase in Cash Balance 6 Years Project Life (years) $160,000.00 Salvage Value 35.00% Marginal Tax Rate PV (CFFA) IRR NPV Decision Units Sold 50000 55000 60000 65000 70000 75000 80000 85000 90000 Taxable Income $ 50,000 50,000.00 $ 75,000 75,000.00 $ 100,000 100,000.00 $ 335,000 335,000.00 $ 10,000,000 10,000,000.00 $ 15,000,000 15,000,000.00 $ 18,333,333 18,333,333.00 Above $18,333,333 Tax Rate 15% 25% 34% 39% 34% 35% 38% 35% Note: Corporate taxes are now flat at 21%. I have kept the old corporate tax table in this example to illustrate the VLOOKUP/HLOOKUP function. In addition, personal taxes are still based on similar tables, as are state corporate taxes