Question

Hybrid cars are touted as a "green" alternative; however, the financial aspects of hybrid ownership are not as clear. Consider the 2018 Edsel 550h, which had a list price of $5,900 (including tax consequences) more than the comparable Edsel 550. Additionally, the annual ownership costs (other than fuel) for the hybrid were expected to be $400 more than the traditional sedan. The EPA mileage estimate was 25 mpg for the hybrid and 17 mpg for the traditional sedan. a. Assume that gasoline costs $3.45 per gallon and you plan to keep either car for six years. How many miles per year would you need to drive to make the decision to buy the hybrid worthwhile, ignoring the time value of money? (Do not round intermediate calculations and round your answer to nearest whole number, e.g., 32.) b. If you drive 14,000 miles per year and keep either car for six years, what price per gallon would make the decision to buy the hybrid worthwhile, ignoring the time value of money? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.) c. Gasoline costs $3.45 per gallon and you plan to keep either car for six years. How many miles per year would you need to drive to make the decision to buy the hybrid worthwhile? Assume the appropriate interest rate is 10 percent and all cash flows occur at the end of the year. (Do not round intermediate calculations and round your answer to the nearest whole number, e.g., 32.) d. If you drive 14,000 miles per year and keep either car for six years, what price per gallon would make the decision to buy the hybrid worthwhile? Assume the appropriate interest rate is 10 percent and all cash flows occur at the end of the year. (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.) a. Miles per year b. Price per gallon c. Miles per year d. Price per gallon

Answer 1

a]

To make the hybrid worthwhile, the cost savings should equal the additional investment.

Yearly cost savings = reduced gas expenses - increased ownership cost.

Reduced gas expenses = ((miles per year / mileage of traditional sedan) - (miles per year / mileage of hybrid)) * cost per gallon.

Increased ownership cost = $400.

Additional investment = $5900.

For the cost savings to equal the initial investment, the cumulative cash flows should equal zero.

The miles per year is calculated using GoalSeek function in Excel.

C6 Fax C = (($B$1/17)-($B$1/25)) *$B$2 D E F G H B 20,000 $3.45 o 1 2 3 4 5 6 $5,900 1 Miles per year 2 Cost of gas 3 4 Year Additional 5 investment Reduced gas 6 expenses Increased 7 ownership cost 8 Cash Flow Cumulative 9 Cash flow $1,299 $1,299 $1,299 $1,299 $1,299 $1,299 $400 $899 $400 $899 $400 $899 $400 $899 $400 $899 $400 $899 ($5,900) ($5,900) ($5,001) ($4,102) ($3,204) ($2,305) ($1,406) ($507) 10

20000 3.45 1 Miles per year 2 Cost of gas 3 4 Year JO1 5 Additional investment 5900 6 Reduced gas expenses =(($B$1/17)-($B$1/25))*$B$2 =(($B$1/17)-($B$1/25))*$B$2 = ($B$1/17)-($B$1/25))*$B$2 =(($B$1/17)-($B$1/25))*$B$2 =(($B$1/17)-($B$1/25))*$B$2 = ($B$1/17)-($B$1/25))*$B$2 7 Increased ownership cost 8 Cash Flow 400 =C6-C7 400 =D6-07 400 =E6-E7 400 =F6-F7 400 =G6-G7 400 =H6-H7 --B5 9 Cumulative Cash flow =B8 =B9+C8 =C9+D8 =D9+E8 =E9+F8 =F9+G8 =G9+H8

D E F G H I J K 1 Miles per year 2 Cost of gas B 20,000 $3.45 Goal Seek ol 1 2 3 4 5 6 Set cell: HS To value: By changing cell: $851 $5,900 $1,299 $1,299 $1,299 $1,299 $1,299 $1,299 OK Cancel 4 Year Additional 5 investment Reduced gas 6 expenses Increased 7 ownership cost 8 Cash Flow Cumulative 9 Cash flow 10 $400 $899 $400 $899 $400 $899 $400 $899 $400 $899 $400 $899 ($5,900) ($5,900) ($5,001) ($4,102) ($3,204) ($2,305) ($1,406) ($507)

The miles per year at which it is worthwhile to buy the hybrid is 21,301 miles.

