Tornado Motors is a major producer of sport and utility trucks. It is a family owned company, started by Jane Biscayne in 1935, at the height of the Great Depression. Today the firm produces 3 lines of trucks. These include a standard, no-frills short bed pickup truck (Model A), a mid-size version (Model B) and a larger, heavy-duty work truck (Model C). Janet Biscayne, the founders grand-daughter is the current CEO. She has asked you to provide a financial analysis of an idea that originated in the companys engineering department. The engineers have developed a green version of the smallest of Tornados trucks Model A. The green version would be made of recycled materials and would run on a hybrid engine. Based on the results of a large market analysis, the CEO believes that there would be a sizable demand for the green version of the Model A truck. Tornado pays an average tax rate of 40%. It requires a 15% return on investments of this character. The engineering department estimates that it would cost $40 million to purchase the additional equipment necessary to convert an existing facility to production of the green trucks. This equipment is expected to last 5 years and could be sold as salvage for $2 million at the end of its useful life. The full cost of the equipment will be depreciated on a straight-line basis over 5 years. The marketing department estimates that with a sticker price of $35,000 sales of the green trucks would be 6,000, 7,500, 8,000, 8,500 and 8,700 in each of the next five years. The green trucks are expected to cost $29,500 each to produce. In addition, fixed costs associated with the production of the new trucks would be $15 million per year and the truck production would require an additional initial one-time investment in Net Working Capital of $5 million. Finally, consider the following two issues: o The company spent $4 million developing a prototype for the hybrid engine that would be used in the green truck. In addition, the company spent $5 million on a marketing study to evaluate demand for the green version. o The marketing department warns that it expects that production of the green trucks would adversely affect sales of the current standard version of the Model A trucks as some of the buyers who would have bought the standard model will be attracted to the green version. The standard version costs $24,500 to produce and sells for $29,000. If the green version is not introduced, the company expects to sell 9,000 units per year for the next five years. However, with competition from the green version, annual sales of the standard version are expected to decline by 500, 700, 1,000, 1,200 and 1,500 units in each of the next 5 years. Furthermore, marketing expects that the company will have to lower the price by $1,000 in order to achieve this level of sales. Project Requirement 1. SPREADSHEET WORTH 50% of the project grade: Using an Excel spreadsheet, calculate the projected cash flows. Once the cash flows are computed, use the spreadsheet finance functions to compute NPV and IRR. Your grade will be based on accuracy, format and visual appeal. WRITTEN PORTION WORTH 10% of the project grade: Please write a paragraph executive summary that states what your recommendation would be. Include and provide an interpretation of the numbers from your spreadsheet. Explain in nonfinancial terms why your decision would be best for the shareholders of the firm