I am having trouble doing part c. How do you do part c of this assignment?

Excel Project #1: Capital Budgeting sh is ar stu ed d vi y re aC s o ou urc rs e eH w er as o. co m Fama Spaceships Corp. (FSC), in the course of developing a new spaceship, invented a new computer chip that can be used in a variety of communications applications. The research and development costs to make the chip operational was $15,000,000. FSC is considering building a manufacturing plant for these computer chips and selling them to communications businesses (this results in no net change in the sales of spaceships). The manufacturing facility will cost $125,000,000, and will be built this year so that sales of the chips can start in 2018. FSC plans to run the plant for 10 years, and then sell it for 40% of its initial cost. When the plant starts up in 2018, the initial fixed costs to run the facility is $20,000,000 per year. The fixed costs will then increase by 4% per year every year the plant is operational. FSC plans to make 300,000 chips the first year, and sell them for $300 each. FSC anticipates that sales will increase by 10% each year, but that they will have to decrease the price of the chips by 7% each year. The variable costs are estimated to be 40% of the total revenue each year. Net working capital required to support sales is 5% of expected annual revenue. FSC plans to finance the facility in a manner that maintains their current capital structure. There are 25,000,000 shares of common stock outstanding with a current price of $97.68 per share. They currently have 500,000 bonds outstanding with 40 years left to maturity that have a coupon rate of 5.5%, paid annually, and are currently trading at $1,170. To partially fund the project, FSC will have a new 20-year bond issue that will sell at a par value of $1000. The new bonds will earn the same YTM as the old bonds. The remainder of the plant will be financed with equity. Debt and equity will be issued in a proportion that will maintain the current capital structure. The flotation cost of debt for FSC is 2.25% and flotation cost of equity is 4.75%. The riskiness of this project is similar to the overall riskiness of FSC. The project will fall under the MACRS 10-year depreciation. The project will require additional working capital in the amount of 2% of annual sales. The firm's marginal tax rate is 30%. Part A: Estimate the beta of FSC stock using the stock price data given in the FSC spreadsheet, \"Stock and Market Data\" tab (Hint: remember to use stock returns - not prices). Assuming the expected return on the market is 11.5% and the 10-year Treasury rate is the risk-free rate, compute the cost of equity using the CAPM. Compute the cost of debt using Excel's RATE function. Part B: Compute the weights of debt and equity, and the weighted average cost of capital (WACC). Calculate the costs associated with issuing new debt and equity, as well the dollar value of new debt and equity to be issued. Th Part C: Create pro-forma income statements for the new manufacturing plant for the ten years it will be in operation (these should include sales revenue, fixed costs, variable costs, depreciation, EBIT, interest expense, EBT, taxes, and net income). Determine the net working capital required each year (assume this amount is required at the end of the previous year, for example, the NWC to support 2018 sales must be in the NWC account at the end of 2017). Part D: Tabulate the after-tax cash flows (OCF's, NCS, NWC, ATS, FC, etc.) on a yearly basis and compute the NPV, IRR, and MIRR (using the NPV, IRR, and MIRR functions, remember how to properly use the NPV function). Indicate if FSC should accept or reject the project. Part E: What is the minimum price FSC can initially charge (in 2018) and still accept the project? What is the maximum percentage of sales can variable costs be and the project still be accepted? https://www.coursehero.com/file/25407398/Excel-Project-1-Capital-Budgetingpdf/ Powered by TCPDF (www.tcpdf.org)