PLEASE USE EXCEL FOR THE WORKSHEET

what information is needed?

4 The production costs (including variable costs and fixed costs) remains at the same in the base case in Assignment 1. Would you recommend the upgrade of the machinery and the product? What is the value of this option to upgrade relative to the base scenario? Assignment 4: Recommendation Based on the analysis you conducted in Assignment 1 through Assignment 3, make a recommendation on whether or not to proceed with this new multi-function color printer project. Prepare a one-page written memo outlining your analysis and making your recommendation

Background Davis Printer Inc. is a midsized printer manufacturer. The company president is Sherry Davis, who inherited the company. When it was founded over 50 years ago, the company originally repaired printers and other household appliances. Over the years, the company expanded into manufacturing and is now a reputable manufacture of various printers and cartridges. Jason Smith, a recent finance graduate, has been hired as a financial analyst by the company's finance department. One of the major revenue-producing items manufactured by Davis Printer is a color printer. Davis Printer currently has one multi-function color printer model on the market, and sales have been excellent. However, as with any electronic item, technology changes rapidly, and the current color printer has limited features in comparison with newer models. Davis Printer has spent $200,000 for a marketing study to determine the expected sales figures for the new color printer. Assignment 1: Direct Cash Flow Analysis Suppose that you are Jason Smith and you have been asked by Sherry Davis, the president of the company, to create an Excel model with a direct cash flow forecast for the new multi-function color printer. After you estimate the incremental cash flows from the new color printer, calculate the NPV of the project. Assumptions: 1. Development cycle (design and engineering of the new multi-function color printer before going to market) - 1-year development cycle - Investment in equipment: $40 million - Investment Timing: 60% of the investment in equipment is invested now (time 0 or at the beginning of year 1 ) and the other 40% will be invested at the beginning of year 2 . - It is believed that the market salvage value of the equipment by the end of the project will be $5 million and the book salvage value by the end of the project will be zero. - The equipment will be depreciated based on the double-declining-balance depreciation approach. For the following assumptions, assume all cash flows occur at year end. 2. Sales - Assume new multi-function printers are launched at the end of the development cycle (or the end of year 1). - Life cycle of the product: sales will start from year 2 and will last for 6 years. - The estimated sales volume is 155,000,165,000,175,000,150,000,125,000, and 100,000 per year for the life cycle of the product, respectively. - The unit price of the new multi-function color printer is estimated to be $420 for the first launching year. In the following years, the unit price will increase by 3% per year. 3. Costs - The variable costs of new multi-function color printers will be $205 each for the first launching year. In the following years the variable cost per unit is expected to increase by 4% per year. - Fixed costs for the operation are estimated to run $5.5 million per year. 4. Erosion - As previously stated, Davis Printer currently manufactures a color printer. Production of the existing model is expected to be terminated three years after the new color printers are launched. - If Davis Printer does not introduce the new color printers, sales of the existing model will be 125,000 units, 115,000 units and 95,000 units for the three years, respectively. The price of the existing color printer is $350 per unit, with variable costs of $145 each and fixed costs of $4.3 million per year. - If Davis Printer does introduce the new color printer, sales of the existing color printer will fall by 50,000 units per year, and the price of the existing units will have to be lowered to $250 each. 5. Synergy - After the new color printer is introduced, the sales of color cartridges will increase. It is expected that the sales of color cartridges will increase by 50,000 each year over the life of the project after the new color printer is launched at the end of year 1 . The unit price of color cartridges is $70 and the unit production cost is $45. - Net working capital for the new color printers will be 15 percent of sales (you need to consider the impacts of erosion and synergy on sales) and will occur with the timing of the cash flows for the year. - At the end of the project, the net working capital will be fully recovered by selling all inventories. 7. Cost of capital - Assume that the riskiness of the new color printer project is compatible with that of the other projects of the company. Hence, the weighted cost of capital of the firm is valid to be cost of capital for this project. - David Printer has 1.1 million shares of its common stock outstanding, which is priced at $60 per share. The stock beta is estimated at 1.2 . The risk-free rate is 4% and the market risk premium is 7%. In addition, the company's 9%,$50 million par value, 20 year, BBBrated semiannual coupon bonds are priced at 120% of its par value. The par value of each bond is $1,000. 8. Tax rate - David Printer has a 21% corporate tax rate. Assignment 2: Scenario Analysis After you conduct the base scenario analysis in Assignment 1 , you are required to do NPV analysis for the best and worst scenarios. - Best scenario: The unit price of the new color printers will be $450 for the first launching year. The variable costs of the new color printers will be $195 each for the first launching year. - Worst scenario: The unit price of the new color printers will be $350 for the first launching year. The variable costs of new color printers will be $230 each for the first launching year. - You believe that there is 50% chance to be the base scenario, 30% of chance to be the best scenario, and 20% of the chance to be the worst scenario. What is the expected NPV of the new color printer project? Assignment 3: Real Option Analysis After the new color printer is launched in the market for two years, the competition of the market for the color printer is becoming more intensive. To maintain the competitive advantage, Davis Printer has an option to upgrade the machinery at the end of the second launching year for a cost of $10 million. - Assume that this upgrade is fully depreciated over its 4-year life according to the straightline depreciation method and it has zero salvage value at the termination of the project. - The upgraded color printer can be sold at $630 in the third launching year and the price will grow at 4% per year. Background Davis Printer Inc. is a midsized printer manufacturer. The company president is Sherry Davis, who inherited the company. When it was founded over 50 years ago, the company originally repaired printers and other household appliances. Over the years, the company expanded into manufacturing and is now a reputable manufacture of various printers and cartridges. Jason Smith, a recent finance graduate, has been hired as a financial analyst by the company's finance department. One of the major revenue-producing items manufactured by Davis Printer is a color printer. Davis Printer currently has one multi-function color printer model on the market, and sales have been excellent. However, as with any electronic item, technology changes rapidly, and the current color printer has limited features in comparison with newer models. Davis Printer has spent $200,000 for a marketing study to determine the expected sales figures for the new color printer. Assignment 1: Direct Cash Flow Analysis Suppose that you are Jason Smith and you have been asked by Sherry Davis, the president of the company, to create an Excel model with a direct cash flow forecast for the new multi-function color printer. After you estimate the incremental cash flows from the new color printer, calculate the NPV of the project. Assumptions: 1. Development cycle (design and engineering of the new multi-function color printer before going to market) - 1-year development cycle - Investment in equipment: $40 million - Investment Timing: 60% of the investment in equipment is invested now (time 0 or at the beginning of year 1 ) and the other 40% will be invested at the beginning of year 2 . - It is believed that the market salvage value of the equipment by the end of the project will be $5 million and the book salvage value by the end of the project will be zero. - The equipment will be depreciated based on the double-declining-balance depreciation approach. For the following assumptions, assume all cash flows occur at year end. 2. Sales - Assume new multi-function printers are launched at the end of the development cycle (or the end of year 1). - Life cycle of the product: sales will start from year 2 and will last for 6 years. - The estimated sales volume is 155,000,165,000,175,000,150,000,125,000, and 100,000 per year for the life cycle of the product, respectively. - The unit price of the new multi-function color printer is estimated to be $420 for the first launching year. In the following years, the unit price will increase by 3% per year. 3. Costs - The variable costs of new multi-function color printers will be $205 each for the first launching year. In the following years the variable cost per unit is expected to increase by 4% per year. - Fixed costs for the operation are estimated to run $5.5 million per year. 4. Erosion - As previously stated, Davis Printer currently manufactures a color printer. Production of the existing model is expected to be terminated three years after the new color printers are launched. - If Davis Printer does not introduce the new color printers, sales of the existing model will be 125,000 units, 115,000 units and 95,000 units for the three years, respectively. The price of the existing color printer is $350 per unit, with variable costs of $145 each and fixed costs of $4.3 million per year. - If Davis Printer does introduce the new color printer, sales of the existing color printer will fall by 50,000 units per year, and the price of the existing units will have to be lowered to $250 each. 5. Synergy - After the new color printer is introduced, the sales of color cartridges will increase. It is expected that the sales of color cartridges will increase by 50,000 each year over the life of the project after the new color printer is launched at the end of year 1 . The unit price of color cartridges is $70 and the unit production cost is $45. - Net working capital for the new color printers will be 15 percent of sales (you need to consider the impacts of erosion and synergy on sales) and will occur with the timing of the cash flows for the year. - At the end of the project, the net working capital will be fully recovered by selling all inventories. 7. Cost of capital - Assume that the riskiness of the new color printer project is compatible with that of the other projects of the company. Hence, the weighted cost of capital of the firm is valid to be cost of capital for this project. - David Printer has 1.1 million shares of its common stock outstanding, which is priced at $60 per share. The stock beta is estimated at 1.2 . The risk-free rate is 4% and the market risk premium is 7%. In addition, the company's 9%,$50 million par value, 20 year, BBBrated semiannual coupon bonds are priced at 120% of its par value. The par value of each bond is $1,000. 8. Tax rate - David Printer has a 21% corporate tax rate. Assignment 2: Scenario Analysis After you conduct the base scenario analysis in Assignment 1 , you are required to do NPV analysis for the best and worst scenarios. - Best scenario: The unit price of the new color printers will be $450 for the first launching year. The variable costs of the new color printers will be $195 each for the first launching year. - Worst scenario: The unit price of the new color printers will be $350 for the first launching year. The variable costs of new color printers will be $230 each for the first launching year. - You believe that there is 50% chance to be the base scenario, 30% of chance to be the best scenario, and 20% of the chance to be the worst scenario. What is the expected NPV of the new color printer project? Assignment 3: Real Option Analysis After the new color printer is launched in the market for two years, the competition of the market for the color printer is becoming more intensive. To maintain the competitive advantage, Davis Printer has an option to upgrade the machinery at the end of the second launching year for a cost of $10 million. - Assume that this upgrade is fully depreciated over its 4-year life according to the straightline depreciation method and it has zero salvage value at the termination of the project. - The upgraded color printer can be sold at $630 in the third launching year and the price will grow at 4% per year