Need help with this project. please fill out excel sheet. Thanks.

FINC 4352 - Project 4 - Cost of Capital and Capital Budgeting Last Name First Name Part 1: Component Costs A) Cost of Debt rd T After tax cost of debt Cost of Preferred Stock Dps Pps rps Cost of Common Equity CAPM Appraoch: rRF b RPM rs Dividend Growth Approach: D0 g D1 P0 rs Part 2: Capital Structure B) Capital Structure Weights Wd Wsd Wps Ws Weighted Average Cost of Capital WACC F re WACC assuming new stock Part 3: Cash Flow Estimation Input Data Equipment cost Net operating working capital/Sales First year sales (in units) Sales price per unit Variable cost per unit (excl. depr.) Nonvariable costs (excl. depr.) Market value of equipment at Year 4 Tax rate WACC Inflation in prices and costs $10,000,000 10% 1000 $24,000 $17,500 $1,000,000 $500,000 40% 10% 3% C) Sales Revenues Sales Revenues Year Units sold Sales price per unit (excl. depr.) Variable costs per unit (excl. depr.) Nonvariable costs (excl. depr.) Variable costs Sales revenue Net Operating Working Capital 0 1 0 1 0 1 D) Salvage Year Basis for depreciation Annual equipment depr. rate Annual depreciation expense Ending Bk Val: Cost - Accum Deprn Salvage value Profit (or loss) on salvage Tax on profit (or loss) Net cash flow due to salvage E) Cash Flows Years Sales revenue (per 1,000 units) Variable costs (per 1,000 units) Nonvariable operating costs Depreciation (equipment) Oper. income before taxes (EBIT) Taxes on operating income (40%) Net operating profit after taxes Add back depreciation Equipment purchases Cash flow due to change in NOWC Net cash flow due to salvage Net Cash Flow Part 4: Capital Budgeting Analysis F) Capital Budgeting Criterion Net Present Value (at 10%) = IRR = MIRR = Payback = Discounted Payback = Data for Payback Years Net cash flow Cumulative CF 0 Years Net cash flow Discounted CF Cumulative CF 0 Data for Discounted Payback 2 3 4 2 3 4 2 3 4 1 2 3 4 1 2 3 4 FINC 4352 - Intermediate Finance Project 4 The goal of this project is to explore the topics of Cost of Capital and Capital Budgeting. The project requires you to work in Excel with the provided spreadsheet. Be sure to fill in the yellow boxes in the Excel file for full credit. In addition, type up a report in Word with an introduction (description of the mini project), findings (answer assignment questions, plots, etc.), and conclusion (summary). Your grade will depend on both quantity and quality. Upon completion, please submit both your Word report and Excel file to blackboard. Questions A, B, C, D, E, and F are worth 10 points each. The report is worth 15 points and the Excel file is worth 10 points. If you employ external references, please cite them in a bibliography section. Cost of Capital and Capital Budgeting The stock of IMB Computing sells for $50, and last year's dividend was $2.10. A flotation cost of 10% would be required to issue new common stock. IMB's preferred stock pays a dividend of $3.30 per share, and new preferred stock could be sold at a price to net the company $30 per share (inclusive of flotation costs). Security analysts are projecting that the common dividend will grow at a rate of 7% a year. The firm can issue additional long-term debt at an interest rate (or a before-tax cost) of 10%, and its marginal tax rate is 35%. The market risk premium is 6%, the risk-free rate is 6.5%, and IMB's beta is 0.83. In its cost-ofcapital calculations, IMB is funded with $22,500,000 of long-term debt, $25,000,000 of common equity, and $2,500,000 of preferred stock. Part 1: Component Costs A) Calculate the after-tax cost of debt. Calculate the cost of preferred stock. Calculate the cost of internal equity. Use both the CAPM method and the dividend growth approach. (10 Points) Part 2: Capital Structure B) Calculate the capital structure weights. Calculate the weighted average cost of capital. Calculate the cost of new common stock or external equity. Calculate the weighted average cost of capital for new common stock. (10 Points) FuncoLand has developed a powerful new server that would be used for corporations' Internet activities. It would cost $10 million at Year 0 to buy the equipment necessary to manufacture the server. The project would require net working capital at the beginning of each year in an amount equal to 10% of the year's projected sales; for example, NWC0 = 10% (Sales1). The servers would sell for $24,000 per unit, and Webmasters believes that variable costs would amount to $17,500 per unit. After Year 1, the sales price and variable costs will increase at the inflation rate of 3%. The company's nonvariable costs would be $1 million at Year 1 and would increase with inflation. The server project would have a life of 4 years. If the project is undertaken, it must be continued for the entire 4 years. Also, the project's returns are expected to be highly correlated with returns on the firm's other assets. The firm believes it could sell 1,000 units per year. The equipment would be depreciated over a 5-year period, using MACRS rates (see page 543). The estimated market value of the equipment at the end of the project's 4-year life is $500,000. FuncoLand's federal-plus-state tax rate is 40%. Its cost of capital is 10%. Part 3: Cash Flow Estimation C) Calculate the sales revenues for each year. (10 Points) D) Calculate the net cash flow due to salvage. (10 Points) E) Calculate net cash flows for each year. (10 Points) Part 2: Capital Budgeting Analysis F) Calculate the NPV, IRR, MIRR, Payback and Discounted Payback. (10 Points)