Help me build this spreadsheet to Pertinent financial information is given below:

The company has $60,000$ bonds with a $30-$year life outstanding, with $14$ years until maturity. The bonds carry a $9$ percent semi$-$annual coupon, and are currently selling for $$925\mathrm{.}75.$

You also have $100,000$ shares of perpetual preferred stock outstanding, which pays a dividend of $$8\mathrm{.}00$ per share. The current market price is $$95\mathrm{.}00.$

The company has $7$ million shares of common stock outstanding with a current price of $$11\mathrm{.}07$ per share. The stock exhibits a constant growth rate of $8$ percent. The last dividend $($D$0)$ was $$\mathrm{.}90.$

Your firm$$s federal $+$ state marginal tax rate is $35\%.$

Your venture would consist of a new product introduction. You estimate that your product will have a six$-$year life span, and the equipment used to manufacture the product falls into the MACRS $5-$year class. The resulting MACRS depreciation percentages for years $1$ through $6,$ respectively, are $20\%,32\%,19\%,12\%,11\%,$ and $6\%.$

Your venture would require a capital investment of $$18,000,000$ in equipment, plus $$1,000,000$ in installation costs. The venture would also result in an increase in accounts receivable and inventories of $$3,000,000($this represents an overall increase in net working capital of $$3,000,000$ needed to get the project started, in addition to the investment in the equipment and installation$).$

At the end of the six$-$year life span of the venture, you estimate that the equipment could be sold at a $$4,800,000$ salvage value.

Your venture would incur fixed costs of $$1,100,000$ per year, while the variable costs of the venture would equal $30$ percent of revenues. You are projecting that revenues generated by the project would equal $$8,000,000$ in year $1,$ $$15,000,000$ in year $2,$ $$16,000,000$ in year $3,$ $$18,000,000$ in year $4,$ $$10,000,000$ in year $5,$ and $$9,000,000$ in year $6.$

The following list of steps provides a structure that you should use in analyzing your new venture. Note: Carry all final calculations to two decimal places. $[$Note: This financial analysis component of the project accounts for $80$ out of $120$ points. See the rubric for details related to the points allocation.$]$

$1.$ Find the costs of the individual capital components:

a$.$ long$-$term debt $($Rd$)$

b$.$ preferred stock $($Rp$)$

c$.$ equity $($use DCF approach$)($Re$)$

$2.$ Determine the weighted average cost of capital.

$3.$ Compute the Year $0$ initial investment for your project.

$4.$ Compute the annual operating cash flows for years $1-6$ of the project.

$5.$ Compute the terminal cash flow at the end of year $6.$

$6.$ Create a timeline that summarizes all of the cash flows for your venture.

$7.$ Compute the capital budgeting analysis using the all four of the following tools:

$-$ IRR

$-$ NPV

$-$ payback period

$-$ discounted payback periodreflect these answers: