Question

When you purchase a car, you may consider buying a brand-new car or a used one. A fundamental trade-offin this case is whether you pay repair bills (uncertain at the time you buy the car) or make loan payments that are certain. Consider two cars, a new one that costs $15,000 and a used one with 75,000 miles for $5,500. Let us assume that your current car's value and your available cash amount to $5,500, so you could purchase the used car outright or make a down payment of $5,500 on the new car. Your credit union is willing to give you a five-year, 10% loan on the $9,500 difference if you buy the new car; this loan will require monthly payments of $201.85 per month for 5 years. Maintenance costs are expected to be $100 for the first year and $300 per year for the second and third years. After taking the used car to your mechanic for an evaluation, you learn the following. First, the car needs some minor repairs within the next few months, including a new battery, work on the suspension and steering mechanism, and replacement of the belt that drives the water pump. Your mechanic has estimated that these repairs will cost $150. Considering the amount you drive, the tires will last another year but will have to be replaced next year for about $200. Beyond that, the mechanic warns you that the cooling system (radiator and hoses) may need to be repaired or replaced this year or next and that the brake system may need work. These and other repairs that an older car may require could lead you to pay anywhere from $500 to $2,500 in each of the next 3 years. If you are lucky, the repair bills will be low or will come later. But you could end up paying a lot of money when you least expect it. Draw a decision tree for this problem. To simplify it, look at the situation on a yearly basis for 3 years. If you buy the new car, you can anticipate cash outflows of 12 * $201.85 = $2,422.20 plus maintenance costs. For the used car, some of the repair costs are known (immediate repairs this year, tires next year), but we must model the uncertainty associated with the rest. In addition to the known repairs, assume that in each year there is a 20% chance that these uncertain repairs will be $500, a 20% chance they will be $2,500, and a 60% chance they will be $1,500. (Hint: You need three chance nodes: one for each year!) To even the comparison of the two cars, we must also consider their values after 3 years. If you buy the new car, it will be worth approximately $8,000, and you will still owe $4,374. Thus, its net salvage value will be $3,626. On the other hand, you would own the used car free and clear (assuming you can keep up with the repair bills!), and it would be worth approximately $2,000.

Solve it by using DecisionTools software

Answer 1

Answer:-

Data Inputs
	New Car	Used Car
Cost	$15,000	$5,500
Cash (Trade-in Value)	$5,500	$5,500
Loan
Principal	$9,500	$0
Interest Rate	10%
Monthly Payment	$201.85	$0
Annual Payment	$2,422.20	$0.00
Known Repairs
Year 1	$0	$150
Year 2	$0	$200
Year 3	$0	$0
Annual Maintenance		Value	Probability
Year 1	$100	$500	20%
		$1,500	60%
		$2,500	20%
Year 2	$300	$500	20%
		$1,500	60%
		$2,500	20%
Year 3	$300	$500	20%
		$1,500	60%
		$2,500	20%
Salvage Value	$8,000	$2,000
Liabilities	$4,374	$0
Net Salvage	$3,626	$2,000

