Scenario: Your real discount rate is 7.33%. Your team is assigned to the Gadget Division of The FGM Corporation, the largest multinational automobile manufacturer in the U.S. Your team is asked to evaluate a project proposal regarding the production of a device, DEVICE, which applies the artificial intelligence technology to improve driving experiences and safety. This upgradeable built-in device gives warnings to drivers and assists them to stay in lane and avoid collision. The DEVICE will be marketed as an optional feature for FGM cars and trucks. An 18-month comprehensive market analysis on the potential demand for the device was completed for a cost of $20.0M, where M is for millions.

Based on the comprehensive market analysis, your team expects annual sale volume of DEVICE to be 4.5M units for the first year and will increase by 6% annually in the following three years. The REAL unit price of the device is $660 (expressed in constant t=0 dollar). Due to the introduction of similar products by competitors at the end of Year 4, the expected annual sale volume will decline by 35% for the final year of the projects life. And the REAL unit price is expected to fall to $440 (expressed in constant t=0 dollar) in the year following the introduction of the competitive products. The REAL unit production costs are estimated at $600 (expressed in constant t=0 dollar) at the beginning of the project, and will not be impacted by the change in competition. Annual REAL growth rates for unit prices and unit production costs are expected to be 1.0% and 2.5%, respectively, over the entire life of the project.

In addition, the implementation of the project demands current assets set at 16% of contemporaneous annual sale revenues, and current liabilities set at 12% of contemporaneous annual production costs. Besides, the introduction of DEVICE will increase the sales volume of cars and trucks that leads to an increase in the annual after-tax operating cash flow of FGM by $15.0M (expressed in constant t=0 dollar, i.e., in real term) for the first year of the project, and declines at an annual real rate of 10% afterwards throughout the life of the project.

The production line for DEVICE is built on a vacant plant site (land) purchased by FGM at a cost of $54.0M twenty-five years ago. The vacant plant site has a current market value of $42.0M and is expected to be sold at the termination of the project for $48.0M in five years. The machinery for producing DEVICE has an invoice price of $105.0M, and its customization costs another $18.0M for meeting the specifications for the project. The machinery has an economic life of five years, and is classified in the MACR 5-year asset class for depreciation purpose. The machinery is expected to be fully obsolete and hence unsalable at the termination of the project.

The corporate handbook of The FGM Corporation states that corporate overhead costs should be reflected in project analyses at the rate of 4.5% of the annual revenue. The acceptance of the project has no impact on the corporate overhead costs. However, financial analysts at the Headquarters believe that every project should bear its fair share of the corporate overhead burden. On the other hand, the Director of the Gadget Division disagrees to this view and believes that the corporate overhead costs should be left out of the analysis. The marginal tax rate of The FGM Corporation is 21%. And any tax loss from the project can be used to write off taxable income of The FGM Corporation. The general inflation rate is 3.8%.

Question:

A potential solution to combat the competition is to regain the competitive edge by upgrading the DEVICE via upgrading the machinery and software technology at the end of the four year for a nominal cost of $85M. Assume that the upgrade is fully depreciated over the projects remaining life according to the straight-line depreciation method, and it has zero value at the termination of the project. The upgraded DEVICE can be sold at a real unit price of $520 (expressed in constant t=0 dollar). Would you recommend the upgrade of the machinery and the product? How much is the value of the option to upgrade (relative to the base scenario)?

Show all work and answers (in excel, if possible)