PROBLEM $2$ DETERMINING CASH FLOWS FOR A PROJECT $(40$ POINTS$)$

I am evaluating a new investment opportunity for my company that will be financed with debt and retained cash from current operations. My current market$-$based capital structure is $50\%$ debt and $50\%$ equity. The sources of capital for this new project will match my current market$-$based capital structure. The entire initial cost of the investment, excluding working capital, is $$12,000,000$ and this amount is fully depreciable over the life of the investment. In addition to this initial investment, I also plan to invest $$1,000,000(10\%$ of my projected first year revenues$)$ for my initial working capital needs. As my revenues grow, my working capital requirements will also grow annually at the rate of $$1$ for every $$10$ in projected revenue growth $(10\%$ of revenue growth$).$ Required investments for working capital needs will occur at the end of every year based on anticipated revenues increases for the following year.

The proposed project has an estimated life of $10$ years and no salvage value and I will account for depreciation expense for the project by applying straight$-$line depreciation. The debt I will use is bond$-$debt with an annual coupon rate of $7\%.$

My Year $1$ revenue$/$sales attributable to the investment opportunity will total $$10,000,000$ and will increase $10\%$ every year. Fixed costs total $$1,500,000$ per year $($excluding depreciation expenses$)$ and variable costs total $$2,500,000$ for year $1$ and increase by $5\%$ per year. In year $6$ of the project, I will invest an additional $$3$ million into the project for capital upgrades to the investment. This capital investment has no salvage value and will be straight$-$line depreciated over a $5-$year period beginning in year $6$ and ending in year $10.$ I will evaluate this project against a weighted average cost of capital of $10\%.$ My marginal tax rate is $21\%.$

From this information, determine the cash flows I should use for this investment from period $0($my year $0$ or initial investment cash outflow$)$ through and including year $5.$ Develop an appropriate spreadsheet with an input$/$assumptions area as necessary to help you arrive at your calculations of the yearly cash flows. For this assignment, turn in your completed and properly formatted spreadsheet which clearly highlights the cash flows for the requested periods.

BONUS FOR $10$ POINTS: EXPAND YOUR SPREADSHEET TABLE TO INCLUDE ALL OF THE CASH FLOWS THROUGH AND INCLUDING YEAR $10$ FOR A BONUS $10$ POINTS. THIS IS A BIT TRICKY DUE TO THE WORKING CAPITAL FIGURE $($WHAT HAPPENS TO IT AT THE END OF THE PROJECT?$)$ AND THE HANDLING OF THE CAPITAL UPGRADE INVESTED IN YEAR $6.$