please help answer 2 & 3 using excel
Central Florida Computer Company Central Florida Computer Co. (CFCC), a leading manufacturer of IBM look-alike computers, is considering the installation of a new production line to manufacture clones of IBM computers Mike Stoltz, the newest financial analyst, is evaluating the proposal in the spring of 2000, with anticipated initial investment occurring at the end of 2000 and the first revenue arriving at the end of 2001. The proposed portion of the plant that company owns was identified last year by an outside consultant at a cost of $60,000. At the moment, a portion of this plant is vacant. The 10,500 square feet required for the assembly line represents 25 percent of the entire plant. The plant originally cost $1,450,000 12 years ago and is being depreciated over 20 years. Warehouse space currently leases for S11 per square foot per year, however the company has strict policy forbidding the renting or leasing out of any of its vacant production facilities. CFCC must spend $1,100,000 on equipment for the line, plus $10,000 in shipping and installation costs. The equipment manufacturer guarantees these expenses, so CFCC's managers are certain of these cash flows. The machines are expected to have a five-year economic life. For tax purposes, they are classified as special manufacturing tools, and hence fall into a three-year MACRS depreciation schedule. (This schedule requires percentage depreciations of 33 percent, 45 percent, 15 percent, and 7 percent for each of the first four years, respectively.) CFCC's marketing department feels that sales for the division and the salvage value of machines at the end of project's life will depend upon the state of the economy. Exhibit i details the marketing department's sales and salvage value estimates. The marketing department expects that, given the state of economy, unit sales will be flat over the five-year life of the assembly line, but prices, and hence revenues, are expected to increase with inflation by 6 percent per year over the life of the line. The initial selling price in 2001 is expected to be $1,500 per unit The engineering department expects that fixed costs (excluding depreciation) will be constant $70,000 per year and that variable component costs (parts assembled to manufacture the computers) and labor costs will be 45 percent of revenues. The department is virtually certain of both of these estimates. CFCC's marginal tax rate is expected to remain at 38 percent. The new assembly line will require an increase in the level of CFCC's raw material inventories, finished goods inventory, and accounts receivable. The expected increase in current assets will be somewhat offset by a corresponding increase in current liabilities. The resulting increase in net working capital will require an investment of $30,000 in 2000 prior to 2001 sales (thus the change in NWC in 2000 is -$30,000). Beginning in 2001, additional annual increases in net working capital will be required they will vary directly with annual changes in revenue at a rate of 11.25 percent per dollar of marginal revenue. Thus, the change in NWC in 2001 will be 11.25 cents per dollar of 2002 revenue increase over the 2001 revenue level. In other words, the change in NWC in 2001 will be 11.25 percent of the difference between 2001 and 2002 revenues or 0.1125 (Revenues 2001 - Revenues 2002). All of the working capital investments will be recoverable at the end of project's life. At the end of the line's operating life, the line will be closed down. CFCC expects to turn the plant square footage over to another project. The assembly line machinery, on the other hand, will be sold for its estimated salvage value. The engineers have provided the estimates of terminal value before taxes (Exhibit 1). CFCC's stock is traded on the over-the-counter market at $30 per share, with an estimated beta of 1.5. Analysts expect that the company will pay $2.1 in dividends per share in 2001; dividends have grown 9 percent annually for the past 10 years. Currently, Treasury securities yield 7 percent and the Standard and Poor's 500 Index is expected to return 125 percent annually for the next several years. CFCC borrows from a local bank at 11 percent CFCC's operating committee has always maintained the company's book value capital structure at what it believes is the company's optimal or target capital structure. Exhibit 2 provides the company's current capital structure. EXHIBIT 1 Central Florida Computer Company Forecasts of Sales and Salvage Value State of Economy Probability Sales (Units) Salvage Value Selling Price) Recession 300 $100,000 Slow growth 0.40 400 400,000 Strong growth 0.25 500 900,000 0.35 EXHIBIT 2 Central Florida Computer Company Capital Structure Source Amou Long-term debt $3,500,000 Capital stock paid in 1,000,000 Retained earings 4,000,000 QUESTIONS 1. Compute the company's current weighted average cost of capital. 2. Assuming that this project has the same risk characteristics as the company's "average" project. estimate the projected cash flows and calculate the expected net present value, expected profitability index, expected internal rate of return, expected modified internal rate of return expected payback period, and expected accounting rate of return. Would you recommend the project? Why or why not? 3. If the economy is in recession, one option might be to abandon the project at the end of 2001 (it will essentially become a one-year project). The machinery, with much less wear, might then have a salvage value of $850,000. However, if the economy is in slow growth or strong growth states, the firm will implement the project over five years. How does this information affect your expected NPV? What is the value of the option to abandon the project in the state of recession? 4. Perform a sensitivity analysis on the sales and salvage value assumptions - that is, fix the salvage value at the most likely value and calculate the project's NPV by changing the sales to 70, 80, 90, 100, 110, 120, and 130 percent of the most likely sales assumption (in other words change sales from its most likely value of 400 units to 0.7x400 - 280 units, 0.8-400 - 320 units, and so on. and recalculate NPV every time). Now hold sales at its most likely value and calculate the project's NPV by changing the salvage value to 70, 80, 90, 100, 110, 120, and 130 percent of the most likely salvage value assumption. Plot the results on the same graph (project NPVs on the vertical axis and percentage changes in sales or salvage value on the horizontal axis). How do you interpret this information? 5. Perform a simulation analysis on the project by doing only 25 iterations. Calculate the mean NPV at the company's cost of capital and its standard deviation. Estimate the probability that the project will have a negative net present value. 6. Assume that, after performing the simulation analysis, you conclude that the project is significantly more risky than the "average project. You believe that the discount rate for such risky projects should be increased by 2 percent in order to compensate for risk. How would this information affect your recommendation? (No calculation is necessary, please explain.) Central Florida Computer Company Central Florida Computer Co. (CFCC), a leading manufacturer of IBM look-alike computers, is considering the installation of a new production line to manufacture clones of IBM computers Mike Stoltz, the newest financial analyst, is evaluating the proposal in the spring of 2000, with anticipated initial investment occurring at the end of 2000 and the first revenue arriving at the end of 2001. The proposed portion of the plant that company owns was identified last year by an outside consultant at a cost of $60,000. At the moment, a portion of this plant is vacant. The 10,500 square feet required for the assembly line represents 25 percent of the entire plant. The plant originally cost $1,450,000 12 years ago and is being depreciated over 20 years. Warehouse space currently leases for S11 per square foot per year, however the company has strict policy forbidding the renting or leasing out of any of its vacant production facilities. CFCC must spend $1,100,000 on equipment for the line, plus $10,000 in shipping and installation costs. The equipment manufacturer guarantees these expenses, so CFCC's managers are certain of these cash flows. The machines are expected to have a five-year economic life. For tax purposes, they are classified as special manufacturing tools, and hence fall into a three-year MACRS depreciation schedule. (This schedule requires percentage depreciations of 33 percent, 45 percent, 15 percent, and 7 percent for each of the first four years, respectively.) CFCC's marketing department feels that sales for the division and the salvage value of machines at the end of project's life will depend upon the state of the economy. Exhibit i details the marketing department's sales and salvage value estimates. The marketing department expects that, given the state of economy, unit sales will be flat over the five-year life of the assembly line, but prices, and hence revenues, are expected to increase with inflation by 6 percent per year over the life of the line. The initial selling price in 2001 is expected to be $1,500 per unit The engineering department expects that fixed costs (excluding depreciation) will be constant $70,000 per year and that variable component costs (parts assembled to manufacture the computers) and labor costs will be 45 percent of revenues. The department is virtually certain of both of these estimates. CFCC's marginal tax rate is expected to remain at 38 percent. The new assembly line will require an increase in the level of CFCC's raw material inventories, finished goods inventory, and accounts receivable. The expected increase in current assets will be somewhat offset by a corresponding increase in current liabilities. The resulting increase in net working capital will require an investment of $30,000 in 2000 prior to 2001 sales (thus the change in NWC in 2000 is -$30,000). Beginning in 2001, additional annual increases in net working capital will be required they will vary directly with annual changes in revenue at a rate of 11.25 percent per dollar of marginal revenue. Thus, the change in NWC in 2001 will be 11.25 cents per dollar of 2002 revenue increase over the 2001 revenue level. In other words, the change in NWC in 2001 will be 11.25 percent of the difference between 2001 and 2002 revenues or 0.1125 (Revenues 2001 - Revenues 2002). All of the working capital investments will be recoverable at the end of project's life. At the end of the line's operating life, the line will be closed down. CFCC expects to turn the plant square footage over to another project. The assembly line machinery, on the other hand, will be sold for its estimated salvage value. The engineers have provided the estimates of terminal value before taxes (Exhibit 1). CFCC's stock is traded on the over-the-counter market at $30 per share, with an estimated beta of 1.5. Analysts expect that the company will pay $2.1 in dividends per share in 2001; dividends have grown 9 percent annually for the past 10 years. Currently, Treasury securities yield 7 percent and the Standard and Poor's 500 Index is expected to return 125 percent annually for the next several years. CFCC borrows from a local bank at 11 percent CFCC's operating committee has always maintained the company's book value capital structure at what it believes is the company's optimal or target capital structure. Exhibit 2 provides the company's current capital structure. EXHIBIT 1 Central Florida Computer Company Forecasts of Sales and Salvage Value State of Economy Probability Sales (Units) Salvage Value Selling Price) Recession 300 $100,000 Slow growth 0.40 400 400,000 Strong growth 0.25 500 900,000 0.35 EXHIBIT 2 Central Florida Computer Company Capital Structure Source Amou Long-term debt $3,500,000 Capital stock paid in 1,000,000 Retained earings 4,000,000 QUESTIONS 1. Compute the company's current weighted average cost of capital. 2. Assuming that this project has the same risk characteristics as the company's "average" project. estimate the projected cash flows and calculate the expected net present value, expected profitability index, expected internal rate of return, expected modified internal rate of return expected payback period, and expected accounting rate of return. Would you recommend the project? Why or why not? 3. If the economy is in recession, one option might be to abandon the project at the end of 2001 (it will essentially become a one-year project). The machinery, with much less wear, might then have a salvage value of $850,000. However, if the economy is in slow growth or strong growth states, the firm will implement the project over five years. How does this information affect your expected NPV? What is the value of the option to abandon the project in the state of recession? 4. Perform a sensitivity analysis on the sales and salvage value assumptions - that is, fix the salvage value at the most likely value and calculate the project's NPV by changing the sales to 70, 80, 90, 100, 110, 120, and 130 percent of the most likely sales assumption (in other words change sales from its most likely value of 400 units to 0.7x400 - 280 units, 0.8-400 - 320 units, and so on. and recalculate NPV every time). Now hold sales at its most likely value and calculate the project's NPV by changing the salvage value to 70, 80, 90, 100, 110, 120, and 130 percent of the most likely salvage value assumption. Plot the results on the same graph (project NPVs on the vertical axis and percentage changes in sales or salvage value on the horizontal axis). How do you interpret this information? 5. Perform a simulation analysis on the project by doing only 25 iterations. Calculate the mean NPV at the company's cost of capital and its standard deviation. Estimate the probability that the project will have a negative net present value. 6. Assume that, after performing the simulation analysis, you conclude that the project is significantly more risky than the "average project. You believe that the discount rate for such risky projects should be increased by 2 percent in order to compensate for risk. How would this information affect your recommendation? (No calculation is necessary, please explain.)