Net Cost Calculations sans TVM New Car Used Car Year o Payment $5,500 $5,500 Year 1 Loan $2,422.20 $0.00 Repairs $0 $150 Maintenance $100 $500 Annual Total: $2,522.20 $650.00 Net Cost Calculations incorporating TVM (Time Value of Money) New Car Used Car Year o $5,500 $5,500 Note: Year 1 $2,293 $591 We used a simplifying assumption when Year 2 $2,250 $579 discounting the future costs to today's Year 3 $2,045 $376 dollars. We assumed that all costs occur Net Salvage $2,724 $1,503 at year's end. This is certainly not true as 3-Yr Net Cost ($9,364) ($5,542) the loan payments are made monthly. This assumption can always be removed later if desired. Year 2 Loan $2,422.20 Repairs $0 Maintenance $300 Annual Total: $2,722.20 $0.00 $200 $500 $700.00 The maintenance costs are not known for the used car. As a place holder, we inserted $500 for the cost. There are 2 options for completing the tree. Year 2 Loan $2,422.20 Repairs $0 Maintenance $300 Annual Total: $2,722.20 $0.00 $0 $500 $500.00 Option 1: Create a separate formula for all possible combinations of maintenance costs for the used car. Column H is when the costs equal $500 each year. You would need to create a total of 27 columns (3 costs for each of 3 years). At this point, students have the tools to carry this out. Option 2 relieves the tedium of having to recreate additional formulas, but students will not be introduced to this method until Chapter 4 Net Salvage $3,626 $2,000 3-Yr Net Cost ($9,840.60) ($5,350.00) Option 2: Create what is called a linked tree, where we link the nodes of the tree to the spreadsheet formula. In this case, we need only one formula. The tree will indicate the 27 different cost combinations by the values of its branches. These values are sent to the spreadsheet formula, the formula calculates the annual total cost, and then sends this value to the end nodes. Please see Chapter 4 or Palisade.com for detailed instructions on linked trees.

This is a linked tree. The chance nodes are linked to the corresponding maintenance costs in the formula. For example, Year 1 Maintenance is linked to cell H12 in the Data & Formulas sheet. The 3 different branch values for Year 1 Main. are sent to H12, then the 3-Yr Net Cost is computed (cell H29), and this value is sent to the end node of the tree.

You can test this out by entering different combinations of costs in the formula and seeing resulting 3-Yr Net Cost value reflected in the tree.

FALSE 0.0% New Car $0.00 $9,840.60 Car Decision Problem 3.25 $8,350.00 20.096 0.8% High $500.00 $5,350.00 20.096 Year 3 Maintenance High $500.00 $6,350.00 60.096 2.4% Medium -$6,350.00 $1,500.00 20.096 0.896 Low $2,500.00 $7,350.00 20.09 Year 2 Maintenance High $500.00 $7,350.00 20.096 2.496 High $500.00 $6,350.00 60.096 Year 3 Maintenance Medium $1,500.00 $7,350.00 60.096 7.2% Medium $7,350.00 $1,500.00 20.096 2.4% Low $8,350.00 $2,500.00 20.096 0.996 High $500.00 $7,350.00 20.096 Year 3 Maintenance Low $2,500.00 $8,350.00 2.4% Medium 60.096 $1,500.00 20.096 $8,350.00 0.8% Low $2,500.00 $9,350.00 TRUE Year 1 Maintenance Used Car $0.00 $8,350.00 20.096 2.4% High $500.00 $6,350.00 20.096 Year 3 Maintenance High $500.00 $7,350.00 60.096 7.296 Medium $1,500.00 $7,350.00 20.096 2.4% Low $2,500.00 $8,350.00

60.0% Medium Year 2 Maintenance $8,350.00 $1,500.00 7.2% -$7,350.00 Medium 60.0% $1,500.00 21.6% -$8,350.00 7.2% 20.0% High $500.00 Year 3 Maintenance $8,350.00 60.0% Medium $1,500.00 20.0% Low $2,500.00 20.0% High $500.00 Year 3 Maintenance $9,350.00 60.0% Medium $1,500.00 $9,350.00 2.4% $8,350.00 Low 20.0% $2,500.00 7.2% -$9,350.00 20.0% 2.4% Low $2,500.00 20.0% $500.00 $10,350.00 0.8% High -$7,350.00 20.0% Year 3 Maintenance High $500.00 $8,350.00 60.0% 2.4% Medium $1,500.00 $8,350.00 0.8% 20.0% Low $2,500.00 $9,350.00 20.0% Year 2 Maintenance Low $2,500.00 $9,350.00