А C D E F G н 1 Miles per year 2 Cost of gas 3 21,301 $3.45 0 1 2 3 4 5 6 $5,900 $1,383 $1,383 $1,383 $1,383 $1,383 $1,383 Additional 5 investment Reduced gas 6 expenses Increased 7 ownership cost 8 Cash Flow Cumulative 9 Cash flow $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 ($5,900) ($5,900) ($4,917) ($3,933) ($2,950) ($1,967) ($983) ($0! 10

(b)

If the miles per year is 14,000. the price per gallon at which the cumulative cash flows equal zero is calculated using GoalSeek in Excel.

C D H JK L 1 Miles per year 2 Cost of gas B 14,000 $3.45 3 Goal Seek ? x 1 2 3 4 5 $5,900 Set cell: $1$9 To value: 0 By changing cell: $B$2 $909 $909 $909 $909 $909 $909 4 Year Additional 5 Reduced gas 6 expenses Increased 7 ownership cost 8 Cash Flow Cumulative 9 Cash flow OK Cancel $400 $509 $400 $509 $400 $509 $400 $509 $400 $509 $400 $509 ($5,900) ($5,900) ($5,391) ($4,882) ($4,372) ($3,863) ($3,354) ($2,845)

At a cost of $5.25 per gallon, the decision to buy the hybrid is worthwhile.

с D E | F G H 1 Miles per year 2 Cost of gas в 14,000 $5.25 3 0 1 2 3 4 5 6 4 Year Additional $5,900 $1,383 $1,383 $1,383 $1,383 $1,383 $1,383 Reduced gas 6 expenses Increased ownership cost 8 Cash Flow Cumulative 9 Cash flow 7 $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 ($5,900) ($5,900) ($4,917) ($3,933) ($2,950) ($1,967) ($983) $0!

(c)

For the hybrid to be worthwhile, the NPV should be at least zero.

NPV is calculated using NPV function in Excel.

B10 fo =NPV(10%,C8:H8)+B8 D E F C G H 1 Miles per year 2 Cost of gas 14,000 $5.25 0 1 2 3 4 5 6 $5,900 4 Year Additional investment Reduced gas 6 expenses Increased 7 ownership cost 8 Cash Flow 10 NPV $1,383 $1,383 $1,383 $1,383 $1,383 $1,383 $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 ($5,900) ($1,617.33)

The miles per year at which NPV is zero is calculated using GoalSeek in Excel.

B10 fac =NPV(10%,C8:H8) +B8 D E F C G H I J K 14,000 $5.25 0 1 2 3 4 5 6 Goal Seek 12 x $5,900 1 Miles per year 2 Cost of gas 3 4 Year Additional 5 investment Reduced gas expenses Increased ownership cost 8 Cash Flow 10 NPV Set cell: B10 To value: By changing cell: $B$1 $1,383 $1,383 $1,383 $1,383 $1,383 $1,383 $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 OK Cancel ($5,900) ($1,617.33)

The miles per year at which it is worthwhile to buy the hybrid is 17,758 miles.

B10 NPV(10%,C8:H8) +B8 F G H 17,758 $5.25 0 1 2 3 4 5 6 $5,900 1 Miles per year 2 Cost of gas 3 4 Year 1 Additional 5 investment Reduced gas 6 expenses Increased 7 ownership cost 8 Cash Flow 10 NPV L $1,755 $1,755 $1,755 $1,755 $1,755 $1,755 $400 ($5,900) $1,355 $0.00! $400 $1,355 $400 $1,355 $400 $1,355 $400 $1,355 $400 $1,355

(d)

The price per gallon at which NPV is zero is calculated using GoalSeek in Excel.

B10 fx =NPV(10%,C8:H8) +B8 D E C J K L 14,000 $5.25 o 1 2 3 4 5 6 Goal Seek 2 $5,900 1 Miles per year 2 Cost of gas 3 4 Year T Additional 5 investment Reduced gas 6 expenses Increased 7 ownership cost 8 Cash Flow 10 NPV Set cell: $B$10 To value: By changing cell: $B$2 $1,383 $1,383 $1,383 $1,383 $1,383 $1,383 $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 $400 $983 OK Cancel ($5,900) ($1,617.33)

The price per gallon at which it is worthwhile to buy the hybrid is $6.66.

B10 fac =NPV(10%,C8:H8) +B8 D E F C G H 1 Miles per year 2 Cost of gas 14,000 $6.66 | 3 0 1 2 3 4 5 $5,900 $1,755 $1,755 $1,755 $1,755 4 Year Additional 5 investment Reduced gas 6 expenses Increased 7 ownership cost 8 Cash Flow 10 NPV 11 $1,755 $1,755 $400 ($5,900) $1,355 $0.00! $400 $1,355 $400 $1,355 $400 $1,355 $400 $1,355 $400 $1,355