HELP WITH THE EXCELL SPREAD SHEET, PLEASE. Road King Trucks, Inc. Specifically I need excel capital budgeting analysis calculations for two engine options: Marcus and Detroit, to determine which one is better. In the excel sheet i need to know what formulas are being used to compute the #'s Below is a list of items I need to identify and quantify to complete the Road King Trucks case in Excel: A. For Each Year 1. Revenue 2. Variable costs a. Labor cost per bus b. Parts cost per bus c. Engine costs per bus (2 options) 3. SG&A costs 4. Training costs 5. Warranty costs a. Bus b. Engine (2 options) 6. Depreciation 7. Working Capital needs B. Project Related 1. Investment costs C. Cost of Capital 1. Companys weighted average cost of capital (WACC) 2. Appropriate cost of capital for this project Also, I need to calculate NPV, IRR, Profitability Index and Payback for each engine option. And, after doing this, you have to form a basis for a go/no go decision on the project, with appropriate support for your decision (think NPV and IRR). Spreadsheet should show calculations for o Operating Cash Flows o Incremental Cash Flows, including investment and salvage o Warranty costs o WACC o NPV, IRR, Payback, and Profitability Index INPUT DATA: Exhibit 1: Sales and Cost Forecast The sales forecast is based on projected levels of demand. All the numbers are expressed in todays dollars. The forecasted average inflation per year is 2.5%. Price per bus $220,000 Units sold per year 11,000 Labor cost per bus $50,000 Components & Parts per bus $95,000 Selling General & Administrative (fixed) $250,000,000 NOTE: Average warranty cost per year per bus for the first five years is $1,000.The present value of this cost will be used as a cost figure for each bus. Afterwards, the bus operator will become responsible the repairs on the buses. The buses can be produced for twenty years. Afterwards, the designs become obsolete. Engine choices: Detroit engines Price per engine including installation: $25,000 Average annual warranty cost per year for five years: $500 Marcus Engine: Price per engine, including installation $18,000 Average annual warranty cost per year for five years: $2,000 Afterwards, the bus operator will become responsible for the repairs on the buses. The chosen engine will be installed in every bus and will become a cost figure for each bus. NOTE: The engine manufacturers are not providing Road King Trucks with any warranty. However, Road King Trucks will provide a warranty to its customers. After the initial five years, the bus operators may purchase an extended warranty from any insurance company that offers such packages. Exhibit 2: Investment Needs To implement the project, the firm has to invest funds as shown in the following table: Year 0: $1 billion Year 1: 100 million Year 2: 100 million Road King Trucks estimated that it would cost a total of $1 billion to build the factory and purchase the necessary equipment to produce the buses. The other $200 million investment, divided equally in years 1 and 2, is for non-depreciable labor training costs. Such investment is treated as regular business expenses. Straight line depreciation will be used for the sake of simplicity. To facilitate the operation of manufacturing the transit buses, the company will have to allocate funds to net working capital (NWC) equivalent to 10% of annual sales. The investment in NWC will be recovered at the end of the project. The equipment will be sold for salvage at about $10,000,000 at the end of the project. Exhibit 3: Financing Assumptions The following assumptions are used to determine the cost of capital. Historically, the company has maintained a debt ratio is 50%. This ratio was used, because lowering the debt implies giving up the debt tax shield, and increasing it makes debt service a burden on the firms cash flow. In addition, increasing the debt level may cause a reduced rating of the companys bonds. The marginal tax rate is 40%. All the numbers are expressed in todays dollars. The forecasted average inflation per year is 2.5%. Cost of debt: The companys bond rating is roughly at the high end of the A range. Surveying the debt market yielded the following information about the cost of debt for different rating levels: Bond rating AA Interest cost range 5.5% ~ 6.5%, A Interest cost range 6.25% ~ 7.5%, BBB Interest cost range 7.5% ~ 9%. The companys current bonds have a yield to maturity of about 6.5%. Cost of equity: The current 10-year Treasury notes have a yield to maturity of 1.75% and the forecast for the S&P 500 market premium is 9.75%. The companys overall is 1.25 Once Excel spreadsheet is provided the following questions need be answered by the supporting information. Issues and Analysis Mr. Livingston noticed that there was a great deal of enthusiasm among the management group about the transit bus opportunity, but his cautious nature told him to also seek a more objective viewpoint. Consequently, he sought out you to analyze the proposed project and provide your recommendations directly to him. The issues he wants you to address in your analysis and report are the following: 1) How much importance should be given to the energy cost situation? 2) What are the projects cash flows for the next twenty years? What assumptions did you use? 3) What is the companys cost of capital? What is the appropriate discount factor (which may be different) for you to use in evaluating the bus project? 4) If you decide to go ahead with the project, which of the two engines should be used in the bus, and why? 5) Evaluate the quality of the project, by using appropriate capital budgeting techniques. 6) Would you recommend that Road King Trucks accept or reject the project? What are the key factors on which you base your recommendation?