20.0% 2.4% High $500.00 -$8,350.00 Medium 60.0% $1,500.00 Year 3 Maintenance $9,350.00 60.0% 7.2% Medium $9,350.00 $1,500.00 20.0% $2,500.00 2.4% Low 20.0% -$10,350.00 0.8% -$9,350.00 High $500.00 20.0% Low Year 3 Maintenance $10,350.00 $2,500.00 60.0% 2.4% Medium $1,500.00 -$10,350.00 20.0% 0.8% Low $2,500.00 $11,350.00

This is a linked tree. The chance nodes are linked to the corresponding maintenance costs in the formula. For example, Year 1 Maintenance is linked to cell H12 in the Data & Formulas sheet. The 3 different branch values for Year 1 Main. are sent to H12, then the 3-Yr Net Cost is computed (cell H29), and this value is sent to the end node of the tree.

You can test this out by entering different combinations of costs in the formula and seeing resulting 3-Yr Net Cost value reflected in the tree.

FALSE 0.096 New Car $0.00 $9,363.62 Car Decision Problem 3.25 Alt -$8,029.30 20.096 0.8% High $500.00 $5,542.45 20.096 Year 3 Maintenance High $500.00 $6,293.76 60.096 2.496 Medium $1,500.00 $6,293.76 20.096 0.896 Low $2,500.00 $7,045.08 20.09 Year 2 Maintenance High $500.00 $7,120.21 20.096 2.496 High $500.00 $6,368.90 60.096 Year 3 Maintenance Medium $1,500.00 $7,120.21 60.096 7.296 Medium $1,500.00 -$7,120.21 20.096 2.496 Low $7,871.53 $2,500.00 20.096 0.896 High $500.00 $7,195.34 20.096 Year 3 Maintenance Low $2,500.00 $7,946.66 60.096 2.4% Medium $1,500.00 $7,946.66 20.096 0.896 Low $2,500.00 $8,697.97 TRUE Year 1 Maintenance Used Car $0.00 $8,029.30 20.096 2.4% High $500.00 $6,451.54 20.096 Year 3 Maintenance High $500.00 $7,202.86 60.096 7.296 Medium $7,202.86 $1,500.00 20.096 $2,500.00 2.496 Low $7,954.17 60.096 Year 2 Maintenance Medium $1,500.00 $8,029.30

20.096 7.296 High $500.00 $7,277.99 60.096 Year 3 Maintenance Medium $1,500.00 $8,029.30 60.096 21.696 Medium $1,500.00 $8,029.30 20.096 7.2% Low $2,500.00 $8,780.62 20.096 2.4% High $500.00 $8,104.43 20.096 Year 3 Maintenance Low $2,500.00 $8,855.75 60.096 7.296 Medium $8,855.75 $1,500.00 20.096 2.4% Low $2,500.00 $9,607.06 20.096 0.896 High $500.00 $7,360.63 20.096 Year 3 Maintenance High $500.00 $8,111.95 60.096 2.496 Medium $8,111.95 $1,500.00 20.096 0.896 Low $2,500.00 $8,863.26 20.096 Year 2 Maintenance Low $2,500.00 $8,938.39 20.096 2.4% High $500.00 $8,187.08 60.096 Year 3 Maintenance Medium $1,500.00 $8,938.39 60.096 7.2% Medium $1,500.00 $8,938.39 20.096 2.496 Low $2,500.00 $9,689.71 20.096 0.896 High $500.00 $9,013.52 20.096 Year 3 Maintenance Low $2,500.00 $9,764.84 60.096 2.4% Medium $1,500.00 $9,764.84 20.096 0.896 Low $2,500.00 $10,516.15

This is also a linked tree. The only difference is that the end nodes are linked to a different formula (one that uses discounting or the time value of money). Spherically, the New Car end node is linked to cell L12 in the Data & Formula sheet and all 27 Used Car end nodes are linked to cell M12.

Again, the different branch values for Year 1 Main. are sent to H12, then year 1 annual total cost is calculated in H13, and finally, this value is discounted in cell M8. Cell M12 subtracts the discounted costs from the discounted salvage value.