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. PARTIAL CREDIT WILL BE AWARDED.