Excel Spreadsheet Capital Budgeting Problem Electric Bike Corporation (EBC) designs, manufactures, and markets several lines of bicycles designed to be powered by proprietary electric motors. The company is evaluating the development of a new line of electric bike called the Cruiser 3, and this would require an expansion of its production plant to enable it to produce the Cruiser 3 for the next 5 years. Last year, the company spent $150,000 to do marketing research analysis to estimate market demand for a new power boat line. The company also spent last year $350,000 for an engineering analysis for the new electric motor for the Cruiser 3 bike. The current expansion scenario would have total construction costs of $8,800,500 and it would take about 150 days to complete (i.e., essentially up-front). EBC would also put in $6,130,000 of new production machinery and equipment. Inventory (raw materials, work-in-process, finished goods) investment needed for the expansion to get started would be $2,330,000.

Except for the inventory investment, the total upfront capital investment can be completely depreciated assuming no/zero salvage value using the straight-line method over four (4) years (which is comparable to an accelerated depreciation method for income tax purposes). The company expects to incur $1,100,000 million in incremental annual interest expense, and the company expects it could increase annual dividends by $0.85 per share (there are 10 million shares outstanding). Incremental sales for this project are based on forecast demand of 80,000 units in the first year; 90,000 units in the second year; 100,000 units in the third year; 120,000 units in the fourth year; and, 100,000 units in the fifth year. Average sales price per unit is expected to be $350 in Year 1; $350 in Year 2; $375 in Year 3; $380 in Year 4; and $320 in Year 5. Cost of goods sold is estimated to be 80% of total sales each year, and incremental fixed costs are estimated to be $850,000 per year. At the end of the projects estimated life, the company estimates it could sell for $1,500,000 some of the machinery and equipment purchased in Year 0, and the expected the book value for these assets would be zero at that time. Also at the end of the project, $1,500,000 of inventory could be liquidated at its original cost (with no income tax effect). The companys income tax rate is expected to be 39% for ordinary income and 21% for capital gains income. If EBC does this project, it will immediately sell some existing surplus equipment for a price of $2,250,000 which has a current book value of $1,125,000 million and which would have future depreciation of $375,000 per year for the next three (3) years. EBCs weighted average cost of capital is 17.00%, and EBC it believes this project should earn at least a 19.00% average annual return. Put the solutions to the following questions into an Excel spreadsheet with appropriate detail, and use whole dollars in the spreadsheet. 1. What is the upfront total after-tax cash costs for this proposed project? 2. What are the Total Annual Free Cash Flows for Year 1? Year 2? Year 3? 3. What is the Total After-Tax Operating Cash Flow for Year 5 (exclude Terminal Year-specific items)? 4. What is Terminal Year-specific Cash Flow (i.e., After-Tax Salvage Value excluding the Annual Operating Cash Flow portion)? Show your work in appropriate detail . 5. Is(Are) there any irrelevant cash flow(s) mentioned in this problem? If so, what is(are) it(they)? 6. Are there any sunk costs for this project? If so, what are they? 7. Are there any opportunity costs for this project? If so, what are they? 8. What is the Net Present Value for this project proposal? 9. What is the Internal Rate of Return for this project proposal? 10. Would this project be a good investment? Why? Or Why not? (Calculate at least two capital budgeting evaluation methods but use only one to decide if the proposed project is a good investment).