Reference no: EM132828215

Create an Excel model with a direct cash flow forecast.

Assumptions:

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.

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.

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.

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.

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

• 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 networking capital will be fully recovered by selling all inventories.

Cost of capital: 9.17%

Tax rate: 21% corporate tax rate