ME 470 Systems DesignIn-class/HomeworkDue March 23, 2010Corporate Finance1. For Example 18.3 of Dieter and Schmidt we showed in lecture how the Present Value (PV) and Net Present Value (NPV) functions in Excel could be used to calculate the Present Value of the costs of Machine A. Create an Excel spreadsheet that shows the annual costs and calculates the Present Value of the costs of Machine B in example 18.3.Do two separate calculations, the first which uses the PV function, and the second which uses the NPV function.2. a. Use Excel to find the NPV for a Cardiac Stent project:• Years 1-5 development: $300M/year• Years 5-9 FDA testing, IP costs, manufacturing ramp up: $ 500M/year• Year 10 until expiration of patent– Volume: 600,000 units / year– Revenue: $2500 / unit– Costs: $1000 / unit• Patent issues at start of year 9 and is enforceable for 17 years• Cost of money is 6% b. Find the NPV if the FDA testing takes twice as long as planned (still at $500M/year)c. For the original case, determine the NPV if there is a 5% probability that there is no FDA approval, a 10% probability of $1.5B intellectual property settlement in year 14, and a 85% probability of business as predicted.3. What price would you pay today for a device that reduces Aerodynamic Drag of a semi-tractor trailer by 3% such that your break even economically?Assume:- 50% of energy for hauling a trailer goes to aerodynamic drag- 30% of energy for hauling a trailer goes to rolling resistance- The nominal mileage for the semi is 6 miles/gallon- Trailer life is about 1,000,000 miles- A trailer is pulled about 500 miles per day- The fuel price is assumed to be $3.00 per gallon- The interest rate is assumed to be 5%Personal Finance4. PowerballPayment options If you win the jackpot, you have 60 days to choose either a 30-year annuity or a lump-sum cash payment of approximately half the amount of the announced jackpot.If you do not select cash or annuity within the 60 days, your prize will be paid as an annuity.Assume you have won 10 million dollars in a Powerball lottery. For what range of interest rates should you take the annuity?5. Interest Rate and Loan Period SensitivityYou are planning to purchase a new car for $30,000 dollars and are wondering how hard to shop for a carloan. Examine the sensitivity of the future value of the costs with respect to interest rate. Assume a down payment of $3000 and annual interest rates of 5%, 6%, 7%, 8% and 9%.a) Use the PMT function in Excel to determine the monthly payment you would need to make at each interest rate (assume a 48 month loan and don’t forget to divide the interest rate by 12 to get the monthly interest rate).b) Determine the amount of interest you paid over the course of the loan (down payment + payments – cost of the car) for each interest rate.c) Repeat parts a) and b) for a loan period of 60 months.6. Break EvenConsider a Honda Civic Hybrid w/Nav (MSRP= $25,800, Combined fuel economy=42 mpg) vs. a Honda Civic EX w/Nav (MSRP =$22,255, Combined fuel economy = 29 mpg) (Source:Edmonds.com).Determine the break even point (number of miles you would have to drive for equivalent cost) if fuel prices are $2.00/gal, $2.50/gal, $3.00/gal, $4.00/gal, or $5.00/gal and report the values in a table.Grading:You must show your work. A reader must be able to easily see the method, the equations, and what spreadsheet commands were used. The use of cash flow diagrams and clear annotation of spreadsheets will go a long way to helping with clarification. There should be sufficient text that another student in theclass could understand and explain what was