Create a capital budgeting analysis in Excel.

Please work on an Excel spreadsheet to conduct a capital budgeting analysis for the following project: 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 founder's grand-daughter is the current CEO. She has asked you to provide a financial analysis of an idea that originated in the company's engineering department. The engineers have developed a "green" version of the smallest of Tornado's 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. Tomado 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. In addition, you are given the following information: 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. 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 cach 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. The company's stock is currently sold at $20.00 per share. It paid $2 dividend per share last year. It is expected that the dividend growth rate will be 5% each year in the future. The company's bond has a 6-year bond outstanding. It is currently priced at $906.15. The bond pays 6% coupon semi-annually. The par value of the bond is $1,000. The company's market value debt-equity ratio is 0.5 (D/E-0.5). Focus x W 29