Mopar Commercial Auto Supplier, Inc.

Mopar Commercial Auto Supplier, Inc., is thinking about investing in new equipment to conduct emissions inspections in the state of Colorado. The equipment will cost $40,000 up front. Each inspection costs $12 for automobiles and $15 for registered trucks. All, except new vehicles, are required to have an annual inspection. New vehicles get a two-year inspection when they are driven off the lot. Managers are anticipating the following numbers of cars annually.

1992 1993 1994 1995 1996 Automobiles 300 350 400 450 550 Trucks 200 250 350 350 400 New vehicles 50 50 75 75 75

• 1. Set up a worksheet with three major sections:
  • Section 1. Number of Inspections-Enter the table above, starting in cells A4 through F7.
  • Section 2. Amount of Income-Enter the prices for inspections in cells A9 through B10.
  • Section 3. Cash Flow-Multiply the number of inspections by the price for each vehicle in cells A12 through F15.

Sections 1 and 2 should be tables that include the price, the five years, and the total. Include the data on automobiles, trucks, and new vehicles.

• 2. Calculate cash flow with a formula that multiplies the number of inspection with the price per inspection. Since we do not know what the mix of new vehicles will be (automobiles versus trucks), we must estimate the price per inspection by the total mix of automobiles and trucks. Use the following formula to estimate the price per inspection for new vehicles

Difference Between Total Number of Truck Inspection Price Automobile Inspections Automobile and Automobile X ------------------------------------ Inspection Price Total Number of ($15 - $12) Inspections + Inspection Price

• 3. Total each column (year) and each row (type of inspection). Create a "total" totals figure-the entire amount of income that will come in from inspections.
• 4. In the cash flow section, the total of each column will be the Cash Flow used in the net present value and internal rate of return calculations. Add the investment outflow of ($40,000) to the beginning of the cash flow variable. The cash flow variable will match the total amount of income.
• 5. Set up three cells, one each for the interest rate, the NPV, and the IRR, Calculate the net present value using, the @NPV function of the investment. Re- member: Do not include the initial investment in the cash flow, but subtract the initial investment from the value given.
• 6. Calculate the internal rate of return of the investment using the @IRR function. With @IRR, you must have the initial investment of ($40,000) as the first cash flow of the range. Use the interest rate as your estimated IRR, or guess an IRR.
• 7. Based on the NPV and IIR you calculated, do you recommend that Mopar Commercial Auto Supplier invest in the emissions inspection equipment? Does it appear that appear will mal
• Include a discussion of the analysis, as well as your recommendation, for this situation.